public void SaveAs(object value, string sheetName, bool printHeader, IConfiguration configuration)
{
using (var archive = new MiniExcelZipArchive(_stream, ZipArchiveMode.Create, true, _utf8WithBom))
{
if (value is IDictionary <string, object> )
{
var sheetId = 0;
var sheets = value as IDictionary <string, object>;
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive, sheets.Keys);
foreach (var sheet in sheets)
{
sheetId++;
var sheetPath = $"xl/worksheets/sheet{sheetId}.xml";
CreateSheetXml(sheet.Value, printHeader, archive, packages, sheetPath);
}
GenerateContentTypesXml(archive, packages);
}
else
{
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive, new[] { sheetName });
var sheetPath = "xl/worksheets/sheet1.xml";
CreateSheetXml(value, printHeader, archive, packages, sheetPath);
GenerateContentTypesXml(archive, packages);
}
}
}
public ExcelOpenXmlSheetWriter(Stream stream, object value, string sheetName, IConfiguration configuration, bool printHeader)
{
this._stream = stream;
this._archive = new MiniExcelZipArchive(_stream, ZipArchiveMode.Create, true, _utf8WithBom);
this._configuration = configuration as OpenXmlConfiguration ?? OpenXmlConfiguration.DefaultConfig;
this._printHeader = printHeader;
this._value = value;
_sheets.Add(new SheetDto {
Name = sheetName, SheetIdx = 1
}); //TODO:remove
}
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>");
}
private void GenerateSheetByIDictionary(StreamWriter writer, MiniExcelZipArchive archive, IEnumerable value, int rowCount, List<object> keys, int xIndex = 1, int yIndex = 1)
{
foreach (IDictionary v in value)
{
writer.Write($"<x:row r=\"{yIndex.ToString()}\">");
var cellIndex = xIndex;
foreach (var key in keys)
{
var cellValue = v[key];
WriteCell(writer, yIndex, cellIndex, cellValue,null);
cellIndex++;
}
writer.Write($"</x:row>");
yIndex++;
}
}
public void SaveAs(object value, string sheetName, bool printHeader, IConfiguration configuration)
{
OpenXmlConfiguration config = configuration as OpenXmlConfiguration ?? OpenXmlConfiguration.DefaultConfig;
using (var archive = new MiniExcelZipArchive(_stream, ZipArchiveMode.Create, true, _utf8WithBom))
{
if (value is IDictionary<string, object>)
{
var sheetId = 0;
var sheets = value as IDictionary<string, object>;
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive, sheets.Keys, config);
foreach (var sheet in sheets)
{
sheetId++;
var sheetPath = $"xl/worksheets/sheet{sheetId}.xml";
CreateSheetXml(sheet.Value, printHeader, archive, packages, sheetPath);
}
GenerateContentTypesXml(archive, packages);
}
else if (value is DataSet)
{
var sheetId = 0;
var sheets = value as DataSet;
var keys = new List<string>();
foreach (DataTable dt in sheets.Tables)
{
keys.Add(dt.TableName);
}
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive, keys, config);
foreach (DataTable dt in sheets.Tables)
{
sheetId++;
var sheetPath = $"xl/worksheets/sheet{sheetId}.xml";
CreateSheetXml(dt, printHeader, archive, packages, sheetPath);
}
GenerateContentTypesXml(archive, packages);
}
else
{
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive, new[] { sheetName }, config);
var sheetPath = "xl/worksheets/sheet1.xml";
CreateSheetXml(value, printHeader, archive, packages, sheetPath);
GenerateContentTypesXml(archive, packages);
}
}
}
private void GenerateContentTypesXml(MiniExcelZipArchive archive, Dictionary <string, ZipPackageInfo> packages)
{
//[Content_Types].xml
var sb = new StringBuilder(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><Types xmlns=""http://schemas.openxmlformats.org/package/2006/content-types""><Default ContentType=""application/xml"" Extension=""xml""/><Default ContentType=""application/vnd.openxmlformats-package.relationships+xml"" Extension=""rels""/>");
foreach (var p in packages)
{
sb.Append($"<Override ContentType=\"{p.Value.ContentType}\" PartName=\"/{p.Key}\" />");
}
sb.Append("</Types>");
ZipArchiveEntry entry = archive.CreateEntry("[Content_Types].xml");
using (var zipStream = entry.Open())
using (StreamWriter writer = new StreamWriter(zipStream, _utf8WithBom))
writer.Write(sb.ToString());
}
internal void GenerateSheetByProperties(StreamWriter writer, MiniExcelZipArchive archive, IEnumerable value, List <ExcelCustomPropertyInfo> props, int rowCount, int xIndex = 1, int yIndex = 1)
{
//body
foreach (var v in value)
{
writer.Write($"<x:row r=\"{yIndex.ToString()}\">");
var cellIndex = xIndex;
foreach (var p in props)
{
if (p == null) //reason:https://github.com/shps951023/MiniExcel/issues/142
{
cellIndex++;
continue;
}
var cellValue = p.Property.GetValue(v);
var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue);
var t = "t=\"str\"";
{
if (decimal.TryParse(cellValueStr, out var outV))
{
t = "t=\"n\"";
}
else if (cellValue is bool)
{
t = "t=\"b\"";
cellValueStr = (bool)cellValue ? "1" : "0";
}
else if (cellValue is DateTime || cellValue is DateTime?)
{
t = "s=\"1\"";
cellValueStr = ((DateTime)cellValue).ToOADate().ToString();
}
}
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" {t}>");
writer.Write($"<x:v>{cellValueStr}");
writer.Write($"</x:v>");
writer.Write($"</x:c>");
cellIndex++;
}
writer.Write($"</x:row>");
yIndex++;
}
}
private void GenerateSheetByProperties(StreamWriter writer, MiniExcelZipArchive archive, IEnumerable value, List<ExcelCustomPropertyInfo> props, int rowCount, int xIndex = 1, int yIndex = 1)
{
foreach (var v in value)
{
writer.Write($"<x:row r=\"{yIndex.ToString()}\">");
var cellIndex = xIndex;
foreach (var p in props)
{
if (p == null) //reason:https://github.com/shps951023/MiniExcel/issues/142
{
cellIndex++;
continue;
}
var cellValue = p.Property.GetValue(v);
WriteCell(writer, yIndex, cellIndex, cellValue,p);
cellIndex++;
}
writer.Write($"</x:row>");
yIndex++;
}
}
internal void GenerateSheetByIDictionary(StreamWriter writer, MiniExcelZipArchive archive, IEnumerable value, int rowCount, List <object> keys, int xIndex = 1, int yIndex = 1)
{
//body
foreach (IDictionary v in value)
{
writer.Write($"<x:row r=\"{yIndex.ToString()}\">");
var cellIndex = xIndex;
foreach (var key in keys)
{
var cellValue = v[key];
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(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" {t}>");
writer.Write($"<x:v>{cellValueStr}");
writer.Write($"</x:v>");
writer.Write($"</x:c>");
cellIndex++;
}
writer.Write($"</x:row>");
yIndex++;
}
}
private void CreateSheetXml(object value, bool printHeader, MiniExcelZipArchive archive, Dictionary<string, ZipPackageInfo> packages, string sheetPath)
{
ZipArchiveEntry entry = archive.CreateEntry(sheetPath);
using (var zipStream = entry.Open())
using (StreamWriter writer = new StreamWriter(zipStream, _utf8WithBom))
{
if (value == null)
{
WriteEmptySheet(writer);
goto End;
}
var type = value.GetType();
Type genericType = null;
//DapperRow
if (value is IDataReader)
{
GenerateSheetByIDataReader(writer, archive, value as IDataReader, printHeader);
}
else if (value is IEnumerable)
{
var values = value as IEnumerable;
var rowCount = 0;
var maxColumnIndex = 0;
List<object> keys = new List<object>();
List<ExcelCustomPropertyInfo> props = null;
string mode = null;
// reason : https://stackoverflow.com/questions/66797421/how-replace-top-format-mark-after-streamwriter-writing
// check mode & get maxRowCount & maxColumnIndex
{
foreach (var item in values) //TODO: need to optimize
{
rowCount = checked(rowCount + 1);
if (item != null && mode == null)
{
if (item is IDictionary<string, object>)
{
var item2 = item as IDictionary<string, object>;
mode = "IDictionary<string, object>";
maxColumnIndex = item2.Keys.Count;
foreach (var key in item2.Keys)
keys.Add(key);
}
else if (item is IDictionary)
{
var item2 = item as IDictionary;
mode = "IDictionary";
maxColumnIndex = item2.Keys.Count;
foreach (var key in item2.Keys)
keys.Add(key);
}
else
{
mode = "Properties";
genericType = item.GetType();
if (genericType.IsValueType)
throw new NotImplementedException($"MiniExcel not support only {genericType.Name} value generic type");
else if (genericType == typeof(string) || genericType == typeof(DateTime) || genericType == typeof(Guid))
throw new NotImplementedException($"MiniExcel not support only {genericType.Name} generic type");
props = Helpers.GetSaveAsProperties(genericType);
maxColumnIndex = props.Count;
}
// not re-foreach key point
var collection = value as ICollection;
if (collection != null)
{
rowCount = checked((value as ICollection).Count);
break;
}
continue;
}
}
}
if (rowCount == 0)
{
WriteEmptySheet(writer);
goto End;
}
writer.Write($@"<?xml version=""1.0"" encoding=""utf-8""?><x:worksheet xmlns:x=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">");
// dimension
var maxRowIndex = rowCount + (printHeader && rowCount > 0 ? 1 : 0); //TODO:it can optimize
writer.Write($@"<x:dimension ref=""{GetDimensionRef(maxRowIndex, maxColumnIndex)}""/>");
//cols
//header
writer.Write($@"<x:sheetData>");
var yIndex = 1;
var xIndex = 1;
if (printHeader)
{
var cellIndex = xIndex;
writer.Write($"<x:row r=\"{yIndex.ToString()}\">");
if (props != null)
{
foreach (var p in props)
{
if (p == null)
{
cellIndex++; //reason : https://github.com/shps951023/MiniExcel/issues/142
continue;
}
var r = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
WriteC(writer, r, columnName: p.ExcelColumnName);
cellIndex++;
}
}
else
{
foreach (var key in keys)
{
var r = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
WriteC(writer, r, columnName: key.ToString());
cellIndex++;
}
}
writer.Write($"</x:row>");
yIndex++;
}
// body
if (mode == "IDictionary<string, object>") //Dapper Row
GenerateSheetByDapperRow(writer, archive, value as IEnumerable, rowCount, keys.Cast<string>().ToList(), xIndex, yIndex);
else if (mode == "IDictionary") //IDictionary
GenerateSheetByIDictionary(writer, archive, value as IEnumerable, rowCount, keys, xIndex, yIndex);
else if (mode == "Properties")
GenerateSheetByProperties(writer, archive, value as IEnumerable, props, rowCount, xIndex, yIndex);
else
throw new NotImplementedException($"Type {type.Name} & genericType {genericType.Name} not Implemented. please issue for me.");
writer.Write("</x:sheetData></x:worksheet>");
}
else if (value is DataTable)
{
GenerateSheetByDataTable(writer, archive, value as DataTable, printHeader);
}
else
{
throw new NotImplementedException($"Type {type.Name} & genericType {genericType.Name} not Implemented. please issue for me.");
}
}
End:
packages.Add(sheetPath, new ZipPackageInfo(entry, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
}
internal 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=""{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>");
}
