/// <summary> /// Reads an Xlsx file and returns an <see cref="IReadOnlyList{SheetInfo}"/> containing the information about the sheets read /// </summary> /// <param name="xlsxPath">The path of the Xlsx file</param> /// <param name="options">An <see cref="IEnumerable{ReadXlsxOptions}"/> describing options for each sheet in the file</param> /// <returns>An <see cref="IReadOnlyList{SheetInfo}"/> containing all the data from the Xlsx file</returns> public IReadOnlyList <SheetInfo> ReadXlsx(string xlsxPath, IEnumerable <ReadXlsxOptions> options) { var results = new List <SheetInfo>(); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(xlsxPath, false)) { foreach (Sheet sheet in doc.WorkbookPart.Workbook.Sheets.OfType <Sheet>()) { SheetInfo sheetData = new SheetInfo(sheet.Name); var option = options.FirstOrDefault(o => o.SheetName == sheetData.Name); if (option?.Ignore ?? false) { continue; } if (option?.Headers != null) { sheetData.ColumnList.AddRange(option.Headers); } Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet; IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); foreach (Row row in rows) { RowInfo rowData = sheetData.AddRow(); int headerIndex = 0; foreach (Cell cell in row.Descendants <Cell>()) { var cellValue = Utility.GetCellValue(doc, cell); if (row.RowIndex.Value == option?.HeaderRowIndex) { sheetData.ColumnList.Add(cellValue); } string headerName = null; if (row.RowIndex >= option?.StartingRowIndex) { headerName = sheetData.ColumnList.Count > headerIndex ? sheetData.ColumnList[headerIndex] : null; } rowData.AddColumn(cell.CellReference, headerName, cellValue); headerIndex++; } } results.Add(sheetData); } } return(results); }
public void WriteXlsx(string xlsxTemplatePath, string xlsxOutputPath, WriteXlsxOptions[] options) { File.Copy(xlsxTemplatePath, xlsxOutputPath); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(xlsxOutputPath, true)) { foreach (Sheet sheet in doc.WorkbookPart.Workbook.Sheets.OfType <Sheet>()) { SheetInfo sheetInfo = new SheetInfo(sheet.Name); foreach (WriteXlsxOptions option in options.Where(o => o.SheetName == sheetInfo.Name && !o.Ignore && ( (o.IndividualReplacements?.Any() ?? false) || ( (o.LineLocators?.Any() ?? false) && (o.LineReplacements?.Any() ?? false) ) ))) { Worksheet worksheet = ((WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id.Value)).Worksheet; IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); bool linesReplaced = false; foreach (Row row in rows) { foreach (Cell cell in row.Descendants <Cell>()) { string cellValue = Utility.GetCellValue(doc, cell); if (string.IsNullOrWhiteSpace(cellValue)) { continue; } if (option.IndividualReplacements != null && option.IndividualReplacements.TryGetValue(cellValue, out object replacementValue)) { Utility.SetCellValue(doc, cell, replacementValue); } if (!linesReplaced && option.LineLocators != null && option.LineLocators.Contains(cellValue)) { linesReplaced = true; foreach (IDictionary <string, object> lineReplacements in option.LineReplacements) { Row cloneRow = (Row)row.CloneNode(true); foreach (var cloneCell in cloneRow.Descendants <Cell>()) { string cloneCellValue = Utility.GetCellValue(doc, cloneCell); if (string.IsNullOrWhiteSpace(cloneCellValue)) { continue; } if (lineReplacements.TryGetValue(cloneCellValue, out object cloneCellReplacementValue)) { Utility.SetCellValue(doc, cloneCell, cloneCellReplacementValue); } } Utility.InsertRow(worksheet, row, cloneRow); } row.Remove(); } } } } } doc.Save(); } }
internal RowInfo(SheetInfo sheetInfo) { _sheetInfo = sheetInfo; }