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
![Synergex](/resource/1715198230000/assets/assets/images/SYNERGEX_Icon_WEB_RGB.png)
It's supported in both .NET Framework and also .NET 6, which is now supported at runtime on Linux in Synergy 12.
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.
![Synergex](/resource/1715198230000/assets/assets/images/SYNERGEX_Icon_WEB_RGB.png)
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.