Import Excel to datatable

Question

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?

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);
            }
        }

Answer 1

Instead of ColumnNames use DetectColumnTypes like below.

ExcelExportDataTableOptions.DetectColumnTypes

HTH