title: Export Excel using Npoi
body: <p>I am looking to export an excel sheet using NPOI library. Is there any way to insert the datatable into the sheet without losing the data format?</p>
  
  <p>Previously, I used to use the Gembox Spreadsheet Library. This worked well for me. The code for that looke like:</p>
  
  <pre><code>    public void ExportTest(DataSet ds)
      {
  
          SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
          ExcelFile ef = new ExcelFile();
          var filename = DateTime.Now.ToString("yyyyMMdd") + "BSI_MEMBERAmendment" + ".xls";
          foreach (DataTable dt in ds.Tables)
          {
              ExcelWorksheet ws = ef.Worksheets.Add(dt.TableName);
              ws.InsertDataTable(dt,
             new InsertDataTableOptions(0, 0)
             {
                 ColumnHeaders = true,
                 StartRow = 0,
  
             });
          }
  
          ef.Save(this.Response, filename);
  </code></pre>
  
  <p>I had to stop using the Gembox library because I need to make excel files with more than 5 sheets. Gembox, unfortunately does not allow that on a free platform. As such, I've switched to NPOI.</p>
  
  <p>Now that I'm using the NPOI library, I've change my code to:</p>
  
  <pre><code>  public void WriteExcelWithNPOI(String extension, DataSet dataSet)
      {
  
  
          HSSFWorkbook workbook = new HSSFWorkbook(); ;
  
         if (extension == "xls")
          {
              workbook = new HSSFWorkbook();
          }
          else
          {
              throw new Exception("This format is not supported");
          }
  
          foreach (DataTable dt in dataSet.Tables)
          {
              var sheet1 = workbook.CreateSheet(dt.TableName);
              // How can i insert the data's from dataTable in this sheet
          }
  
          using (var exportData = new MemoryStream())
          {
              Response.Clear();
              workbook.Write(exportData);
              if (extension == "xls") 
              {
                  Response.ContentType = "application/vnd.ms-excel";
                  Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_dict.xls"));
                  Response.BinaryWrite(exportData.GetBuffer());
              }
              Response.End();
          }
      }
  </code></pre>
  
  <p>The problem that I'm encountering with using the NPOI library is, all the cells in the exported excel file is formatted as text. I'd like to retain the format that's used in the data table. 
  Looking for help. Thanks in advance!!!</p>