/// <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; } }