worksheet.ExportDataTable() - how to preserve data types?

[GerhardFinee]

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

[Betsy.jiang]

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

[GerhardFinee]

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

[Betsy.jiang]

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

[GerhardFinee]

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

[Betsy.jiang]

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

[LOLAlicense]

Hello,

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

Regards,

MN

[Annika.Zhou]

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

[Bandonia]

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.

[Triste.Dai]

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