ExeclParseResult ParseFileImpl(Stream stream, ITableDescription table) { if (table == null) { throw new ArgumentNullException(nameof(table)); } var rv = new List <ExpandoObject>(); List <String> columns = new List <String>(); using (var doc = SpreadsheetDocument.Open(stream, isEditable: false)) { var workBookPart = doc.WorkbookPart; var workBook = workBookPart.Workbook; var sheet = workBook.Descendants <Sheet>().First(); if (sheet == null) { throw new InteropException($"The workbook does not have a sheet"); } var workSheetPart = (WorksheetPart)workBookPart.GetPartById(sheet.Id); var sharedStringPart = workBookPart.SharedStringTablePart; var sharedStringTable = sharedStringPart.SharedStringTable; var stylesPart = workBookPart.WorkbookStylesPart; // This formats is NUMBER, not standard! var numFormats = stylesPart.Stylesheet.Descendants <NumberingFormat>()?.ToDictionary(x => x.NumberFormatId.Value.ToString()); var rows = workSheetPart.Worksheet.Descendants <Row>().ToList(); if (rows == null) { throw new InteropException($"The sheet does not have a rows"); } var hdr = rows[0]; var hdrCells = hdr.Elements <Cell>().ToList(); for (var ci = 0; ci < hdrCells.Count; ci++) { var c = hdrCells[ci]; if (c.DataType != null && c.DataType == CellValues.SharedString) { Int32 ssid = Int32.Parse(c.CellValue.Text); String str = sharedStringTable.ChildElements[ssid].InnerText; str = ReplaceUnacceptableChars(str); columns.Add(str); } else { columns.Add($"Empty-{ci}"); } } for (var ri = 1 /*1!*/; ri < rows.Count; ri++) { var r = rows[ri]; var dataRow = table.NewRow(); var cells = r.Elements <Cell>().ToList(); for (var ci = 0; ci < cells.Count; ci++) { var c = cells[ci]; var colIndex = ToIndex(c.CellReference) - 1; if (c.DataType != null && c.DataType == CellValues.SharedString) { Int32 ssid = Int32.Parse(c.CellValue.Text); String str = sharedStringTable.ChildElements[ssid].InnerText; table.SetValue(dataRow, columns[colIndex], str); } else if (c.StyleIndex != null && c.CellValue != null) { Int32 ix = Int32.Parse(c.StyleIndex); var cellFormat = workBookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[ix] as CellFormat; var fmtId = cellFormat?.NumberFormatId; if (numFormats != null && numFormats.ContainsKey(fmtId)) { // number if (Double.TryParse(c.CellValue.Text, NumberStyles.Any, CultureInfo.InvariantCulture, out Double dblVal)) { table.SetValue(dataRow, columns[colIndex], dblVal); } else { throw new InteropException($"invalid cell value '{c.CellValue.Text}' for format '{cellFormat.InnerText}'"); } } else { Object cellVal = GetCellValue(c.CellValue.Text, cellFormat); if (cellVal != null) { table.SetValue(dataRow, columns[colIndex], cellVal); } } } else if (c.CellValue != null) { table.SetValue(dataRow, columns[colIndex], c.CellValue.Text); } } } } return(new ExeclParseResult() { Data = table.ToObject(), Columns = columns }); }
Object ParseFileImpl(Stream stream, ITableDescription table) { if (table == null) { throw new ArgumentNullException(nameof(table)); } var rv = new List <ExpandoObject>(); using (var doc = SpreadsheetDocument.Open(stream, isEditable: false)) { var workBookPart = doc.WorkbookPart; var workBook = workBookPart.Workbook; var sheet = workBook.Descendants <Sheet>().First(); if (sheet == null) { throw new InteropException($"The workbook does not have a sheet"); } var workSheetPart = (WorksheetPart)workBookPart.GetPartById(sheet.Id); var sharedStringPart = workBookPart.SharedStringTablePart; var sharedStringTable = sharedStringPart.SharedStringTable; var rows = workSheetPart.Worksheet.Descendants <Row>().ToList(); if (rows == null) { throw new InteropException($"The sheet does not have a rows"); } var hdr = rows[0]; List <String> columns = new List <String>(); var hdrCells = hdr.Elements <Cell>().ToList(); for (var ci = 0; ci < hdrCells.Count; ci++) { var c = hdrCells[ci]; if (c.DataType != null && c.DataType == CellValues.SharedString) { Int32 ssid = Int32.Parse(c.CellValue.Text); String str = sharedStringTable.ChildElements[ssid].InnerText; columns.Add(str); } } for (var ri = 1 /*1!*/; ri < rows.Count; ri++) { var r = rows[ri]; var dataRow = table.NewRow(); var cells = r.Elements <Cell>().ToList(); for (var ci = 0; ci < cells.Count; ci++) { var c = cells[ci]; var colIndex = ToIndex(c.CellReference) - 1; if (c.DataType != null && c.DataType == CellValues.SharedString) { Int32 ssid = Int32.Parse(c.CellValue.Text); String str = sharedStringTable.ChildElements[ssid].InnerText; table.SetValue(dataRow, columns[colIndex], str); } else if (c.CellValue != null) { table.SetValue(dataRow, columns[colIndex], c.CellValue.Text); } } } } return(table.ToObject()); }