Export data from caché (v 2012) to Excel, used validation data like dropdown list

Question

Question

Youness ARRAF · Jan 6, 2020

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

Discussion (5)0

Log in or sign up to continue

Youness ARRAF · Jan 6, 2020

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 :)

0 0

score 0 · Answer 1 · 2020-01-06T08:47:01-05:00

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.

score 0 · Answer 2 · 2020-01-06T09:41:03-05:00

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 ?

score 0 · Answer 3 · 2020-01-06T09:48:18-05:00

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

score 0 · Answer 4 · 2020-01-06T09:58:19-05:00

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.