/****************************************************************************************/
#region Constructors
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingCollection"/>
/// </summary>
/// <param name="worksheet"></param>
internal ExcelConditionalFormattingCollection(
ExcelWorksheet worksheet)
: base(
worksheet.NameSpaceManager,
worksheet.WorksheetXml.DocumentElement)
{
Require.Argument(worksheet).IsNotNull("worksheet");
_worksheet = worksheet;
SchemaNodeOrder = _worksheet.SchemaNodeOrder;
// Look for all the <conditionalFormatting>
var conditionalFormattingNodes = TopNode.SelectNodes(
"//" + ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
_worksheet.NameSpaceManager);
// Check if we found at least 1 node
if ((conditionalFormattingNodes != null) &&
(conditionalFormattingNodes.Count > 0))
{
// Foreach <conditionalFormatting>
foreach (XmlNode conditionalFormattingNode in conditionalFormattingNodes)
{
// Check if @sqref attribute exists
if (conditionalFormattingNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Sqref] == null)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingSqrefAttribute);
}
// Get the @sqref attribute
ExcelAddress address = new ExcelAddress(
conditionalFormattingNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Sqref].Value);
// Check for all the <cfRules> nodes and load them
var cfRuleNodes = conditionalFormattingNode.SelectNodes(
ExcelConditionalFormattingConstants.Paths.CfRule,
_worksheet.NameSpaceManager);
// Foreach <cfRule> inside the current <conditionalFormatting>
foreach (XmlNode cfRuleNode in cfRuleNodes)
{
// Check if @type attribute exists
if (cfRuleNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Type] == null)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingTypeAttribute);
}
// Check if @priority attribute exists
if (cfRuleNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Priority] == null)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingPriorityAttribute);
}
// Get the <cfRule> main attributes
string typeAttribute = ExcelConditionalFormattingHelper.GetAttributeString(
cfRuleNode,
ExcelConditionalFormattingConstants.Attributes.Type);
int priority = ExcelConditionalFormattingHelper.GetAttributeInt(
cfRuleNode,
ExcelConditionalFormattingConstants.Attributes.Priority);
// Transform the @type attribute to EPPlus Rule Type (slighty diferente)
var type = ExcelConditionalFormattingRuleType.GetTypeByAttrbiute(
typeAttribute,
cfRuleNode,
_worksheet.NameSpaceManager);
// Create the Rule according to the correct type, address and priority
var cfRule = ExcelConditionalFormattingRuleFactory.Create(
type,
address,
priority,
_worksheet,
cfRuleNode);
// Add the new rule to the list
if (cfRule != null)
{
_rules.Add(cfRule);
}
}
}
}
}
public void Remove(ExcelRichText Item)
{
TopNode.RemoveChild(Item.TopNode);
}
internal ExcelPivotTable(PackageRelationship rel, ExcelWorksheet sheet) :
base(sheet.NameSpaceManager)
{
WorkSheet = sheet;
PivotTableUri = PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
Relationship = rel;
var pck = sheet._package.Package;
Part = pck.GetPart(PivotTableUri);
PivotTableXml = new XmlDocument();
LoadXmlSafe(PivotTableXml, Part.GetStream());
init();
TopNode = PivotTableXml.DocumentElement;
Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));
_cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);
LoadFields();
//Add row fields.
foreach (XmlElement rowElem in TopNode.SelectNodes("d:rowFields/d:field", NameSpaceManager))
{
int x;
if (int.TryParse(rowElem.GetAttribute("x"), out x) && x >= 0)
{
RowFields.AddInternal(Fields[x]);
}
else
{
rowElem.ParentNode.RemoveChild(rowElem);
}
}
////Add column fields.
foreach (XmlElement colElem in TopNode.SelectNodes("d:colFields/d:field", NameSpaceManager))
{
int x;
if (int.TryParse(colElem.GetAttribute("x"), out x) && x >= 0)
{
ColumnFields.AddInternal(Fields[x]);
}
else
{
colElem.ParentNode.RemoveChild(colElem);
}
}
//Add Page elements
//int index = 0;
foreach (XmlElement pageElem in TopNode.SelectNodes("d:pageFields/d:pageField", NameSpaceManager))
{
int fld;
if (int.TryParse(pageElem.GetAttribute("fld"), out fld) && fld >= 0)
{
var field = Fields[fld];
field._pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, pageElem, field, fld);
PageFields.AddInternal(field);
}
}
//Add data elements
//index = 0;
foreach (XmlElement dataElem in TopNode.SelectNodes("d:dataFields/d:dataField", NameSpaceManager))
{
int fld;
if (int.TryParse(dataElem.GetAttribute("fld"), out fld) && fld >= 0)
{
var field = Fields[fld];
var dataField = new ExcelPivotTableDataField(NameSpaceManager, dataElem, field);
DataFields.AddInternal(dataField);
}
}
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
{
if (firstField == false)
{
//Pivot field
var topNode = _table.PivotTableXml.SelectSingleNode("//d:pivotFields", _table.NameSpaceManager);
var fieldNode = _table.PivotTableXml.CreateElement("pivotField", ExcelPackage.schemaMain);
fieldNode.SetAttribute("compact", "0");
fieldNode.SetAttribute("outline", "0");
fieldNode.SetAttribute("showAll", "0");
fieldNode.SetAttribute("defaultSubtotal", "0");
topNode.AppendChild(fieldNode);
var field = new ExcelPivotTableField(_table.NameSpaceManager, fieldNode, _table, _table.Fields.Count, Index);
field.DateGrouping = groupBy;
XmlNode rowColFields;
if (IsRowField)
{
rowColFields = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
}
else
{
rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
}
int fieldIndex, index = 0;
foreach (XmlElement rowfield in rowColFields.ChildNodes)
{
if (int.TryParse(rowfield.GetAttribute("x"), out fieldIndex))
{
if (_table.Fields[fieldIndex].BaseIndex == BaseIndex)
{
var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
newElement.SetAttribute("x", field.Index.ToString());
rowColFields.InsertBefore(newElement, rowfield);
break;
}
}
index++;
}
if (IsRowField)
{
_table.RowFields.Insert(field, index);
}
else
{
_table.ColumnFields.Insert(field, index);
}
_table.Fields.AddInternal(field);
AddCacheField(field, startDate, endDate, interval);
return(field);
}
else
{
firstField = false;
DateGrouping = groupBy;
Compact = false;
SetDateGroup(groupBy, startDate, endDate, interval);
return(this);
}
}
public void Clear()
{
_list.Clear();
TopNode.RemoveAll();
}
/// <summary>
/// Remove all mentions from the collection
/// </summary>
internal void Clear()
{
_mentionList.Clear();
TopNode.RemoveAll();
}
internal XmlNode GetNode(string path)
{
return(TopNode.SelectSingleNode(path, NameSpaceManager));
}
internal XmlNodeList GetNodes(string path)
{
return(TopNode.SelectNodes(path, NameSpaceManager));
}
public void Visit(TopNode node)
{
node.Accept(_visitor);
}
private void CreateNewChart(ExcelDrawings drawings, ExcelChart topChart, XmlDocument chartXml = null, eChartType?type = null)
{
if (topChart == null)
{
XmlElement graphFrame = TopNode.OwnerDocument.CreateElement("graphicFrame", ExcelPackage.schemaSheetDrawings);
graphFrame.SetAttribute("macro", "");
TopNode.AppendChild(graphFrame);
graphFrame.InnerXml = string.Format("<xdr:nvGraphicFramePr><xdr:cNvPr id=\"{0}\" name=\"Chart 1\" /><xdr:cNvGraphicFramePr /></xdr:nvGraphicFramePr><xdr:xfrm><a:off x=\"0\" y=\"0\" /> <a:ext cx=\"0\" cy=\"0\" /></xdr:xfrm><a:graphic><a:graphicData uri=\"http://schemas.openxmlformats.org/drawingml/2006/chart\"><c:chart xmlns:c=\"http://schemas.openxmlformats.org/drawingml/2006/chart\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\"rId1\" /> </a:graphicData> </a:graphic>", _id);
TopNode.AppendChild(TopNode.OwnerDocument.CreateElement("clientData", ExcelPackage.schemaSheetDrawings));
var package = drawings.Worksheet._package.Package;
UriChart = GetNewUri(package, "/xl/charts/chart{0}.xml");
if (chartXml == null)
{
ChartXml = new XmlDocument
{
PreserveWhitespace = ExcelPackage.preserveWhitespace
};
LoadXmlSafe(ChartXml, ChartStartXml(type.Value), Encoding.UTF8);
}
else
{
ChartXml = chartXml;
}
// save it to the package
Part = package.CreatePart(UriChart, "application/vnd.openxmlformats-officedocument.drawingml.chart+xml", _drawings._package.Compression);
StreamWriter streamChart = new StreamWriter(Part.GetStream(FileMode.Create, FileAccess.Write));
ChartXml.Save(streamChart);
streamChart.Close();
package.Flush();
var chartRelation = drawings.Part.CreateRelationship(UriHelper.GetRelativeUri(drawings.UriDrawing, UriChart), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart");
graphFrame.SelectSingleNode("a:graphic/a:graphicData/c:chart", NameSpaceManager).Attributes["r:id"].Value = chartRelation.Id;
package.Flush();
_chartNode = ChartXml.SelectSingleNode(string.Format("c:chartSpace/c:chart/c:plotArea/{0}", GetChartNodeText()), NameSpaceManager);
}
else
{
ChartXml = topChart.ChartXml;
Part = topChart.Part;
_plotArea = topChart.PlotArea;
UriChart = topChart.UriChart;
_axis = topChart._axis;
XmlNode preNode = _plotArea.ChartTypes[_plotArea.ChartTypes.Count - 1].ChartNode;
_chartNode = ((XmlDocument)ChartXml).CreateElement(GetChartNodeText(), ExcelPackage.schemaChart);
preNode.ParentNode.InsertAfter(_chartNode, preNode);
if (topChart.Axis.Length == 0)
{
AddAxis();
}
string serieXML = GetChartSerieStartXml(type.Value, int.Parse(topChart.Axis[0].Id), int.Parse(topChart.Axis[1].Id), topChart.Axis.Length > 2 ? int.Parse(topChart.Axis[2].Id) : -1);
_chartNode.InnerXml = serieXML;
}
GetPositionSize();
if (IsType3D())
{
Init3DProperties();
}
}
/****************************************************************************************/
#region Private Properties
#endregion Private Properties
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingFiveIconSet(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.FiveIconSet,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode != null && itemElementNode.HasChildNodes)
{
XmlNode iconNode4 = TopNode.SelectSingleNode("d:iconSet/d:cfvo[position()=4]", NameSpaceManager);
Icon4 = new ExcelConditionalFormattingIconDataBarValue(
eExcelConditionalFormattingRuleType.FiveIconSet,
address,
worksheet,
iconNode4,
namespaceManager);
XmlNode iconNode5 = TopNode.SelectSingleNode("d:iconSet/d:cfvo[position()=5]", NameSpaceManager);
Icon5 = new ExcelConditionalFormattingIconDataBarValue(
eExcelConditionalFormattingRuleType.FiveIconSet,
address,
worksheet,
iconNode5,
namespaceManager);
}
else
{
XmlNode iconSetNode = TopNode.SelectSingleNode("d:iconSet", NameSpaceManager);
var iconNode4 = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(iconNode4);
Icon4 = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Percent,
60,
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode4,
namespaceManager);
var iconNode5 = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(iconNode5);
Icon5 = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Percent,
80,
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode5,
namespaceManager);
}
}
public XmlNodeList TestCases()
{
return(TopNode.SelectNodes("//test-case"));
}
private void CreateSelectionElement()
{
_selectionNode = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
TopNode.AppendChild(_selectionNode);
TopNode = _selectionNode;
}
/// <summary>
/// Freeze the columns/rows to left and above the cell
/// </summary>
/// <param name="Row"></param>
/// <param name="Column"></param>
public void FreezePanes(int Row, int Column)
{
//TODO:fix this method to handle splits as well.
if (Row == 1 && Column == 1)
{
UnFreezePanes();
}
string sqRef = SelectedRange, activeCell = ActiveCell;
XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
if (paneNode == null)
{
CreateNode(_paneNodePath);
paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
}
paneNode.RemoveAll(); //Clear all attributes
if (Column > 1)
{
paneNode.SetAttribute("xSplit", (Column - 1).ToString());
}
if (Row > 1)
{
paneNode.SetAttribute("ySplit", (Row - 1).ToString());
}
paneNode.SetAttribute("topLeftCell", ExcelCellBase.GetAddress(Row, Column));
paneNode.SetAttribute("state", "frozen");
RemoveSelection();
if (Row > 1 && Column == 1)
{
paneNode.SetAttribute("activePane", "bottomLeft");
XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel.SetAttribute("pane", "bottomLeft");
if (activeCell != "")
{
sel.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel.SetAttribute("sqref", sqRef);
}
sel.SetAttribute("sqref", sqRef);
TopNode.InsertAfter(sel, paneNode);
}
else if (Column > 1 && Row == 1)
{
paneNode.SetAttribute("activePane", "topRight");
XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel.SetAttribute("pane", "topRight");
if (activeCell != "")
{
sel.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel.SetAttribute("sqref", sqRef);
}
TopNode.InsertAfter(sel, paneNode);
}
else
{
paneNode.SetAttribute("activePane", "bottomRight");
XmlElement sel1 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel1.SetAttribute("pane", "topRight");
string cell = ExcelCellBase.GetAddress(1, Column);
sel1.SetAttribute("activeCell", cell);
sel1.SetAttribute("sqref", cell);
paneNode.ParentNode.InsertAfter(sel1, paneNode);
XmlElement sel2 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
cell = ExcelCellBase.GetAddress(Row, 1);
sel2.SetAttribute("pane", "bottomLeft");
sel2.SetAttribute("activeCell", cell);
sel2.SetAttribute("sqref", cell);
sel1.ParentNode.InsertAfter(sel2, sel1);
XmlElement sel3 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel3.SetAttribute("pane", "bottomRight");
if (activeCell != "")
{
sel3.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel3.SetAttribute("sqref", sqRef);
}
sel2.ParentNode.InsertAfter(sel3, sel2);
}
Panes = LoadPanes();
}
private void UpdateDefinedNamesXml()
{
try
{
XmlNode top = WorkbookXml.SelectSingleNode("//d:definedNames", NameSpaceManager);
if (!ExistsNames())
{
if (top != null)
{
TopNode.RemoveChild(top);
}
return;
}
else
{
if (top == null)
{
CreateNode("d:definedNames");
top = WorkbookXml.SelectSingleNode("//d:definedNames", NameSpaceManager);
}
else
{
top.RemoveAll();
}
foreach (ExcelNamedRange name in _names)
{
XmlElement elem = WorkbookXml.CreateElement("definedName", ExcelPackage.schemaMain);
top.AppendChild(elem);
elem.SetAttribute("name", name.Name);
if (name.IsNameHidden)
{
elem.SetAttribute("hidden", "1");
}
if (!string.IsNullOrEmpty(name.NameComment))
{
elem.SetAttribute("comment", name.NameComment);
}
SetNameElement(name, elem);
}
}
foreach (ExcelWorksheet ws in _worksheets)
{
if (!(ws is ExcelChartsheet))
{
foreach (ExcelNamedRange name in ws.Names)
{
XmlElement elem = WorkbookXml.CreateElement("definedName", ExcelPackage.schemaMain);
top.AppendChild(elem);
elem.SetAttribute("name", name.Name);
elem.SetAttribute("localSheetId", name.LocalSheetId.ToString());
if (name.IsNameHidden)
{
elem.SetAttribute("hidden", "1");
}
if (!string.IsNullOrEmpty(name.NameComment))
{
elem.SetAttribute("comment", name.NameComment);
}
SetNameElement(name, elem);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Internal error updating named ranges ", ex);
}
}
/// <summary>
/// Insert a rich text string at the specified index.
/// </summary>
/// <param name="index">The zero-based index at which rich text should be inserted.</param>
/// <param name="text">The text to insert.</param>
/// <returns></returns>
public ExcelRichText Insert(int index, string text)
{
ConvertRichtext();
XmlDocument doc;
if (TopNode is XmlDocument)
{
doc = TopNode as XmlDocument;
}
else
{
doc = TopNode.OwnerDocument;
}
var node = doc.CreateElement("d", "r", ExcelPackage.schemaMain);
if (index < _list.Count)
{
TopNode.InsertBefore(node, TopNode.ChildNodes[index]);
}
else
{
TopNode.AppendChild(node);
}
var rt = new ExcelRichText(NameSpaceManager, node, this);
if (_list.Count > 0)
{
ExcelRichText prevItem = _list[index < _list.Count ? index : _list.Count - 1];
rt.FontName = prevItem.FontName;
rt.Size = prevItem.Size;
if (prevItem.Color.IsEmpty)
{
rt.Color = Color.Black;
}
else
{
rt.Color = prevItem.Color;
}
rt.PreserveSpace = rt.PreserveSpace;
rt.Bold = prevItem.Bold;
rt.Italic = prevItem.Italic;
rt.UnderLine = prevItem.UnderLine;
}
else if (_cells == null)
{
rt.FontName = "Calibri";
rt.Size = 11;
}
else
{
var style = _cells.Offset(0, 0).Style;
rt.FontName = style.Font.Name;
rt.Size = style.Font.Size;
rt.Bold = style.Font.Bold;
rt.Italic = style.Font.Italic;
_cells.IsRichText = true;
}
rt.Text = text;
rt.PreserveSpace = true;
if (_cells != null)
{
rt.SetCallback(UpdateCells);
UpdateCells();
}
_list.Insert(index, rt);
return(rt);
}
public void Visit(TopNode node)
{
Nodes.Push(new TopNode((IntegerNode)node.Expression));
}
/// <summary>
/// Rebuilds the collection with the elements sorted by the property StartIndex.
/// </summary>
internal void SortAndAddMentionsToXml()
{
_mentionList.Sort((x, y) => x.StartIndex.CompareTo(y.StartIndex));
TopNode.RemoveAll();
_mentionList.ForEach(x => TopNode.AppendChild(x.TopNode));
}