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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Import excel data to datatable

Thread ID:





96640 Sep 23,2010 07:17 PM UTC Nov 12,2012 07:12 AM UTC ASP.NET Web Forms (Classic) 5
Tags: XlsIO
Asked On September 23, 2010 07:17 PM UTC


I am using syncfusion to import excel to datattable.when the datat is imported to datatable the columns are all strings .how to convert these columns to specific datatype?how do i validate the column data type?

i am using an old so I do not see detectcolumntypes however..version

using Syncfusion.XlsIO;

private void btnImport_Click(object sender, EventArgs e) { ImportExcelFile(); } private void ImportExcelFile() { try { string strFileName = ""; OpenFileDialog openFileDialog = new OpenFileDialog();

openFileDialog.Filter = "Files (*.xls)|*.xls|(*.xlsm)|*.xlsm";
openFileDialog.DefaultExt = ".xls";
if (openFileDialog.ShowDialog() == DialogResult.OK)
strFileName = openFileDialog.FileName;
#region Get Correct Worksheet in excel file
DateTime dtStart = DateTime.Now;
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(strFileName);
IWorksheet sheet = workbook.Worksheets[0];

DataTable dt = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);


//Close the workbook.

//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;


catch (Exception err)

Poornima R [Syncfusion]
Replied On September 24, 2010 08:39 AM UTC

Hi Harshini,

Thank you for your interest in Syncfusion Products.

We have prepared the sample for your requirement. Could you please find the sample from the below link. Try running the sample and let us know if this helps.


Please let us know if you have any questions.


Replied On September 24, 2010 01:18 PM UTC

Hi Poornima,

we are using an older version of syncfusion and detect column types is not yet added in that version .

However my problem got solved.
DataTable dt = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);

I copied the above datatable into another datatable and changed the column data types.

But when i was converting one of the column has computed formula in it..so when i was converting from string to int iam getting an error saying G2*1000000 cannot be converted to int.Also if i dont convert it to int it displays computed formula and not the originnal value.

Is there any way that i can use computed formula and column names?

i.e how to use computed formula and also change the datatype of the value in it

Poornima R [Syncfusion]
Replied On September 27, 2010 09:53 AM UTC

Hi Harshini,

Thank you for your update.

We have prepared the sample for your requirement. Could you please find the sample from the below link. Try running the sample and let us know if this helps.

Sample link:

Please let us know if you have any questions.


Dattaram Phansekar
Replied On November 8, 2012 08:37 AM UTC

Can you please provide an example where the excel sheet has a DateTime column.
I'm facing issues with parsing the data from a column on which datetime formatting is applied. The ExportDataTable function does not recognize the datetime type for this column and parses the values as String.

Manikandan M [Syncfusion]
Replied On November 12, 2012 07:12 AM UTC

Hi Dattaram,

Thank you very much for your patience.

We have created the simplified sample to export the sheet values to datatable with data types and attached the same below. Please make use of this and let us know if this helps you.

Code snippet:
DataTable dt = sheet.ExportDataTable(sheet.Range["A1:C4"], ExcelExportDataTableOptions.DetectColumnTypes);

Please let us know if you need any clarifications.




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.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon 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.Close Icon