/// <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.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")).ToDate();
}
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);
}
/// <summary>
/// store the data into Excel format, Open Office XML, .xlsx
///
/// this makes use of the EPPlus library
/// http://epplus.codeplex.com/
/// </summary>
private static void Xml2ExcelWorksheet(XmlDocument ADoc, ExcelWorksheet AWorksheet, bool AWithHashInCaption = true)
{
Int32 rowCounter = 1;
Int16 colCounter = 1;
// first write the header of the csv file
List <string> AllAttributes = new List <string>();
List <XmlNode> AllNodes = new List <XmlNode>();
GetAllAttributesAndNodes(ADoc.DocumentElement, ref AllAttributes, ref AllNodes);
foreach (string attrName in AllAttributes)
{
if (AWithHashInCaption)
{
AWorksheet.Cells[rowCounter, colCounter].Value = "#" + attrName;
}
else
{
AWorksheet.Cells[rowCounter, colCounter].Value = attrName;
}
colCounter++;
}
rowCounter++;
colCounter = 1;
foreach (XmlNode node in AllNodes)
{
foreach (string attrName in AllAttributes)
{
if (attrName == "childOf")
{
AWorksheet.Cells[rowCounter, colCounter].Value = TXMLParser.GetAttribute(node.ParentNode, "name");
}
else
{
string value = TXMLParser.GetAttribute(node, attrName);
if (value.StartsWith(eVariantTypes.eDateTime.ToString() + ":"))
{
AWorksheet.Cells[rowCounter, colCounter].Value = TVariant.DecodeFromString(value).ToDate();
AWorksheet.Cells[rowCounter, colCounter].Style.Numberformat.Format = "dd/mm/yyyy";
}
else if (value.StartsWith(eVariantTypes.eInteger.ToString() + ":"))
{
AWorksheet.Cells[rowCounter, colCounter].Value = TVariant.DecodeFromString(value).ToInt64();
}
else if (value.StartsWith(eVariantTypes.eDecimal.ToString() + ":"))
{
AWorksheet.Cells[rowCounter, colCounter].Value = TVariant.DecodeFromString(value).ToDecimal();
}
else
{
AWorksheet.Cells[rowCounter, colCounter].Value = value;
}
}
colCounter++;
}
rowCounter++;
colCounter = 1;
}
}