private static void AppendNumericCell(string cellReference, string cellStringValue, DocumentFormat.OpenXml.OpenXmlWriter writer)
{
// Add a new numeric Excel Cell to our Row.
writer.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellStringValue),
CellReference = cellReference,
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number
});
}
private static void AppendDateCell(string cellReference, DateTime dateTimeValue, DocumentFormat.OpenXml.OpenXmlWriter writer)
{
// Add a new "datetime" Excel Cell to our Row.
//
string cellStringValue = dateTimeValue.ToShortDateString();
writer.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellStringValue),
CellReference = cellReference,
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
});
}
private static void AppendHeaderTextCell(string cellReference, string cellStringValue, DocumentFormat.OpenXml.OpenXmlWriter writer)
{
// Add a new "text" Cell to the first row in our Excel worksheet
// We set these cells to use "Style # 3", so they have a gray background color & white text.
writer.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellStringValue),
CellReference = cellReference,
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
});
}
private static void AppendTextCell(string cellReference, string cellStringValue, DocumentFormat.OpenXml.OpenXmlWriter writer)
{
// Add a new "text" Cell to our Row
#if DATA_CONTAINS_FORMULAE
// If this item of data looks like a formula, let's store it in the Excel file as a formula rather than a string.
if (cellStringValue.StartsWith("="))
{
AppendFormulaCell(cellReference, cellStringValue, writer);
return;
}
void AppendFormulaCell()
{
// Add a new "formula" Excel Cell to our Row
writer.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellFormula = new DocumentFormat.OpenXml.Spreadsheet.CellFormula(cellStringValue),
CellReference = cellReference,
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number
});
}
#endif
// Add a new Excel Cell to our Row
writer.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Cell
{
CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellStringValue),
CellReference = cellReference,
DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
});
}
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();
}
