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.

Thu Nov 07, 2019 11:10 am

Hello,

when exporting a worksheet to a DataTable, all values in the resulting Table are of type string. Is there a generic way to tell Spire to export date and numeric values as DateTime and Decimal, respectively?

KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Fri Nov 08, 2019 5:59 am

Hi Gerhard,

Thanks for your inquiry.
Sorry that Spire.XLS doesn't support that. We will consider adding the new feature in our future upgrade.
If there is any update in the future, we will let you know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Nov 08, 2019 8:15 am

Hi Betsy,

thanks for your prompt reply. I am looking forward to this feature in a future version. I guess it's as simple as exporting cell.Value2 instead of cell.Value.
Meanwhile - could you give a hint on how to best achieve the desired behaviour manually? The following code snippet does the trick, but it is disappointingly slow:

Code: Select all
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(worksheet.Range.Columns.Select(s => new DataColumn("Column" + s.Column, typeof(object))).ToArray());
foreach (CellRange row in worksheet.Range.Rows)
{
    dataTable.Rows.Add(row.Cells.Select(s => s.Value2).ToArray());
}


Especially when it comes to large data volumes, the code snippet can't compete with
Code: Select all
worksheet.ExportDataTable(range: worksheet.Range, exportColumnNames: false);


KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Fri Nov 08, 2019 10:11 am

Hi Gerhard,

Thanks for your response.
Please try to collect the rows firstly, then add the values.
Code: Select all
            CellRange[] rows = worksheet.Rows;
            foreach (CellRange row in rows)
            {
                dataTable.Rows.Add(row.Cells.Select(s => s.Value2).ToArray());
            }

If this doesn't help, I am afraid there is no other good way. Maybe you need to wait that new feature.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Fri Nov 08, 2019 12:15 pm

Hi,

thanks, I tried that but unfortunately it didn't affect the performance at all. Guess I'll have to hope and wait for a new feature.

KR,
Gerhard

GerhardFinee
 
Posts: 26
Joined: Thu Nov 07, 2019 7:57 am

Mon Nov 11, 2019 1:52 am

Hi Gerhard,

Thanks for your feedback.
Once there is any update regarding the new feature, we will let you know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Mar 22, 2022 6:09 pm

Hello,

I got the same issue. Please let me know if any hotfix Spire.Xls could preserve datatypes?

Regards,

MN

LOLAlicense
 
Posts: 1
Joined: Wed Jan 08, 2020 10:37 pm

Wed Mar 23, 2022 8:30 am

Hello,

Thank you for your inquiry.
Sorry the new feature of preserving data types when using worksheet.ExportDataTable() is not implemented yet. Our Dev team is still working on it. If there is any update, we will keep you informed. Thanks for your understanding.

Sincerely,
Annika
E-iceblue support team
User avatar

Annika.Zhou
 
Posts: 1643
Joined: Wed Apr 07, 2021 2:50 am

Fri Jun 09, 2023 8:18 pm

Hi Annika,

Is this feature available yet? I currently have numbers in an xml file that are being read in & converted to a datetime. I have outlined my issues with this in another ticket I have created.

Bandonia
 
Posts: 34
Joined: Tue Nov 16, 2021 9:40 pm

Mon Jun 12, 2023 3:02 am

Bandonia wrote:Hi Annika,

Is this feature available yet? I currently have numbers in an xml file that are being read in & converted to a datetime. I have outlined my issues with this in another ticket I have created.

Hi,

Thanks for your following-up.
Sorry the new feature of preserving data types when using worksheet.ExportDataTable() is not implemented yet. Our Dev team is still working on it. If there is any update, we will keep you informed. Thanks for your understanding.

Sincerely,
Triste
E-iceblue support team
User avatar

Triste.Dai
 
Posts: 1000
Joined: Tue Nov 15, 2022 3:59 am

Return to Spire.XLS