Loading an Excel file Worbook constructor throws 'Invalid cell name' in .NET

Aspose.Cells Product Family
1

Hello,

I’m trying to process some Excel files (.xlsx) using Aspose Cell 6.0.0 for .NET.

The Workbook constructor throws an exception with the file I’m using. I cannot figure out what’s wrong with that file. Excel 2007 opens it
without complaining.

Could someone help me?

Thank in advance!

Source code:
public static void OpenWorkbook(String file)
{
…
// Open Excel workbook
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
Aspose.Cells.Workbook wb = new Workbook(file, loadOptions);
…

Stack trace:
Unhandled Exception: Aspose.Cells.CellsException: Invalid cell name
at Aspose.Cells.CellsHelper.CellNameToIndex(String cellName, Int32& row, Int3
2& column)
at ?.?.?(XmlTextReader ?, Row ?, Int32 ?, Int32 ?)
at ?.?.?(XmlTextReader ?, Int32 ?)
at ?.?.?(XmlTextReader ?)
at ?.?.?(XmlTextReader ?)
at ?.?.?(? ?, String ?)
at ?.?.?()
at ?.?.?()
at Aspose.Cells.Workbook.?(Stream ?, LoadOptions ?)
at Aspose.Cells.Workbook.?(String ?, LoadOptions ?)
at Aspose.Cells.Workbook…ctor(String file, LoadOptions loadOptions)

Sample file:
See attachment.

2

Hi,

Thanks for the sample file and code.

After an initial test, I am able to reproduce the issue as you have mentioned. I have logged a ticket with an id: CELLSNET-28521 for your issue. We will fix it soon.

thank you.

3

Hi,


We have handled the exception mentioned under Issue ID CELLSNET-28521 in our latest fix version of Aspose.Cells for .NET v6.0.0.1. Please test your scenario with this latest assembly and let us know of your feedback.

Thank you.

4

Hi,

We tested the new version, and effectively when we rewrite the file in 2003 format, we can read the file. But when we rewrite the file in 2010 format, the file is not well formatted against the OpenXML rules, there is still a problem with some cell names.

But for us this solutions suites.

Greetings,


Christ

For ELIA.

5

Hi,


Thanks for your feedback.
If you share your problematic output file and identify the Cells that you have mentioned in your last post. We can look into it to solve this issue as well.
6

Hi Babar,

Thanks for your support.

We (Elia) really appreciate the responsiveness of your support. Thanks to your fix, we now have a workaround for processing those damned Excel files. As mentioned by Christ, the OLE DB diver still complains when we save using the XSLX format. But using XLS will do the job.

Thanks again.

Bruno Biebuyck (for Elia).

7

Hi,

I have tested your file with the latest version Aspose.Cells for .NET v6.0.0.1. My approach was to load your source xlsx file and convert it into the xls file then load the xls file again to see if there any exception occurs. I found, no exception occurs. Below is my code.

C#


string path = @“F:\Shak-Data-RW\Downloads\auction_spot_france_2011.xlsx”;


//Load the workbook xlsx file

Workbook workbook = new Workbook(path);


//But save it in xls format

workbook.Save(path + “.out.xls”, SaveFormat.Excel97To2003);


//Now load the xls file again to see if converted excel loads fine.

workbook = new Workbook(path + “.out.xls”);

8

Hi,

I Will give you additional information, so that you understand our way of working. We receive files from third party's. We need to import those excel files into a datawarehouse. This import happens through SSIS/DTS packages, and the download of those files happens through an automated process.

We stated two problems, the first one is, when we opened a file (@ the time before the patch), we received an exception. We implemented your patch and we are able to read the file "xlsx" and write it back without any action on it as a xls file. This is fine by us. When we open the file and we rewrite the file as an xslx, the file is not readeable within our SSIS/DTS.

Some times we receive also xls files (not xlsx files) who have the same issues as the xlsx. And a rewrite of that file didn't function. To solve our issue, we implemented the following code.

Console.WriteLine("Reading Excel file : " + xlsFromFile);

var fileStream = new FileStream(xlsFromFile, FileMode.Open, FileAccess.Read);
var asposeCells = new Aspose.Cells.Workbook(xlsFromFile);
var asposeCpy = new Aspose.Cells.Workbook();

foreach (Aspose.Cells.Worksheet ws in asposeCells.Worksheets)
{
asposeCpy.Worksheets.Add(ws.Name);
asposeCpy.Worksheets[ws.Name].Copy(ws);
}

Console.WriteLine("Write file to : " + xlsToFile);
fileStream = new FileStream(xlsToFile, FileMode.Create, FileAccess.Write);
asposeCpy.Save(fileStream, SaveFormat.Excel97To2003);
fileStream.Close();

By copying the file into a new workbook we are able to read the file with the following T-SQL line:

select * from openrowset('Microsoft.ACE.OLEDB.12.0',
* 'Excel 12.0;HDR=YES;Database=C:\_dev\ITeam\XlsRewrite\XlsRewrite\Data\auction_spot_germany_austria_2009.xlsxaspose.xls.Copy.xls',
'select * from [Prices$]')

Hoping that this helps you to find out what happens, and how we can use this without this work arround.

Greetings,

Christ Losfeld.

For Elia.

9

Hi,

When you save your workbook into xlsx format, then please follow this approach. You need to set ExportCellName property to true.

C#


string path = @“d:\test\issue\yourFile.xlsx”;


Workbook wb = new Workbook(path);

OoxmlSaveOptions xlsxSaveOptions = new OoxmlSaveOptions(SaveFormat.Xlsx);

xlsxSaveOptions.ExportCellName = true; //Set export cell name to true to get rid of your problem

wb.Save(path + “.out.xlsx”, xlsxSaveOptions);

10

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

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