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");
}
}
}
}
internal eAddressCollition Collide(ExcelAddressBase address)
{
if (address.WorkSheet != WorkSheet)
{
return eAddressCollition.No;
}
if (address._fromRow > _toRow || address._fromCol > _toCol
||
_fromRow > address._toRow || _fromCol > address._toCol)
{
return eAddressCollition.No;
}
else if (address._fromRow == _fromRow && address._fromCol == _fromCol &&
address._toRow == _toRow && address._toCol == _toCol)
{
return eAddressCollition.Equal;
}
else if (address._fromRow >= _fromRow && address._toRow <= _toRow &&
address._fromCol >= _fromCol && address._toCol <= _toCol)
{
return eAddressCollition.Inside;
}
else
return eAddressCollition.Partly;
}
internal static AddressType IsValid(string Address)
{
string ws = "";
if (Address.StartsWith("'"))
{
int ix = Address.IndexOf('\'', 1);
if (ix > -1)
{
ws = Address.Substring(1, ix - 1);
Address = Address.Substring(ix + 2);
}
}
if (Address.IndexOfAny(new char[] { '(', ')', '+', '-', '*', '/', '.', '=', '^', '&', '%', '\"' }) > -1)
{
return(AddressType.Invalid);
}
if (Address.IndexOf('!') > 0)
{
string[] split = Address.Split('!');
if (split.Length == 2)
{
ws = split[0];
Address = split[1];
}
else if (split.Length == 3 && split[1] == "#REF" && split[2] == "")
{
ws = split[0];
Address = "#REF!";
if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
{
return(AddressType.ExternalAddress);
}
else
{
return(AddressType.InternalAddress);
}
}
else
{
return(AddressType.Invalid);
}
}
int _fromRow, _fromCol, _toRow, _toCol;
if (ExcelAddressBase.GetRowColFromAddress(Address, out _fromRow, out _fromCol, out _toRow, out _toCol))
{
if (_fromRow > 0 && _fromCol > 0 && _toRow <= ExcelPackage.MaxRows && _toCol <= ExcelPackage.MaxColumns)
{
if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
{
return(AddressType.ExternalAddress);
}
else
{
return(AddressType.InternalAddress);
}
}
else
{
return(AddressType.Invalid);
}
}
else
{
if (IsValidName(Address))
{
if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
{
return(AddressType.ExternalName);
}
else
{
return(AddressType.InternalName);
}
}
else
{
return(AddressType.Invalid);
}
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column=ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while(column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if(cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
/// <summary>
/// Handels changes of properties on the style objects
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <returns></returns>
internal int PropertyChange(StyleBase sender, Style.StyleChangeEventArgs e)
{
var address = new ExcelAddressBase(e.Address);
var ws = _wb.Worksheets[e.PositionID];
Dictionary<int, int> styleCashe = new Dictionary<int, int>();
//Set single address
SetStyleAddress(sender, e, address, ws, ref styleCashe);
if (address.Addresses != null)
{
//Handle multiaddresses
foreach (var innerAddress in address.Addresses)
{
SetStyleAddress(sender, e, innerAddress, ws, ref styleCashe);
}
}
return 0;
}
