private void GenerateSheetByIDataReader(StreamWriter writer, MiniExcelZipArchive archive, IDataReader value, bool printHeader) { var xy = ExcelOpenXmlUtils.ConvertCellToXY("A1"); writer.Write($@"<?xml version=""1.0"" encoding=""utf-8""?><x:worksheet xmlns:x=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">"); { var yIndex = xy.Item2; // TODO: dimension //var maxRowIndex = value.Rows.Count + (printHeader && value.Rows.Count > 0 ? 1 : 0); //var maxColumnIndex = value.Columns.Count; //writer.Write($@"<x:dimension ref=""{GetDimensionRef(maxRowIndex, maxColumnIndex)}""/>"); writer.Write("<x:sheetData>"); int fieldCount = value.FieldCount; if (printHeader) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; for (int i = 0; i < fieldCount; i++) { var r = ExcelOpenXmlUtils.ConvertXyToCell(xIndex, yIndex); writer.Write($"<x:c r=\"{r}\" t=\"str\">"); writer.Write($"<x:v>{value.GetName(i)}"); writer.Write($"</x:v>"); writer.Write($"</x:c>"); xIndex++; } writer.Write($"</x:row>"); yIndex++; } while (value.Read()) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; for (int i = 0; i < fieldCount; i++) { var cellValue = value.GetValue(i); WriteCell(writer, yIndex, xIndex, cellValue); xIndex++; } writer.Write($"</x:row>"); yIndex++; } } writer.Write("</x:sheetData></x:worksheet>"); }
private void GenerateSheetByDataTable(StreamWriter writer, MiniExcelZipArchive archive, DataTable value, bool printHeader) { var xy = ExcelOpenXmlUtils.ConvertCellToXY("A1"); //GOTO Top Write: writer.Write($@"<?xml version=""1.0"" encoding=""utf-8""?><x:worksheet xmlns:x=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">"); { var yIndex = xy.Item2; // dimension var maxRowIndex = value.Rows.Count + (printHeader && value.Rows.Count > 0 ? 1 : 0); var maxColumnIndex = value.Columns.Count; writer.Write($@"<x:dimension ref=""{GetDimensionRef(maxRowIndex, maxColumnIndex)}""/><x:sheetData>"); if (printHeader) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; foreach (DataColumn c in value.Columns) { var r = ExcelOpenXmlUtils.ConvertXyToCell(xIndex, yIndex); writer.Write($"<x:c r=\"{r}\" t=\"str\">"); writer.Write($"<x:v>{c.Caption ?? c.ColumnName}"); writer.Write($"</x:v>"); writer.Write($"</x:c>"); xIndex++; } writer.Write($"</x:row>"); yIndex++; } for (int i = 0; i < value.Rows.Count; i++) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; for (int j = 0; j < value.Columns.Count; j++) { var cellValue = value.Rows[i][j]; WriteCell(writer, yIndex, xIndex, cellValue); xIndex++; } writer.Write($"</x:row>"); yIndex++; } } writer.Write("</x:sheetData></x:worksheet>"); }
internal static void GenerateSheetByDataTable(StreamWriter writer, ZipArchive archive, DataTable value, bool printHeader) { var xy = ExcelOpenXmlUtils.ConvertCellToXY("A1"); //GOTO Top Write: writer.Write($@"<?xml version=""1.0"" encoding=""utf-8""?><x:worksheet xmlns:x=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">"); { var yIndex = xy.Item2; // dimension var maxRowIndex = value.Rows.Count + (printHeader && value.Rows.Count > 0 ? 1 : 0); var maxColumnIndex = value.Columns.Count; writer.Write($@"<dimension ref=""{GetDimension(maxRowIndex, maxColumnIndex)}""/><x:sheetData>"); if (printHeader) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; foreach (DataColumn c in value.Columns) { var columname = ExcelOpenXmlUtils.ConvertXyToCell(xIndex, yIndex); writer.Write($"<x:c r=\"{columname}\" t=\"str\">"); writer.Write($"<x:v>{c.ColumnName}"); writer.Write($"</x:v>"); writer.Write($"</x:c>"); xIndex++; } writer.Write($"</x:row>"); yIndex++; } for (int i = 0; i < value.Rows.Count; i++) { writer.Write($"<x:row r=\"{yIndex.ToString()}\">"); var xIndex = xy.Item1; for (int j = 0; j < value.Columns.Count; j++) { var cellValue = value.Rows[i][j]; var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var t = "t=\"str\""; { if (decimal.TryParse(cellValueStr, out var outV)) { t = "t=\"n\""; } if (cellValue is bool) { t = "t=\"b\""; cellValueStr = (bool)cellValue ? "1" : "0"; } if (cellValue is DateTime || cellValue is DateTime?) { t = "s=\"1\""; cellValueStr = ((DateTime)cellValue).ToOADate().ToString(); } } var columname = ExcelOpenXmlUtils.ConvertXyToCell(xIndex, yIndex); writer.Write($"<x:c r=\"{columname}\" {t}>"); writer.Write($"<x:v>{cellValueStr}"); writer.Write($"</x:v>"); writer.Write($"</x:c>"); xIndex++; } writer.Write($"</x:row>"); yIndex++; } } writer.Write("</x:sheetData></x:worksheet>"); }