Hi,
Well, Cells.ExportDataTable() method decides which data type would be setup for particular column in Excel sheet. By default, it is based on the first value (normally) in the column, if it is string value, then the DataColumn would have string data type for the data table, if it has numeric value, the numeric data type would be taken for the column. Please make sure that if it goes in that way.
Due to what is discussed above, you are getting the exception regarding your variant data in different rows in a column. I think for your case, you should define your desired DataTable based on your desired columns and specify your desired data types. Then use the relevant ExportDatatTable() overloaded version to store the data into it.
See the sample code segment below:
e.g
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));
//.............
worksheet.Cells.ExportDataTable(dt, 0, 0, 1000, true);
Alternatively, you may use ExportDataTableAsString() method, but in this case all the data would be exported as string/text.
Thank you.