Problems opening a SpreadsheetML file (not created using Excel)

Hi, I am demoing Aspose Cells and I am having trouble getting our SpreadsheetML XML file to open correctly. I am trying to take a legacy SpreadsheetML file and add charts to it. I
have already accomplished this using Excel automation, however it is too
slow/error prone for my needs so I am looking at other options.

I am using:
LoadOptions loadOptions = new LoadOptions(LoadFormat.SpreadsheetML);
Workbook workbook = new Workbook(sourceFile, loadOptions);

This opens the file correctly only after I have manually opened the file in Excel and re-saved it in the XML Spreadsheet 2003 format. I did this as a test to make sure I wasn’t missing something that was required to open the file. Obviously this is not a acceptable solution as the process of opening this file needs to be automated.

Errors that I am getting:
Error 1
Error message: "Line 3: "
Inner exception: null
This is caused by my default namespace being xmlns=“urn:schemas-microsoft-com:office:Spreadsheet” instead of xmlns=“urn:schemas-microsoft-com:office:spreadsheet”. Changing the ‘s’ in Spreadsheet to a lower case ‘s’ solves this problem.

Error 2
Error message: Line 693: A worksheet must have a name.
Inner exception: null
Line 693:
This line originally had namespaces included in it. The copy that was saved by Excel (that works) however had all of the namespaces defined at the top of the file so I tried that. It doesn’t work either way (namespaces on the Worksheet element or on the Workbook element). The worksheet has a name (and other names that I have tried don’t work either) so I have no idea what I could do to fix this here.

Any help would be appreciated.

Hi,


Well, since you are opening a legacy type xml file that might not follow MS Excel’s Spreadsheet ML standards, anyways, could you try our latest version/fix: Please download and try the latest fix: Aspose.Cells for .NET v7.0.4.5

If you still find any issue, kindly attach your template file here, we will check your issue and find out if we can support or figure it out.

Thank you.

I will try the new version. I forgot to mention in my previous post but I am using 7.0.4.0 evaluation edition. I will need to get approval before I can post my XML source file so that may take some time.

—EDIT—
I just checked the new version and all of the problems that I am having exist in that version too.
—END EDIT—

—EDIT 2—
Error 1/2
I have been able to modify my namespaces to use a lower case s, and the file is able to be opened by Excel and Aspose Cells now so this is no longer an issue for me. I still think it should be addressed in some manner though, even if it is just a better error message.
—END EDIT 2—

Also, I have partially debugged my problems. My second problem was actually related to my first in that I had two references to the urn:schemas-microsoft-com:office:Spreadsheet namespace, one as the default and one named ss. Changing both of them to use urn:schemas-microsoft-com:office:spreadsheet cleared up a good number of my problems. I know I can change the ss namespace in my code that is generating the XML file, I am not sure about the default namespace though. Does it make sense for the application to fail because of a capitalization error in the namespace?

Also, I have found three new issues now that I can get the file to open and be re-saved. I am just using the code that I posted earlier to open the file and using workbook.Save(".xlsx", SaveFormat.Auto) to test with.

Issue 3
In some instances a cell that holds “0” is removed and in the resulting xlsx file the cell is empty. This causes #NUM! errors in some of my formulas.
This snippet prints as a zero when the file is opened in Excel but as a blank cell when opened/re-saved with Aspose Cells.




—Update—
When the Data element is in this format I get a blank cell. I do not know if this the intended behavior, but my original data is not in this format. The format of my original data is

and this does give me a 0 in the cell, which is what I require. The shortened version was due to a tool that I was using to make my XML file readable (it is very hard to read without reformatting it). These should be equivalent, so the fact that they print out two different things is cause for some concern unless this is a documented behavior.
—END Update—

Issue 4
My named ranges are being read incorrectly. A sample from my XML file is (located under /Workbook/Names, not sure if the names defined in each Worksheet are having problems):

however, this name and all of my other names are missing the first character of the formula that they refer to in the resulting xlsx file, so the example above refers to
ummary!R2C4:R7C4
which in turn causes all of my aggregation formulas to fail.
I have been able to temporarily work around this by just doing a manual rename to make the named ranges have an extra character at the beginning of their RefersTo attribute.

— Update —
I looked at how Excel makes NamedRange’s and I noticed that they have an extra equals sign (that isn’t necessary) after refers to so an Excel made NamedRange looks like

This allows named ranges to be used correctly by Aspose Cells and Excel. I will update my code accordingly.
—END Update—

Issue 5
I believe that new lines are being parsed for Data elements. I have the following in my XML file

Concept:


and the result in the xlsx file is “Concept:” and if I try to edit that cell, when I click off of the cell (without making changes) it is transformed into Concept: and then a new-line character.
—Update—
This is definitely caused by the new-line character in the XML. I formatted the XML with a tool that removed the new-lines and the went away. I just noticed that the new-line character is being inserted in the original XML by my code on accident and I will remove it.
—END Update—

Thanks.

I have been able to solve all of the issues that I have listed above.

I listed some concerns in my updates/edits in my previous post, so if anyone could answer those I would appreciate it.

Thanks.

Hi,

1)Issue 1, 2:

We will look into it.

2)Issue 3:

We will fix it.

3)Issue 4
If the file saved by MS Excel, the referto should start with "=". So we removed the first char.

We will fix this issue.

4)Issue 5
It's better that you can convert new_line to as MS Excel.

And we will remove '\r' when we read the data from xml file to avoid .

Hi,

These issues had been logged as CELLSNET-40268.

We have fixed these issues. Please download: Aspose.Cells for .NET v7.0.4.6


The issues you have found earlier (filed as CELLSNET-40268) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.