This project is read-only.

Generic method to populate cells from a datatable

Apr 2, 2012 at 10:52 PM
Edited Apr 2, 2012 at 11:45 PM

Has anyone got a snippet of code that will populate rows in a worksheet directly from a datatable? I mean something that handles the different data column data types and does the proper conversion to NPOI's counterpart. The following populates the worksheet, but because the 2nd column in the datatable is an Int64 datatype, the values are getting stuffed into the cells as a string. Duh, the .ToString() method, huh? There must be a way to accomplish this w/o the data types getting munged.

// add the first row to the worksheet
HSSFRow row = ws.CreateRow(0);

// populate cells containing column headings
foreach (DataColumn dataColumn in dtSource.Columns)
{
  row.CreateCell(dataColumn.Ordinal).SetCellValue(dataColumn.ColumnName);
}

// populate remainder of worksheet
foreach (DataRow dataRow in dtSource.Rows)
{
  row = ws.CreateRow(ws.LastRowNum + 1);

  foreach (DataColumn dataColumn in dtSource.Columns)
  {
    row.CreateCell(dataColumn.Ordinal).SetCellValue(dataRow[dataColumn].ToString());
  }

}

 

Thanks