When saving from data table, all data is saved as text.

[skyfox1964]

When saving to excel file (editing an existing file) from a DataGridView control, everything gets saved as text and messes up the calculations in other sheets that depends on data from this particular worksheet. Gridview contains text, integer, and double data types. I want to maintain all those formats as is. What am I doing wrong?

Code: Select all

       
Dim workbook As Spire.Xls.Workbook = New Spire.Xls.Workbook
workbook.LoadFromFile(xlfileName)
Dim sheet As Spire.Xls.Worksheet = workbook.Worksheets(2)
sheet.InsertDataTable(DataGridView1.DataSource, False, 1, 1)      'All data from gridview gets saved as text.
Workbook.SaveToFile(xlfileName)

[Elena.Zhang]

Hello,

Thanks for your inquiry.
I simulated your scenario and tested with the latest Spire.Xls(Hotfix) Version:10.9.16, but did not find that int and double data were saved as text. Below is my test code and output. If you are using an older version, please download the latest version and try again.

Code: Select all

Dim dt As DataTable = New DataTable
        dt.Columns.Add("Country", Type.GetType("System.String"))
        dt.Columns.Add("Jun", Type.GetType("System.Int32"))
        dt.Columns.Add("Aug", Type.GetType("System.Double"))
        dt.Rows.Add("Cuba", "6000", "32.00")
        dt.Rows.Add("Mexico", "8000", "20.00")
        dt.Rows.Add("France", "9000", "40.00")
        dt.Rows.Add("German", "8500", "23.00")
        Me.DataGridView1.DataSource = dt


        Dim workbook As Spire.Xls.Workbook = New Spire.Xls.Workbook
        Dim sheet As Spire.Xls.Worksheet = workbook.Worksheets(0)
        sheet.InsertDataTable(DataGridView1.DataSource, False, 1, 1)
        workbook.SaveToFile("result.xlsx", FileFormat.Version2013)


screenshot.png (5.78 KiB) Viewed 753 times

In addition, we also provide a direct method to convert Text to Number, below is the corresponding code for your reference.

Code: Select all

sheet.Range("A2").ConvertToNumber()


Sincerely,
Elena
E-iceblue support team

[skyfox1964]

Thanks Elena,

Appreciate the quick feedback. I will try the new binaries as I was using 10.1.xx.

Cheers.

[Elena.Zhang]

Hello,

Thanks for your response.
If you encounter any issue during testing, please feel free to write back.
Have a nice day!

Sincerely,
Elena
E-iceblue support team