Warning message when open xls file using DataExport

[MichelleChen]

Hi

I am using Spire.DataExport.dll, version 2.0.3.0. After I export a C# gridview context into excel, the .xls file can be generated, but when I open this file, I get warning message as:

"We found a problem with some context in [filename.xls]. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes. "

How can I get rid of this warning message?

Thank you!
Michelle

[Nina.Tang]

Dear Michelle,

Thanks for your inquiry.
I debugged your code and indeed found the issue you mentioned on my side. I have referred it to our dev team for further investigations. If there is any good news, we will let you know. Besides, I noticed that if i directly export data from the datatable to sheet using below code, the issue will not happen. You could download our latest Spire.DataExport for .NET (Hot Fix) Version:3.5.86 and use below code to have a test.

Code: Select all

//Create a DataTable
DataTable dt = new DataTable("MyDataTable");

dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Price", typeof(decimal));

DataRow row1 = dt.NewRow();
row1["Id"] = 1;
row1["Date"] = new DateTime(2017, 12, 1);
row1["Name"] = "Michelle";
row1["Price"] = 122.12;
dt.Rows.Add(row1);

DataRow row2 = dt.NewRow();
row2["Id"] = 2;
row2["Date"] = new DateTime(2017, 12, 15);
row2["Name"] = "Chen";
row2["Price"] = 15000.45;
dt.Rows.Add(row2);

//Create instance of CellExport
CellExport myCellExport = new CellExport();
           
WorkSheet sheet = new WorkSheet();

//Export data from the datatable to sheet
sheet.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
sheet.DataTable = dt;

sheet.SheetName = "ABC";
sheet.ItemType = CellItemType.Row;
sheet.AutoFitColWidth = true;
this.cellExport1.Sheets.Add(sheet);
myCellExport.Sheets.Add(sheet);
myCellExport.SaveToFile("MyTest.xls");


Sincerely,
Nina
E-iceblue support team

[MichelleChen]

Dear Nina,

Thanks for your response. The reason for us not directly export data from the datatable to sheet is: we need to set each column's format by ourselves, like adding $ sign in front of some columns' data number, etc. That is why we rewrite each cell's data. As long as we write a cell, there will be the warning message when we open the generated excel, even in your new Hot fix, version 3.5.86

Regards,
Michelle

[Nina.Tang]

Hello,

Thanks for your feedback.
After an investigation, i found there is an issue in your code. You didn't set the cell type to datetime. After adding this line, the issue will not happen. Please have a try.

Code: Select all

if (typeof(DateTime) == a[i].GetType())
{
       //add this line
       cell.CellType = CellType.DateTime;
       cell.Value = Convert.ToDateTime(a[i]).ToString();
}


Sincerely,
Nina
E-iceblue support team

[MichelleChen]

Hi Nina,

Thanks for pointing out I missed setting DateTime CellType. But the warning message is not because of setting CellType.

I wrote another simple piece code as below. The table just have one string column. I still got the same warning message as my original post of this thread.

Thanks!
Michelle



// Create a DataTable
DataTable dt = new DataTable("MyDataTable");

dt.Columns.Add("Name", typeof(string));

DataRow row1 = dt.NewRow();
row1["Name"] = "Michelle";
dt.Rows.Add(row1);

DataRow row2 = dt.NewRow();
row2["Name"] = "Chen";
dt.Rows.Add(row2);

// Create instance of CellEXport
CellExport myCellExport = new CellExport();

WorkSheet sheet = new WorkSheet();
sheet.DataExported = false;
sheet.SheetName = "ABC";
sheet.ItemType = CellItemType.Row;
sheet.AutoFitColWidth = true;
this._cellExport.Sheets.Add(sheet);

Cell cell1 = new Cell();
cell1.Row = 1;
cell1.Column = 1;
cell1.Value = "Name";
sheet.Cells.Add(cell1);

// Add Excel content rows
int currentRow = 1;
foreach (DataRow dr in dt.Rows)
{
currentRow++;
object[] a = dr.ItemArray;

for (int i = 0; i < dt.Columns.Count; i++)
{

Cell cell = new Cell();
cell.Row = currentRow;
cell.Column = i + 1;

cell.CellType = CellType.String;
cell.Value = a[i].ToString();

sheet.Cells.Add(cell);
}
}
myCellExport.Sheets.Add(sheet);
myCellExport.SaveToFile("C:\\Temp2\\MyTest.xls");

[MichelleChen]

Hi Nina,

For the code you post on Feb 11, 2018 6:51 am. I have 2 questions:

(1) I need to change "this.cellExport1" to be "myCellExport" to make the code be able to be compiled.

(2) The DataTime column cannot show data correctly. It turns the DateTime to be a 5 digits number. Like:
12/1/2017 --> 43070
12/15/2017 -- > 43084

Thanks,
Michelle

[Nina.Tang]

Hello,

Thanks for your feedback.
For the first issue, I run your code and indeed found the issue. After discussing with our dev team further, sorry to tell you that in fact the method of creating cells and setting cell's format you were using is not allowed in Spire.DataExport, it can only be used as a simple data export tool but can't operate cells in sheet like you. If you need to do that, you need to use our Spire.Xls product. Thanks for your understanding.

For the second issue, I have noticed that the column cannot show the correct DateTime format and logged it into our bug tracking system. If it is fixed, we will let you know. So sorry for the inconvenience caused.

Sincerely,
Nina
E-iceblue support team