internal static void ImportNamedRanges(XmlReader reader, ExcelXmlWorkbook book, Worksheet ws)
        {
            if (!reader.IsEmptyElement)
            {
                while (reader.Read() && !(reader.Name == "Names" && reader.NodeType == XmlNodeType.EndElement))
                {
                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        if (reader.Name == "NamedRange")
                        {
                            Range  range = null;
                            string name  = string.Empty;

                            foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
                            {
                                if (xa.LocalName == "Name" && xa.HasValue)
                                {
                                    name = xa.Value;
                                }

                                if (xa.LocalName == "RefersTo" && xa.HasValue)
                                {
                                    range = new Range(xa.Value);
                                }
                            }

                            NamedRange nr = new NamedRange(range, name, ws);
                            book.NamedRanges.Add(nr);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Imports a excel xml workbook into a ExcelXmlWorkbook instance
        /// </summary>
        /// <param name="importFile">File to import</param>
        /// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
        public static ExcelXmlWorkbook Import(string importFile)
        {
            if (!File.Exists(importFile))
            {
                return(null);
            }

            Stream stream;

            try
            {
                stream = new FileStream(importFile, FileMode.Open, FileAccess.Read);
            }
            catch (IOException)
            {
                return(null);
            }
            catch (SecurityException)
            {
                return(null);
            }
            catch (UnauthorizedAccessException)
            {
                return(null);
            }

            ExcelXmlWorkbook book = Import(stream);

            stream.Close();
            stream.Dispose();

            return(book);
        }
Exemple #3
0
        internal void Import(XmlReader reader)
        {
            foreach (XmlReaderAttributeItem xa in reader.GetAttributes())
            {
                if (xa.LocalName == "Name" && xa.HasValue)
                {
                    this.Name = xa.Value;
                }

                if (xa.LocalName == "StyleID" && xa.HasValue)
                {
                    this.Style = this.ParentBook.GetStyleByID(xa.Value);
                }
            }

            while (reader.Read() && !(reader.Name == "Worksheet" && reader.NodeType == XmlNodeType.EndElement))
            {
                if (reader.NodeType == XmlNodeType.Element)
                {
                    switch (reader.Name)
                    {
                    case "Names":
                    {
                        ExcelXmlWorkbook.ImportNamedRanges(reader, this.GetParentBook(), this);

                        break;
                    }

                    case "Table":
                    {
                        this.ImportTable(reader);

                        break;
                    }

                    case "WorksheetOptions":
                    {
                        this.ImportOptions(reader);

                        break;
                    }
                    }
                }
            }
        }
        /// <summary>
        /// Imports a excel xml workbook into a ExcelXmlWorkbook instance
        /// </summary>
        /// <param name="stream">Stream to import</param>
        /// <returns>If import was successful, the ExcelXmlWorkbook instance, null otherwise</returns>
        public static ExcelXmlWorkbook Import(Stream stream)
        {
            XmlReaderSettings settings = new XmlReaderSettings();

            settings.CloseInput     = false;
            settings.IgnoreComments = true;
            settings.IgnoreProcessingInstructions = true;
            settings.IgnoreWhitespace             = true;

            if (!stream.CanRead)
            {
                return(null);
            }

            XmlReader reader = XmlReader.Create(stream, settings);

            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            book.Styles.Clear();

            int workSheet = 0;

            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element)
                {
                    switch (reader.Name)
                    {
                    // Document Properties
                    case "DocumentProperties":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book.Properties.Import(reader);
                        }

                        break;
                    }

                    case "Styles":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book.ImportStyles(reader);
                        }

                        break;
                    }

                    case "Names":
                    {
                        ImportNamedRanges(reader, book, null);

                        break;
                    }

                    case "Worksheet":
                    {
                        if (!reader.IsEmptyElement)
                        {
                            book[workSheet++].Import(reader);
                        }

                        break;
                    }
                    }
                }
            }

            book.ResolveNamedRangeReferences();

            book.ResolveCellReferences();

            reader.Close();

            stream.Close();
            stream.Dispose();

            return(book);
        }
        /// <summary>
        /// Converts a dataset to a work book
        /// </summary>
        /// <param name="source">The source dataset to convert to a work book</param>
        /// <returns>Returns the <see cref="TMP.ExcelXml.ExcelXmlWorkbook"/>
        /// for the dataset.</returns>
        /// <remarks>All the tables are converted into sheets with sheet names as table + table number,
        /// eg. "Table0" "Table1" etc. Supported types which can be successfully
        /// converted to cells are the same as described in <see cref="TMP.ExcelXml.Cell"/>
        /// except <see cref="TMP.ExcelXml.Cell"/> and
        /// <see cref="TMP.ExcelXml.Formula"/></remarks>
        public static ExcelXmlWorkbook DataSetToWorkbook(DataSet source)
        {
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();

            for (int tableNumber = 0; tableNumber < source.Tables.Count; tableNumber++)
            {
                Worksheet sheet = book[tableNumber];
                sheet.Name = "Table" + tableNumber.ToString(CultureInfo.InvariantCulture);

                int columnCount = source.Tables[tableNumber].Columns.Count;
                for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
                {
                    sheet[columnNumber, 0].Value = source.Tables[tableNumber].Columns[columnNumber].ColumnName;

                    sheet[columnNumber, 0].Font.Bold = true;
                }

                int rowNumber = 0;
                foreach (DataRow row in source.Tables[tableNumber].Rows)
                {
                    rowNumber++;

                    for (int columnNumber = 0; columnNumber < columnCount; columnNumber++)
                    {
                        string rowType = row[columnNumber].GetType().FullName;

                        switch (rowType)
                        {
                        case "System.DateTime":
                        {
                            sheet[columnNumber, rowNumber].Value = (DateTime)row[columnNumber];
                            break;
                        }

                        case "System.Boolean":
                        {
                            sheet[columnNumber, rowNumber].Value = (bool)row[columnNumber];
                            break;
                        }

                        case "System.SByte":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                        case "System.UInt16":
                        case "System.UInt32":
                        case "System.UInt64":
                        case "System.Single":
                        case "System.Double":
                        case "System.Decimal":
                        {
                            sheet[columnNumber, rowNumber].Value = Convert.ToDecimal(row[columnNumber],
                                                                                     CultureInfo.InvariantCulture);
                            break;
                        }

                        case "System.DBNull":
                            break;

                        // case "System.String": <-- default covers this...
                        default:
                        {
                            sheet[columnNumber, rowNumber].Value = row[columnNumber].ToString();
                            break;
                        }
                        }
                    }
                }
            }

            return(book);
        }