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++; } }
internal void GenerateSheetByProperties(StreamWriter writer, ZipArchive archive, IEnumerable value, Type genericType, List <ExcelCustomPropertyInfo> props, 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.Property.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 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")); }
private void UpdateDimensionAndGetRowsInfo(Dictionary <string, object> inputMaps, ref XmlDocument doc, ref XmlNodeList rows) { // note : dimension need to put on the top ![image](https://user-images.githubusercontent.com/12729184/114507911-5dd88400-9c66-11eb-94c6-82ed7bdb5aab.png) var dimension = doc.SelectSingleNode("/x:worksheet/x:dimension", _ns) as XmlElement; if (dimension == null) { throw new NotImplementedException("Excel Dimension Xml is null, please issue file for me. https://github.com/shps951023/MiniExcel/issues"); } var maxRowIndexDiff = 0; foreach (XmlElement row in rows) { // ==== get ienumerable infomation & maxrowindexdiff ==== //Type ienumerableGenricType = null; //IDictionary<string, PropertyInfo> props = null; //IEnumerable ienumerable = null; var xRowInfo = new XRowInfo(); xRowInfo.Row = row; XRowInfos.Add(xRowInfo); foreach (XmlElement c in row.SelectNodes($"x:c", _ns)) { var r = c.GetAttribute("r"); // ==== mergecells ==== if (this.XMergeCellInfos.ContainsKey(r)) { if (xRowInfo.RowMercells == null) { xRowInfo.RowMercells = new List <XMergeCell>(); } xRowInfo.RowMercells.Add(this.XMergeCellInfos[r]); } c.SetAttribute("r", $"{StringHelper.GetLetter(r)}{{{{$rowindex}}}}"); //TODO: var v = c.SelectSingleNode("x:v", _ns); if (v?.InnerText == null) { continue; } var matchs = (_isExpressionRegex.Matches(v.InnerText).Cast <Match>().GroupBy(x => x.Value).Select(varGroup => varGroup.First().Value)).ToArray(); var matchCnt = matchs.Length; var isMultiMatch = matchCnt > 1 || (matchCnt == 1 && v.InnerText != $"{{{{{matchs[0]}}}}}"); foreach (var formatText in matchs) { xRowInfo.FormatText = formatText; var propNames = formatText.Split('.'); if (propNames[0].StartsWith("$")) //e.g:"$rowindex" it doesn't need to check cell value type { continue; } if (!inputMaps.ContainsKey(propNames[0])) { throw new System.Collections.Generic.KeyNotFoundException($"Please check {propNames[0]} parameter, it's not exist."); } var cellValue = inputMaps[propNames[0]]; // 1. From left to right, only the first set is used as the basis for the list if (cellValue is IEnumerable && !(cellValue is string)) { if (this.XMergeCellInfos.ContainsKey(r)) { if (xRowInfo.IEnumerableMercell == null) { xRowInfo.IEnumerableMercell = this.XMergeCellInfos[r]; } } xRowInfo.CellIEnumerableValues = cellValue as IEnumerable; // get ienumerable runtime type if (xRowInfo.IEnumerableGenricType == null) //avoid duplicate to add rowindexdiff ![image](https://user-images.githubusercontent.com/12729184/114851348-522ac000-9e14-11eb-8244-4730754d6885.png) { var first = true; //TODO:if CellIEnumerableValues is ICollection or Array then get length or Count foreach (var element in xRowInfo.CellIEnumerableValues) //TODO: optimize performance? { xRowInfo.CellIEnumerableValuesCount++; if (xRowInfo.IEnumerableGenricType == null) { if (element != null) { xRowInfo.IEnumerablePropName = propNames[0]; xRowInfo.IEnumerableGenricType = element.GetType(); if (element is IDictionary <string, object> ) { xRowInfo.IsDictionary = true; var dic = element as IDictionary <string, object>; xRowInfo.PropsMap = dic.Keys.ToDictionary(key => key, key => dic[key] != null ? new PropInfo { UnderlyingTypePropType = Nullable.GetUnderlyingType(dic[key].GetType()) ?? dic[key].GetType() } : new PropInfo { UnderlyingTypePropType = typeof(object) }); } else { xRowInfo.PropsMap = xRowInfo.IEnumerableGenricType.GetProperties() .ToDictionary(s => s.Name, s => new PropInfo { PropertyInfo = s, UnderlyingTypePropType = Nullable.GetUnderlyingType(s.PropertyType) ?? s.PropertyType }); } } } // ==== get demension max rowindex ==== if (!first) //avoid duplicate add first one, this row not add status ![image](https://user-images.githubusercontent.com/12729184/114851829-d2512580-9e14-11eb-8e7d-520c89a7ebee.png) { maxRowIndexDiff = maxRowIndexDiff + (xRowInfo.IEnumerableMercell == null ? 1 : xRowInfo.IEnumerableMercell.Height); } first = false; } } //TODO: check if not contain 1 index //only check first one match IEnumerable, so only render one collection at same row // auto check type https://github.com/shps951023/MiniExcel/issues/177 var prop = xRowInfo.PropsMap[propNames[1]]; var type = prop.UnderlyingTypePropType; //avoid nullable // if (!xRowInfo.PropsMap.ContainsKey(propNames[1])) { throw new InvalidDataException($"{propNames[0]} doesn't have {propNames[1]} property"); } if (isMultiMatch) { c.SetAttribute("t", "str"); } else if (Helpers.IsNumericType(type)) { c.SetAttribute("t", "n"); } else if (Type.GetTypeCode(type) == TypeCode.Boolean) { c.SetAttribute("t", "b"); } else if (Type.GetTypeCode(type) == TypeCode.DateTime) { c.SetAttribute("t", "str"); } break; } else if (cellValue is DataTable) { var dt = cellValue as DataTable; if (xRowInfo.CellIEnumerableValues == null) { xRowInfo.IEnumerablePropName = propNames[0]; xRowInfo.IEnumerableGenricType = typeof(DataRow); xRowInfo.IsDataTable = true; xRowInfo.CellIEnumerableValues = dt.Rows.Cast <DataRow>(); //TODO: need to optimize performance var first = true; foreach (var element in xRowInfo.CellIEnumerableValues) { // ==== get demension max rowindex ==== if (!first) //avoid duplicate add first one, this row not add status ![image](https://user-images.githubusercontent.com/12729184/114851829-d2512580-9e14-11eb-8e7d-520c89a7ebee.png) { maxRowIndexDiff++; } first = false; } //TODO:need to optimize //maxRowIndexDiff = dt.Rows.Count <= 1 ? 0 : dt.Rows.Count-1; xRowInfo.PropsMap = dt.Columns.Cast <DataColumn>().ToDictionary(col => col.ColumnName, col => new PropInfo { UnderlyingTypePropType = Nullable.GetUnderlyingType(col.DataType) } ); } var column = dt.Columns[propNames[1]]; var type = Nullable.GetUnderlyingType(column.DataType) ?? column.DataType; //avoid nullable if (!xRowInfo.PropsMap.ContainsKey(propNames[1])) { throw new InvalidDataException($"{propNames[0]} doesn't have {propNames[1]} property"); } if (isMultiMatch) { c.SetAttribute("t", "str"); } else if (Helpers.IsNumericType(type)) { c.SetAttribute("t", "n"); } else if (Type.GetTypeCode(type) == TypeCode.Boolean) { c.SetAttribute("t", "b"); } else if (Type.GetTypeCode(type) == TypeCode.DateTime) { c.SetAttribute("t", "str"); } } else { var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue?.ToString()); if (isMultiMatch) // if matchs count over 1 need to set type=str ![image](https://user-images.githubusercontent.com/12729184/114530109-39d46d00-9c7d-11eb-8f6b-52ad8600aca3.png) { c.SetAttribute("t", "str"); } else if (decimal.TryParse(cellValueStr, out var outV)) { c.SetAttribute("t", "n"); } else if (cellValue is bool) { c.SetAttribute("t", "b"); cellValueStr = (bool)cellValue ? "1" : "0"; } else if (cellValue is DateTime || cellValue is DateTime?) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } v.InnerText = v.InnerText.Replace($"{{{{{propNames[0]}}}}}", cellValueStr); //TODO: auto check type and set value } } //if (xRowInfo.CellIEnumerableValues != null) //2. From left to right, only the first set is used as the basis for the list // break; } } // e.g <dimension ref=\"A1:B6\" /> only need to update B6 to BMaxRowIndex var @refs = dimension.GetAttribute("ref").Split(':'); var letter = new String(refs[1].Where(Char.IsLetter).ToArray()); var digit = int.Parse(new String(refs[1].Where(Char.IsDigit).ToArray())); dimension.SetAttribute("ref", $"{refs[0]}:{letter}{digit + maxRowIndexDiff}"); }
private void WriteSheetXml(Stream stream, XmlDocument doc, XmlNode sheetData) { //Q.Why so complex? //A.Because try to use string stream avoid OOM when rendering rows sheetData.RemoveAll(); sheetData.InnerText = "{{{{{{split}}}}}}"; //TODO: bad code smell var prefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $"{sheetData.Prefix}:"; var endPrefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $":{sheetData.Prefix}"; //![image](https://user-images.githubusercontent.com/12729184/115000066-fd02b300-9ed4-11eb-8e65-bf0014015134.png) var contents = doc.InnerXml.Split(new string[] { $"<{prefix}sheetData>{{{{{{{{{{{{split}}}}}}}}}}}}</{prefix}sheetData>" }, StringSplitOptions.None);; using (var writer = new StreamWriter(stream, Encoding.UTF8)) { writer.Write(contents[0]); writer.Write($"<{prefix}sheetData>"); // prefix problem #region Generate rows and cells int originRowIndex; int rowIndexDiff = 0; foreach (var rowInfo in XRowInfos) { var row = rowInfo.Row; //TODO: some xlsx without r originRowIndex = int.Parse(row.GetAttribute("r")); var newRowIndex = originRowIndex + rowIndexDiff; if (rowInfo.CellIEnumerableValues != null) { var first = true; var iEnumerableIndex = 0; foreach (var item in rowInfo.CellIEnumerableValues) { iEnumerableIndex++; var newRow = row.Clone() as XmlElement; newRow.SetAttribute("r", newRowIndex.ToString()); newRow.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); if (rowInfo.IsDictionary) { var dic = item as IDictionary <string, object>; foreach (var propInfo in rowInfo.PropsMap) { var key = $"{{{{{rowInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = dic[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue?.ToString()); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else if (rowInfo.IsDataTable) { var datarow = item as DataRow; foreach (var propInfo in rowInfo.PropsMap) { var key = $"{{{{{rowInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = datarow[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue?.ToString()); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else { foreach (var propInfo in rowInfo.PropsMap) { var prop = propInfo.Value.PropertyInfo; var key = $"{{{{{rowInfo.IEnumerablePropName}.{prop.Name}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = prop.GetValue(item); if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue?.ToString()); var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } // note: only first time need add diff ![image](https://user-images.githubusercontent.com/12729184/114494728-6bceda80-9c4f-11eb-9685-8b5ed054eabe.png) if (!first) { //rowIndexDiff++; rowIndexDiff = rowIndexDiff + (rowInfo.IEnumerableMercell == null ? 1 : rowInfo.IEnumerableMercell.Height); //TODO:base on the merge size } first = false; var mergeBaseRowIndex = newRowIndex; newRowIndex = newRowIndex + (rowInfo.IEnumerableMercell == null ? 1 : rowInfo.IEnumerableMercell.Height); writer.Write(CleanXml(newRow.OuterXml, endPrefix)); newRow = null; //mergecells if (rowInfo.RowMercells != null) { foreach (var mergeCell in rowInfo.RowMercells) { var newMergeCell = new XMergeCell(mergeCell); newMergeCell.Y1 = newMergeCell.Y1 + rowIndexDiff; newMergeCell.Y2 = newMergeCell.Y2 + rowIndexDiff; this.NewXMergeCellInfos.Add(newMergeCell); } // Last merge one don't add new row, or it'll get duplicate result like : https://github.com/shps951023/MiniExcel/issues/207#issuecomment-824550950 if (iEnumerableIndex == rowInfo.CellIEnumerableValuesCount) { continue; } // https://github.com/shps951023/MiniExcel/issues/207#issuecomment-824518897 for (int i = 1; i < rowInfo.IEnumerableMercell.Height; i++) { mergeBaseRowIndex++; var _newRow = row.Clone() as XmlElement; _newRow.SetAttribute("r", mergeBaseRowIndex.ToString()); var cs = _newRow.SelectNodes($"x:c", _ns); // all v replace by empty // TODO: remove c/v foreach (XmlElement _c in cs) { _c.RemoveAttribute("t"); foreach (XmlNode ch in _c.ChildNodes) { _c.RemoveChild(ch); } } _newRow.InnerXml = _newRow.InnerXml.Replace($"{{{{$rowindex}}}}", mergeBaseRowIndex.ToString()); writer.Write(CleanXml(_newRow.OuterXml, endPrefix)); } } } } else { row.SetAttribute("r", newRowIndex.ToString()); row.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); writer.Write(CleanXml(row.OuterXml, endPrefix)); //mergecells if (rowInfo.RowMercells != null) { foreach (var mergeCell in rowInfo.RowMercells) { var newMergeCell = new XMergeCell(mergeCell); newMergeCell.Y1 = newMergeCell.Y1 + rowIndexDiff; newMergeCell.Y2 = newMergeCell.Y2 + rowIndexDiff; this.NewXMergeCellInfos.Add(newMergeCell); } } } // get the row's all mergecells then update the rowindex } #endregion writer.Write($"</{prefix}sheetData>"); if (this.NewXMergeCellInfos.Count != 0) { writer.Write($"<{prefix}mergeCells count=\"{this.NewXMergeCellInfos.Count}\">"); foreach (var cell in this.NewXMergeCellInfos) { writer.Write(cell.ToXmlString(prefix)); } writer.Write($"</{prefix}mergeCells>"); } writer.Write(contents[1]); } }
private void GenerateSheetXmlImpl(ZipArchiveEntry sheetZipEntry, Stream stream, Stream sheetStream, Dictionary <string, object> inputMaps, List <string> sharedStrings, XmlWriterSettings xmlWriterSettings = null) { var doc = new XmlDocument(); doc.Load(sheetStream); sheetStream.Dispose(); sheetZipEntry.Delete(); // ZipArchiveEntry can't update directly, so need to delete then create logic var worksheet = doc.SelectSingleNode("/x:worksheet", _ns); var sheetData = doc.SelectSingleNode("/x:worksheet/x:sheetData", _ns); var newSheetData = sheetData.Clone(); //avoid delete lost data var rows = newSheetData.SelectNodes($"x:row", _ns); ReplaceSharedStringsToStr(sharedStrings, ref rows); //Update dimension && Check if the column contains a collection and get type and properties infomations UpdateDimensionAndGetCollectionPropertiesInfos(inputMaps, ref doc, ref rows); #region Render cell values //Q.Why so complex? //A.Because try to use string stream avoid OOM when rendering rows sheetData.RemoveAll(); sheetData.InnerText = "{{{{{{split}}}}}}"; //TODO: bad smell var prefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $"{sheetData.Prefix}:"; var endPrefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $":{sheetData.Prefix}"; //![image](https://user-images.githubusercontent.com/12729184/115000066-fd02b300-9ed4-11eb-8e65-bf0014015134.png) var contents = doc.InnerXml.Split(new string[] { $"<{prefix}sheetData>{{{{{{{{{{{{split}}}}}}}}}}}}</{prefix}sheetData>" }, StringSplitOptions.None);; using (var writer = new StreamWriter(stream, Encoding.UTF8)) { writer.Write(contents[0]); writer.Write($"<{prefix}sheetData>"); // prefix problem int originRowIndex; int rowIndexDiff = 0; foreach (var xInfo in XRowInfos) { var row = xInfo.Row; //TODO: some xlsx without r originRowIndex = int.Parse(row.GetAttribute("r")); var newRowIndex = originRowIndex + rowIndexDiff; if (xInfo.CellIEnumerableValues != null) { var first = true; foreach (var item in xInfo.CellIEnumerableValues) { var newRow = row.Clone() as XmlElement; newRow.SetAttribute("r", newRowIndex.ToString()); newRow.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); if (xInfo.IsDictionary) { var dic = item as IDictionary <string, object>; foreach (var propInfo in xInfo.PropsMap) { var key = $"{{{{{xInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = dic[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else if (xInfo.IsDataTable) { var datarow = item as DataRow; foreach (var propInfo in xInfo.PropsMap) { var key = $"{{{{{xInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = datarow[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else { foreach (var propInfo in xInfo.PropsMap) { var prop = propInfo.Value.PropertyInfo; var key = $"{{{{{xInfo.IEnumerablePropName}.{prop.Name}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = prop.GetValue(item); if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } // note: only first time need add diff ![image](https://user-images.githubusercontent.com/12729184/114494728-6bceda80-9c4f-11eb-9685-8b5ed054eabe.png) if (!first) { rowIndexDiff++; } first = false; newRowIndex++; writer.Write(CleanXml(newRow.OuterXml, endPrefix)); newRow = null; } } else { row.SetAttribute("r", newRowIndex.ToString()); row.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); writer.Write(CleanXml(row.OuterXml, endPrefix)); } } writer.Write($"</{prefix}sheetData>"); writer.Write(contents[1]); } #endregion }
private void RenderRowsAndCells(Stream stream, XmlDocument doc, XmlNode sheetData) { //Q.Why so complex? //A.Because try to use string stream avoid OOM when rendering rows sheetData.RemoveAll(); sheetData.InnerText = "{{{{{{split}}}}}}"; //TODO: bad smell var prefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $"{sheetData.Prefix}:"; var endPrefix = string.IsNullOrEmpty(sheetData.Prefix) ? "" : $":{sheetData.Prefix}"; //![image](https://user-images.githubusercontent.com/12729184/115000066-fd02b300-9ed4-11eb-8e65-bf0014015134.png) var contents = doc.InnerXml.Split(new string[] { $"<{prefix}sheetData>{{{{{{{{{{{{split}}}}}}}}}}}}</{prefix}sheetData>" }, StringSplitOptions.None);; using (var writer = new StreamWriter(stream, Encoding.UTF8)) { writer.Write(contents[0]); writer.Write($"<{prefix}sheetData>"); // prefix problem int originRowIndex; int rowIndexDiff = 0; foreach (var xInfo in XRowInfos) { var row = xInfo.Row; //TODO: some xlsx without r originRowIndex = int.Parse(row.GetAttribute("r")); var newRowIndex = originRowIndex + rowIndexDiff; if (xInfo.CellIEnumerableValues != null) { var first = true; foreach (var item in xInfo.CellIEnumerableValues) { var newRow = row.Clone() as XmlElement; newRow.SetAttribute("r", newRowIndex.ToString()); newRow.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); if (xInfo.IsDictionary) { var dic = item as IDictionary <string, object>; foreach (var propInfo in xInfo.PropsMap) { var key = $"{{{{{xInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = dic[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else if (xInfo.IsDataTable) { var datarow = item as DataRow; foreach (var propInfo in xInfo.PropsMap) { var key = $"{{{{{xInfo.IEnumerablePropName}.{propInfo.Key}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = datarow[propInfo.Key]; if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = propInfo.Value.UnderlyingTypePropType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } else { foreach (var propInfo in xInfo.PropsMap) { var prop = propInfo.Value.PropertyInfo; var key = $"{{{{{xInfo.IEnumerablePropName}.{prop.Name}}}}}"; if (item == null) //![image](https://user-images.githubusercontent.com/12729184/114728510-bc3e5900-9d71-11eb-9721-8a414dca21a0.png) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValue = prop.GetValue(item); if (cellValue == null) { newRow.InnerXml = newRow.InnerXml.Replace(key, ""); continue; } var cellValueStr = ExcelOpenXmlUtils.EncodeXML(cellValue); var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; if (type == typeof(bool)) { cellValueStr = (bool)cellValue ? "1" : "0"; } else if (type == typeof(DateTime)) { //c.SetAttribute("t", "d"); cellValueStr = ((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss"); } //TODO: ![image](https://user-images.githubusercontent.com/12729184/114848248-17735880-9e11-11eb-8258-63266bda0a1a.png) newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr); } } // note: only first time need add diff ![image](https://user-images.githubusercontent.com/12729184/114494728-6bceda80-9c4f-11eb-9685-8b5ed054eabe.png) if (!first) { rowIndexDiff++; } first = false; newRowIndex++; writer.Write(CleanXml(newRow.OuterXml, endPrefix)); newRow = null; } } else { row.SetAttribute("r", newRowIndex.ToString()); row.InnerXml = row.InnerXml.Replace($"{{{{$rowindex}}}}", newRowIndex.ToString()); writer.Write(CleanXml(row.OuterXml, endPrefix)); } } writer.Write($"</{prefix}sheetData>"); writer.Write(contents[1]); } }