asp.net - Convert Excel Cell Value From Number To Text Using C# -

i using below process open excel:

if (dt != null)  {     foreach(datacolumn dc in dt.columns)      {         response.write(dc.columnname + "\t");         //sep = ";";     }     response.write(system.environment.newline);     foreach(datarow dr in dt.rows)      {         (int = 0; < dt.columns.count; i++)          {             response.write(dr[i].tostring() + "\t");         }         response.write("\n");     }      response.flush();     response.suppresscontent = true;     httpcontext.current.applicationinstance.completerequest(); } 

in datatable have 1 numeric value align right when excel renders default property of excel align numeric values right. per requirement have align numeric value left side in excel. tried adding space back-end excel ignore space when renders. tried adding ' before column value when excel renders column value shows '2015, '2016.

anybody can me figure out. in advance.

you're exporting csv file. if use openxml able specify column datatypes (formats) , apply left alignment numbers eg:

using officeopenxml; using officeopenxml.style;  public static bool isnumeric(this datacolumn col) {     if (col == null)         return false;      //this should moved const in order improve performance     var numerictypes = new [] { typeof(byte), typeof(decimal), typeof(double), typeof(int16), typeof(int32), typeof(int64), typeof(sbyte), typeof(single), typeof(uint16), typeof(uint32), typeof(uint64)};     return numerictypes.contains(col.datatype); }  public static void exporttoexcelopen(dataset ds, string filename) {     // create spreadsheet document supplying filename     // default, autosave = true, editable = true, , type = xlsx      httpcontext.current.response.clearheaders();     httpcontext.current.response.clearcontent();     httpcontext.current.response.clear();     httpcontext.current.response.buffer = true;     httpcontext.current.response.addheader("content-disposition", "attachment; filename=" + filename);     httpcontext.current.response.contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";     httpcontext.current.response.charset = "";      memorystream ms = new memorystream();      using (var objspreadsheet = spreadsheetdocument.create(ms, documentformat.openxml.spreadsheetdocumenttype.workbook))     {         var workbookpart = objspreadsheet.addworkbookpart();         objspreadsheet.workbookpart.workbook = new documentformat.openxml.spreadsheet.workbook();         objspreadsheet.workbookpart.workbook.sheets = new documentformat.openxml.spreadsheet.sheets();          uint sheetid = 1;          foreach (datatable table in ds.tables)         {             var sheetpart = objspreadsheet.workbookpart.addnewpart<worksheetpart>();             var sheetdata = new documentformat.openxml.spreadsheet.sheetdata();             sheetpart.worksheet = new documentformat.openxml.spreadsheet.worksheet(sheetdata);              documentformat.openxml.spreadsheet.sheets sheets = objspreadsheet.workbookpart.workbook.getfirstchild<documentformat.openxml.spreadsheet.sheets>();             string relationshipid = objspreadsheet.workbookpart.getidofpart(sheetpart);              sheetid += 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();              dictionary<string, bool> columns = new dictionary<string, bool>();              foreach (datacolumn column in table.columns)              {                  //check numeric column here!!                 columns.add(column.columnname, column.isnumeric());                     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);              numberformatinfo valuenumberformatinfo = new numberformatinfo() { numberdecimalseparator = ".", numbergroupseparator = string.empty };             foreach (datarow dsrow in table.rows)              {                  documentformat.openxml.spreadsheet.row newrow = new documentformat.openxml.spreadsheet.row();                  foreach (keyvaluepair<string, bool> col in columns)                  {                      documentformat.openxml.spreadsheet.cell cell = new documentformat.openxml.spreadsheet.cell();                     if (col.value)                     {                         cell.datatype = documentformat.openxml.spreadsheet.cellvalues.number;                          cell.cellvalue = new documentformat.openxml.spreadsheet.cellvalue(string.format(valuenumberformatinfo, "0.####################", dsrow[col.key]));                          //left alignment here                          cell.style.horizontalalignment = excelhorizontalalignment.left;                     }                     else                     {                         cell.datatype = documentformat.openxml.spreadsheet.cellvalues.string;                          cell.cellvalue = new documentformat.openxml.spreadsheet.cellvalue(dsrow[col.key].tostring());                     }                      newrow.appendchild(cell);                  }                   sheetdata.appendchild(newrow);              }         }          objspreadsheet.close();         ms.writeto(httpcontext.current.response.outputstream);         ms.close();          httpcontext.current.response.flush();         httpcontext.current.response.end();     } } 

ref: http://forums.asp.net/t/1860267.aspx?how+to+retain+data+types+when+exporting+to+excel+using+open+xml

ref: set text align center in excel document using openxml c#