/// <summary>
/// Load an OpenDocument .ods file into a datatable
/// </summary>
public static DataTable ParseODSStream2DataTable(MemoryStream AStream,
bool AHasHeader = false,
int AWorksheetID = 0,
List <string> AColumnsToImport = null)
{
XmlDocument doc = GetContent(AStream);
XmlNode OfficeBody = TXMLParser.GetChild(doc.DocumentElement, "office:body");
XmlNode OfficeDocument = TXMLParser.GetChild(OfficeBody, "office:spreadsheet");
int countWorksheets = 0;
XmlNode worksheet = null;
foreach (XmlNode worksheetLoop in OfficeDocument.ChildNodes)
{
if (worksheetLoop.Name != "table:table")
{
continue;
}
if (countWorksheets == AWorksheetID)
{
worksheet = worksheetLoop;
}
countWorksheets++;
}
DataTable result = new DataTable();
if (worksheet == null)
{
return(result);
}
List <string> ColumnNames = new List <string>();
bool firstRow = true;
foreach (XmlNode rowNode in worksheet.ChildNodes)
{
if (rowNode.Name != "table:table-row")
{
continue;
}
// create columns
if (firstRow)
{
int columnCounter = 0;
foreach (XmlNode cellNode in rowNode.ChildNodes)
{
if (cellNode.Name != "table:table-cell")
{
continue;
}
if (TXMLParser.HasAttribute(cellNode, "table:number-columns-repeated"))
{
// just ignore duplicate columns in the header line, the values must be unique anyway
continue;
}
string ColumnName = ((AHasHeader && cellNode.FirstChild != null) ? cellNode.FirstChild.InnerText : string.Format("Column {0}", columnCounter));
ColumnNames.Add(ColumnName);
columnCounter++;
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnName))
{
continue;
}
result.Columns.Add(ColumnName);
}
}
// import row
if (!firstRow || !AHasHeader)
{
DataRow NewRow = result.NewRow();
int columnCounter = 0;
foreach (XmlNode cellNode in rowNode.ChildNodes)
{
if (cellNode.Name != "table:table-cell")
{
continue;
}
Int32 NumberColumnsRepeated = 1;
// handle columns with same value
if (TXMLParser.HasAttribute(cellNode, "table:number-columns-repeated"))
{
NumberColumnsRepeated = Convert.ToInt32(TXMLParser.GetAttribute(cellNode, "table:number-columns-repeated"));
if (!TXMLParser.HasAttribute(cellNode, "office:value-type"))
{
// skip empty columns
columnCounter += NumberColumnsRepeated;
continue;
}
}
while (NumberColumnsRepeated > 0)
{
string CellType = TXMLParser.GetAttribute(cellNode, "office:value-type");
if ((AColumnsToImport != null) && !AColumnsToImport.Contains(ColumnNames[columnCounter]))
{
// skip this column
}
else if (CellType == "float")
{
TVariant variant = new TVariant(TXMLParser.GetAttribute(cellNode, "office:value"));
NewRow[ColumnNames[columnCounter]] = variant.ToObject();
}
else if (CellType == "date")
{
NewRow[ColumnNames[columnCounter]] =
new TVariant(TXMLParser.GetAttribute(cellNode, "office:date-value")).EncodeToString();
}
else if (CellType == "boolean")
{
NewRow[ColumnNames[columnCounter]] =
(TXMLParser.GetAttribute(cellNode, "office:boolean-value") == "true");
}
else if (CellType == "string")
{
NewRow[ColumnNames[columnCounter]] = cellNode.FirstChild.InnerText;
}
columnCounter++;
NumberColumnsRepeated--;
}
}
result.Rows.Add(NewRow);
}
firstRow = false;
}
return(result);
}