Exemplo n.º 1
0
        /// <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);
        }