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 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);
}
}
}
internal static void SaveAs(Stream stream, object value, bool printHeader)
{
using (var archive = new ZipArchive(stream, ZipArchiveMode.Create, true, Utf8WithBom))
{
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive);
var sheetPath = "xl/worksheets/sheet1.xml";
{
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();
//var genericType = type.GetGenericArguments()[0]; not 100% right
Type genericType = null;
//DapperRow
if (value is IEnumerable)
{
var values = value as IEnumerable;
var rowCount = 0;
var maxColumnIndex = 0;
List <object> keys = new List <object>();
PropertyInfo[] props = null;
string mode = null;
{
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();
props = Helpers.GetProperties(genericType);
//props = genericType.GetProperties();
if (props.Length == 0)
{
throw new InvalidOperationException($"Generic Type : {genericType} valid properties count is 0, if you have trouble please issue for me.");
}
maxColumnIndex = props.Length;
}
// 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($@"<dimension ref=""{GetDimension(maxRowIndex, maxColumnIndex)}""/><x:sheetData>");
//header
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)
{
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" t=\"str\"><x:v>{p.Name}</x:v></x:c>");
cellIndex++;
}
}
else
{
foreach (var key in keys)
{
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" t=\"str\"><x:v>{key}</x:v></x:c>");
cellIndex++;
}
}
writer.Write($"</x:row>");
yIndex++;
}
if (mode == "IDictionary<string, object>") //Dapper Row
{
GenerateSheetByDapperRow(writer, archive, value as IEnumerable, genericType, printHeader, rowCount, keys.Cast <string>().ToList(), xIndex, yIndex);
}
else if (mode == "IDictionary") //IDictionary
{
GenerateSheetByIDictionary(writer, archive, value as IEnumerable, genericType, printHeader, rowCount, keys, xIndex, yIndex);
}
else if (mode == "Properties")
{
GenerateSheetByProperties(writer, archive, value as IEnumerable, genericType, props, printHeader, rowCount, keys, 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.");
}
//TODO:
}
End:
packages.Add(sheetPath, new ZipPackageInfo(entry, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
}
GenerateContentTypesXml(archive, packages);
}
}
public void SaveAs(object value, bool printHeader, IConfiguration configuration)
{
using (var archive = new MiniExcelZipArchive(_stream, ZipArchiveMode.Create, true, _utf8WithBom))
{
var packages = DefualtOpenXml.GenerateDefaultOpenXml(archive);
var sheetPath = "xl/worksheets/sheet1.xml";
{
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 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=""{GetDimension(maxRowIndex, maxColumnIndex)}""/><x:sheetData>");
//header
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 columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" t=\"str\"><x:v>{p.ExcelColumnName}</x:v></x:c>");
cellIndex++;
}
}
else
{
foreach (var key in keys)
{
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" t=\"str\"><x:v>{key}</x:v></x:c>");
cellIndex++;
}
}
writer.Write($"</x:row>");
yIndex++;
}
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.");
}
//TODO:
}
End:
packages.Add(sheetPath, new ZipPackageInfo(entry, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
}
GenerateContentTypesXml(archive, packages);
}
}