public static IEnumerable <string> GetSheetNames(this Stream stream)
{
var archive = new ExcelOpenXmlZip(stream);
foreach (var item in ExcelOpenXmlSheetReader.GetWorkbookRels(archive.Entries))
{
yield return(item.Name);
}
}
public void SaveAsByTemplateImpl(Stream templateStream, object value)
{
//only support xlsx
Dictionary <string, object> values = null;
if (value is Dictionary <string, object> )
{
values = value as Dictionary <string, object>;
}
else
{
var type = value.GetType();
var props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
values = new Dictionary <string, object>();
foreach (var p in props)
{
values.Add(p.Name, p.GetValue(value));
}
}
{
templateStream.CopyTo(stream);
var reader = new ExcelOpenXmlSheetReader(stream);
var _archive = new ExcelOpenXmlZip(stream, mode: ZipArchiveMode.Update, true, Encoding.UTF8);
{
//read sharedString
var sharedStrings = reader.GetSharedStrings();
//read all xlsx sheets
var sheets = _archive.ZipFile.Entries.Where(w => w.FullName.StartsWith("xl/worksheets/sheet", StringComparison.OrdinalIgnoreCase) ||
w.FullName.StartsWith("/xl/worksheets/sheet", StringComparison.OrdinalIgnoreCase)
).ToList();
foreach (var sheet in sheets)
{
this.XRowInfos = new List <XRowInfo>(); //every time need to use new XRowInfos or it'll cause duplicate problem: https://user-images.githubusercontent.com/12729184/115003101-0fcab700-9ed8-11eb-9151-ca4d7b86d59e.png
this.XMergeCellInfos = new Dictionary <string, XMergeCell>();
this.NewXMergeCellInfos = new List <XMergeCell>();
var sheetStream = sheet.Open();
var fullName = sheet.FullName;
ZipArchiveEntry entry = _archive.ZipFile.CreateEntry(fullName);
using (var zipStream = entry.Open())
{
GenerateSheetXmlImpl(sheet, zipStream, sheetStream, values, sharedStrings);
//doc.Save(zipStream); //don't do it beacause : 
}
}
}
_archive.ZipFile.Dispose();
}
}
internal Dictionary <int, string> GetSharedStrings(ExcelOpenXmlZip archive)
{
var sharedStringsEntry = archive.GetEntry("xl/sharedStrings.xml");
if (sharedStringsEntry == null)
{
return(null);
}
using (var stream = sharedStringsEntry.Open())
{
var xl = XElement.Load(stream);
var ts = xl.Descendants(ExcelOpenXmlXName.T).Select((s, i) => new { i, v = s.Value?.ToString() })
.ToDictionary(s => s.i, s => s.v)
;//TODO:need recode
return(ts);
}
}
public ExcelOpenXmlStyles(ExcelOpenXmlZip zip)
{
using (var Reader = zip.GetXmlReader(@"xl/styles.xml"))
{
if (!Reader.IsStartElement("styleSheet", NsSpreadsheetMl))
{
return;
}
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
return;
}
while (!Reader.EOF)
{
if (Reader.IsStartElement("cellXfs", NsSpreadsheetMl))
{
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
return;
}
var index = 0;
while (!Reader.EOF)
{
if (Reader.IsStartElement("xf", NsSpreadsheetMl))
{
int.TryParse(Reader.GetAttribute("xfId"), out var xfId);
int.TryParse(Reader.GetAttribute("numFmtId"), out var numFmtId);
_cellXfs.Add(index, new StyleRecord()
{
XfId = xfId, NumFmtId = numFmtId
});
Reader.Skip();
index++;
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
else if (Reader.IsStartElement("cellStyleXfs", NsSpreadsheetMl))
{
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
return;
}
var index = 0;
while (!Reader.EOF)
{
if (Reader.IsStartElement("xf", NsSpreadsheetMl))
{
int.TryParse(Reader.GetAttribute("xfId"), out var xfId);
int.TryParse(Reader.GetAttribute("numFmtId"), out var numFmtId);
_cellStyleXfs.Add(index, new StyleRecord()
{
XfId = xfId, NumFmtId = numFmtId
});
Reader.Skip();
index++;
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
}
public static List <string> GetSheetNames(this Stream stream)
{
var archive = new ExcelOpenXmlZip(stream);
return(new ExcelOpenXmlSheetReader(stream, null).GetWorkbookRels(archive.entries).Select(s => s.Name).ToList());
}
public ExcelOpenXmlSheetReader(Stream stream)
{
_archive = new ExcelOpenXmlZip(stream);
}
public ExcelOpenXmlStyles(ExcelOpenXmlZip zip)
{
using (var Reader = zip.GetXmlReader(@"xl/styles.xml"))
{
if (!XmlReaderHelper.IsStartElement(Reader, "styleSheet", _ns))
{
return;
}
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
return;
}
while (!Reader.EOF)
{
if (XmlReaderHelper.IsStartElement(Reader, "cellXfs", _ns))
{
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
continue;
}
var index = 0;
while (!Reader.EOF)
{
if (XmlReaderHelper.IsStartElement(Reader, "xf", _ns))
{
int.TryParse(Reader.GetAttribute("xfId"), out var xfId);
int.TryParse(Reader.GetAttribute("numFmtId"), out var numFmtId);
_cellXfs.Add(index, new StyleRecord()
{
XfId = xfId, NumFmtId = numFmtId
});
Reader.Skip();
index++;
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
else if (XmlReaderHelper.IsStartElement(Reader, "cellStyleXfs", _ns))
{
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
continue;
}
var index = 0;
while (!Reader.EOF)
{
if (XmlReaderHelper.IsStartElement(Reader, "xf", _ns))
{
int.TryParse(Reader.GetAttribute("xfId"), out var xfId);
int.TryParse(Reader.GetAttribute("numFmtId"), out var numFmtId);
_cellStyleXfs.Add(index, new StyleRecord()
{
XfId = xfId, NumFmtId = numFmtId
});
Reader.Skip();
index++;
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
else if (XmlReaderHelper.IsStartElement(Reader, "numFmts", _ns))
{
if (!XmlReaderHelper.ReadFirstContent(Reader))
{
continue;
}
while (!Reader.EOF)
{
if (XmlReaderHelper.IsStartElement(Reader, "numFmt", _ns))
{
int.TryParse(Reader.GetAttribute("numFmtId"), out var numFmtId);
var formatCode = Reader.GetAttribute("formatCode");
//TODO: determine the type according to the format
var type = typeof(string);
if (DateTimeHelper.IsDateTimeFormat(formatCode))
{
type = typeof(DateTime?);
}
_customFormats.Add(numFmtId, new NumberFormatString(formatCode, type));
Reader.Skip();
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
else if (!XmlReaderHelper.SkipContent(Reader))
{
break;
}
}
}
}
public ExcelOpenXmlSheetReader(Stream stream, IConfiguration configuration)
{
_archive = new ExcelOpenXmlZip(stream);
_config = (OpenXmlConfiguration)configuration ?? OpenXmlConfiguration.DefaultConfig;
SetSharedStrings();
}
public IEnumerable <IDictionary <string, object> > Query(Stream stream, bool UseHeaderRow = false)
{
using (var archive = new ExcelOpenXmlZip(stream))
{
//TODO:need to optimize
_SharedStrings = GetSharedStrings(archive);
// if sheets count > 1 need to read xl/_rels/workbook.xml.rels and
var sheets = archive.Entries.Where(w => w.FullName.StartsWith("xl/worksheets/sheet", StringComparison.OrdinalIgnoreCase) ||
w.FullName.StartsWith("/xl/worksheets/sheet", StringComparison.OrdinalIgnoreCase)
);
ZipArchiveEntry firstSheetEntry = null;
if (sheets.Count() > 1)
{
ReadWorkbookRels(archive.Entries);
firstSheetEntry = sheets.Single(w => w.FullName == $"xl/{_sheetRecords[0].Path}" || w.FullName == $"/xl/{_sheetRecords[0].Path}");
}
else
{
firstSheetEntry = sheets.Single();
}
// TODO: need to optimize performance
var withoutCR = false;
var maxRowIndex = -1;
var maxColumnIndex = -1;
//TODO: merge one open read
using (var firstSheetEntryStream = firstSheetEntry.Open())
using (XmlReader reader = XmlReader.Create(firstSheetEntryStream, XmlSettings))
{
while (reader.Read())
{
if (reader.IsStartElement("c", ns))
{
var r = reader.GetAttribute("r");
if (r != null)
{
if (ReferenceHelper.ParseReference(r, out var column, out var row))
{
column = column - 1;
row = row - 1;
maxRowIndex = Math.Max(maxRowIndex, row);
maxColumnIndex = Math.Max(maxColumnIndex, column);
}
}
else
{
withoutCR = true;
break;
}
}
//this method logic depends on dimension to get maxcolumnIndex, if without dimension then it need to foreach all rows first time to get maxColumn and maxRowColumn
else if (reader.IsStartElement("dimension", ns))
{
var @ref = reader.GetAttribute("ref");
if (string.IsNullOrEmpty(@ref))
{
throw new InvalidOperationException("Without sheet dimension data");
}
var rs = @ref.Split(':');
// issue : https://github.com/shps951023/MiniExcel/issues/102
if (ReferenceHelper.ParseReference(rs.Length == 2 ? rs[1] : rs[0], out int cIndex, out int rIndex))
{
maxColumnIndex = cIndex - 1;
maxRowIndex = rIndex - 1;
break;
}
else
{
throw new InvalidOperationException("Invaild sheet dimension start data");
}
}
}
}
if (withoutCR)
{
using (var firstSheetEntryStream = firstSheetEntry.Open())
using (XmlReader reader = XmlReader.Create(firstSheetEntryStream, XmlSettings))
{
if (!reader.IsStartElement("worksheet", ns))
{
yield break;
}
if (!XmlReaderHelper.ReadFirstContent(reader))
{
yield break;
}
while (!reader.EOF)
{
if (reader.IsStartElement("sheetData", ns))
{
if (!XmlReaderHelper.ReadFirstContent(reader))
{
continue;
}
while (!reader.EOF)
{
if (reader.IsStartElement("row", ns))
{
maxRowIndex++;
if (!XmlReaderHelper.ReadFirstContent(reader))
{
continue;
}
//Cells
{
var cellIndex = -1;
while (!reader.EOF)
{
if (reader.IsStartElement("c", ns))
{
cellIndex++;
maxColumnIndex = Math.Max(maxColumnIndex, cellIndex);
}
if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
}
}
else if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
}
else if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
}
}
using (var firstSheetEntryStream = firstSheetEntry.Open())
using (XmlReader reader = XmlReader.Create(firstSheetEntryStream, XmlSettings))
{
if (!reader.IsStartElement("worksheet", ns))
{
yield break;
}
if (!XmlReaderHelper.ReadFirstContent(reader))
{
yield break;
}
while (!reader.EOF)
{
if (reader.IsStartElement("sheetData", ns))
{
if (!XmlReaderHelper.ReadFirstContent(reader))
{
continue;
}
Dictionary <int, string> headRows = new Dictionary <int, string>();
int rowIndex = -1;
int nextRowIndex = 0;
while (!reader.EOF)
{
if (reader.IsStartElement("row", ns))
{
nextRowIndex = rowIndex + 1;
if (int.TryParse(reader.GetAttribute("r"), out int arValue))
{
rowIndex = arValue - 1; // The row attribute is 1-based
}
else
{
rowIndex++;
}
if (!XmlReaderHelper.ReadFirstContent(reader))
{
continue;
}
// fill empty rows
{
if (nextRowIndex < rowIndex)
{
for (int i = nextRowIndex; i < rowIndex; i++)
{
if (UseHeaderRow)
{
yield return(Helpers.GetEmptyExpandoObject(headRows));
}
else
{
yield return(Helpers.GetEmptyExpandoObject(maxColumnIndex));
}
}
}
}
// Set Cells
{
var cell = UseHeaderRow ? Helpers.GetEmptyExpandoObject(headRows) : Helpers.GetEmptyExpandoObject(maxColumnIndex);
var columnIndex = withoutCR ? -1 : 0;
while (!reader.EOF)
{
if (reader.IsStartElement("c", ns))
{
var aS = reader.GetAttribute("s");
var cellValue = ReadCell(reader, columnIndex, withoutCR, out var _columnIndex);
columnIndex = _columnIndex;
// TODO: bad code smell
if (!string.IsNullOrEmpty(aS)) // if c with s meaning is custom style need to check type by xl/style.xml
{
int xfIndex = -1;
if (int.TryParse(aS, NumberStyles.Any, CultureInfo.InvariantCulture, out var styleIndex))
{
xfIndex = styleIndex;
}
// only when have s attribute then load styles xml data
if (_style == null)
{
_style = new ExcelOpenXmlStyles(archive);
}
//if not using First Head then using 1,2,3 as index
if (UseHeaderRow)
{
if (rowIndex == 0)
{
var customStyleCellValue = _style.ConvertValueByStyleFormat(xfIndex, cellValue)?.ToString();
if (!string.IsNullOrWhiteSpace(customStyleCellValue))
{
headRows.Add(columnIndex, customStyleCellValue);
}
}
else
{
if (headRows.ContainsKey(columnIndex))
{
var key = headRows[columnIndex];
var v = _style.ConvertValueByStyleFormat(int.Parse(aS), cellValue);
cell[key] = _style.ConvertValueByStyleFormat(xfIndex, cellValue);
}
}
}
else
{
//if not using First Head then using A,B,C as index
cell[Helpers.GetAlphabetColumnName(columnIndex)] = _style.ConvertValueByStyleFormat(xfIndex, cellValue);
}
}
else
{
if (UseHeaderRow)
{
if (rowIndex == 0)
{
var valueString = cellValue?.ToString();
if (!string.IsNullOrWhiteSpace(valueString))
{
headRows.Add(columnIndex, valueString);
}
}
else
{
if (headRows.ContainsKey(columnIndex))
{
cell[headRows[columnIndex]] = cellValue;
}
}
}
else
{
//if not using First Head then using A,B,C as index
cell[Helpers.GetAlphabetColumnName(columnIndex)] = cellValue;
}
}
}
else if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
if (UseHeaderRow && rowIndex == 0)
{
continue;
}
yield return(cell);
}
}
else if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
}
else if (!XmlReaderHelper.SkipContent(reader))
{
break;
}
}
}
}
