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 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>");
}
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 UpdateDimensionAndGetRowsInfo(Dictionary <string, object> inputMaps, ref XmlDocument doc, ref XmlNodeList rows)
{
// note : dimension need to put on the top 
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 
{
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 
{
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 
{
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 
{
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}"; //
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) //
{
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: 
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) //
{
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: 
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) //
{
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: 
newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr);
}
}
// note: only first time need add diff 
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}"; //
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) //
{
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: 
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) //
{
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: 
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) //
{
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: 
newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr);
}
}
// note: only first time need add diff 
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}"; //
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) //
{
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: 
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) //
{
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: 
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) //
{
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: 
newRow.InnerXml = newRow.InnerXml.Replace(key, cellValueStr);
}
}
// note: only first time need add diff 
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]);
}
}
