/// <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);
}
}
}
/// <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);
}
/// <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));
}
/// <summary>
/// Deletes the specified row from the worksheet.
/// If shiftOtherRowsUp=true then all formula are updated to take account of the deleted row.
/// </summary>
/// <param name="rowToDelete">The number of the row to be deleted</param>
/// <param name="shiftOtherRowsUp">Set to true if you want the other rows renumbered so they all move up</param>
public void DeleteRow(int rowToDelete, bool shiftOtherRowsUp)
{
var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
var nodes = sheetDataNode.Nodes().Cast <XElement>().ToList();
int nodeCount = nodes.Count;
int rowNodeID = 0;
XElement rowNode = null;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID == rowToDelete)
{
rowNodeID = i;
rowNode = nodes[i];
}
}
if (shiftOtherRowsUp)
{
// update the existing row ids
for (int i = rowNodeID + 1; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID > rowToDelete)
{
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, rowToDelete, 0);
}
}
}
}
// delete the row
if (rowNode != null)
{
rowNode.Remove();
}
}
// Update stored rows
Rows.Remove(rowToDelete);
ShiftRows(rowToDelete, -1);
}
/// <summary>
/// Saves the worksheet to the package. For internal use only.
/// </summary>
protected internal void Save() // Worksheet Save
{
#region Delete the printer settings component (if it exists)
// we also need to delete the relationship from the pageSetup tag
var pageSetup = WorksheetXml.XPathSelectElement("//d:pageSetup", NameSpaceManager);
if (pageSetup != null)
{
XAttribute attr = pageSetup.Attribute(ExcelPackage.schemaRelationships + "id");
if (attr != null)
{
string relID = attr.Value;
// first delete the attribute from the XML
attr.Remove();
// get the URI
PackageRelationship relPrinterSettings = Part.GetRelationship(relID);
Uri printerSettingsUri = new Uri("/xl" + relPrinterSettings.TargetUri.ToString().Replace("..", ""), UriKind.Relative);
// now delete the relationship
Part.DeleteRelationship(relPrinterSettings.Id);
// now delete the part from the package
xlPackage.Package.DeletePart(printerSettingsUri);
}
}
#endregion
if (_worksheetXml != null)
{
// save the header & footer (if defined)
if (_headerFooter != null)
{
HeaderFooter.Save();
}
// replace the numeric Cell IDs we inserted with AddNumericCellIDs()
ReplaceNumericCellIDs();
// save worksheet to package
PackagePart partPack = xlPackage.Package.GetPart(WorksheetUri);
WorksheetXml.Save(partPack.GetStream(FileMode.Create, FileAccess.Write));
xlPackage.WriteDebugFile(WorksheetXml, @"xl\worksheets", "sheet" + SheetID + ".xml");
}
}