//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); } } } } }
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"); } } } }