//internal bool _isCalculated=false;
internal void GetExternalReferences()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:externalReferences/d:externalReference", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
string rID = elem.GetAttribute("r:id");
var rel = Part.GetRelationship(rID);
var part = _package.Package.GetPart(UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri));
XmlDocument xmlExtRef = new XmlDocument();
LoadXmlSafe(xmlExtRef, part.GetStream());
XmlElement book = xmlExtRef.SelectSingleNode("//d:externalBook", NameSpaceManager) as XmlElement;
if (book != null)
{
string rId_ExtRef = book.GetAttribute("r:id");
var rel_extRef = part.GetRelationship(rId_ExtRef);
if (rel_extRef != null)
{
_externalReferences.Add(rel_extRef.TargetUri.OriginalString);
}
}
}
}
}
/// <summary>
/// Recreate the workbook part with a new contenttype
/// </summary>
/// <param name="contentType">The new contenttype</param>
private void ChangeContentTypeWorkbook(string contentType)
{
var p = _package.Package;
var part = Part;
var rels = part.GetRelationships();
p.DeletePart(WorkbookUri);
part = p.CreatePart(WorkbookUri, contentType);
foreach (var rel in rels)
{
p.DeleteRelationship(rel.Id);
var newRel = part.CreateRelationship(rel.TargetUri, rel.TargetMode, rel.RelationshipType);
if (rel.RelationshipType.EndsWith("worksheet"))
{
var sheetNode = (XmlElement)WorkbookXml.SelectSingleNode(string.Format("d:workbook/d:sheets/d:sheet[@r:id='{0}']", rel.Id), NameSpaceManager);
sheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, newRel.Id);
}
else if (rel.RelationshipType.EndsWith("pivotCacheDefinition"))
{
var sheetNode = (XmlElement)WorkbookXml.SelectSingleNode(string.Format("d:workbook/d:pivotCaches/d:pivotCache[@r:id='{0}']", rel.Id), NameSpaceManager);
sheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, newRel.Id);
}
}
}
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);
}
}
internal void AddPivotTable(string cacheID, Uri defUri)
{
CreateNode("d:pivotCaches");
XmlElement item = WorkbookXml.CreateElement("pivotCache", ExcelPackage.schemaMain);
item.SetAttribute("cacheId", cacheID);
var rel = Part.CreateRelationship(UriHelper.ResolvePartUri(WorkbookUri, defUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition");
item.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);
var pivotCaches = WorkbookXml.SelectSingleNode("//d:pivotCaches", NameSpaceManager);
pivotCaches.AppendChild(item);
}
private void ClearBookViews()
{
var node = WorkbookXml.SelectSingleNode("//d:bookViews/d:workbookView", NameSpaceManager);
if (node != null)
{
var firstSheet = node.Attributes["firstSheet"];
if (firstSheet != null)
{
node.Attributes.Remove(firstSheet);
}
}
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
try
{
string fullAddress = elem.InnerText;
int localSheetID;
ExcelWorksheet nameWorksheet;
if (!int.TryParse(elem.GetAttribute("localSheetId"), NumberStyles.Number, CultureInfo.InvariantCulture, out localSheetID))
{
localSheetID = -1;
nameWorksheet = null;
}
else
{
nameWorksheet = Worksheets[localSheetID + _package._worksheetAdd];
}
var addressType = ExcelAddressBase.IsValid(fullAddress);
ExcelRangeBase range;
ExcelNamedRange namedRange;
if (fullAddress.IndexOf("[") == 0)
{
int start = fullAddress.IndexOf("[");
int end = fullAddress.IndexOf("]", start);
if (start >= 0 && end >= 0)
{
string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
int index;
if (int.TryParse(externalIndex, NumberStyles.Any, CultureInfo.InvariantCulture, out index))
{
if (index > 0 && index <= _externalReferences.Count)
{
fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
}
}
}
}
if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType == ExcelAddressBase.AddressType.Formula || addressType == ExcelAddressBase.AddressType.CellAddress) //A value or a formula
{
double value;
range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
if (nameWorksheet == null)
{
namedRange = _names.Add(elem.GetAttribute("name"), range);
}
else
{
namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
}
if (ConvertUtil._invariantCompareInfo.IsPrefix(fullAddress, "\"")) //String value
{
namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
}
else if (double.TryParse(fullAddress, NumberStyles.Number, CultureInfo.InvariantCulture, out value))
{
namedRange.NameValue = value;
}
else
{
//if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
//{
// var r = new ExcelAddress(fullAddress);
// namedRange.NameFormula = '\'[' + r._wb
//}
//else
//{
namedRange.NameFormula = fullAddress;
//}
}
}
else
{
ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
if (localSheetID > -1)
{
if (string.IsNullOrEmpty(addr._ws))
{
namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + _package._worksheetAdd], fullAddress, false));
}
else
{
namedRange = Worksheets[localSheetID + _package._worksheetAdd].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
}
}
else
{
var ws = Worksheets[addr._ws];
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
}
}
if (namedRange == null)
{
continue;
}
namedRange.RawFormula = elem.InnerText;
if (elem.GetAttribute("hidden") == "1")
{
namedRange.IsNameHidden = true;
}
if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
{
namedRange.NameComment = elem.GetAttribute("comment");
}
}
catch (Exception)
{
// Quickfix: Hidden named ranges that references worksheets which do not exist will not be added to all defined names.
}
}
}
}
internal void GetDefinedNames()
{
XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
if (nl != null)
{
foreach (XmlElement elem in nl)
{
string fullAddress = elem.InnerText;
int localSheetID;
ExcelWorksheet nameWorksheet;
if (!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
{
localSheetID = -1;
nameWorksheet = null;
}
else
{
nameWorksheet = Worksheets[localSheetID + 1];
}
var addressType = ExcelAddressBase.IsValid(fullAddress);
ExcelRangeBase range;
ExcelNamedRange namedRange;
if (fullAddress.IndexOf("[") > -1)
{
int start = fullAddress.IndexOf("[");
int end = fullAddress.IndexOf("]", start);
if (start >= 0 && end >= 0)
{
string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
int index;
if (int.TryParse(externalIndex, out index))
{
if (index > 0 && index <= _externalReferences.Count)
{
fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
}
}
}
}
if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName) //A value or a formula
{
double value;
range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
if (nameWorksheet == null)
{
namedRange = _names.Add(elem.GetAttribute("name"), range);
}
else
{
namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
}
if (fullAddress.StartsWith("\"")) //String value
{
namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
}
else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
{
namedRange.NameValue = value;
}
else
{
namedRange.NameFormula = fullAddress;
}
}
else
{
ExcelAddress addr = new ExcelAddress(fullAddress);
if (localSheetID > -1)
{
if (string.IsNullOrEmpty(addr._ws))
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
}
else
{
namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
}
}
else
{
var ws = Worksheets[addr._ws];
namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
}
}
if (elem.GetAttribute("hidden") == "1" && namedRange != null)
{
namedRange.IsNameHidden = true;
}
if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
{
namedRange.NameComment = elem.GetAttribute("comment");
}
}
}
}