Regarding the ExportDataTable method, how are datatype determined? Do you look at the cell value or the cell format?
Here is my scenario: User input value in a range, then I have to retrieve that range. Problem is user can input anything in the range. So even if the first row is null or a typo (ex: 2009-a-b). I need the whole column to be export as datetime (with typo set to null).
How can i make sure a column is always export in a specific datatype? If i set the format of the column in excel, will it work? is there an another way?
We evaluate a column's datatype based on the first value in the field. You can get an error if there are mixed value types in the column(s) e.g., first value is integer and third value is alpha-numeric.
I think you may try to create a datatable and add your desired data types for the columns. You may try our latest version(4.6.0.x) which has Cells.ExportDataTable(DataTable dataTable,int firstRow, int firstColumn, int rowNumber, bool exportColumnName) method to export the data table.
e.g..,
Workbook workbook = new Workbook(); // ....................
Cells cells = workbook.Worksheets[0].Cells; //Create a datatable and add columns to it. //NOTE: If there are mixed value types, please always use String data types for those columns. DataTable dt = new DataTable(); dt.Columns.Add("column1", typeof(string)); dt.Columns.Add("column2", typeof(string)); dt.Columns.Add("column3", typeof(string)); dt.Columns.Add("column4", typeof(double)); dt.Columns.Add("column5", typeof(double)); dt.Columns.Add("column6", typeof(double)); dt.Columns.Add("column7", typeof(double)); //............. cells.ExportDataTable(dt, 0, 0, 232, true); workbook.Worksheets.Add("NewSheet"); workbook.Worksheets["NewSheet"].Cells.ImportDataTable(dt, true, "A1"); workbook.Save("f:\\test\\outMyBook.xls");