Exemplo n.º 1
0
        /// <summary>
        /// Create empty rows and cols to improve performance.
        /// </summary>
        /// <param name="rowCount"></param>
        /// <param name="colCount"></param>
        internal void CreateEmptyCells(int rowCount, int colCount)
        {
            if (Rows.Count != 0)
            {
                throw new InvalidOperationException("Must be called before rows are filled");
            }

            var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);

            for (int rowNum = 1; rowNum <= rowCount; rowNum++)
            {
                // Add element
                var rowElement = ExtensonMethods.NewElement("row");
                rowElement.SetAttribute("r", rowNum.ToString());
                sheetDataNode.Add(rowElement);

                ExcelRow row = new ExcelRow(this, rowElement);
                Rows.Add(rowNum, row);

                for (int colNum = 1; colNum <= colCount; colNum++)
                {
                    var cellElement = ExtensonMethods.NewElement("c");
                    cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString());
                    rowElement.Add(cellElement);

                    ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
                    row.Cells.Add(colNum, cell);
                }
            }
        }
Exemplo n.º 2
0
        public void Add(String name, String rangeRef)
        {
            if (!IsValidName(name))
            {
                throw new ArgumentException("name");
            }
            if (!IsValidRangeRef(rangeRef))
            {
                throw new ArgumentException("Invalid rangeRef");
            }

            if (Contains(name))
            {
                throw new ArgumentException("Already exists: " + name);
            }

            // Create list element if needed
            if (_definedNames == null)
            {
                var wbNode = _worsheetsXml.XPathSelectElement("//d:workbook", _worksheets.NsManager);
                if (wbNode == null)
                {
                    throw new NullReferenceException("Workbook node missing.");
                }

                _definedNames = ExtensonMethods.NewElement("definedNames");
                wbNode.Add(_definedNames);
            }

            var dnElement = ExtensonMethods.NewElement("definedName").SetAttribute("name", name);

            dnElement.Value = rangeRef;
            _definedNames.Add(dnElement);
        }
Exemplo n.º 3
0
        private void SaveAttribute(string name, ExcelHeaderFooterText excelHeaderFooterText)
        {
            var elm = ExtensonMethods.NewElement(name);

            _headerFooterNode.Add(elm);
            elm.Value = GetHeaderFooterText(excelHeaderFooterText);
        }
Exemplo n.º 4
0
 void CreateSharedStringsDoc()
 {
     // create the shared strings xml doc (with no entries in it)
     _xmlSharedStrings = new XDocument(
         ExtensonMethods.NewElement("sst")
         .SetAttribute("count", "0")
         .SetAttribute("uniqueCount", "0"));
 }
Exemplo n.º 5
0
        /// <summary>
        /// Inserts conditional formatting for the cell range.
        /// Currently only supports the dataBar style.
        /// </summary>
        /// <param name="startCell"></param>
        /// <param name="endCell"></param>
        /// <param name="color"></param>
        public void CreateConditionalFormatting(ExcelCell startCell, ExcelCell endCell, string color)
        {
            var formatNode = WorksheetXml.XPathSelectElement("//d:conditionalFormatting", NameSpaceManager);

            if (formatNode == null)
            {
                formatNode = ExtensonMethods.NewElement("conditionalFormatting");
                var prevNode = WorksheetXml.XPathSelectElement("//d:mergeCells", NameSpaceManager);
                if (prevNode == null)
                {
                    prevNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
                }
                prevNode.AddAfterSelf(formatNode);
            }
            XAttribute attr = formatNode.Attribute("sqref");

            if (attr == null)
            {
                attr = new XAttribute("sqref", "");
                formatNode.Add(attr);
            }
            attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress);

            var node = formatNode.XPathSelectElement("./d:cfRule", NameSpaceManager);

            if (node == null)
            {
                node = ExtensonMethods.NewElement("cfRule");
                formatNode.Add(node);
            }

            attr = node.Attribute("type");
            if (attr == null)
            {
                attr = new XAttribute("type", "");
                node.Add(attr);
            }
            attr.Value = "dataBar";

            attr = node.Attribute("priority");
            if (attr == null)
            {
                attr = new XAttribute("priority", "");
                node.Add(attr);
            }
            attr.Value = "1";

            // the following is poor code, but just an example!!!
            var databar = ExtensonMethods.NewElement(
                "databar",
                ExtensonMethods.NewElement("cfvo").SetAttrValue("type", "min").SetAttrValue("val", "0"),
                ExtensonMethods.NewElement("cfvo").SetAttrValue("type", "max").SetAttrValue("val", "0"),
                ExtensonMethods.NewElement("color").SetAttrValue("rgb", color)
                );

            node.Add(databar);
        }
Exemplo n.º 6
0
        /// <summary>
        /// Inserts a new row into the spreadsheet.  Existing rows below the insersion position are
        /// shifted down.  All formula are updated to take account of the new row.
        /// </summary>
        /// <param name="position">The position of the new row</param>
        public void InsertRow(int position)
        {
            XElement rowNode = null;
            // create the new row element
            XElement rowElement = ExtensonMethods.NewElement("row").SetAttrValue("r", position.ToString());

            var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);

            if (sheetDataNode != null)
            {
                int      renumberFrom         = 1;
                var      nodes                = sheetDataNode.Nodes().Cast <XElement>().ToList();
                int      nodeCount            = nodes.Count;
                XElement insertAfterRowNode   = null;
                int      insertAfterRowNodeID = 0;
                for (int i = 0; i < nodeCount; i++)
                {
                    int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
                    if (currentRowID < position)
                    {
                        insertAfterRowNode   = nodes[i];
                        insertAfterRowNodeID = i;
                    }
                    if (currentRowID >= position)
                    {
                        renumberFrom = currentRowID;
                        break;
                    }
                }

                // update the existing row ids
                for (int i = insertAfterRowNodeID + 1; i < nodeCount; i++)
                {
                    int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
                    if (currentRowID >= renumberFrom)
                    {
                        nodes[i].Attribute("r").Value = Convert.ToString(currentRowID + 1);

                        // now update any formula that are in the row
                        var formulaNodes = nodes[i].XPathSelectElements("./d:c/d:f", NameSpaceManager);
                        foreach (var formulaNode in formulaNodes)
                        {
                            formulaNode.Value = ExcelCell.UpdateFormulaReferences(formulaNode.Value, 1, 0, position, 0);
                        }
                    }
                }

                // now insert the new row
                insertAfterRowNode?.AddAfterSelf(rowElement);
            }

            // Update stored rows
            ShiftRows(position, 1);
            Rows.Add(position, new ExcelRow(this, rowElement));
        }
Exemplo n.º 7
0
        /// <summary>
        /// Creates a new instance of the ExcelWorksheets class.
        /// For internal use only!
        /// </summary>
        /// <param name="xlPackage"></param>
        protected internal ExcelWorksheets(ExcelPackage xlPackage)
        {
            _xlPackage = xlPackage;
            //  Create a NamespaceManager to handle the default namespace,
            //  and create a prefix for the default namespace:
            NameTable nt = new NameTable();

            _nsManager = new XmlNamespaceManager(nt);
            _nsManager.AddNamespace("d", ExcelPackage.schemaMain.NamespaceName);
            _nsManager.AddNamespace("r", ExcelPackage.schemaRelationships.NamespaceName);

            // obtain container node for all worksheets
            _worksheetsNode = _xlPackage.Workbook.WorkbookXml.XPathSelectElement("//d:sheets", _nsManager);
            if (_worksheetsNode == null)
            {
                // create new node as it did not exist
                _worksheetsNode = ExtensonMethods.NewElement("sheets", ExcelPackage.schemaMain);
                _xlPackage.Workbook.WorkbookXml.Document.Add(_worksheetsNode);
            }

            _worksheets = new List <ExcelWorksheet>();
            foreach (var sheetNode in _worksheetsNode.Nodes().Cast <XElement>())
            {
                string name = sheetNode.Attribute("name").Value;
                //  Get the relationship id attribute:
                string relId   = sheetNode.Attribute("r:id").Value;
                int    sheetID = Convert.ToInt32(sheetNode.Attribute("sheetId").Value);
                //if (sheetID != count)
                //{
                //  // renumber the sheets as they are in an odd order
                //  sheetID = count;
                //  sheetNode.Attributes["sheetId"].Value = sheetID.ToString();
                //}
                // get hidden attribute (if present)
                bool hidden = false;
                var  attr   = sheetNode.Attribute("hidden");
                if (attr != null)
                {
                    hidden = Convert.ToBoolean(attr.Value);
                }

                //string type = "";
                //attr = sheetNode.Attributes["type"];
                //if (attr != null)
                //  type = attr.Value;

                PackageRelationship sheetRelation = _xlPackage.Workbook.Part.GetRelationship(relId);
                Uri uriWorksheet = PackUriHelper.ResolvePartUri(_xlPackage.Workbook.WorkbookUri, sheetRelation.TargetUri);

                // add worksheet to our collection
                _worksheets.Add(new ExcelWorksheet(_xlPackage, relId, name, uriWorksheet, sheetID, hidden));
            }

            _definedNames = new ExcelDefinedNames(this, _xlPackage.Workbook.WorkbookXml);
        }
Exemplo n.º 8
0
        /// <summary>
        /// Creates the XML document representing a new empty worksheet
        /// </summary>
        /// <returns></returns>
        protected internal XDocument CreateNewWorksheet()
        {
            // create the new worksheet
            var worksheetXml = new XDocument(
                ExtensonMethods.NewElement("worksheet",
                                           ExtensonMethods.NewElement("sheetViews",
                                                                      ExtensonMethods.NewElement("sheetView").SetAttribute("workbookViewId", "0"))).SetAttribute("xmlns:r", ExcelPackage.schemaRelationships.NamespaceName),
                // create the empty sheetData tag (must be present, but can be empty)
                ExtensonMethods.NewElement("sheetData"));

            return(worksheetXml);
        }
Exemplo n.º 9
0
        private XElement GetOrCreateCellElement(ExcelWorksheet xlWorksheet, int row, int col)
        {
            XElement cellNode = null;
            // this will create the row if it does not already exist
            var rowNode = xlWorksheet.Row(row).Node;

            if (rowNode != null)
            {
                cellNode = (XElement)rowNode.XPathSelectElement(string.Format("./d:c[@" + ExcelWorksheet.tempColumnNumberTag + "='{0}']", col), _xlWorksheet.NameSpaceManager);
                if (cellNode == null)
                {
                    //  Didn't find the cell so create the cell element
                    cellNode = ExtensonMethods.NewElement("c");
                    cellNode.SetAttribute(ExcelWorksheet.tempColumnNumberTag, col.ToString());

                    // You must insert the new cell at the correct location.
                    // Loop through the children, looking for the first cell that is
                    // beyond the cell you're trying to insert. Insert before that cell.
                    XElement biggerNode = null;
                    var      cellNodes  = rowNode.XPathSelectElements("./d:c", _xlWorksheet.NameSpaceManager);
                    if (cellNodes != null)
                    {
                        foreach (var node in cellNodes)
                        {
                            var colNode = node.Attribute(ExcelWorksheet.tempColumnNumberTag);
                            if (colNode != null)
                            {
                                int colFound = Convert.ToInt32(colNode.Value);
                                if (colFound > col)
                                {
                                    biggerNode = node;
                                    break;
                                }
                            }
                        }
                    }
                    if (biggerNode == null)
                    {
                        rowNode.Add(cellNode);
                    }
                    else
                    {
                        biggerNode.AddBeforeSelf(cellNode);
                    }
                }
            }
            return(cellNode);
        }
Exemplo n.º 10
0
        /// <summary>
        /// Adds a new formula node to the cell in the correct location
        /// </summary>
        /// <returns></returns>
        protected internal XElement AddFormulaElement()
        {
            var formulaElement = ExtensonMethods.NewElement("f");
            // find the right location for insersion
            var valueNode = _cellElement.XPathSelectElement("./d:v", _xlWorksheet.NameSpaceManager);

            if (valueNode == null)
            {
                _cellElement.Add(formulaElement);
            }
            else
            {
                valueNode.AddBeforeSelf(formulaElement);
            }
            return(formulaElement);
        }
Exemplo n.º 11
0
        /// <summary>
        /// Creates a new instance of the ExcelRow class.
        /// For internal use only!
        /// </summary>
        /// <param name="worksheet">The parent worksheet</param>
        /// <param name="row">The row number</param>
        protected internal ExcelRow(ExcelWorksheet worksheet, int row)
        {
            _xlWorksheet = worksheet;

            //  Search for the existing row
            _rowElement = (XElement)worksheet.WorksheetXml.XPathSelectElement($"//d:sheetData/d:row[@r='{row}']", _xlWorksheet.NameSpaceManager);
            if (_rowElement == null)
            {
                // We didn't find the row, so add a new row element.
                // HOWEVER we MUST insert new row in the correct position - otherwise Excel 2007 will complain!!!
                _rowElement = ExtensonMethods.NewElement("row");
                _rowElement.SetAttribute("r", row.ToString());

                // now work out where to insert the new row
                var sheetDataNode = worksheet.WorksheetXml.XPathSelectElement("//d:sheetData", _xlWorksheet.NameSpaceManager);
                if (sheetDataNode != null)
                {
                    XElement followingRow = null;
                    foreach (var currentRow in worksheet.WorksheetXml.XPathSelectElements("//d:sheetData/d:row", _xlWorksheet.NameSpaceManager))
                    {
                        int rowFound = Convert.ToInt32(currentRow.Attribute("r").Value);
                        if (rowFound > row)
                        {
                            followingRow = currentRow;
                            break;
                        }
                    }
                    if (followingRow == null)
                    {
                        // no data rows exist, so just add row
                        sheetDataNode.Add(_rowElement);
                    }
                    else
                    {
                        sheetDataNode.AddBeforeSelf(_rowElement, followingRow);
                    }
                }
            }
        }
Exemplo n.º 12
0
 public XElement New(string name, params object[] content)
 {
     return(ExtensonMethods.NewElement(name, Namespace, content));
 }
Exemplo n.º 13
0
        /// <summary>
        /// Creates a new instance of the ExcelColumn class.
        /// For internal use only!
        /// </summary>
        /// <param name="Worksheet"></param>
        /// <param name="col"></param>
        protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
        {
            NameTable nt = new NameTable();

            _nsManager = new XmlNamespaceManager(nt);
            _nsManager.AddNamespace("d", ExcelPackage.schemaMain.NamespaceName);

            _xlWorksheet = Worksheet;
            var parent = Worksheet.WorksheetXml.XPathSelectElement("//d:cols", _nsManager);

            if (parent == null)
            {
                parent = ExtensonMethods.NewElement("cols");
                var refChild = Worksheet.WorksheetXml.XPathSelectElement("//d:sheetData", _nsManager);
                refChild.AddBeforeSelf(parent);
            }
            XAttribute minAttr;
            XAttribute maxAttr;
            XElement   insertBefore = null;
            // the column definitions cover a range of columns, so find the one we want
            bool insertBeforeFound = false;

            foreach (XElement colNode in parent.Nodes())
            {
                int min = 1;
                int max = 1;
                minAttr = colNode.Attribute("min");
                if (minAttr != null)
                {
                    min = int.Parse(minAttr.Value);
                }
                maxAttr = colNode.Attribute("max");
                if (maxAttr != null)
                {
                    max = int.Parse(maxAttr.Value);
                }
                if (!insertBeforeFound && (col <= min || col <= max))
                {
                    insertBeforeFound = true;
                    insertBefore      = colNode;
                }
                if (col >= min && col <= max)
                {
                    _colElement = colNode;
                    break;
                }
            }
            if (_colElement == null)
            {
                // create the new column definition
                _colElement = ExtensonMethods.NewElement("col");
                _colElement.SetAttribute("min", col.ToString());
                _colElement.SetAttribute("max", col.ToString());

                if (insertBefore != null)
                {
                    insertBefore.AddBeforeSelf(_colElement);
                }
                else
                {
                    parent.Add(_colElement);
                }
            }
        }
Exemplo n.º 14
0
        /// <summary>
        /// Adds a blank worksheet with the desired name
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="rowCount">Number of rows to to create initially (for performance)</param>
        /// <param name="colCount">Number of columns to to create</param>
        /// <returns></returns>
        public ExcelWorksheet Add(string Name, int rowCount, int colCount)
        {
            // first find maximum existing sheetID
            // also check the name is unique - if not throw an error
            int sheetID = 0;

            foreach (var sheet in _worksheetsNode.Nodes().Cast <XElement>())
            {
                var attr = sheet.Attribute("sheetId");
                if (attr != null)
                {
                    int curID = int.Parse(attr.Value);
                    if (curID > sheetID)
                    {
                        sheetID = curID;
                    }
                }
                attr = sheet.Attribute("name");
                if (attr != null)
                {
                    if (attr.Value == Name)
                    {
                        throw new Exception("Add worksheet Error: attempting to create worksheet with duplicate name");
                    }
                }
            }
            // we now have the max existing values, so add one
            sheetID++;

            // add the new worksheet to the package
            Uri         uriWorksheet  = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
            PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", CompressionOption.Normal);

            // create the new, empty worksheet and save it to the package
            StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
            var          worksheetXml    = CreateNewWorksheet();

            worksheetXml.Save(streamWorksheet);
            streamWorksheet.Dispose();
            _xlPackage.Package.Flush();

            // create the relationship between the workbook and the new worksheet
            PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships.NamespaceName + "/worksheet");

            _xlPackage.Package.Flush();

            // now create the new worksheet tag and set name/SheetId attributes in the workbook.xml
            var worksheetNode = ExtensonMethods.NewElement("sheet");

            // create the new sheet node
            worksheetNode.SetAttribute("name", Name);
            worksheetNode.SetAttribute("sheetId", sheetID.ToString());
            // set the r:id attribute
            worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);
            // insert the sheet tag with all attributes set as above
            _worksheetsNode.Add(worksheetNode);

            // create a reference to the new worksheet in our collection
            ExcelWorksheet worksheet = new ExcelWorksheet(_xlPackage, rel.Id, Name, uriWorksheet, sheetID, false);

            _worksheets.Add(worksheet);
            worksheet.CreateEmptyCells(rowCount, colCount);

            return(worksheet);
        }