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