I have a couple of pain points with Excel conversions I’m doing at my day job and I can probably get away with a black box Xojo console app using LibXl if it will solve it in those situations I’m encountering.
We’re using Aspose and keep bumping into 2 situations. (1) When reading numbers it reads them as formatted rather than as stored. In other words if 147.32 is the cell’s value but it’s formatted to zero decimal places then we just get 147 unless we open it with Excel and manually change formatting to 2 decimal places or general or currency. (2) in some cases if the Excel workbook is set to prevent editing, all numerics show as zero until we manually turn on editing using the Excel UI. All of this is on Windows.
If LibXl has ways around either or both of these, I might be able to do business with you. I already snuck in a bit of Python code and a Python Excel lib over there because it supports really ancient Excel formats back to Excel 2.0 that we occasionally run into here in the 21st century. We call that as a console app. So I can toss a Xojo console app in as well if I need to. Easier to ask forgiveness than permission kinda thing.
There is a cheaper solution than LibXL and MBS, The Einhugur Excel reader plugin… (You also get the Excel writer plugin and a lot of others)
I just tested it and in both cases and I was able get the full double precision values for the numbers in the cells regardless of formatting and with the entire sheet protected using the simple example project included.
I just took a look at te Demo code for the Einhugur reader. Even though it returns the “raw” numeric value , it does so as a string. Te simple example just reads the first 3 columns of an excel spreadsheet and and displayed them in a listbox,
Teh code Code is:
Dim f as FolderItem
Dim ft as FileType = new FileType()
Dim sheet as ExcelReader.Worksheet
Dim row as ExcelReader.Row
Dim cell as ExcelReader.Cell
ft.Extensions = "xlsx"
ft.Name = "Excel document"
f = GetOpenFolderItem(ft)
if f <> nil then
Dim reader as ExcelReader.Workbook = ExcelReader.Workbook.Open(f)
if reader <> nil then
if reader.WorksheetCount > 0 then
sheet = reader.Worksheet(0) // We are only interested in the first worksheet in this example
for populatedRowNumber as Integer = 0 to sheet.PopulatedRowCount - 1
row = sheet.PopulatedRow(populatedRowNumber)
Listbox1.AddRow("")
for populatedColumnNumber as Integer = 0 to row.PopulatedCellCount - 1
cell = row.PopulatedCell(populatedColumnNumber)
if cell.ColumnNumber <= 3 then
if cell.Type = ExcelReader.Cell.CellType.Date then
Listbox1.Cell(ListBox1.LastIndex,cell.ColumnNumber) = cell.DateValue.ShortDate
else
Listbox1.Cell(ListBox1.LastIndex,cell.ColumnNumber) = cell.Value
end if
end if
next
next
end if
end if
end if
Well that is cool. It seems like this should be the default when it comes to numbers and it’s important to us because these are A/R balances and even though we store them in the DB and report them as int64’s, we often combine certain accounts before putting them into storage and they should sum correctly without drift from rounding errors, with the only rounding happening at the end of the process.
This may give me an opportunity / excuse to work with Xojo in a different way so I am going to figure out first if I can get away with this. Thanks for the tips!
We got a WriteStringAsNumber function for XLSheetMBS class, so you can pass a number value as string and format it the way you need. This avoids rounding errors with using doubles:
Is this function for writing into a sheet or getting a number out?
My problem isn’t doubles and their inability to precisely represent certain numbers, but that Aspose converts values as displayed, not as stored. So if the value is 456.78, and formatting is set to zero decimal places, you get 457. There is no way to get the underlying value, only the value as it would appear if you opened it in Excel and viewed it with whatever formatting is in place. This seems insane to me but maybe it’s something in the design of Excel that makes it difficult for libs / plugins to circumvent, IDK.
I should add that my application is purely reading spreadsheets to extract numbers from them to go into a database. I’m not creating spreadsheets at all. I see that WriteStringAsNumber() is for putting values into cells, so I probably didn’t adequately define my use case. These spreadsheets come from hundreds of sources, all sorts of versions, .xls and .xlsx, some created in Excel and some created in other 3rd party libraries. But the consistent thing is this favoring of formatting in front of the actual value.
The other thing is that sometimes (not always) if the sheet is write protected, when you open it in excel all the numeric fields show zero until you turn off write protection, then the numbers show their true values. I would like to be able to read these sheets automatically and get the underlying values rather than the displayed values.
And that would be one reason the Einhugur classes alone are not sufficient for your stated purpose. I have both Einhugur and MBS and LibXL, but only a macOS LibXL license (so far). I highly recommend both Einhugur and MBS Complete in any serious toolbox. For the price they are both a no-brainer in my book. LibXL is certainly more expensive when compared at a price per function basis, but still quite possibly very justifiable when it provides features you can’t easily / quickly do otherwise. (And that is why I license LibXL too…)
The Einhugur classes will ONLY read *.xlsx files, not prior Excel formats. I actually don’t know how far back LibXL will go, but it easily handles Excel 4 files in *.xls format. The xlsx format is actually just XML compressed in a zip compatible format but with a xlsx extension. You could decompress the file contents and navigate the XML yourself – the format is documented – but the Einhugur classes will do that for you much cheaper if “time is money”.
By the same token, prior Excel formats are documented too. But I still license LibXL because I don’t want to re-invent those wheels. Same reason I license both Einhugur, MBS Complete, and some other extensions. Even when you license because of an immediate need, you find over time that they are useful for so many other scenarios as well.
Yes Excel 4 is sufficient. I use a python lib when I occasionally need to reach all the way back to Excel 2.
Come to it, I suppose I should go back and review the docs on that python lib to see if it can solve this issue for me.
I would even get quite a bit of benefit from something limited to .xlsx as that is definitely most of what is coming in with these 2 problems I described.
Well if the volume is low enough, automate the *.xlsx and for those you can’t automate, open in Excel and re-save as *.xlsx or find / create a tool that can do that for you. LibXL really shines when you need to do more than just simple read / write of data. The Einhugur classes do that quite nicely for xlsx.
LibXL will get you compatibility with much more than *.xlsx, plus LOTS of quite sophisticated ability. Which when you need it, may well be worth the price of admission. One final point, the LibXL license is not really required to be paid annually. That would be true if you wanted to keep getting access to new versions. But if you license once, I believe you can continue to use things released during your license period.
When you need new features or things break because MS changes the format and a new LibXL is needed to properly read things, you would need to license again to get newer versions.