Can Flexcel load Excel 5.0/95 *.xls files?

Very old version I know, and I would not be surprised if there is no way of loading these, but I am just hoping... I can load them in Excel 2016/2019 and resave them in a newer format. However, there may be 10000+ files.... to do so is not really feasible manually. If there is a Flexcel solution I will be a very happy person.

Hi,
Yes, FlexCel can read Excel 2 or newer files. It can't save back to Excel 95 (it will only save xlsx or Excel'97 xls), but you can open an Excel'95 file and save it as xls(97) or xlsx without problems.

Thanks Adrian. That is good to know. Unfortunately I now need to ask another question. Is there anything I need to do in order to make it work. I found this because I was getting failure to open files and it was on an Excel 5.0 file. When I opened it in Excel and saved it as Excel 97 then my app could read it.

So are there any flags I need, or is it actually a different reader component? If not, then maybe the format is a red-herring and there is something slightly odd in this file (and many more unfortunately)

If it is an up-to date version of FlexCel there should be no extra steps needed. (support for xls 95 was improved in FlexCel 6.19: What's new in FlexCel Studio for VCL and FireMonkey | FlexCel Studio for VCL and FireMonkey documentation so you need at least that)

This is the code that you would need:

uses VCL.FleXCel.Core, FlexCel.XlsAdapter;
...

var
  xls: TExcelFile;
begin
  xls := TXlsFile.Create(filename_excel95_file.xls', true);
  try
    xls.Save('filename.xlsx');
  finally
    xls.Free;
  end;

If this code isn't working, can you send me a file showing the problem to adrian@tmssoftware.com?
It might be indeed another file format, or maybe there is something in the file that we don't understand. If it is the second option we should be able to fix that.

Finally, one thing you can try is to open the file with a text editor. Do you see any text? (many times people renamed html files as xls so when you click them Excel would open them)

Finally, when you do File-Save as in Excel, is xl95 selected?

Thanks.

OK I need to do a little more digging, and I may need to get permission to send the file.

It is most definitely not an HTML file (I looked at it in my own binary file investigating tool).

When I do the SaveAs in Excel I do see it with xl95 selected. That is how I spotted it as a potential issue.

In the end I will not need to try saving it under any other format as long as I can read it. The idea is that we are pulling all the data from it and moving it to a SQL database. The problem is that we may have 50k+ files to process (hence the desire for the speed of Flexcel... :slight_smile: )

Ok, let me know if you get permission to send the file. FlexCel support for xls95 should be very complete (we've tested some hundreds of xls95 files) but there can always be some thing that we didn't cover, specially because xls95 isn't a file format very used now.

Another thing you can try is to open the file in Excel, save it, but as Excel95 again, and try opening that file with FlexCel. If that doesn't fix it, it is likely a bug in FlexCel and we should fix it. If that fixes it, then it is likely some xls95 file generated by some third party which contains some invalid record. (most of the use right now for opening xls95 files is not for actual xls95 files, but for files made by third party tools which don't know how to produce something more modern)

Well this has got more interesting.

I had tried opening and re-saving as xl95, and it still failed. But I have just seen a message thrown that seems to have shed some light. Though it is still a little strange.

I got a report

image

However this only seems to happen if I try and process an xl95 file. I have tested against many xl97 and xlsx files in the debugger, and never saw this. It is only now I found some xl95 issues that I have pulled those files across for testing on the debug platform. The main release code is clearly just failing to open the file which we log, but not the exception itself.

So when I added that to the uses clause on my main form it all runs properly with no problem and I can process my xl95 files.

I don't really need an answer as to what the difference is between xl95 and xl97 files, but clearly something in the xl95 needs the Render unit used, while xl97 and xlsx files are OK without it. It may jog your memory, or you may chose to just ignore it now.

Thanks for your time.

Ah ok, glad to know it was fixed :slight_smile:

The issue of needed FlexCel.Render is just that column widths are stored in different units as in Excel 97. In Excel 97 column widths are stored as "how many '0's can you fit in the column in the "normal" style font". So if the normal format is arial 12, and you can fit "00000" in the column with Arial 12, the column width is 5. I don't remember right away what is used in Excel 95, but it is different. So in order to convert the column width from Excel 95 to 97, we need to measure strings in a given font, and for this we need the graphics engine.

When opening and saving Excel 97 files nothing is converted, we just read a value and write it back, so no need to use FlexCel.Render.

I am not doing any saving, just reading, but I guess the code does not necessarily know that... :slight_smile:

Yes, that's expected. Imagine you have:
file1: width = 198 myawesomeunits
file2: width = 324 myevenmoreawesomeunits

FlexCel internally uses myevenmoreawesomeunits, because supporting both would be chaos. So when we read file2, we just read 324, and store it in a record. When we read file1, we convert the 198 myawesomeunits into 324 myevenmoreawesomeunits, and store 324 into the record.

A final note: FlexCel.Render might not be needed in every case. FlexCel knows some common options, so f.i it knows that if the font is arial 10, the width of "0" is 7, and it doesn't need to use a graphics engine to measure a "0". But if the main font was changed in the spreadsheet, then we have no recourse than measuring the string with the given font. This might explain why you see the problem in some files and not in others.