private void parseSheetProperties(Worksheet worksheet, XWorksheet xWorksheet)
{
if (worksheet.SheetProperties != null)
{
if (worksheet.SheetProperties.OutlineProperties != null)
{
xWorksheet.SummaryBelow = worksheet.SheetProperties.OutlineProperties.SummaryBelow;
}
}
}
private void parseMegreCells(Worksheet worksheet, XWorksheet xWorksheet)
{
foreach (MergeCells mergeCells in worksheet.Descendants <MergeCells>())
{
foreach (MergeCell mergeCell in mergeCells.Descendants <MergeCell>())
{
XMergeCell xmc = new XMergeCell(mergeCell.Reference);
xWorksheet.MergeCells.Add(xmc);
}
}
}
private void parseSheetData(Worksheet worksheet, XWorksheet xWorksheet)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
foreach (Row row in sheetData.Descendants <Row>())
{
int dtRowIndex = (int)(uint)row.RowIndex - 1;
// группировка строк
DataRow opRow = xWorksheet.OutlinePropertiesTable.Rows[dtRowIndex];
opRow[0] = (row.OutlineLevel == null) ? 0 : (int)row.OutlineLevel;
// данные
DataRow cvRow = xWorksheet.CellValuesTable.Rows[dtRowIndex];
foreach (Cell cell in row.Descendants <Cell>())
{
XReference xr = new XReference(cell.CellReference);
cvRow[xr.ColumnIndex] = getCellValue(cell);
}
}
}
public void LoadXlsx(string path)
{
// Буфер - чтобы не держать файл открытым.
MemoryStream memoryStream = new MemoryStream();
using (Stream stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
stream.CopyTo(memoryStream);
}
using (SpreadsheetDocument package = SpreadsheetDocument.Open(memoryStream, false))
{
WorkbookPart workbookPart = package.WorkbookPart;
SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart;
this.sharedStringTable = sharedStringTablePart.SharedStringTable;
WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart;
this.stylesheet = workbookStylesPart.Stylesheet;
Workbook workbook = workbookPart.Workbook;
Sheets sheets = workbook.Sheets;
foreach (Sheet sheet in sheets.Descendants <Sheet>())
{
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
// размеры
XDimention xd = new XDimention(worksheet.SheetDimension.Reference);
// добавляем новый лист в представление Net
XWorksheet xWorksheet = new XWorksheet(xd);
XWorksheets.Add(xWorksheet);
xWorksheet.Name = sheet.Name;
// разбор свойств листа
parseSheetProperties(worksheet, xWorksheet);
// разбор данных
parseSheetData(worksheet, xWorksheet);
// разбор объединений ячеек
parseMegreCells(worksheet, xWorksheet);
}
}
memoryStream.Dispose();
}
public void Add(XWorksheet xWorksheet)
{
Array.Resize <XWorksheet>(ref xwss, Count + 1);
xwss[Count++] = xWorksheet;
}