Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Jun 16, 2017 5:15 pm

Since upgrading from Spire.XLS version 7.3.12.7040 to 7.12.27.6040 my InsertDataTable calls fail to insert Integer data.

My code:
ws.InsertDataTable(subset, False, 2, 2, True)

In every field that is an integer I get #NAME? in the spreadsheet cell. It does not matter if it is a 0 , 1, or 2017101110.

If my datatable contains a varchar, or floating point the insert works fine, but something is wrong when inserting an integer datatype.

otsc
 
Posts: 3
Joined: Wed May 22, 2013 9:09 pm

Mon Jun 19, 2017 2:44 am

Hello,

Sorry for the late reply due to the weekend day off.
With the information you provided, I could't reproduce the issue on my side. Below is my code for testing:
Code: Select all
  Spire.Xls.Workbook book = new Spire.Xls.Workbook();
            Worksheet sheet=book.Worksheets.Add("mySheet");
            DataTable subset = new DataTable();
            subset.Columns.Add(new DataColumn("name", Type.GetType("System.String")));
            subset.Columns.Add(new DataColumn("age", Type.GetType("System.Int32")));
            DataRow dr;
            dr = subset.NewRow();
            dr["name"]= "name1";
            dr["age"] = 1;
            subset.Rows.Add(dr);

            dr = subset.NewRow();
            dr["name"] = "name2";
            dr["age"] = 2;
            subset.Rows.Add(dr);

            dr = subset.NewRow();
            dr["name"] = "name3";
            dr["age"] = 3;
            subset.Rows.Add(dr);

            sheet.InsertDataTable(subset, false, 2, 2, true);
            book.SaveToFile("10866.xlsx",FileFormat.Version2013);

In order to help us with a better investigation, could you share your sample datatable with us and provide more details?

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Mon Jun 19, 2017 3:45 pm

I have found a few more details. To recreate the error you need to create an existing Spreadsheet and use LoadFromFile.

The existing spreadsheet should have the following data in it.
In Cell A1 =query_1
In Cell B1 =query_2
In Cell C1 =query_3

After cell entry Excel will display #NAME? in each cell

The format for all is General.

I set the sheet name to Report.

I saved this file as ReportTest.xls.

Here is my code that recreates the issue
Dim subset As New System.Data.DataTable

subset.Columns.Add("IntNum", GetType(Integer))
subset.Columns.Add("FloatNum", GetType(Decimal))
subset.Columns.Add("StrNum", GetType(String))

Dim dr As System.Data.DataRow

dr = subset.NewRow()
dr("IntNum") = 1
dr("FloatNum") = 1.0
dr("StrNum") = "test"
subset.Rows.Add(dr)

Dim wb As Workbook = New Workbook()
wb.LoadFromFile("C:\ReportTest.xls")

Dim ws As Worksheet = wb.Worksheets("Report")

ws.InsertDataTable(subset, False, 1, 1, True)
wb.SaveToFile("C:\Test2.xls")

When I open the C:\Text2.xls file
Cell A1 has no change, it shows #NAME? and contains =query_1
Cell B1 and C1 have the correct data in them.

Hope you can find the issue because this used to work fine in past versions.

otsc
 
Posts: 3
Joined: Wed May 22, 2013 9:09 pm

Tue Jun 20, 2017 2:52 am

Hello,

Thanks for your reply.
I have noticed the issue you mentioned and referred it to our dev team.Once there's any update, I will let you know.
Sorry for the inconvenience caused.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Fri Jun 30, 2017 9:40 am

Hello,

Glad to informa that the issue has been resolved, please try the hotfix(Spire.XLS Pack Hotfix Version:7.12.43).

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Fri Jun 30, 2017 1:36 pm

I can confirm it is working now. I thought I had an issue but it was a clean/rebuild problem.

Thanks

otsc
 
Posts: 3
Joined: Wed May 22, 2013 9:09 pm

Mon Jul 03, 2017 12:57 am

Hello,

Thanks for your feedback.
Please feel free to contact us if you need any help.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Return to Spire.XLS

cron