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