We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Exporting strings containing numbers to Excel

Thread ID:

Created:

Updated:

Platform:

Replies:

123186 Feb 24,2016 09:59 AM Feb 25,2016 08:13 AM WPF 1
loading
Tags: SfDataGrid
sabrina c.
Asked On February 24, 2016 09:59 AM

Hi,
I have the following problem, I've a SfDataGrid containing an arbitrary built DataTable created with a Query.
Some of the columns are strings containing numbers (codes) and the standard export made with the ExcelEngine ExportToExcel method, automatically converts the strings beginning with number in numbers loosing formatting and changing the meaning of codes.

I've looked in the ExcelExportingOptions but there is no option to tell the engine to mantain the type of the columns in the Excel worksheet
which is the best practice in this case?

I have three events in the exporting options of the engine:

ExportingEventHandler
ChildExportingEventHandler
CellsExportingEventHandler

None of the events allows me a direct access to the column type of the source so there is not a simple way to tell excel which columns are and must remain strings or change the content adding the apostrophe in front of the number when needed.
I was thinking to set a class level variable with the list of the string fields and check the cell content adding the leading apostrophe to the fields starting with a number when the column name is contained in the list  but which is the best event where to do it, ChildExporting or CellsExporting, or maybe there is another way to tell the exporter to preserve the type of the cells?

thank you in advance
Sabrina





Sowndaiyan Paulpandi [Syncfusion]
Replied On February 25, 2016 08:13 AM

Hi Sabrina,


Thanks for contacting Syncfusion Support.


You can set ExportMode as Text to export all cells as string to Excel instead of number.
http://help.syncfusion.com/wpf/sfdatagrid/export-to-excel#export-mode

In another way, you can handle exporting of each cell using CellsExportingEventHandler. Please find the documentation link,
http://help.syncfusion.com/wpf/sfdatagrid/export-to-excel#cell-customization-in-excel-while-exporting

You can get the column name using GridCellExcelExportingEventArgs.ColumnName property and also you can get the cell type from column name through datagrid.Columns[e.ColumnName].CellType.
http://help.syncfusion.com/wpf/sfdatagrid/export-to-excel#customize-cell-value-while-exporting

You can change the value in CellsExportingEventHandler by setting e.Range.Cells[0].Text or e.Range.Cells[0].Number. For more information about formatting refer the below link,
http://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#apply-number-formats

Regards,

Sowndaiyan


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;