/// <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>
/// Adds numeric cell identifiers so that it is easier to work out position of cells
/// Private method, for internal use only!
/// </summary>
private void AddNumericCellIDs()
{
// process each row
foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
// remove the spans attribute. Excel simply recreates it when the file is opened.
XmlAttribute attr = (XmlAttribute)rowNode.Attributes.GetNamedItem("spans");
if (attr != null)
{
rowNode.Attributes.Remove(attr);
}
int row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value);
// process each cell in current row
foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager))
{
XmlAttribute cellAddressAttr = (XmlAttribute)colNode.Attributes.GetNamedItem("r");
if (cellAddressAttr != null)
{
string cellAddress = cellAddressAttr.Value;
int col = ExcelCell.GetColumnNumber(cellAddress);
attr = WorksheetXml.CreateAttribute(tempColumnNumberTag);
if (attr != null)
{
attr.Value = col.ToString();
colNode.Attributes.Append(attr);
// remove all cell Addresses like A1, A2, A3 etc.
colNode.Attributes.Remove(cellAddressAttr);
}
}
}
}
}
/// <summary>
/// Adds numeric cell identifiers so that it is easier to work out position of cells
/// Private method, for internal use only!
/// </summary>
private void AddNumericCellIDs()
{
// process each row
foreach (var rowNode in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager))
{
// remove the spans attribute. Excel simply recreates it when the file is opened.
XAttribute attr = (XAttribute)rowNode.Attribute("spans");
if (attr != null)
{
attr.Remove();
}
int row = Convert.ToInt32(rowNode.AttributeValue("r"));
// process each cell in current row
foreach (var colNode in rowNode.XPathSelectElements("./d:c", NameSpaceManager))
{
XAttribute cellAddressAttr = (XAttribute)colNode.Attribute("r");
if (cellAddressAttr != null)
{
string cellAddress = cellAddressAttr.Value;
int col = ExcelCell.GetColumnNumber(cellAddress);
attr = new XAttribute(tempColumnNumberTag, "");
if (attr != null)
{
attr.Value = col.ToString();
colNode.Add(attr);
// remove all cell Addresses like A1, A2, A3 etc.
cellAddressAttr.Remove();
}
}
}
}
}
/// <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);
}
}
}
/// <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);
}
}
}
/// <summary>
/// Replaces the numeric cell identifiers we inserted with AddNumericCellIDs with the traditional
/// A1, A2 cell identifiers that Excel understands.
/// Private method, for internal use only!
/// </summary>
private void ReplaceNumericCellIDs()
{
int maxColumn = 0;
// process each row
foreach (var rowNode in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager))
{
int row = Convert.ToInt32(rowNode.AttributeValue("r"));
int count = 0;
// process each cell in current row
foreach (var colNode in rowNode.XPathSelectElements("./d:c", NameSpaceManager))
{
XAttribute colNumber = (XAttribute)colNode.Attribute(tempColumnNumberTag);
if (colNumber != null)
{
count++;
if (count > maxColumn)
{
maxColumn = count;
}
int col = Convert.ToInt32(colNumber.Value);
string cellAddress = ExcelCell.GetColumnLetter(col) + row.ToString();
XAttribute attr = new XAttribute("r", "");
if (attr != null)
{
attr.Value = cellAddress;
// the cellAddress needs to be the first attribute, otherwise Excel complains
if (!colNode.Attributes().Any())
{
colNode.Add(attr);
}
else
{
//var firstAttr =colNode.Attributes().First();
colNode.AddFirst(attr);
}
}
// remove all numeric cell addresses added by AddNumericCellIDs
colNumber.Remove();
}
}
}
// process each row and add the spans attribute
// TODO: Need to add proper spans handling.
//foreach (var rowNode in XmlDoc.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
//{
// // we must add or update the "spans" attribute of each row
// XAttribute spans = (XAttribute)rowNode.Attributes.GetNamedItem("spans");
// if (spans == null)
// {
// spans = XmlDoc.CreateAttribute("spans");
// rowNode.Add(spans);
// }
// spans.Value = "1:" + maxColumn.ToString();
//}
}
/// <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>
/// Replaces the numeric cell identifiers we inserted with AddNumericCellIDs with the traditional
/// A1, A2 cell identifiers that Excel understands.
/// Private method, for internal use only!
/// </summary>
private void ReplaceNumericCellIDs()
{
var maxColumn = 0;
// process each row
foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
var row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value);
var count = 0;
// process each cell in current row
foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager))
{
var colNumber = (XmlAttribute)colNode.Attributes.GetNamedItem(tempColumnNumberTag);
if (colNumber != null)
{
count++;
if (count > maxColumn)
{
maxColumn = count;
}
var col = Convert.ToInt32(colNumber.Value);
var cellAddress = ExcelCell.GetColumnLetter(col) + row;
var attr = WorksheetXml.CreateAttribute("r");
if (attr != null)
{
attr.Value = cellAddress;
// the cellAddress needs to be the first attribute, otherwise Excel complains
if (colNode.Attributes.Count == 0)
{
colNode.Attributes.Append(attr);
}
else
{
colNode.Attributes.InsertBefore(attr, (XmlAttribute)colNode.Attributes.Item(0));
}
}
// remove all numeric cell addresses added by AddNumericCellIDs
colNode.Attributes.Remove(colNumber);
}
}
}
// process each row and add the spans attribute
// TODO: Need to add proper spans handling.
//foreach (XmlNode rowNode in XmlDoc.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
//{
// // we must add or update the "spans" attribute of each row
// XmlAttribute spans = (XmlAttribute)rowNode.Attributes.GetNamedItem("spans");
// if (spans == null)
// {
// spans = XmlDoc.CreateAttribute("spans");
// rowNode.Attributes.Append(spans);
// }
// spans.Value = "1:" + maxColumn.ToString();
//}
}
/// <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>
/// 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)
{
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
XmlNodeList nodes = sheetDataNode.ChildNodes;
int nodeCount = nodes.Count;
int rowNodeID = 0;
XmlNode rowNode = null;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attributes["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].Attributes["r"].Value);
if (currentRowID > rowToDelete)
{
nodes[i].Attributes["r"].Value = Convert.ToString(currentRowID - 1);
// now update any formula that are in the row
XmlNodeList formulaNodes = nodes[i].SelectNodes("./d:c/d:f", NameSpaceManager);
foreach (XmlNode formulaNode in formulaNodes)
{
formulaNode.InnerText = ExcelCell.UpdateFormulaReferences(formulaNode.InnerText, -1, 0, rowToDelete, 0);
}
}
}
}
// delete the row
if (rowNode != null)
{
sheetDataNode.RemoveChild(rowNode);
}
}
// Update stored rows
Rows.Remove(rowToDelete);
ShiftRows(rowToDelete, -1);
}
public IEnumerable <ExcelCell> GetUsedCells()
{
// process each row
foreach (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
var row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value);
// process each cell in current row
foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager))
{
var column = Convert.ToInt32(colNode.Attributes[tempColumnNumberTag].Value);
yield return(new ExcelCell(this, row, column));
}
}
}
/// <summary>
/// Creates a shared formula based on the formula already in startCell
/// Essentially this supports the formula attributes such as t="shared" ref="B2:B4" si="0"
/// as per Brian Jones: Open XML Formats blog. See
/// http://blogs.msdn.com/brian_jones/archive/2006/11/15/simple-spreadsheetml-file-part-2-of-3.aspx
/// </summary>
/// <param name="startCell">The cell containing the formula</param>
/// <param name="endCell">The end cell (i.e. end of the range)</param>
public void CreateSharedFormula(ExcelCell startCell, ExcelCell endCell)
{
XmlElement formulaElement;
var formula = startCell.Formula;
if (formula == "")
{
throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!");
}
// find or create a shared formula ID
var sharedID = -1;
foreach (XmlNode node in WorksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager))
{
var curID = int.Parse(node.Value);
if (curID > sharedID)
{
sharedID = curID;
}
}
sharedID++; // first value must be zero
for (var row = startCell.Row; row <= endCell.Row; row++)
{
for (var col = startCell.Column; col <= endCell.Column; col++)
{
var cell = Cell(row, col);
// to force Excel to re-calculate the formula, we must remove the value
cell.RemoveValue();
formulaElement = (XmlElement)cell.Element.SelectSingleNode("./d:f", NameSpaceManager);
if (formulaElement == null)
{
formulaElement = cell.AddFormulaElement();
}
formulaElement.SetAttribute("t", "shared");
formulaElement.SetAttribute("si", sharedID.ToString());
}
}
// finally add the shared cell range to the startCell
formulaElement = (XmlElement)startCell.Element.SelectSingleNode("./d:f", NameSpaceManager);
formulaElement.SetAttribute("ref", string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress));
}
/// <summary>
/// Saves the worksheet to the package. For internal use only.
/// </summary>
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.SelectSingleNode("//d:pageSetup", NameSpaceManager);
if (pageSetup != null)
{
var attr =
(XmlAttribute)pageSetup.Attributes.GetNamedItem("id", ExcelPackage.schemaRelationships);
if (attr != null)
{
var relID = attr.Value;
// first delete the attribute from the XML
pageSetup.Attributes.Remove(attr);
// get the URI
var relPrinterSettings = Part.GetRelationship(relID);
var 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
// 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
var partPack = xlPackage.Package.GetPart(WorksheetUri);
WorksheetXml.Save(partPack.GetStream(FileMode.Create, FileAccess.Write));
xlPackage.WriteDebugFile(WorksheetXml, @"xl\worksheets", "sheet" + SheetID + ".xml");
//clear field _worksheetXml becouse need rebuild column number tag in columns
_worksheetXml = null;
}
/// <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");
}
}
Dictionary <int, ExcelRow> ReadData()
{
Dictionary <int, ExcelRow> rows = new Dictionary <int, ExcelRow>();
foreach (var rowElement in WorksheetXml.XPathSelectElements("//d:sheetData/d:row", NameSpaceManager))
{
int rowNum = Convert.ToInt32(rowElement.Attribute("r").Value);
ExcelRow row = new ExcelRow(this, rowElement);
rows.Add(rowNum, row);
// Get all cells for the row
foreach (var cellElement in rowElement.XPathSelectElements("./d:c", NameSpaceManager))
{
int colNum = Convert.ToInt32(cellElement.AttributeValue(ExcelWorksheet.tempColumnNumberTag));
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
return(rows);
}
/// <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;
}
