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(); }