Spire.DataExport for .NET is a 100% pure data .NET library suit for exporting data into MS Word, Excel, RTF, Access, PDF, XPS, HTML, XML, Text, CSV, DBF, SYLK, SQL Script, DIF, Clipboard, etc.

Fri Feb 09, 2018 10:04 pm

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

MichelleChen
 
Posts: 11
Joined: Mon Dec 04, 2017 5:56 pm

Sun Feb 11, 2018 6:51 am

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
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Sun Feb 11, 2018 10:35 pm

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

MichelleChen
 
Posts: 11
Joined: Mon Dec 04, 2017 5:56 pm

Mon Feb 12, 2018 4:04 am

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
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Tue Feb 13, 2018 4:52 pm

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
 
Posts: 11
Joined: Mon Dec 04, 2017 5:56 pm

Tue Feb 13, 2018 7:27 pm

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

MichelleChen
 
Posts: 11
Joined: Mon Dec 04, 2017 5:56 pm

Wed Feb 14, 2018 9:00 am

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
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Return to Spire.DataExport