Export data from caché (v 2012) to Excel, used validation data like dropdown list
I use the 2012 version of the caché, and I need to export data in an Excel file, the point that seems to complicate is to use the dropdown list in the cell and then trigger a validation if I exceed an interval of two values.
Help me, by your seggestions, solutions
Thank you
Hi Youness,
how are you exporting the data to excel : as a .csv file ?
I am not sure if this is your problem, but when exporting data (numbers) to excel, you need to be careful on the decimal character you are using : if i export numbers with a decimal point, and my locale is set on comma as a decimal , the numbers are treated as strings, and no validation works.
Thank you Danny for your replay,
i export the data to excel using csv file, this work fine,
but some data i neet to display its inside a dropdown list , how i can do this,
there's some classes in caché to handle the data in sheet and cells from Excel file ?
Hi Youness,
There is no direct way from within Caché to modify excel sheets.
I use .csv files, or generate a .txt file, and call an excel file that runs a macro to read the data, and set all the stuff using VB code in the macro.
You can look at this article that talks about different options :
https://community.intersystems.com/post/use-cache-write-excel-files
Hi Danny,
Great, Thank you Danny, i think it's the great idea,
I ll try to use VBA code :)
i will come back to you Danny :)
For dropdowns I think the only option is to use Apache POI to generate XLSX. Here's Apache POI JGW wrapper and ObjectScript API but you'll need to extend it.
@Danny Wijnschenk linked the post with some discussion on different options, check it out.