internal bool Remove(ExcelThreadedComment comment)
{
var index = _commentList.IndexOf(comment);
_commentList.Remove(comment);
var commentNode = TopNode.SelectSingleNode("tc:threadedComment[@id='" + comment.Id + "']", NameSpaceManager);
if (commentNode != null)
{
TopNode.RemoveChild(commentNode);
//Reset the parentid to the first item in the list if we remove the first comment
if (index == 0 && _commentList.Count > 0)
{
((XmlElement)_commentList[0].TopNode).RemoveAttribute("parentId");
for (int i = 1; i < _commentList.Count; i++)
{
_commentList[i].ParentId = _commentList[0].Id;
}
}
RebuildIndex();
return(true);
}
return(false);
}
/// <summary>
/// Sets a password for the sheet.
/// </summary>
/// <param name="Password"></param>
public void SetPassword(string Password)
{
if (IsProtected == false)
{
IsProtected = true;
}
Password = Password.Trim();
if (Password == "")
{
string passwordPath = "d:sheetProtection";
XmlNode node = TopNode.SelectSingleNode(passwordPath, NameSpaceManager);
if (node != null)
{
TopNode.RemoveChild(node);
//node.OwnerDocument.Attributes.Remove(node as XmlAttribute);
//(node as XmlAttribute).OwnerElement.Attributes.Remove(node as XmlAttribute);
}
return;
}
int hash = EncryptedPackageHandler.CalculatePasswordHash(Password);
SetXmlNodeString(_passwordPath, ((int)hash).ToString("x"));
}
private void SaveImageToPackage(ePictureType type, byte[] img)
{
var package = _drawings.Worksheet._package.ZipPackage;
ContentType = PictureStore.GetContentType(type.ToString());
IPictureContainer container = this;
container.UriPic = GetNewUri(package, "/xl/media/image{0}." + type.ToString());
var store = _drawings._package.PictureStore;
var ii = store.AddImage(img, container.UriPic, ContentType);
string relId;
if (!_drawings._hashes.ContainsKey(ii.Hash))
{
Part = ii.Part;
container.RelPic = _drawings.Part.CreateRelationship(UriHelper.GetRelativeUri(_drawings.UriDrawing, ii.Uri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/image");
relId = container.RelPic.Id;
_drawings._hashes.Add(ii.Hash, new HashInfo(relId));
AddNewPicture(img, relId);
}
else
{
relId = _drawings._hashes[ii.Hash].RelId;
var rel = _drawings.Part.GetRelationship(relId);
container.UriPic = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
}
container.ImageHash = ii.Hash;
_image = Image.FromStream(new MemoryStream(img));
SetPosDefaults(_image);
//Create relationship
TopNode.SelectSingleNode("xdr:pic/xdr:blipFill/a:blip/@r:embed", NameSpaceManager).Value = relId;
package.Flush();
}
/// <summary>
/// Add a rich text string
/// </summary>
/// <param name="Text">The text to add</param>
/// <returns></returns>
public ExcelParagraph Add(string Text)
{
XmlDocument doc;
if (TopNode is XmlDocument)
{
doc = TopNode as XmlDocument;
}
else
{
doc = TopNode.OwnerDocument;
}
XmlNode parentNode = TopNode.SelectSingleNode(_path, NameSpaceManager);
if (parentNode == null)
{
CreateNode(_path);
}
var node = doc.CreateElement("a", "r", ExcelPackage.schemaDrawings);
parentNode.AppendChild(node);
var childNode = doc.CreateElement("a", "rPr", ExcelPackage.schemaDrawings);
node.AppendChild(childNode);
var rt = new ExcelParagraph(NameSpaceManager, node, "", SchemaNodeOrder);
rt.ComplexFont = "Calibri";
rt.LatinFont = "Calibri";
rt.Size = 11;
rt.Text = Text;
_list.Add(rt);
return(rt);
}
/// <summary>
/// Sets a password for the sheet.
/// </summary>
/// <param name="Password"></param>
public void SetPassword(string Password)
{
#if CORECLR
throw new NotSupportedException();
#else
if (IsProtected == false)
{
IsProtected = true;
}
Password = Password.Trim();
if (Password == "")
{
var node = TopNode.SelectSingleNode(_passwordPath, NameSpaceManager);
if (node != null)
{
(node as XmlAttribute).OwnerElement.Attributes.Remove(node as XmlAttribute);
}
return;
}
int hash = EncryptedPackageHandler.CalculatePasswordHash(Password);
SetXmlNodeString(_passwordPath, ((int)hash).ToString("x"));
#endif
}
private void SetSerieFunction(string value)
{
CreateNode(_seriesPath, true);
CreateNode(_numCachePath, true);
if (ExcelCellBase.IsValidAddress(value))
{
SetXmlNodeString(_seriesPath, ExcelCellBase.GetFullAddress(_chart.WorkSheet.Name, value));
}
else
{
SetXmlNodeString(_seriesPath, value);
}
if (_chart.PivotTableSource != null)
{
XmlNode cache = TopNode.SelectSingleNode(string.Format("{0}/c:numRef/c:numCache", _seriesTopPath), NameSpaceManager);
if (cache != null)
{
cache.ParentNode.RemoveChild(cache);
}
SetXmlNodeString(string.Format("{0}/c:numRef/c:numCache", _seriesTopPath), "General");
}
XmlNode lit = TopNode.SelectSingleNode(_seriesNumLitPath, NameSpaceManager);
if (lit != null)
{
lit.ParentNode.RemoveChild(lit);
}
}
private void SetXSerieFunction()
{
if (_xSeriesPath.IndexOf("c:numRef") > 0)
{
XmlNode cache = TopNode.SelectSingleNode(string.Format("{0}/c:numRef/c:numCache", _xSeriesTopPath), NameSpaceManager);
if (cache != null)
{
cache.ParentNode.RemoveChild(cache);
}
XmlNode lit = TopNode.SelectSingleNode(_xSeriesNumLitPath, NameSpaceManager);
if (lit != null)
{
lit.ParentNode.RemoveChild(lit);
}
}
else
{
XmlNode cache = TopNode.SelectSingleNode(string.Format("{0}/c:strRef/c:strCache", _xSeriesTopPath), NameSpaceManager);
if (cache != null)
{
cache.ParentNode.RemoveChild(cache);
}
XmlNode lit = TopNode.SelectSingleNode(_xSeriesStrLitPath, NameSpaceManager);
if (lit != null)
{
lit.ParentNode.RemoveChild(lit);
}
}
}
internal ExcelChartPlotArea(XmlNamespaceManager ns, XmlNode node, ExcelChart firstChart)
: base(ns, node)
{
_firstChart = firstChart;
if (TopNode.SelectSingleNode("c:dTable", NameSpaceManager) != null)
{
_dataTable = new ExcelChartDataTable(NameSpaceManager, TopNode);
}
}
internal void ClearChildren(string path)
{
var n = TopNode.SelectSingleNode(path, NameSpaceManager);
if (n != null)
{
n.InnerXml = null;
}
}
private void SetAutoFilter()
{
if (_autoFilter == null)
{
var node = TopNode.SelectSingleNode(AUTOFILTER_PATH, NameSpaceManager);
_autoFilter = new ExcelAutoFilter(NameSpaceManager, node, this);
_autoFilter.Address = AutoFilterAddress;
}
}
private void CreateFillTopNode(eFillStyle value)
{
if (_fillTypeNode != null)
{
TopNode.RemoveChild(_fillTypeNode);
}
CreateNode(_fillPath + "/a:" + GetStyleText(value), false);
_fillNode = TopNode.SelectSingleNode(_fillPath + "/a:" + GetStyleText(value), NameSpaceManager);
}
private void Cleartx()
{
var n = TopNode.SelectSingleNode("c:tx", NameSpaceManager);
if (n != null)
{
n.InnerXml = "";
}
}
/// <summary>
/// Create the 'cfvo'/'color' nodes in the right order. They should appear like this:
/// "cfvo" --> Low Value (value object)
/// "cfvo" --> Middle Value (value object)
/// "cfvo" --> High Value (value object)
/// "color" --> Low Value (color)
/// "color" --> Middle Value (color)
/// "color" --> High Value (color)
/// </summary>
/// <param name="nodeType"></param>
/// <param name="attributePath"></param>
/// <param name="attributeValue"></param>
private void CreateNodeByOrdem(
eExcelConditionalFormattingValueObjectNodeType nodeType,
string attributePath,
string attributeValue)
{
// Save the current TopNode
XmlNode currentTopNode = TopNode;
string nodePath = ExcelConditionalFormattingValueObjectType.GetNodePathByNodeType(nodeType);
int nodeOrder = GetNodeOrder();
eNodeInsertOrder nodeInsertOrder = eNodeInsertOrder.SchemaOrder;
XmlNode referenceNode = null;
if (nodeOrder > 1)
{
// Find the node just before the one we need to include
referenceNode = TopNode.SelectSingleNode(
string.Format(
"{0}[position()={1}]",
// {0}
nodePath,
// {1}
nodeOrder - 1),
_worksheet.NameSpaceManager);
// Only if the prepend node exists than insert after
if (referenceNode != null)
{
nodeInsertOrder = eNodeInsertOrder.After;
}
}
// Create the node in the right order
var node = CreateComplexNode(
TopNode,
string.Format(
"{0}[position()={1}]",
// {0}
nodePath,
// {1}
nodeOrder),
nodeInsertOrder,
referenceNode);
// Point to the new node as the temporary TopNode (we need it for the XmlHelper functions)
TopNode = node;
// Add/Remove the attribute (if the attributeValue is empty then it will be removed)
SetXmlNodeString(
node,
attributePath,
attributeValue,
true);
// Point back to the <cfvo>/<color> parent node
TopNode = currentTopNode;
}
internal void Add(ExcelThreadedComment comment)
{
_commentList.Add(comment);
if (TopNode.SelectSingleNode("tc:threadedComment[@id='" + comment.Id + "']", NameSpaceManager) == null)
{
TopNode.AppendChild(comment.TopNode);
}
RebuildIndex();
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
{
if (firstField == false)
{
ExcelPivotTableField field = _pivotTable.Fields.AddDateGroupField(Index);
XmlNode rowColFields;
if (IsRowField)
{
rowColFields = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
}
else
{
rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
}
var index = 0;
foreach (XmlElement rowfield in rowColFields.ChildNodes)
{
if (int.TryParse(rowfield.GetAttribute("x"), out int fieldIndex))
{
if (_pivotTable.Fields[fieldIndex].BaseIndex == BaseIndex)
{
var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
newElement.SetAttribute("x", field.Index.ToString());
rowColFields.InsertBefore(newElement, rowfield);
break;
}
}
index++;
}
var cacheRef = _pivotTable.CacheDefinition._cacheReference;
field._cacheField = cacheRef.AddDateGroupField(field, groupBy, startDate, endDate, interval);
UpdatePivotTableGroupItems(field, cacheRef, false);
if (IsRowField)
{
_pivotTable.RowFields.Insert(field, index);
}
else
{
_pivotTable.ColumnFields.Insert(field, index);
}
return(field);
}
else
{
firstField = false;
Compact = false;
_cacheField.SetDateGroup(this, groupBy, startDate, endDate, interval);
UpdatePivotTableGroupItems(this, _pivotTable.CacheDefinition._cacheReference, true);
return(this);
}
}
public SkipReason WhatSkipReason()
{
var msgNode = TopNode.SelectSingleNode("properties/property[@name='_SKIPREASON']");
return(msgNode != null &&
new[] { "contains no tests", "Has no TestFixtures" }.Any(msgNode.GetAttribute("value")
.Contains)
? SkipReason.NoNUnitTests
: SkipReason.LoadFailure);
}
internal bool ExistNode(string path)
{
if (TopNode == null || TopNode.SelectSingleNode(path, NameSpaceManager) == null)
{
return(false);
}
else
{
return(true);
}
}
internal ExcelChartPlotArea(XmlNamespaceManager ns, XmlNode node, ExcelChart firstChart)
: base(ns, node)
{
AddSchemaNodeOrder(new string[] { "areaChart", "area3DChart", "lineChart", "line3DChart", "stockChart", "radarChart", "scatterChart", "pieChart", "pie3DChart", "doughnutChart", "barChart", "bar3DChart", "ofPieChart", "surfaceChart", "surface3DChart", "valAx", "catAx", "dateAx", "serAx", "dTable", "spPr" },
ExcelDrawing._schemaNodeOrderSpPr);
_firstChart = firstChart;
if (TopNode.SelectSingleNode("c:dTable", NameSpaceManager) != null)
{
DataTable = new ExcelChartDataTable(firstChart, NameSpaceManager, TopNode);
}
}
/// <summary>
/// All or none of the margin attributes must exist. Create all att ones.
/// </summary>
private void CreateMargins()
{
if (_marginsCreated == false && TopNode.SelectSingleNode(_leftMarginPath, NameSpaceManager) == null)
{
_marginsCreated = true;
LeftMargin = 0.7087M;
RightMargin = 0.7087M;
TopMargin = 0.7480M;
BottomMargin = 0.7480M;
HeaderMargin = 0.315M;
FooterMargin = 0.315M;
}
}
private void CreateDefaultText()
{
if (TopNode.SelectSingleNode($"{_nsPrefix}:txPr", NameSpaceManager) == null)
{
if (!ExistNode($"{_nsPrefix}:spPr"))
{
var spNode = CreateNode($"{_nsPrefix}:spPr");
spNode.InnerXml = "<a:noFill/><a:ln><a:noFill/></a:ln><a:effectLst/>";
}
var node = CreateNode($"{_nsPrefix}:txPr");
node.InnerXml = "<a:bodyPr anchorCtr=\"1\" anchor=\"ctr\" bIns=\"19050\" rIns=\"38100\" tIns=\"19050\" lIns=\"38100\" wrap=\"square\" vert=\"horz\" vertOverflow=\"ellipsis\" spcFirstLastPara=\"1\" rot=\"0\"><a:spAutoFit/></a:bodyPr><a:lstStyle/>";
}
}
private void EnsureRootElementExists()
{
var node = TopNode.SelectSingleNode(ExternalDataValidationPath, _worksheet.NameSpaceManager) as XmlElement;
if (node == null)
{
node = (XmlElement)CreateNode(ExternalDataValidationPath.TrimStart('/'), false, true);
node.SetAttribute("xmlns:xm", ExcelPackage.schemaMainXm);
((XmlElement)node.ParentNode).SetAttribute("xmlns:x14", ExcelPackage.schemaMainX14);
((XmlElement)node.ParentNode).SetAttribute("uri", "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}");
}
TopNode = node;
}
private void LoadFields()
{
int index = 0;
var pivotFieldNode = TopNode.SelectSingleNode("d:pivotFields", NameSpaceManager);
//Add fields.
foreach (XmlElement fieldElem in pivotFieldNode.SelectNodes("d:pivotField", NameSpaceManager))
{
var fld = new ExcelPivotTableField(NameSpaceManager, fieldElem, this, index, index);
fld._cacheField = CacheDefinition._cacheReference.Fields[index++];
fld.LoadItems();
Fields.AddInternal(fld);
}
}
internal protected XmlNode AddTitleNode()
{
var node = TopNode.SelectSingleNode($"{_nsPrefix}:title", NameSpaceManager);
if (node == null)
{
node = CreateNode($"{_nsPrefix}:title");
if (_chart._isChartEx == false)
{
node.InnerXml = ExcelChartTitle.GetInitXml(_nsPrefix);
}
}
return(node);
}
/// <summary>
/// Removes the validations that matches the predicate
/// </summary>
/// <param name="match"></param>
public void RemoveAll(Predicate <IExcelDataValidation> match)
{
var matches = _validations.FindAll(match);
foreach (var m in matches)
{
if (!(m is ExcelDataValidation))
{
throw new InvalidCastException("The supplied item must inherit OfficeOpenXml.DataValidation.ExcelDataValidation");
}
TopNode.SelectSingleNode(DataValidationPath.TrimStart('/'), NameSpaceManager).RemoveChild(((ExcelDataValidation)m).TopNode);
}
_validations.RemoveAll(match);
OnValidationCountChanged();
}
internal void DeleteNode(string path)
{
var node = TopNode.SelectSingleNode(path, NameSpaceManager);
if (node != null)
{
if (node is XmlAttribute)
{
var att = (XmlAttribute)node;
att.OwnerElement.Attributes.Remove(att);
}
else
{
node.ParentNode.RemoveChild(node);
}
}
}
/// <summary>
/// Unlock all rows and columns to scroll freely
/// /// </summary>
public void UnFreezePanes()
{
string sqRef = SelectedRange, activeCell = ActiveCell;
XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
if (paneNode != null)
{
paneNode.ParentNode.RemoveChild(paneNode);
}
RemoveSelection();
Panes = LoadPanes();
SelectedRange = sqRef;
ActiveCell = activeCell;
}
private void MoveSheetXmlNode(ExcelWorksheet sourceSheet, ExcelWorksheet targetSheet, bool placeAfter)
{
var sourceNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", sourceSheet.SheetID), _namespaceManager);
var targetNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", targetSheet.SheetID), _namespaceManager);
if (sourceNode == null || targetNode == null)
{
throw new Exception("Source SheetId and Target SheetId must be valid");
}
if (placeAfter)
{
TopNode.InsertAfter(sourceNode, targetNode);
}
else
{
TopNode.InsertBefore(sourceNode, targetNode);
}
}
internal void GenerateLit()
{
var s = new ExcelAddress(Series);
var ix = 0;
var sb = new StringBuilder();
for (int row = s._fromRow; row <= s._toRow; row++)
{
for (int c = s._fromCol; c <= s._toCol; c++)
{
sb.AppendFormat("<c:pt idx=\"{0}\"><c:v>1</c:v></c:pt>", ix++);
}
}
CreateNode(BUBBLESIZE_TOPPATH + "/c:numLit", true);
XmlNode lit = TopNode.SelectSingleNode(string.Format("{0}/c:numLit", BUBBLESIZE_TOPPATH), _ns);
lit.InnerXml = string.Format("<c:formatCode>General</c:formatCode><c:ptCount val=\"{0}\"/>{1}", ix, sb.ToString());
}
/****************************************************************************************/
#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 ExcelConditionalFormattingFourIconSet(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.FourIconSet,
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.FourIconSet,
address,
worksheet,
iconNode4,
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,
75,
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode4,
namespaceManager);
}
}
private void AddFieldItems(int items)
{
XmlElement prevNode = null;
XmlElement itemsNode = TopNode.SelectSingleNode("d:items", NameSpaceManager) as XmlElement;
for (int x = 0; x < items; x++)
{
var itemNode = itemsNode.OwnerDocument.CreateElement("item", ExcelPackage.schemaMain);
itemNode.SetAttribute("x", x.ToString());
if (prevNode == null)
{
itemsNode.PrependChild(itemNode);
}
else
{
itemsNode.InsertAfter(itemNode, prevNode);
}
prevNode = itemNode;
}
itemsNode.SetAttribute("count", (items + 1).ToString());
}
