/// <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;
}