Laurence:
I have contacted our other vendor and I am awaiting more details on the incompatibility that is being experienced when trying to load a file that was saved from Aspose.Cells. (Something a little more informative than "Invalid File".)
In the meantime, I have downloaded an evaluation copy of another conponent and it also cannot load an Excel file that has been saved from Aspose.Cells. Again, this component claims compatibility with Excel 97 - 2003. In this case, the error is simply a "corrupt stream" error.
I understand that neither of these errors are particularly helpful but I wanted to keep you updated on my progress in case it sparked a thought on why I am having problems loading the Aspose.Cells output in other tools.
Thanks,
Tom Murphy
[split off from Thread 68513 - Calculation Issues (pertainent history follows)]
>Hi Tom,
>Aspose.Cells is totally compatible with Excel97-2003. We claims to open any MS Excel97-
>Excel2003 created files without any problem. If there is any problem, we will solve it ASAP. And
>we claims that MS Excel will open our created files without any problem.
>For other 3rd party components, could you please contact them for the cause of problem? That will
>help us to know if it's a problem of Aspose.Cells or theirs. Thank you.
>>Laurence:
>>Would this have any effect on the format of the resulting file? I have a legacy application (using a
>>different component that claims compatibility with Excel 97-2002 format) that throws an "Invalid
>>File" error when trying to load a file modified by Aspose.Cells.
>>If I open the file in Excel first and save it, the resulting file loads fine.
>>I am really stumped on where to look for the problem so any input would be very helpful.
>>Tom Murphy
Laurence:
I have heard back from the other vendor and here is the information I received:
The corruptfile → Invalid File Error appears to be resulting from sheet 0 row 17 col 3 → ‘Questionnaire’ D18 returning an invalid formula length of 65280 bytes.
There may be other issues but this is the first we encounter.
I am still at a loss as to how this has occurred. Could you verify whether this is an issue being generated as part of your save process?
Thanks,
Tom Murphy
Hi Tom,
Well, I don't think that your underlying issue has any involvement with Aspose.Cells' save process. Any how, thanks for your information, we will look into it and get back to you soon.
Thank you.
Hi Tom,
The corruptfile -> Invalid File Error appears to be resulting from sheet 0 row 17 col 3 -> ‘Questionnaire’ D18 returning an invalid formula length of 65280 bytes.
It seems that they cannot correctly parse formula in 'Questionnaire' worksheet D18 cell. But why MS Excel can recognize it correctly?
Could you please zip and post this file here?
Laurence:
Attached is a copy of the XLS that was saved using Aspose.Cells. I have tried to load this into our other component as well as the evaluation component I downloaded to test. In both cases, loading this file failed.
Tom Murphy
Laurence:
Please let me know if there is any additional information I can provide to help figure out this issue. I am at a standstill at this point in trying to get things working and I need to figure this issue out before we can deploy our application.
Thanks,
Tom Murphy
Hi Tom,
What's your 3rd party tool?
The compatibility problem is caused by this tool, not Aspose.Cells.
Aspose promises to:
1. Be able to open any Excel97-Excel2003 file which can be successfully opened by MS Excel.
2. Create file that can be successfully opened in MS Excel.
I tried your file and it works fine with MS Excel. And "Questionair" D18 correctly shows the formula.
So to solve it, you have to contact with that vendor for more information.
Laurence:
The third party tool we need to maintain compatibility with is Actuate's eSpreadsheet. The file I forwarded to you earlier was saved from Aspose.Cells and does not load in Actuate's eSpreadsheet, throwing the error described earlier.
In my efforts to determine where the issue resided, I also downloaded an evaluation copy of SpreadsheetGear's .NET solution as it seems to be a stable and respected component as well. I wrote a quick test harness and tried to load the Aspose.Cells generated file. Unfortunately, this test also failed, leading me to believe that the problem is on the generation side (Aspose.Cells) and not the load side (Actuate and SpreadsheetGear).
Microsoft Excel (2002 sp3) does indeed load the file. However, when attempting to close the file, I receive a warning stating that "Microsoft Excel recalculates formula when opening files last saved by an earlier version of Excel." and asks me to save. While I know, from previous conversations, that Aspose.Cells does not save recalculations, I was more interested in the fact that Excel sees the file as being generated "by an earlier version of Excel". This has also further led me to believe that the problem might lie on the Aspose.Cells side.
As I have said, I am at a loss in trying to resolve this issue, as you tell me to talk to Actuate and Actuate tells me to talk to you.
Tom Murphy
Hi Tom,
I'd like to solve this problem from my side but I also cannot make any further steps because:
We have to use MS Excel as a test platform. So if Actuate creates a file that can be recognize by MS Excel but cannot be opened by Aspose.Cells, it's sure a problem of Aspose.Cells, not Actuate.
I am also eager to figure out this problem but I don't have any clues because I don't know their implementation details and I don't find the problem in MS Excel. So could you contact them for why this error happens? If I know the root of problem, I will try my best to solve it from my side.
We create the file according to http://sc.openoffice.org/excelfileformat.pdf .
For this "Microsoft Excel recalculates formula when opening files last saved by an earlier version of Excel." warning message, I don't think it caused this problem.
And do all Aspose created files fail with your 3rd party tools?
Laurence:
I have contacted Actuate regarding the issue and am trying to work with them on it as well. The latest information I received from them is the more detailed information that they are getting a forumla length of 65280 bytes for cell QuestionnaireD18.
I am forwarding over the link you provided for the Excel File Format specification in hopes that this will help on their side. They are currently looking to what Excel is doing to "correct the mistake" when the file is opened in Excel in hopes that this will shed some light on the problem.
We have four models (spreadsheets) that we are using for this project. I have loaded each of them up in our tool, changed values in each and saved the results to a file. In three of the four cases, the file loads fine in the Actuate tool. The fourth one is the one that fails and is the one I forwarded in a previous e-mail.
I understand that you cannot base your development efforts on Actuate and must base it on Excel and I truly appreciate your efforts to this point.
Tom Murphy
Hi Tom,
Thanks for your information.
It's impossible that cell QuestionnaireD18 contains a formula with length of 65280 bytes. If that's true, Excel will crash or discard this cell. I will keep track on this issue but I think there may be some misinterpration when they reading this cell.
If finanlly we find this is really a problem caused by Aspose.Cells, sure we will update our product.
Laurence:
Further technical information from Actuate:
It’s more complicated than simply how many chars.
We find the issue when reading the xls record.
This particular record (406 Formula Cell Record) is misrepresenting its length to be FF00.
Clearly from looking at the cell it is not truly this long.
Excel must do some cleanup of these bad declarations (probably for the back and forward compatibility of their own product).
eSS currently expects that the record types will be properly declared.
I have Engineering debugging the files again to try and collect as much info as possible about where this breaks and how the record should be set up.
I will pass along any additional information that I receive. Hope this helps shed some light.
Tom Murphy
Hi Tom,
Following is some of my information that you can provide to Actuate:
1. Formula record is 0x0006, not 0x0406 in BIFF8. Only BIFF4 use 0x0406.
2. From your previous information, cell D18 in "Questionaire" fails to be opened in Actuate. This is the data in Aspose.Cells created file:
06 00 1b 00 11 00 03 00 2d 00 00 00 00 00 00 00 44 40 02 00 12 00 03 ff 05 00 01 11 00 03 00
I don't find any problem in it. Could you ask them what caused their problem? Thank you.
Laurence:
Here is Actuate’s latest information:
Here is exactly what Aspose should need to look at and resolve.
It is actually the SHRFMLA (Shared Formula : 0x00BC) record. Both Aspose and Excel give the length of the record to be 27 which makes 17 the length of the parsed expression (27 - 2 - 2 - 1 -1 -2 -2 = 17). Below is the description of the SHRFMLA record taken from the EXCEL SDK documentation.
Size(bytes)
Name
Values
2
First Row
2
Last Row
1
First Column
1
Last Column
2
(Reserved)
Aspose
Excel
2
Length of parsed expression
65280
17
N
Parsed expression
Let me know if they need more information.
Please let me know if you need any additional information and I will work to retrieve it.
Tom Murphy
Hi Tom,
The following is the record data:
bc 04 1b 00 11 00 16 00 03 03 00 06 11 00 4c 00 00 ff c0 44 0d 00 04 00 05 1e 00 00 41 1b 00
The formula length is also 17 in Aspose.
So the problem may be:
1. You give me the wrong file.
2. Or Actuate doesn't parse it correctly.
If it's not the case, could you please ask Acutuate to give me the whole data of this SHRFMLA (Shared Formula : 0x00BC) record? Thank you very much.
Laurence:
I will forward your request on to Actuate.
In the meantime, I have included a copy of the spreadsheet that I have just verified is failing the load process, to make sure you and I are on the same page.
Thanks,
Tom Murphy
Hi Tom,
Thank you.
By the way, do you use a template file to create this file? If yes, does your template file work fine with Actuate?
Laurence:
We do not use a template file to create this file if you mean in the sense of a *.XLT.
We do however have XLS files that function as templates within the context of our application. We make a copy of these "templates" and use the copies as active workbooks. These "templates" are created in Excel and open correctly with both components. The active workbooks open correctly in both components prior to any cells being modified. (The copy is done in the database and not using either component.)
We have a total of 4 of these "templates" for our current project. They all open fine with both components. Three of the four open fine in both components after a cell is changed and the file is saved using Aspose.Cells. The fourth is the one that causes the issue but only after having been modified by Aspose.Cells.
Making a change with the Actuate component does not cause this issue as the resulting file can be loaded in both components.
Please let me know if you have any additional questions.
Tom Murphy
Hi Tom,
The fourth is the one that causes the issue but only after having been modified by Aspose.Cells.
Could you please post this template file here? And please post your code that change the data. Thank you very much.
Laurence:
Attached is a copy of the "template" that we used as a starting point for the spreadsheet that failed to load.
All I did was run it through the following code:
Aspose.Cells.License cellsLicense = new Aspose.Cells.License();
cellsLicense.SetLicense("Aspose.Custom.lic");
Workbook workbook = new Workbook();
workbook.Open("C:\\IOM.xls");
Worksheet questionnaire = workbook.Worksheets["Questionnaire"];
questionnaire.Cells["D15"].PutValue(24);
workbook.CalculateFormula(true);
workbook.Save("C:\\IOM ASPOSE.xls");
The resulting file is the one I send to you earlier today that failed to load.
Please let me know if you need anything else.
Tom Murphy