internal ExcelParagraphCollection(ExcelDrawing drawing, XmlNamespaceManager ns, XmlNode topNode, string path, string[] schemaNodeOrder, float defaultFontSize = 11) :
base(ns, topNode)
{
_drawing = drawing;
_defaultFontSize = defaultFontSize;
AddSchemaNodeOrder(schemaNodeOrder, new string[] { "strRef", "rich", "f", "strCache", "bodyPr", "lstStyle", "p", "ptCount", "pt", "pPr", "lnSpc", "spcBef", "spcAft", "buClrTx", "buClr", "buSzTx", "buSzPct", "buSzPts", "buFontTx", "buFont", "buNone", "buAutoNum", "buChar", "buBlip", "tabLst", "defRPr", "r", "br", "fld", "endParaRPr" });
_path = path;
var pars = TopNode.SelectNodes(path, NameSpaceManager);
foreach (XmlElement par in pars)
{
_paragraphs.Add(par);
var nl = par.SelectNodes("a:r", NameSpaceManager);
if (nl != null)
{
foreach (XmlNode n in nl)
{
if (_list.Count == 0 || n.ParentNode != _list[_list.Count - 1].TopNode.ParentNode)
{
_paragraphs.Add((XmlElement)n.ParentNode);
}
_list.Add(new ExcelParagraph(drawing._drawings, ns, n, "", schemaNodeOrder));
}
}
}
}
internal void LoadItems()
{
_items = new ExcelPivotTableFieldItemsCollection(this);
if (Cache.DatabaseField == false)
{
return;
}
EPPlusReadOnlyList <object> cacheItems;
if (Cache.Grouping == null)
{
cacheItems = Cache.SharedItems;
}
else
{
cacheItems = Cache.GroupItems;
}
foreach (XmlElement node in TopNode.SelectNodes("d:items//d:item", NameSpaceManager))
{
var item = new ExcelPivotTableFieldItem(node);
if (item.X >= 0)
{
item.Value = cacheItems[item.X];
}
_items.AddInternal(item);
}
}
private void LoadFields()
{
//Fields.Clear();
//int ix=0;
//foreach(XmlElement fieldNode in PivotXml.SelectNodes("//d:pivotFields/d:pivotField",NameSpaceManager))
//{
// Fields.AddInternal(new ExcelPivotTableField(NameSpaceManager, fieldNode, this, ix++));
//}
int index = 0;
//Add fields.
foreach (XmlElement fieldElem in TopNode.SelectNodes("d:pivotFields/d:pivotField", NameSpaceManager))
{
var fld = new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index, index++);
Fields.AddInternal(fld);
}
//Add fields.
index = 0;
foreach (XmlElement fieldElem in _cacheDefinition.TopNode.SelectNodes("d:cacheFields/d:cacheField", NameSpaceManager))
{
var fld = Fields[index++];
fld.SetCacheFieldNode(fieldElem);
}
}
internal ExcelChartDataLabelCollection(ExcelChart chart, XmlNamespaceManager ns, XmlNode topNode, string[] schemaNodeOrder) : base(ns, topNode)
{
SchemaNodeOrder = schemaNodeOrder;
_list = new List <ExcelChartDataLabelItem>();
foreach (XmlNode pointNode in TopNode.SelectNodes(ExcelChartDataPoint.topNodePath, ns))
{
_list.Add(new ExcelChartDataLabelItem(chart, ns, pointNode, "idx", schemaNodeOrder));
}
_chart = chart;
}
internal ExcelChartDataPointCollection(ExcelChart chart, XmlNamespaceManager ns, XmlNode topNode, string[] schemaNodeOrder) : base(ns, topNode)
{
SchemaNodeOrder = schemaNodeOrder;
foreach (XmlNode pointNode in TopNode.SelectNodes(ExcelChartDataPoint.topNodePath, ns))
{
var item = new ExcelChartDataPoint(chart, ns, pointNode);
_dic.Add(item.Index, item);
}
_chart = chart;
}
private void RemoveSelection()
{
//Find selection nodes and remove them
XmlNodeList selections = TopNode.SelectNodes(_selectionNodePath, NameSpaceManager);
foreach (XmlNode sel in selections)
{
sel.ParentNode.RemoveChild(sel);
}
}
internal ExcelChartExDataLabelCollection(ExcelChartExSerie serie, XmlNamespaceManager ns, XmlNode node, string[] schemaNodeOrder) :
base(serie, ns, node)
{
_chart = serie._chart;
AddSchemaNodeOrder(schemaNodeOrder, new string[] { "numFmt", "spPr", "txPr", "visibility", "separator" });
foreach (XmlNode pointNode in TopNode.SelectNodes(ExcelChartExDataLabel._dataLabelPath, ns))
{
var item = new ExcelChartExDataLabelItem(serie, ns, pointNode);
_dic.Add(item.Index, item);
}
}
/// <summary>
/// Removes all 'cfRule' from the collection and from the XML.
/// <remarks>
/// This is the same as removing all the 'conditionalFormatting' nodes.
/// </remarks>
/// </summary>
public void RemoveAll()
{
// Look for all the <conditionalFormatting> nodes
var conditionalFormattingNodes = TopNode.SelectNodes(
"//" + ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
_worksheet.NameSpaceManager);
// Remove all the <conditionalFormatting> nodes one by one
foreach (XmlNode conditionalFormattingNode in conditionalFormattingNodes)
{
conditionalFormattingNode.ParentNode.RemoveChild(conditionalFormattingNode);
}
// Clear the <cfRule> item list
_rules.Clear();
}
private ExcelThemeElements(ExcelPackage package, XmlNamespaceManager nameSpaceManager) : base(nameSpaceManager, null)
{
TopNode = GetThemesXmlTopNode(package);
XmlNodeList themeNodes = TopNode?.SelectNodes("//a:themeElements/*", NameSpaceManager);
if (themeNodes == null)
{
return;
}
foreach (XmlElement themeElement in themeNodes)
{
_themeElements.Add(ExcelThemeElement.Create(NameSpaceManager, themeElement));
}
}
private ExcelWorksheetPanes[] LoadPanes()
{
XmlNodeList nodes = TopNode.SelectNodes("//d:selection", NameSpaceManager);
if (nodes.Count == 0)
{
return(new ExcelWorksheetPanes[] { new ExcelWorksheetPanes(NameSpaceManager, TopNode) });
}
else
{
ExcelWorksheetPanes[] panes = new ExcelWorksheetPanes[nodes.Count];
int i = 0;
foreach (XmlElement elem in nodes)
{
panes[i++] = new ExcelWorksheetPanes(NameSpaceManager, elem);
}
return(panes);
}
}
public ExcelConnections(ExcelPackage package, XmlNamespaceManager nameSpaceManager)
: base(nameSpaceManager, null)
{
_connections = new Dictionary <int, ExcelConnection>();
Uri connectionsUri = package.Workbook.ConnectionsUri;
if (!package.Package.PartExists(connectionsUri))
{
return;
}
XmlDocument connectionsXml = new XmlDocument();
LoadXmlSafe(connectionsXml, package.Package.GetPart(connectionsUri).GetStream());
TopNode = connectionsXml.DocumentElement;
foreach (XmlElement connectionElement in TopNode.SelectNodes("//d:connection", NameSpaceManager))
{
var connection = new ExcelConnection(NameSpaceManager, connectionElement);
_connections.Add(connection.Id, connection);
}
}
internal ExcelChartExDataPointCollection(ExcelChartExSerie serie, XmlNamespaceManager ns, XmlNode topNode, string[] schemaNodeOrder) : base(ns, topNode)
{
SchemaNodeOrder = schemaNodeOrder;
foreach (XmlNode pointNode in TopNode.SelectNodes(ExcelChartExDataPoint.dataPtPath, ns))
{
var item = new ExcelChartExDataPoint(serie, ns, pointNode, SchemaNodeOrder);
_dic.Add(item.Index, item);
}
foreach (XmlElement stNode in TopNode.SelectNodes(ExcelChartExDataPoint.SubTotalPath, ns))
{
var ix = int.Parse(stNode.GetAttribute("val"));
if (_dic.ContainsKey(ix))
{
_dic[ix].SubTotal = true;
}
else
{
var item = new ExcelChartExDataPoint(serie, ns, TopNode, ix, SchemaNodeOrder);
_dic.Add(item.Index, item);
}
}
_serie = serie;
}
internal XmlNodeList GetNodes(string path)
{
return(TopNode.SelectNodes(path, NameSpaceManager));
}
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();
PivotTableXml.Load(Part.GetStream());
init();
TopNode = PivotTableXml.DocumentElement;
Address = new ExcelAddressBase(GetXmlNodeString("d:location/@ref"));
_cacheDefinition = new ExcelPivotCacheDefinition(sheet.NameSpaceManager, this);
LoadFields();
//int index=0;
////Add fields.
//foreach (XmlElement fieldElem in TopNode.SelectNodes("d:pivotFields/d:pivotField", NameSpaceManager))
//{
// var fld=new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index++);
// Fields.AddInternal(fld);
//}
////Add fields.
//index = 0;
//foreach (XmlElement fieldElem in _cacheDefinition.TopNode.SelectNodes("d:cacheFields/d:cacheField", NameSpaceManager))
//{
// var fld = Fields[index++];
// fld.SetCacheFieldNode(fieldElem);
//}
//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);
}
}
}
public XmlNodeList TestCases()
{
return(TopNode.SelectNodes("//test-case"));
}
/****************************************************************************************/
#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);
// Checking the count of cfRuleNodes "materializes" the collection which prevents a rare infinite loop bug
if (cfRuleNodes.Count == 0)
{
continue;
}
// 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);
}
}
}
}
}
private IList <SchemeColor> GetSchemeColors()
{
XmlNodeList xmlNodeList = TopNode.SelectNodes("*", NameSpaceManager);
return(xmlNodeList == null ? new List <SchemeColor>() : xmlNodeList.OfType <XmlElement>().Select(x => new SchemeColor(x)).ToList());
}
