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.

Import excel data to datatable

Thread ID:

Created:

Updated:

Platform:

Replies:

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

Hello,

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];
#endregion


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

dgIDCImport.GridDataSource(dt,ucGrid.GridTypes.IDCImport);

//Close the workbook.
workbook.Close();

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



}
}

catch (Exception err)
{
base.DisplayError(err);
}
}



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

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.

Sample:
http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=F96640-117557104.zip

Please let us know if you have any questions.

Regards,
Poornima


harshini
Replied On September 24, 2010 09:18 AM

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 05:53 AM

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:
http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=F96640-680346254.zip

Please let us know if you have any questions.

Regards,
Poornima




Dattaram Phansekar
Replied On November 8, 2012 03:37 AM

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 02:12 AM

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.

Thanks,
Manikandan.M




XlsIOSample_5eaf3e2f.zip

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.

;