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 ![image](https://user-images.githubusercontent.com/12729184/118770190-9eee3480-b8b3-11eb-9f5a-87a439f5e320.png) 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 ![image](https://user-images.githubusercontent.com/12729184/163686902-286abb70-877b-4e84-bd3b-001ad339a84a.png) } 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")); }