internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol) { if (address.IndexOf("!") == -1 || address == "#REF!") { if (fullRowCol) { string[] cells = address.Split(':'); if (cells.Length > 0) { address = string.Format("'{0}'!{1}", worksheetName, cells[0]); if (cells.Length > 1) { address += string.Format(":{0}", cells[1]); } } } else { var a = new ExcelAddressBase(address); if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows) || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns)) { address = string.Format("'{0}'!{1}{2}:{3}{4}", worksheetName, ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow); } else { address = GetFullAddress(worksheetName, address, true); } } } return(address); }
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"); } } } }