c# - Formatting issue with Importing data from a DataSet to an Excel Sheet -

i working data in dataset , trying import excel file using openxml in c# , successful in doing in dataset have columns of type datetime, integer , double code importing columns plain text , making impossible me sort them value on worksheet. using following code

public void exportdataset()         {              try             {                 string fromformat = "dd/mm/yyyy";                 string toformat = "mm-dd-yyyy";                  datetime newdate = datetime.parseexact(datetime.today.tostring(fromformat), fromformat, null);                   string filedate = newdate.tostring(toformat);                  string destination = @"z:\physical db data " + filedate + ".xls";                   using (var workbook = spreadsheetdocument.create(destination, documentformat.openxml.spreadsheetdocumenttype.workbook))                 {                     var workbookpart = workbook.addworkbookpart();                      workbook.workbookpart.workbook = new documentformat.openxml.spreadsheet.workbook();                      workbook.workbookpart.workbook.sheets = new documentformat.openxml.spreadsheet.sheets();                      dataset ds = new dataset();                     ds = getphysicalgrainreportautomation();                     foreach (system.data.datatable table in ds.tables)                     {                          var sheetpart = workbook.workbookpart.addnewpart<worksheetpart>();                         var sheetdata = new documentformat.openxml.spreadsheet.sheetdata();                         sheetpart.worksheet = new documentformat.openxml.spreadsheet.worksheet(sheetdata);                          documentformat.openxml.spreadsheet.sheets sheets = workbook.workbookpart.workbook.getfirstchild<documentformat.openxml.spreadsheet.sheets>();                         string relationshipid = workbook.workbookpart.getidofpart(sheetpart);                          uint sheetid = 1;                         if (sheets.elements<documentformat.openxml.spreadsheet.sheet>().count() > 0)                         {                             sheetid =                                 sheets.elements<documentformat.openxml.spreadsheet.sheet>().select(s => s.sheetid.value).max() + 1;                         }                          documentformat.openxml.spreadsheet.sheet sheet = new documentformat.openxml.spreadsheet.sheet() { id = relationshipid, sheetid = sheetid, name = table.tablename };                         sheets.append(sheet);                          documentformat.openxml.spreadsheet.row headerrow = new documentformat.openxml.spreadsheet.row();                          list<string> columns = new list<string>();                         foreach (system.data.datacolumn column in table.columns)                         {                             columns.add(column.columnname);                              documentformat.openxml.spreadsheet.cell cell = new documentformat.openxml.spreadsheet.cell();                             cell.datatype = documentformat.openxml.spreadsheet.cellvalues.string;                             cell.cellvalue = new documentformat.openxml.spreadsheet.cellvalue(column.columnname);                             headerrow.appendchild(cell);                         }                           sheetdata.appendchild(headerrow);                          foreach (system.data.datarow dsrow in table.rows)                         {                             documentformat.openxml.spreadsheet.row newrow = new documentformat.openxml.spreadsheet.row();                             foreach (string col in columns)                             {                                 documentformat.openxml.spreadsheet.cell cell = new documentformat.openxml.spreadsheet.cell();                                 cell.datatype = documentformat.openxml.spreadsheet.cellvalues.string;                                 cell.cellvalue = new documentformat.openxml.spreadsheet.cellvalue(dsrow[col].tostring()); //                                 newrow.appendchild(cell);                             }                              sheetdata.appendchild(newrow);                         }                      }                 }              }             catch (exception ex)             {                 messagebox.show(ex.message);             }          } 

what should code work normal , not import fields own type? worked fine when looped , imported dataset using interop taking long time dataset huge of 15 columns , more 50000 rows.

i thought issue @ declaring columns list string follows

list<string> columns = new list<string>(); 

and

cell.datatype = documentformat.openxml.spreadsheet.cellvalues.string; 

but not sure how deal this.

i recommend use npoi xssf/hssf in order avoid because facilitates cell value property set integer, datetime, formula etc. , fast too. left checking dataset value , set cell property according type. more information visit https://npoi.codeplex.com/sourcecontrol/latest