Go Back

Code to read/write xlsx files

Hi,

Does anybody have any code ideas to read/write xlsx files?  I generally convert to csv and then iterate through the commas, but I have been presented with a file that has an unusually large number of columns.  

Thank you!

Michael
 

3 Answers
0   | Posted by Michael Mariani to Synergy DBL on 4/1/2024 9:58 PM
Steve Ives
If you're on Windows there seem to be several .NET libraries that you could use to interact with Excel files. This seems to be one of the more popular ones: https://www.nuget.org/packages/Microsoft.Office.Interop.Excel

It's supported in both .NET Framework and also .NET 6, which is now supported at runtime on Linux in Synergy 12.

4/4/2024 9:19 PM   0  
Chris Blundell
I created a class on OpenVMS to deal with csv files, I can add rows and columns and set values etc. I added a method to export the CSV as an XML format Excel document. It makes for large file's but you can do just about anything including functions etc.When I get it in email I open it and save it as a binary Excel doc making it about 10% of the size. For large files I zip them up before attaching them to email. I can add styles and apply them dynamically as well. I normally create the csv and then modify it in excel to see what I need it to look like, saving it back out as XML and then making a style that matches. Applying styles to cells is pretty easy something like this...

                foreach fldName in mColumnNames
                begin
                    using fldName.type select
                    ('String'),
                    begin
                        rowstring += '<Cell ss:StyleID="s99"><Data ss:Type="String">' + encodestring(row.item(fldName.Name).value) + '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    ('Currency'),
                    begin
                        rowstring += '<Cell ss:StyleID="s67"><Data ss:Type="Number">' + encodestring(row.item(fldName.Name).value) + '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    ('Int'),
                    begin
                        rowstring += '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + encodestring(row.item(fldName.Name).value) + '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    ('Number'),
                    begin
                        rowstring += '<Cell ss:StyleID="s66"><Data ss:Type="Number">' + encodestring(row.item(fldName.Name).value) + '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    ('DateTime'),
                    begin
                        rowstring += '<Cell ss:StyleID="s65"><Data ss:Type="DateTime">' + encodestring(row.item(fldName.Name).value) + '</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    ('Formula'),
                    begin
                        rowstring += '<Cell ss:StyleID="s69" ss:Formula="' + row.item(fldName.Name).value + '"><Data ss:Type="Number">0</Data><NamedCell ss:Name="_FilterDatabase"/></Cell>' + char(32) + char(10)
                    end
                    endusing
                end


Chris.

4/5/2024 11:50 AM   0  
Kish Baley

Hi Michael,

I believe you are trying to parse a CSV with many columns. Did you find a solution?

If not, we wrote a DBL class that contains several methods for working with CSV files. Most of the methods are for creating CSV rows, but one of the methods can be used to extract fields from the CSV row one column at a time. You can invoke it in one line, and it returns the leftmost field:

fld = csv.ExtractLeftField(currentCSVRow)

The method updates currentCSVRow so the after the call leftmost field is removed and all the other fields then shift left by one column. So the next time you execute the same call, the variable fld will now contain the value in the second column, etc. It works really well in a FOR loop.

I've tested it in Windows and VMS, but it should work in Linux too. Let me know if you are interested and I can share the code.

4/11/2024 10:51 PM   0  
Please log in to comment or answer this question.