public static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell()
{
CellReference = cellReference
};
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild <SheetData>();
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
var headerRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
}
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
var newExcelRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
}
}