Issue in saving Excel in XLSX format

Aspose.Cells Product Family
1

I have a module where I write data into an Excel document and upload it on a server. It works when I create an excel in .xls format. But when I save it in .xlsx format, MS Excel complains about the content being unreadable. Although it still repairs the file and opens up, it looks like there is some issue in saving to the stream or reading from stream.


Here is the relevant part of the code:

//While opening
workbook = new Workbook(FileFormatType.Xlsx);

//Do some operations

//Save the excel
MemoryStream ms = new MemoryStream();
workbook.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));
///ms.Seek(0, SeekOrigin.Begin);
doc.Content = ms;


I tried the other overloads in ‘Save’ method, but none worked. I went through the existing posts, and one of them mentions adding ‘ms.Seek(0, SeekOrigin.Begin);’, but that did not help either. Not sure if I am missing something. Any help would be highly appreciated.

- Anup
2

Hi,

Thanks for using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.0.5

There are many ways of opening and saving the workbooks in different formats. Please refer to these articles for your complete reference.

  1. Opening Files
  2. Saving Files

Also, for your help, I have written the following code. Please copy and use it at your end. The code creates a workbook object and then write a Hello Aspose message in cell A1. Then it saves the workbook in XLS and XLSX formats.

Please also see the generated output xls and xlsx files as well as screenshot for your reference.

C#

//Create a workbook

Workbook workbook = new Workbook();


//Access the first worksheet

Worksheet worksheet = workbook.Worksheets[0];


//Write message in cell A1

Cell a1 = worksheet.Cells[“A1”];

a1.PutValue(“Hello Aspose!”);


//Autofit the columns.

worksheet.AutoFitColumns();


//Save the workbook in xls format

workbook.Save(“output.xls”, SaveFormat.Excel97To2003);


//Save the workbook in xlsx format

workbook.Save(“output.xlsx”, SaveFormat.Xlsx);


Screenshot:
3

Thanks for the quick response.


I have been using the code for creation and saving the excel. It works perfectly fine with 97-2003 excel format and .xls extension. But when I try to save it in .xlsx format, the saved file is somehow corrupt. Excel repairs it and I am still able to see that file. Is it something to do with creating an instance with fileformat.xlsx? (workbook = new Workbook(FileFormatType.Xlsx);). Do we even need it or can we just create an instance with the default constructor (workbook = new Workbook();)?

The only difference between the code that generates xls and the modified code for generating xlsx is as follows:
1. //For creating the workbook instance
workbook = new Workbook(); - for xls
workbook = new Workbook(FileFormatType.Xlsx); - for xlsx

2. While saving:
//For xlsx
MemoryStream ms = new MemoryStream();
workbook.Save(ms, new OoxmlSaveOptions(SaveFormat.Xlsx));

For xls:
MemoryStream ms = new MemoryStream();
ms = workbook.SaveToStream();

Did I miss anything in the above for xlsx?

And by the way, I am using Aspose Cells version 7.2.2.0.

Regards,
Anup




4

Hi,

Well, I have checked your code and it is quite ok, but you do not need to differentiate between xls and xlsx except that

For Xlsx, please use OoxmlSaveOptions and for Xls, please use XlsSaveOptions. See the following codes.

C#


//For Xlsx

OoxmlSaveOptions opts1 = new OoxmlSaveOptions(SaveFormat.Xlsx);

workbook.Save(“output.xlsx”, opts1);


//For Xls

XlsSaveOptions opts2 = new XlsSaveOptions(SaveFormat.Excel97To2003);

workbook.Save(“output.xls”, opts2);


Please also do not use Workbook.SaveToStream but use this code instead. SaveToStream will not work for XLSX format.

C#


MemoryStream ms1 = new MemoryStream();

workbook.Save(ms1, SaveFormat.Xlsx);


//Or

MemoryStream ms2 = new MemoryStream();

workbook.Save(ms2, SaveFormat.Excel97To2003);

If you want to convert your workbook into byte array, please use this code.

C#
MemoryStream ms1 = new MemoryStream();
workbook.Save(ms1, SaveFormat.Xlsx);

byte[] bytes = ms1.ToArray();


5

Hi,


Apparently you did perfectly right for creating and saving for xls/xlsx file(s), although it is not necessary all the time to use: “workbook = new Workbook(FileFormatType.Xlsx); - for xlsx” except for some exceptional cases.

Please use the latest fix v7.3.0.5 that is shared by Shakeel Faiz. Also, he shared some useful code segments that work absolutely fine. If you still find any issue, please use v7.3.0.5 to create a sample application, zip it and post it here with all the files, we will run your project on our end and let you know the result. Also if there is something wrong in your example code, we will figure it out etc.

Thank you.
6

I think I narrowed down the problem. It looks like the issue is using ‘Range’ to populate data and saving in .Xlsx format. When I removed that part, it works fine.


The following saves excel in corrupt format: (While opening .Xlsx, it shows a ‘content unreadable’ exception)
Range range = wsCoverSheet.Cells.CreateRange(0, 0, 1, 1);
//Setting the name of the named range
range.Name = “Cover Sheet”;
range[0, 0].PutValue(“Blah”);

The following works:
Worksheet wsCoverSheet = workbook.Worksheets[0];
Cell a1 = wsCoverSheet.Cells[“A1”];
a1.PutValue(“Hello Aspose!”);

I believe you can easily reproduce this issue by doing the following:
1. Create an excel, use Range to write data (Put value)
2. Save the workbook in .Xlsx format

Please let me know if I am missing anything or I should get rid of Range in my code.


7

Hi,

Thanks for your feedback.

I think, you rightly narrowed down the problem.Please get rid of range code and use the method mentioned by us.

We will also look into range related code and resolve the issue if we found any error.

Please also let us know if you need any further help, we will be glad to assist you asap.

8

I verified that using Range and saving in Xlsx format corrupts the excel. Would you log a ticket and try to fix it in the next Aspose Cells version?

9

Hi,

Thanks for your posting and considering Aspose.Cells.

I was able to replicate your mentioned issue using the following code with the latest version:
Aspose.Cells
for .NET v7.3.0.5

I have logged this issue in our database. We will look into it and fix the problems and get back to you asap.

This issue has been logged as CELLSNET-40966.

I have also attached the generated output.xlsx file and the screenshot showing the error.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Range range = worksheet.Cells.CreateRange(0, 0, 1, 1);

//Setting the name of the named range

range.Name = “Cover Sheet”;


range[0, 0].PutValue(“Blah”);


workbook.Save(“output.xlsx”);


Screenshot:
10
Hi,

CodeRed:
The following saves excel in corrupt format: (While opening .Xlsx, it shows a 'content unreadable' exception)
Range range = wsCoverSheet.Cells.CreateRange(0, 0, 1, 1);
//Setting the name of the named range
range.Name = "Cover Sheet";
range[0, 0].PutValue("Blah");

You have put space while setting the Name of the range, this is not allowed while creating named ranges in MS Excel either, you may confirm this behavior in MS Excel, it is the limitation of MS Excel and nothing to do with Aspose.Cells for .NET component that follows MS Excel standards.

Please change your code to:

Range range = wsCoverSheet.Cells.CreateRange(0, 0, 1, 1);
//Setting the name of the named range
range.Name = "CoverSheet";
//Or
//range.Name = "Cover_Sheet";
range[0, 0].PutValue("Blah");

Now, it will work fine.

Thanks for your understanding!
11

It works perfectly fine when I generate an excel in.xls format. We have been adding spaces in the range names and it worked fine for us earlier. Does getting rid of spaces fix the ‘unreadable content’ issue with Xlsx file?

12

Hi,


Well, you cannot add space in the range’s name even for XLS file, you may confirm this behavior in MS Excel 2003 if you want.

And, yes, when you remove any space in the name of the range, the error will not occur.

thank you.