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