/// <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>
/// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments
/// if they fall after the afterRow and afterColumn.
/// Supports inserting rows and columns into existing templates.
/// </summary>
/// <param name="Formula">The Excel formula</param>
/// <param name="rowIncrement">The amount to increment the cell reference by</param>
/// <param name="colIncrement">The amount to increment the cell reference by</param>
/// <param name="afterRow">Only change rows after this row</param>
/// <param name="afterColumn">Only change columns after this column</param>
/// <returns></returns>
public static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn)
{
string newFormula = "";
Regex getAlphaNumeric = new Regex(@"[^a-zA-Z0-9]", RegexOptions.IgnoreCase);
Regex getSigns = new Regex(@"[a-zA-Z0-9]", RegexOptions.IgnoreCase);
string alphaNumeric = getAlphaNumeric.Replace(Formula, " ").Replace(" ", " ");
string signs = getSigns.Replace(Formula, " ");
char[] chrSigns = signs.ToCharArray();
int count = 0;
int length = 0;
foreach (string cellAddress in alphaNumeric.Split(' '))
{
count++;
length += cellAddress.Length;
// if the cellAddress contains an alpha part followed by a number part, then it is a valid cellAddress
int row = GetRowNumber(cellAddress);
int col = GetColumnNumber(cellAddress);
string newCellAddress = "";
if (ExcelCell.GetCellAddress(row, col) == cellAddress) // this checks if the cellAddress is valid
{
// we have a valid cell address so change its value (if necessary)
if (row >= afterRow)
{
row += rowIncrement;
}
if (col >= afterColumn)
{
col += colIncrement;
}
newCellAddress = GetCellAddress(row, col);
}
if (newCellAddress == "")
{
newFormula += cellAddress;
}
else
{
newFormula += newCellAddress;
}
for (int i = length; i < signs.Length; i++)
{
if (chrSigns[i] == ' ')
{
break;
}
if (chrSigns[i] != ' ')
{
length++;
newFormula += chrSigns[i].ToString();
}
}
}
return(newFormula);
}
/// <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>
/// Returns the row number from the cellAddress
/// e.g. D5 would return 5
/// </summary>
/// <param name="cellAddress">An Excel format cell addresss (e.g. D5)</param>
/// <returns>The row number</returns>
public static int GetRowNumber(string cellAddress)
{
// find out position where characters stop and numbers begin
int iPos = 0;
bool found = false;
foreach (char chr in cellAddress.ToCharArray())
{
iPos++;
if (char.IsNumber(chr))
{
found = true;
break;
}
}
if (found)
{
string NumberPart = cellAddress.Substring(iPos - 1, cellAddress.Length - (iPos - 1));
if (ExcelCell.IsNumericValue(NumberPart))
{
return(int.Parse(NumberPart));
}
}
return(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)
{
XmlNode rowNode = null;
// create the new row element
XmlElement rowElement = WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
rowElement.Attributes.Append(WorksheetXml.CreateAttribute("r"));
rowElement.Attributes["r"].Value = position.ToString();
XmlNode sheetDataNode = WorksheetXml.SelectSingleNode("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
int renumberFrom = 1;
XmlNodeList nodes = sheetDataNode.ChildNodes;
int nodeCount = nodes.Count;
XmlNode insertAfterRowNode = null;
int insertAfterRowNodeID = 0;
for (int i = 0; i < nodeCount; i++)
{
int 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 (int i = insertAfterRowNodeID + 1; i < nodeCount; i++)
{
int 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
XmlNodeList 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)
{
rowNode = 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 (XmlNode rowNode in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
int row = Convert.ToInt32(rowNode.Attributes.GetNamedItem("r").Value);
int count = 0;
// process each cell in current row
foreach (XmlNode colNode in rowNode.SelectNodes("./d:c", NameSpaceManager))
{
XmlAttribute colNumber = (XmlAttribute)colNode.Attributes.GetNamedItem(tempColumnNumberTag);
if (colNumber != null)
{
count++;
if (count > maxColumn)
{
maxColumn = count;
}
int col = Convert.ToInt32(colNumber.Value);
string cellAddress = ExcelCell.GetColumnLetter(col) + row.ToString();
XmlAttribute 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>
/// Provides access to an individual cell within the worksheet.
/// </summary>
/// <param name="rowNum">The row number in the worksheet</param>
/// <param name="colNum">The column number in the worksheet</param>
/// <returns></returns>
public ExcelCell Cell(int rowNum, int colNum)
{
ExcelRow row = Row(rowNum);
ExcelCell cell;
if (row.Cells.TryGetValue(colNum, out cell))
{
return(cell);
}
cell = new ExcelCell(this, rowNum, colNum);
row.Cells.Add(colNum, cell);
return(cell);
}
/// <summary>
/// Creates a ExcelWorkSheetDimension using a string with Cell Range representation like 'A1:B5'.
/// </summary>
/// <param name="dimension">a string with Cell Range representation like 'A1:B5'</param>
public ExcelWorkSheetDimension(String dimension)
{
String[] dimensions = dimension.Split(':');
this.topLeft = dimensions[0];
this.bottomRight = dimensions[1];
if (!ExcelCell.IsValidCellAddress(topLeft) || (!ExcelCell.IsValidCellAddress(BottomRight)))
{
throw new ArgumentException("No valid excel sheet dimension!");
}
firstRow = ExcelCell.GetRowNumber(topLeft);
firstCol = ExcelCell.GetColumnNumber(topLeft);
lastCol = ExcelCell.GetColumnNumber(bottomRight);
lastRow = ExcelCell.GetRowNumber(bottomRight);
}
/// <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);
}
/// <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;
string formula = startCell.Formula;
if (formula == "")
{
throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!");
}
// find or create a shared formula ID
int sharedID = -1;
foreach (XmlNode node in _worksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager))
{
int curID = int.Parse(node.Value);
if (curID > sharedID)
{
sharedID = curID;
}
}
sharedID++; // first value must be zero
for (int row = startCell.Row; row <= endCell.Row; row++)
{
for (int col = startCell.Column; col <= endCell.Column; col++)
{
ExcelCell 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>
/// Get a range reference in Excel format
/// e.g. GetRange("sheet", 1,5,10,2) => "sheet!$J$1:$K$5"
/// </summary>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="rowCount">Number of rows to include, >=1</param>
/// <param name="startCol"></param>
/// <param name="colCount">Number of columns to include, >=1</param>
/// <returns></returns>
public static String GetRangeRef(String worksheet,
int startRow, int rowCount, int startCol, int colCount)
{
// Be tolerant
if (rowCount <= 0)
{
rowCount = 1;
}
if (colCount <= 0)
{
colCount = 1;
}
return(worksheet + "!" +
"$" + ExcelCell.GetColumnLetter(startCol) + "$" + startRow + ":" +
"$" + ExcelCell.GetColumnLetter(startCol + colCount - 1) + "$" + (startRow + rowCount - 1));
}
internal ExcelCell Clone(ExcelWorksheet added, int row, int col)
{
ExcelCell newCell = new ExcelCell(added, row, col);
if (_hyperlink != null)
{
newCell.Hyperlink = Hyperlink;
}
newCell._formula = _formula;
newCell._formulaR1C1 = _formulaR1C1;
newCell.IsRichText = IsRichText;
newCell.Merge = Merge;
newCell._sharedFormulaID = _sharedFormulaID;
newCell._styleName = _styleName;
newCell._styleID = _styleID;
newCell._value = _value;
return(newCell);
}
Dictionary <int, ExcelRow> ReadData()
{
Dictionary <int, ExcelRow> rows = new Dictionary <int, ExcelRow>();
foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value);
ExcelRow row = new ExcelRow(this, rowElement);
rows.Add(rowNum, row);
// Get all cells for the row
foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager))
{
int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value);
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;
}
internal ExcelCell Clone(ExcelWorksheet added, int row, int col)
{
ExcelCell newCell = new ExcelCell(added, row, col);
if(_hyperlink!=null) newCell.Hyperlink = Hyperlink;
newCell._formula = _formula;
newCell._formulaR1C1 = _formulaR1C1;
newCell.IsRichText = IsRichText;
newCell.Merge = Merge;
newCell._sharedFormulaID = _sharedFormulaID;
newCell._styleName = _styleName;
newCell._styleID = _styleID;
newCell._value = _value;
return newCell;
}
internal ExcelCell Clone(ExcelWorksheet added)
{
ExcelCell newCell = new ExcelCell(added, _row, _col);
//newCell.DataType = DataType;
//if (Formula != "" && SharedFormulaID < 0)
//{
// newCell.Formula = Formula;
// added._formulaCells.Add(newCell);
//}
if(_hyperlink!=null) newCell.Hyperlink = Hyperlink;
newCell._formula = _formula;
newCell._formulaR1C1 = _formulaR1C1;
newCell.IsRichText = IsRichText;
newCell.Merge = Merge;
newCell._sharedFormulaID = _sharedFormulaID;
newCell._styleName = _styleName;
newCell._styleID = _styleID;
newCell._value = _value;
return newCell;
}
/// <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 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;
}
/// <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;
string formula = startCell.Formula;
if (formula == "") throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!");
// find or create a shared formula ID
int sharedID = -1;
foreach (XmlNode node in _worksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager))
{
int curID = int.Parse(node.Value);
if (curID > sharedID) sharedID = curID;
}
sharedID++; // first value must be zero
for (int row = startCell.Row; row <= endCell.Row; row++)
{
for (int col = startCell.Column; col <= endCell.Column; col++)
{
ExcelCell 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));
}
internal OpenXmlExcelCell(ExcelCell obj)
{
_obj = obj;
}
Dictionary<int, ExcelRow> ReadData()
{
Dictionary<int, ExcelRow> rows = new Dictionary<int,ExcelRow>();
foreach (XmlElement rowElement in WorksheetXml.SelectNodes("//d:sheetData/d:row", NameSpaceManager))
{
int rowNum = Convert.ToInt32(rowElement.Attributes.GetNamedItem("r").Value);
ExcelRow row = new ExcelRow(this, rowElement);
rows.Add(rowNum, row);
// Get all cells for the row
foreach (XmlElement cellElement in rowElement.SelectNodes("./d:c", NameSpaceManager))
{
int colNum = Convert.ToInt32(cellElement.Attributes[ExcelWorksheet.tempColumnNumberTag].Value);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
return rows;
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1);
newCol.ColumnMax = column.ColumnMax;
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
private void CloneCells(ExcelWorksheet Copy, ExcelWorksheet added)
{
bool sameWorkbook = (Copy.Workbook == _pck.Workbook);
bool doAdjust = _pck.DoAdjustDrawings;
_pck.DoAdjustDrawings = false;
added.MergedCells.List.AddRange(Copy.MergedCells.List);
//Formulas
foreach (IRangeID f in Copy._formulaCells)
{
added._formulaCells.Add(f);
}
//Shared Formulas
foreach (int key in Copy._sharedFormulas.Keys)
{
added._sharedFormulas.Add(key, Copy._sharedFormulas[key]);
}
Dictionary <int, int> styleCashe = new Dictionary <int, int>();
//Cells
foreach (ExcelCell cell in Copy._cells)
{
if (sameWorkbook) //Same workbook == same styles
{
added._cells.Add(cell.Clone(added));
}
else
{
ExcelCell addedCell = cell.Clone(added);
if (styleCashe.ContainsKey(cell.StyleID))
{
addedCell.StyleID = styleCashe[cell.StyleID];
}
else
{
addedCell.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, cell.StyleID);
if (cell.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(cell.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(cell.StyleName);
ns.StyleXfId = addedCell.StyleID;
}
}
styleCashe.Add(cell.StyleID, addedCell.StyleID);
}
added._cells.Add(addedCell);
}
}
//Rows
foreach (ExcelRow row in Copy._rows)
{
row.Clone(added);
if (!sameWorkbook) //Same workbook == same styles
{
ExcelRow addedRow = added.Row(row.Row) as ExcelRow;
if (styleCashe.ContainsKey(row.StyleID))
{
addedRow.StyleID = styleCashe[row.StyleID];
}
else
{
addedRow.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedRow.StyleID);
if (row.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(row.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(row.StyleName);
ns.StyleXfId = addedRow.StyleID;
}
}
styleCashe.Add(row.StyleID, addedRow.StyleID);
}
}
}
//Columns
foreach (ExcelColumn col in Copy._columns)
{
col.Clone(added);
if (!sameWorkbook) //Same workbook == same styles
{
ExcelColumn addedCol = added.Column(col.ColumnMin) as ExcelColumn;
if (styleCashe.ContainsKey(col.StyleID))
{
addedCol.StyleID = styleCashe[col.StyleID];
}
else
{
addedCol.StyleID = added.Workbook.Styles.CloneStyle(Copy.Workbook.Styles, addedCol.StyleID);
if (col.StyleName != "") //Named styles
{
if (!Copy.Workbook.Styles.NamedStyles.ExistsKey(col.StyleName))
{
var ns = Copy.Workbook.Styles.CreateNamedStyle(col.StyleName);
ns.StyleXfId = addedCol.StyleID;
}
}
styleCashe.Add(col.StyleID, addedCol.StyleID);
}
}
}
added._package.DoAdjustDrawings = doAdjust;
}
/// <summary>
/// Provides access to an individual cell within the worksheet.
/// </summary>
/// <param name="rowNum">The row number in the worksheet</param>
/// <param name="colNum">The column number in the worksheet</param>
/// <returns></returns>
public ExcelCell Cell(int rowNum, int colNum)
{
ExcelRow row = Row(rowNum);
ExcelCell cell;
if (row.Cells.TryGetValue(colNum, out cell)) { return cell; }
cell = new ExcelCell(this, rowNum, colNum);
row.Cells.Add(colNum, cell);
return cell;
}
