private static void WriteDataTableToExcelWorksheet(System.Data.DataTable dt, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.OpenXmlWriter writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart, System.Text.Encoding.ASCII);
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Worksheet());
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
string cellValue;
string cellReference;
// 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[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = 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;
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
System.Data.DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue;
System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.InvariantCulture;
foreach (System.Data.DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
if (DateTime.TryParse(cellValue, out DateTime dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
