private static void WriteCell(StreamWriter writer, int yIndex, int cellIndex, object value, ExcelCustomPropertyInfo p)
{
var v = string.Empty;
var t = "str";
var s = "2";
if (value == null)
{
v = "";
}
else if (value is string)
{
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
else
{
Type type = null;
if (p == null)
{
type = value.GetType();
type = Nullable.GetUnderlyingType(type) ?? type;
}
else
{
type = p.ExcludeNullableType; //sometime it doesn't need to re-get type like prop
}
if (Helpers.IsNumericType(type))
{
t = "n";
v = value.ToString();
}
else if (type == typeof(bool))
{
t = "b";
v = (bool)value ? "1" : "0";
}
else if (type == typeof(DateTime))
{
if(p==null || p.ExcelFormat == null)
{
t = null;
s = "3";
v = ((DateTime)value).ToOADate().ToString();
}
else
{
t = "str";
v = ((DateTime)value).ToString(p.ExcelFormat);
}
}
else
{
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
}
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
//t check avoid format error 
writer.Write($"<x:c r=\"{columname}\" {(t == null ? "" : $"t =\"{t}\"")} s=\"{s}\"><x:v>{v}</x:v></x:c>");
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 static void WriteCell(StreamWriter writer, int yIndex, int cellIndex, object value)
{
var v = string.Empty;
var t = string.Empty;
if (value == null)
{
t = "t=\"str\"";
v = "";
}
else if (value is string)
{
t = "t=\"str\"";
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
else
{
var type = value.GetType();
type = Nullable.GetUnderlyingType(type) ?? type;
if (Helpers.IsNumericType(type))
{
t = "t=\"n\"";
v = value.ToString();
}
else if (type == typeof(bool))
{
t = "t=\"b\"";
v = (bool)value ? "1" : "0";
}
else if (type == typeof(DateTime))
{
t = "s=\"1\"";
v = ((DateTime)value).ToOADate().ToString();
}
else
{
t = "t=\"str\"";
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
}
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, yIndex);
writer.Write($"<x:c r=\"{columname}\" {t}><x:v>{v}</x:v></x:c>");
}
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++;
}
}
internal static void GenerateSheetByProperties(StreamWriter writer, ZipArchive archive, IEnumerable value, Type genericType, PropertyInfo[] props, bool printHeader, int rowCount, List <object> keys, 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)
{
var cellValue = p.GetValue(v);
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++;
}
}
internal void GenerateSheetByDapperRow(StreamWriter writer, ZipArchive archive, IEnumerable value, Type genericType, int rowCount, List <string> keys, int xIndex = 1, int yIndex = 1)
{
//body
foreach (IDictionary <string, object> 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++;
}
}
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>");
}
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);
}
}
private void WriteCell(MiniExcelStreamWriter writer, int rowIndex, int cellIndex, object value, ExcelColumnInfo p)
{
var v = string.Empty;
var t = "str";
var s = "2";
if (value == null)
{
v = "";
}
else if (value is string str)
{
v = ExcelOpenXmlUtils.EncodeXML(str);
}
else if (p?.ExcelFormat != null && value is IFormattable formattableValue)
{
var formattedStr = formattableValue.ToString(p.ExcelFormat, _configuration.Culture);
v = ExcelOpenXmlUtils.EncodeXML(formattedStr);
}
else
{
Type type = null;
if (p == null || p.Key != null)
{
// TODO: need to optimize
// Dictionary need to check type every time, so it's slow..
type = value.GetType();
type = Nullable.GetUnderlyingType(type) ?? type;
}
else
{
type = p.ExcludeNullableType; //sometime it doesn't need to re-get type like prop
}
if (type.IsEnum)
{
t = "str";
var description = CustomPropertyHelper.DescriptionAttr(type, value); //TODO: need to optimze
if (description != null)
{
v = description;
}
else
{
v = value.ToString();
}
}
else if (TypeHelper.IsNumericType(type))
{
if (_configuration.Culture != CultureInfo.InvariantCulture)
{
t = "str"; //TODO: add style format
}
else
{
t = "n";
}
if (type.IsAssignableFrom(typeof(decimal)))
{
v = ((decimal)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int32)))
{
v = ((Int32)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Double)))
{
v = ((Double)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int64)))
{
v = ((Int64)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt32)))
{
v = ((UInt32)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt16)))
{
v = ((UInt16)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(UInt64)))
{
v = ((UInt64)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Int16)))
{
v = ((Int16)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Single)))
{
v = ((Single)value).ToString(_configuration.Culture);
}
else if (type.IsAssignableFrom(typeof(Single)))
{
v = ((Single)value).ToString(_configuration.Culture);
}
else
{
v = (decimal.Parse(value.ToString())).ToString(_configuration.Culture);
}
}
else if (type == typeof(bool))
{
t = "b";
v = (bool)value ? "1" : "0";
}
else if (type == typeof(byte[]) && _configuration.EnableConvertByteArray)
{
var bytes = (byte[])value;
if (bytes != null)
{
// TODO: Setting configuration because it might have high cost?
var format = ImageHelper.GetImageFormat(bytes);
//it can't insert to zip first to avoid cache image to memory
//because sheet xml is opening.. https://github.com/shps951023/MiniExcel/issues/304#issuecomment-1017031691
//int rowIndex, int cellIndex
var file = new FileDto()
{
Byte = bytes,
RowIndex = rowIndex,
CellIndex = cellIndex,
SheetId = currentSheetIndex
};
if (format != ImageFormat.unknown)
{
file.Extension = format.ToString();
file.IsImage = true;
}
else
{
file.Extension = "bin";
}
_files.Add(file);
//TODO:Convert to base64
var base64 = $"@@@fileid@@@,{file.Path}";
v = ExcelOpenXmlUtils.EncodeXML(base64);
s = "4";
}
}
else if (type == typeof(DateTime))
{
if (_configuration.Culture != CultureInfo.InvariantCulture)
{
t = "str";
v = ((DateTime)value).ToString(_configuration.Culture);
}
else if (p == null || p.ExcelFormat == null)
{
t = null;
s = "3";
v = ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture);
}
else
{
// TODO: now it'll lose date type information
t = "str";
v = ((DateTime)value).ToString(p.ExcelFormat, _configuration.Culture);
}
}
else
{
//TODO: _configuration.Culture
v = ExcelOpenXmlUtils.EncodeXML(value.ToString());
}
}
var columname = ExcelOpenXmlUtils.ConvertXyToCell(cellIndex, rowIndex);
if (v != null && (v.StartsWith(" ", StringComparison.Ordinal) || v.EndsWith(" ", StringComparison.Ordinal))) /*Prefix and suffix blank space will lost after SaveAs #294*/
{
writer.Write($"<x:c r=\"{columname}\" {(t == null ? "" : $"t =\"{t}\"")} s=\"{s}\" xml:space=\"preserve\"><x:v>{v}</x:v></x:c>");
private void CreateSheetXml(object value, string sheetPath)
{
ZipArchiveEntry entry = _archive.CreateEntry(sheetPath);
using (var zipStream = entry.Open())
using (MiniExcelStreamWriter writer = new MiniExcelStreamWriter(zipStream, _utf8WithBom, _configuration.BufferSize))
{
if (value == null)
{
WriteEmptySheet(writer);
goto End; //for re-using code
}
var type = value.GetType();
Type genericType = null;
//DapperRow
if (value is IDataReader)
{
GenerateSheetByIDataReader(writer, value as IDataReader);
}
else if (value is IEnumerable)
{
var values = value as IEnumerable;
// try to get type from reflection
// genericType = null
var rowCount = 0;
var maxColumnIndex = 0;
//List<object> keys = new List<object>();
List <ExcelColumnInfo> props = null;
string mode = null;
// reason : https://stackoverflow.com/questions/66797421/how-replace-top-format-mark-after-MiniExcelStreamWriter-writing
// check mode & get maxRowCount & maxColumnIndex
{
foreach (var item in values) //TODO: need to optimize
{
rowCount = checked (rowCount + 1);
//TODO: if item is null but it's collection<T>, it can get T type from reflection
if (item != null && mode == null)
{
if (item is IDictionary <string, object> )
{
mode = "IDictionary<string, object>";
var dic = item as IDictionary <string, object>;
props = GetDictionaryColumnInfo(dic, null);
maxColumnIndex = props.Count;
}
else if (item is IDictionary)
{
var dic = item as IDictionary;
mode = "IDictionary";
props = GetDictionaryColumnInfo(null, dic);
//maxColumnIndex = dic.Keys.Count;
maxColumnIndex = props.Count; // why not using keys, because ignore attribute 
}
else
{
var _t = item.GetType();
if (_t != genericType)
{
genericType = item.GetType();
}
genericType = item.GetType();
SetGenericTypePropertiesMode(genericType, ref mode, out maxColumnIndex, out props);
}
var collection = value as ICollection;
if (collection != null)
{
rowCount = checked ((value as ICollection).Count);
break;
}
continue;
}
}
}
if (rowCount == 0)
{
// only when empty IEnumerable need to check this issue #133 https://github.com/shps951023/MiniExcel/issues/133
genericType = TypeHelper.GetGenericIEnumerables(values).FirstOrDefault();
if (genericType == null || genericType == typeof(object) || // sometime generic type will be object, e.g: https://user-images.githubusercontent.com/12729184/132812859-52984314-44d1-4ee8-9487-2d1da159f1f0.png
typeof(IDictionary <string, object>).IsAssignableFrom(genericType) ||
typeof(IDictionary).IsAssignableFrom(genericType))
{
WriteEmptySheet(writer);
goto End; //for re-using code
}
else
{
SetGenericTypePropertiesMode(genericType, ref mode, out maxColumnIndex, out props);
}
}
writer.Write($@"<?xml version=""1.0"" encoding=""utf-8""?><x:worksheet xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"" 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:width
var ecwProp = props?.Where(x => x?.ExcelColumnWidth != null).ToList();
if (ecwProp != null && ecwProp.Count > 0)
{
writer.Write($@"<x:cols>");
foreach (var p in ecwProp)
{
writer.Write($@"<x:col min=""{p.ExcelColumnIndex + 1}"" max=""{p.ExcelColumnIndex + 1}"" width=""{p.ExcelColumnWidth}"" customWidth=""1"" />");
}
writer.Write($@"</x:cols>");
}
//header
writer.Write($@"<x:sheetData>");
var yIndex = 1;
var xIndex = 1;
if (_printHeader)
{
var cellIndex = xIndex;
writer.Write($"<x:row r=\"{yIndex}\">");
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++;
}
writer.Write($"</x:row>");
yIndex++;
}
// body
if (mode == "IDictionary<string, object>") //Dapper Row
{
GenerateSheetByColumnInfo <IDictionary <string, object> >(writer, value as IEnumerable, props, xIndex, yIndex);
}
else if (mode == "IDictionary") //IDictionary
{
GenerateSheetByColumnInfo <IDictionary>(writer, value as IEnumerable, props, xIndex, yIndex);
}
else if (mode == "Properties")
{
GenerateSheetByColumnInfo <object>(writer, value as IEnumerable, props, xIndex, yIndex);
}
else
{
throw new NotImplementedException($"Type {type.Name} & genericType {genericType.Name} not Implemented. please issue for me.");
}
writer.Write("</x:sheetData>");
if (_configuration.AutoFilter)
{
writer.Write($"<x:autoFilter ref=\"A1:{ExcelOpenXmlUtils.ConvertXyToCell(maxColumnIndex, maxRowIndex == 0 ? 1 : maxRowIndex)}\" />");
}
writer.Write("<x:drawing r:id=\"drawing" + currentSheetIndex + "\" /></x:worksheet>");
}
else if (value is DataTable)
{
GenerateSheetByDataTable(writer, value as DataTable);
}
else
{
throw new NotImplementedException($"Type {type.Name} & genericType {genericType.Name} not Implemented. please issue for me.");
}
}
End: //for re-using code
_zipDictionary.Add(sheetPath, new ZipPackageInfo(entry, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"));
}
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"));
}
