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

            XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);

            for (int rowNum = 1; rowNum <= rowCount; rowNum++)
            {
                // Add element
                XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
                rowElement.SetAttribute("r", rowNum.ToString());
                sheetDataNode.AppendChild(rowElement);

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

                for (int colNum = 1; colNum <= colCount; colNum++)
                {
                    XmlElement cellElement = WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
                    cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString());
                    rowElement.AppendChild(cellElement);

                    ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
                    row.Cells.Add(colNum, cell);
                }
            }
        }
Exemple #2
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)
        {
            // create the new row element
            var rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);

            rowElement.Attributes.Append(WorksheetXml.CreateAttribute("r"));
            rowElement.Attributes["r"].Value = position.ToString();

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

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

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

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

                // now insert the new row
                if (insertAfterRowNode != null)
                {
                    sheetDataNode.InsertAfter(rowElement, insertAfterRowNode);
                }
            }
        }
Exemple #3
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)
        {
            XmlNode formatNode = WorksheetXml.SelectSingleNode("//d:conditionalFormatting", NameSpaceManager);

            if (formatNode == null)
            {
                formatNode = WorksheetXml.CreateElement("conditionalFormatting", ExcelPackage.schemaMain);
                XmlNode prevNode = WorksheetXml.SelectSingleNode("//d:mergeCells", NameSpaceManager);
                if (prevNode == null)
                {
                    prevNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
                }
                WorksheetXml.DocumentElement.InsertAfter(formatNode, prevNode);
            }
            XmlAttribute attr = formatNode.Attributes["sqref"];

            if (attr == null)
            {
                attr = WorksheetXml.CreateAttribute("sqref");
                formatNode.Attributes.Append(attr);
            }
            attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress);

            XmlNode node = formatNode.SelectSingleNode("./d:cfRule", NameSpaceManager);

            if (node == null)
            {
                node = WorksheetXml.CreateElement("cfRule", ExcelPackage.schemaMain);
                formatNode.AppendChild(node);
            }

            attr = node.Attributes["type"];
            if (attr == null)
            {
                attr = WorksheetXml.CreateAttribute("type");
                node.Attributes.Append(attr);
            }
            attr.Value = "dataBar";

            attr = node.Attributes["priority"];
            if (attr == null)
            {
                attr = WorksheetXml.CreateAttribute("priority");
                node.Attributes.Append(attr);
            }
            attr.Value = "1";

            // the following is poor code, but just an example!!!
            XmlNode databar = WorksheetXml.CreateElement("databar", ExcelPackage.schemaMain);

            node.AppendChild(databar);

            XmlNode child = WorksheetXml.CreateElement("cfvo", ExcelPackage.schemaMain);

            databar.AppendChild(child);
            attr = WorksheetXml.CreateAttribute("type");
            child.Attributes.Append(attr);
            attr.Value = "min";
            attr       = WorksheetXml.CreateAttribute("val");
            child.Attributes.Append(attr);
            attr.Value = "0";

            child = WorksheetXml.CreateElement("cfvo", ExcelPackage.schemaMain);
            databar.AppendChild(child);
            attr = WorksheetXml.CreateAttribute("type");
            child.Attributes.Append(attr);
            attr.Value = "max";
            attr       = WorksheetXml.CreateAttribute("val");
            child.Attributes.Append(attr);
            attr.Value = "0";

            child = WorksheetXml.CreateElement("color", ExcelPackage.schemaMain);
            databar.AppendChild(child);
            attr = WorksheetXml.CreateAttribute("rgb");
            child.Attributes.Append(attr);
            attr.Value = color;
        }