XLSX File is corrupted on Save

Hi
I am using Excel 2007 for this test - and Aspose Total .Net - Cells v5.1.3.
Here is the scenario…

1. I have dbusers.xlsx file with a TABLE defined in sheet1 (as Table1)
2. I have pvt.xlsx with a PIVOT table that uses dbusers.xlsx Table1 as a datasource.

3. I then open dbusers.xlsx via Aspose Cells and ImportDataTable into the Table1 and then SAVE the dbusers.xlsx file via Aspose Cells.

4. Now when I open pvt.xlsx to look at my pivot table, the dbusers.xlsx file is corrupted and does not work. I get the error “external table is not in the expected format”.

5. So as a 2nd test - i started from scratch and created brand new dbusers.xlsx and pvt.xlsx files. I then just OPENED dbusers.xlsx file using Aspose Cells aand then immediately SAVED it via Aspose Cells. I made NO CHANGES to dbusers.xlsx what so ever! But the pvt.xlsx still says my datasource file is corrupt. I get the same error: “external table is not in the expected format”.

Can u please help! I have the latest Cells from Aspose Total .NET - v5.1.3
We have an enterprise license.


Nitin
Berll Canada




Hi Nitin,

Please send us the sample dbuser.xlsx & povittable.xlsx file you are using. This will help us to find out the root cause of the issue.

Thanks,

Hi Salman,

I have attached both files.

Thanks

Nitin
Bell canada

Hi Salman,

Have you had a chance to look at this issue. We are desperate to resolve this as I have a deadline. I do not want to work with the COM object model because it is not a maintainable solution for me.

Thanks in advance!

Nitin

Hi,

Kindly try the attached version/fix v5.1.3.5, it should fix your issue.

Kindly give us your feedback.

Thank you.

Hi Amjad,

I am trying it out right now and will let you know after testing.

Thanks for the quick response.

Nitin

Hi Amjad,

I used the new Cells DLL you sent me.

Sorry but i still get the same problem!

I did the same scenario as in the first post of this thread and i still get the “external table is not in the expected format” error when i open the pivot.xlsx file!

Nitin

Hi,

I could not find the issue. Kindly give me complete steps involved on how to reproduce the issue. It would be nice too if you could give us your screen shots at your end and give us your saved files by Aspose.Cells for .NET to show the issue. We will further look into it soon.

Thanks for your cooperation and sorry for any inconvenience caused!

Hi,

i have created a video (sorry could not get the audio to record - but i have callouts) of what i am trying to do. See attachment which this also includes the .xlsx files in the demo.
Here’s the C# code i execute on the button onclick event:

private void btnInjectData_Click(object sender, EventArgs e)
{
string connectionString = “MAXIMOCMS_UAT”;
string sql = "select a.USERID, a.STATUS, b.PLUSPCUSTVENDOR, a.Type from " +
"dbo.MAXUSER a, " +
"dbo.PERSON b " +
"where a.personid = b.personid " +
“and PLUSPCUSTVENDOR is not null”;

DataTable dtable = DataServices.ExcelDataInjectorManager.ExecuteSql(connectionString, sql);

if (dtable == null || dtable.Rows.Count == 0)
return;
LoadOptions lops = new LoadOptions(LoadFormat.Xlsx);
Aspose.Cells.Workbook wb = new Workbook(TXT_EXCEL_DATASOURCE.Text, lops);



Worksheet ws = wb.Worksheets[0];

Int32 worksheetInsertStartRow = 1; // NOT 0 because 0 is the header

//append complete table into worksheet
ws.Cells.ImportDataTable(dtable, false, worksheetInsertStartRow, 0, true);


wb.Save(TXT_EXCEL_DATASOURCE.Text, SaveFormat.Xlsx);


MessageBox.Show(“Done!”);
}

Hope this helps…

Nitin

Hi,

Thanks for the video and sample files.

I have logged your issue into our issue tracking system with an id: CELLSNET-20765. We will soon look into your issue and get back to you soon.

Thank you.

Hi Amjad,

Have you been able to figure out the bug?
I hope you have a fix soon…

Nitin

Hi,

After looking into your issue, I think for your issue, you may try the following sample code:
Aspose.Cells.Workbook wb = new Workbook(TXT_EXCEL_DATASOURCE.Text, lops);

OoxmlSaveOptions options = new OoxmlSaveOptions(SaveFormat.Xlsx);
options.ExportCellName = true;


wb.Save(TXT_EXCEL_DATASOURCE.Text, options );

You should set ExportCellName to true before saving and check if it works fine now.

Thank you.

Hi Amjad.

SUPER!!! That fixed the problem.

I now have one more issue - don’t know if it’s related to the above.
But I am opening a large file (24 MB) on a remote server called: CiscoAttachRatePmart_DS.xlsx
I have also attached this file to this thread.

this is the C# code i am executing:

LoadOptions options = new LoadOptions(LoadFormat.Xlsx);
Aspose.Cells.Workbook wb = new Workbook(TXT_EXCEL_DATASOURCE.Text, options);

I get the following error (while i was debugging the app):

ContextSwitchDeadlock was detected:

The CLR has been unable to transition from COM context 0x277580 to COM context 0x2776f0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.


if I click continue, then it works ok. But I will be creating a Windows service so no GUI.

Can u advise?

Thanks

Nitin

Hi,

A simple test with your sample file and code, I could not find the issue, it works fine here. I even tried opening and re-saving your sample file, it works fine too.

Here is a sample code:
LoadOptions options = new LoadOptions(LoadFormat.Xlsx);

Aspose.Cells.Workbook wb = new Workbook(“e:\test\CiscoAttachRatePmart_DS.xlsx”, options);
OoxmlSaveOptions options2 = new OoxmlSaveOptions(SaveFormat.Xlsx);
options2.ExportCellName = true;

wb.Save(“e:\test\outCiscoAttachRatePmart_DS.xlsx”, options2);

Thank you.

Hi,

ok i think it may be because i was running in debug mode within the VS2008 - IDE.

I shut down VS2008 and then ran the app. It worked perfectly - and no ContextSwitchDeadlock was detected error!


Thanks so much for all your help guys!

Nitin

Hi,
I was trying to read the work book in our webapp , but getting file is corrupted. When i was trying locally with unit test project its working well. I have attached the workbook.

Note: If file size is less then it is working well. i.e (10 MB). Rows : 102200 Cols : 167

Code:
new Workbook(new MemoryStream(fileBytes), new LoadOptions(LoadFormat.Xlsx)

@sidgup,

Thanks for the details.

I do not see your attachment. Could you create a simple sample project (runnable), zip it and provide us with the template file, we will check it soon.

Hi,
I am running in my web app. Its a company site, but I can share the template. Kindly share your email id as this template is big in size (100 MB).

@sidgup,

Since you are reading big file (100MB file), it may demand more memory and the process will take some time. Please make sure that you have sufficient amount of memory (about ten times or more of your actual Excel file. In web scenario/ case it may require even more) for the process.

We cannot receive bigger files via email. Please upload to some file sharing service (e.g Dropbox, Google drive, or your custom file sharing server) and provide link to download it.