Problem: Error: data may have been lost

slheureux · July 3, 2017, 1:18am

Hi,

We have a web site that use Aspose.Cell to generate different reports in excell format, some uses a template, some don't. Every report works except one. When we try to open the file we have this "error: data may have been lost".

The report use a template.

Every report pass trought the same function for the creation, save, etc...

In the past we were using a very old version Aspose. The version was Aspose.Excell 1.8.0.1 and everything was working.

Any idea?

I have Excell 2007 installed. We save in Excell2000 format but I also tried with Excell2003 and Excell2007 format and I'am having the same problem.

We are using Aspose.Cell v4.4.0.0

Ps: I attached the template the post.

Amjad_Sahi · July 3, 2017, 1:19am

Hi,

Thanks for the template file.

Well, I tried your template file and I don't find the problem you mentioned when opened the saved file in MS Excel. In a Web application, I opened your template file, input some data into it and save to generate the file into Ms Excel and I don't find any error. Could you create a sample test project using your template file, zip it and post it here. You code might help us to reproduce the problem. So that we can figure it out soon.

Thank you.

slheureux · July 3, 2017, 1:19am

Hi,

Thanks a lot for the help.

I did a little Test application and I attached it to this post.

To test it, you can unzip in your C:\.

The problem seem to be related to the number of line of data we try to input in the template. If we try less than 32 rows there is no error message but as soon as we have more rows we have the error.

Also from row 2 to row 32, the the alignment of the column H is LEFT. For row 33 and more, the alignment is Right. The problem of "data may been lost" seem to start at line 33.

Finally, it seem to have a problem with the last column (L) in sheet two. This is a column with a formula. For each row, we take the value of the column H * Value of the Cell C21 of the first Sheet.

For the rows with data, the last column (L) is always empty.

One row after the value of the last column (L) is 0

And for the rest of the rows, the value is the Formula.

Amjad_Sahi · July 3, 2017, 1:19am

Hi,

Thanks for the project with template file.

The problem seem to be related to the number of line of data we try to input in the template. If we try less than 32 rows there is no error message but as soon as we have more rows we have the error.

Well, I checked, the error message "Error: data may have been lost" only occurs in Excel 2007. I checked Excel 2000-Excel 2003 works fine.

Also from row 2 to row 32, the the alignment of the column H is LEFT. For row 33 and more, the alignment is Right. The problem of "data may been lost" seem to start at line 33.

Yes the problem is only in Excel 2007, other excel versions don't show alignment problem.

Finally, it seem to have a problem with the last column (L) in sheet two. This is a column with a formula. For each row, we take the value of the column H * Value of the Cell C21 of the first Sheet. For the rows with data, the last column (L) is always empty.

Well, this seams to be an issue, We will check and figure out soon.

We will resolve all the problems (mentioned above) soon and your output excel file will be shown fine without any problem (error message, formattings problem, formula problem etc.)in all the versions of MS Excel.

Thank you.

slheureux · July 3, 2017, 1:19am

Hi Amjad,

First of all, thank you very much for your help, it's really appreciated.

Is it possible for you to give me an estimated date for the availability of a new version of Aspose.Cell with these fixes?

Thank you!

Warren · July 3, 2017, 1:19am

Hi,

We have found the problem.

After checking your tempalte file , we find you should workbook.Worksheets[1].Cells.ImportDataTable(dataTable, false, 1, 0,false); to import data table.

Warren · July 3, 2017, 1:19am

Hi,

Please try this fix.

slheureux · July 3, 2017, 1:19am

Hi Warren,

Thanks for the help.

With this new fix if I do "oAsposeExcel.Worksheets(1).Cells.ImportDataTable(DataTable, False, 1, 0)" the file is able to open without any error, the alignment, font, etc. is correct for every rows but for every rows with data the last column with a formula is empty and for rows without data the formula is still there.

If I do "oAsposeExcel.Worksheets(1).Cells.ImportDataTable(GetDate, False, 1, 0, False)" the file open without error, the formula are there and the calculation data are correct for every rows with data except for the last one, the cell is empty.

Also for some rows the formatting is bad. Some rows have a different font, font size, alignment, some are in bold. I added an example of the result.

Thanks again!

Amjad_Sahi · July 3, 2017, 1:19am

Hi,

Yes we found the problems you have mentioned, We will figure out the issue soon.

Thank you.

Warren · July 3, 2017, 1:19am

Hi,

Please try this fix. We fixed a bug of import data table.

If you want to keep the style of the cells, please use Cells.ImportDataTable(DataTable, false, 1, 0) or Cells.ImportDataTable(DataTable, false, 1, 0,true)

If you do not want to keep the style , please use Cells.ImportDataTable(DataTable, false, 1, 0,false). This usage will save running time.

slheureux · July 3, 2017, 1:19am

Hi guys,

Again, thanks a lot for your help.

With this new version, we still have a problem, we are still trying with the same Test Application that I attached to the post earlier with Excell 2007.

With Cells.ImportDataTable(DataTable, false, 1, 0) :

No error when we open the file
Font, Font Size, Color, Alignement, etc. is correct for every rows
For rows with data, the last column is still empty, the formula was erased
For every rows without data, the formula is still there in the last column

With Cells.ImportDataTable(DataTable, false, 1, 0,true) :

No error when we open the file
Font, Font Size, Color, Alignement, etc. is correct for every rows
For rows with data, the last column is still empty, the formula was erased
For every rows without data, the formula is still there in the last column

With Cells.ImportDataTable(DataTable, false, 1, 0,false) :

No error when we open the file
For every row with data, except the last one, with data the formula is there in the last column and the calculation is correct.
For the last column of the last row with data the formula was erased.
Some rows have different font, font size, alignement, bold, etc.

Thanks!

Amjad_Sahi · July 3, 2017, 1:19am

Hi,

Thanks for all the details.

Well, Perhaps Warren might have attached the wrong fix, We will soon attach the valid fix here.

Thanks for.

slheureux · July 3, 2017, 1:19am

Hi Amjad,

Again, thanks for the help.

Is it possible for you to give me an estimated date for the availability of a new version of Aspose.Cell with these fixes?

Thank you!

Amjad_Sahi · July 3, 2017, 1:20am

Hi,

We will get back to you soon for your issues.

Thanks for being patient!

Warren · July 3, 2017, 1:19am

Hi,

The method Cells.ImportDataTable(DataTable, false, 1, 0) will call the Cells.ImportDataTable(DataTable, false, 1, 0,true) when we impelement this method. So they works same. If you call one of the two methods, we will keep the the format and insert some rows.So the last column is empty. If you still want to call one of the two methods,please set the formulas by yourself after importing data from datatable.See following codes:

int rowNumber = workbook.Worksheets[1].Cells.ImportDataTable(dataTable, false, 1, 0);
Cells cells = workbook.Worksheets[1].Cells;
cells["L2"].SetSharedFormula("=SUM(H2*'Read me'!$C$21)", rowNumber, 1);

The method Cells.ImportDataTable(DataTable, false, 1, 0,false) ,we do not apply the formats of the first row to other rows , so you have to set the format by youself. We have fixed the bug of losing the last cell of the last column. please try the attached fix.

slheureux · July 3, 2017, 1:19am

Hi,

I tested the new version and the bug of losing the last cell of the last column is fix. Thank you very much.

My last concern is the fact that with the function Cells.ImportDataTable(DataTable, false, 1, 0), I need to set the formula by code.

We cannot do that cause we have a lot of reports generated by this function and each reports have their own formula, number of column, etc, so it's impossible.

In the past we were using Aspose.Excell 1.8.0.1 with the function Cells.ImportDataTable(DataTable, false, 1, 0) and the formula was there.

I don't like the idea of changing the code to use function Cells.ImportDataTable(DataTable, false, 1, 0, false) and losing the formatting of the cell.

Is there a way of having both functionality, the formatting and the formula because like I said earlier it was working with Aspose.Excell. We cannot stay with Aspose.Excell because we're having problem with Excell 2007.

Warren · July 3, 2017, 1:20am

Hi,

We have updated the Cells.ImportDataTable(DataTable, false, 1, 0) since 1.8.

You only need to change a few line of code. Please use Cells.ImportDataTable(DataTable, false, 1, 0) method and change your template file.Appling the format of first cell of the second row in the file to the whole column,then change the format of the first row. See the attached file and following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\1.05E - ADDJ Daily Sales Manifest.xls");
int rowNumber = workbook.Worksheets[1].Cells.ImportDataTable(dataTable, false, 1, 0, false);
workbook.Save(@"F:\FileTemp\dest.xls");