public static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
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);
}
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
writer.AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == typeof(decimal).FullName) || (col.DataType.FullName == typeof(double).FullName) || (col.DataType.FullName == typeof(Single).FullName);
IsDateColumn[colInx] = (col.DataType.FullName == typeof(DateTime).FullName);
}
writer.WriteEndElement(); // End of header "Row"
double cellFloatValue = 0;
CultureInfo ci = Thread.CurrentThread.CurrentCulture; //new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
writer.AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces ? NumericFormatDecimaPlaces.Two : NumericFormatDecimaPlaces.Zero);
}
}
else if (IsDateColumn[colInx])
{
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
writer.AppendDateCell(cellReference, dateValue);
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
