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 : ![image](https://user-images.githubusercontent.com/12729184/114361127-61a5d100-9ba8-11eb-9bb9-34f076ee28a2.png) } } } _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; } } } }