Exporting DataTable to xlsx format

Aspose.Cells Product Family
1

Hello,

I am using Aspose.Cells to export a datatable to xlsx format, this process is done by creating the workbook, converting it to bytes, and then opening it in a web response. This is my code:

Workbook wbk = new Workbook(FileFormatType.Xlsx);
wbk.Worksheets.RemoveAt(“Sheet1”);
foreach (DataTable dataTable in dataSet.Tables)
{
Worksheet wks = wbk.Worksheets.Add(dataTable.TableName);
ExcelWorkbookHelper.AddWorkSheetDataAspose(dataTable, wks);
}
byte[] array;
MemoryStream ms = new MemoryStream();
ms = wbk.SaveToStream();
array = ms.ToArray();
return array;

then:
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = false;
Response.AppendHeader(“Content-Type”, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
string filename = string.Format("{0}.{1}", “CodeComparison-” + txtCode.Text, “xlsx”);
Response.AppendHeader(“Content-Disposition”, “attachment; filename=”" + filename + “”");
Response.AppendHeader(“Content-Length”, excelBytes.Length.ToString());
Response.Flush();
Response.OutputStream.Write(excelBytes, 0, excelBytes.Length);
Response.Flush();
Response.Close();


this is giving me an error when opening the file that the file format or extension is not valid, if I change the file extension into xls, it will open normally

2

Hi,


Please change the line(s) of code in your code segment:
i.e.,
byte[] array;
MemoryStream ms = new MemoryStream();
ms = wbk.SaveToStream();
array = ms.ToArray();
return array;

with:
byte[] array;
MemoryStream ms = new MemoryStream();
wbk.Save(ms, SaveFormat.Xlsx);
array = ms.ToArray();
return array;

it should work fine.

For your information Workbook.SaveToStream() method (older) saves the Workbook to XLS file format, so you are getting this error.

Thank you.


3

that worked fine
Thank you

4

Hi,


Thanks for your feedback.

Good to know that it figures out your issue now. Feel free to write back if you have further comments or questions, we will be happy to assist you soon.

Thank you