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