public ExeclParseResult ParseFile(Stream stream, ITableDescription table)
{
try
{
return(ParseFileImpl(stream, table));
}
catch (FileFormatException ex)
{
String msg = ErrorMessage;
if (String.IsNullOrEmpty(msg))
{
msg = ex.Message;
}
throw new InteropException(msg);
}
}
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());
}
protected PdcTable(ITableDescription tableDescription)
{
Description = tableDescription;
}
