Import excel data to datatable

[harshini]

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]

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]

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]

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]

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]

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

[Prasath Sridhar]

Hi,

Facing problem while importing excel into datatable, if there is any cell which contains date format then after importing excel in datatable it is showing with time also.

In above code, I modified excel file and added a row which contains dates (10/11/12, 10/11/13, 10/11/14....) but after importing the result in datatable is (10/11/12 12:00:00 AM, 10/11/13 12:00:00 AM , 10/11/14 12:00:00 AM ....).

I saw pattern that, if date lie between 1 to 13 then it convert (5/11/2021) to ( 5/11/2021 12:00:00 AM).

How to resolve this issue.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Prasath, 

Greetings from Syncfusion. 

When you enter a date into Excel cell, its format will be detected based on system date settings. 

Example: 

1. Consider your system date settings have the format as m/d/yyyy
2. If you enter the value into Excel cell as 10/11/2021, then this will be detected as date.
3. If you enter the value into Excel cell as 14/11/2021, then this will be detected as string. (Because 14 is not a valid month).
4. So the values detected as date will be rendered with date and time because we are parsing a DateTime object where as the values detected as string will be rendered will show the date without time in data table.

So, to achieve your requirement and rendering the values as date alone into data table, we suggest you to first set the number format as Text and then assign values in Microsoft Excel. After that, you can use the Excel document in XlsIO to export as data table. 

Kindly try the suggestion and let us know if this helps. 

Regards, 

Keerthi.