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