| /******************************************************************************* |
| * You may amend and distribute as you like, but don't remove this header! |
| * |
| * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets. |
| * See http://www.codeplex.com/EPPlus for details. |
| * |
| * Copyright (C) 2011 Jan Källman |
| * |
| * This library is free software; you can redistribute it and/or |
| * modify it under the terms of the GNU Lesser General Public |
| * License as published by the Free Software Foundation; either |
| * version 2.1 of the License, or (at your option) any later version. |
| |
| * This library is distributed in the hope that it will be useful, |
| * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
| * See the GNU Lesser General Public License for more details. |
| * |
| * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php |
| * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html |
| * |
| * All code and executables are provided "as is" with no warranty either express or implied. |
| * The author accepts no liability for any damage or loss of business that this product may cause. |
| * |
| * Code change notes: |
| * |
| * Author Change Date |
| * ****************************************************************************** |
| * Jan Källman Initial Release 2010-01-28 |
| * Jan Källman License changed GPL-->LGPL 2011-12-27 |
| * Eyal Seagull Conditional Formatting 2012-04-03 |
| *******************************************************************************/ |
| |
| using System; |
| using System.Collections; |
| using System.Collections.Generic; |
| using System.Globalization; |
| using System.Security; |
| using System.Text; |
| using System.Xml; |
| |
| namespace AppsheetEpplus; |
| |
| /// <summary> |
| /// A range of cells |
| /// </summary> |
| public class ExcelRangeBase |
| : ExcelAddress, |
| IExcelCell, |
| IDisposable, |
| IEnumerable<ExcelRangeBase>, |
| IEnumerator<ExcelRangeBase> { |
| /// <summary> |
| /// Reference to the worksheet |
| /// </summary> |
| protected ExcelWorksheet _worksheet; |
| internal ExcelWorkbook _workbook; |
| |
| private delegate void ChangePropHandler(SetValueHandler method, object value); |
| |
| private delegate void SetValueHandler(object value, int row, int col); |
| |
| private ChangePropHandler _changePropMethod; |
| private int _styleID; |
| |
| private class CopiedCell { |
| internal int Row { get; set; } |
| |
| internal int Column { get; set; } |
| |
| internal object Value { get; set; } |
| |
| internal string Type { get; set; } |
| |
| internal object Formula { get; set; } |
| |
| internal int? StyleID { get; set; } |
| |
| internal Uri HyperLink { get; set; } |
| |
| internal ExcelComment Comment { get; set; } |
| |
| internal Byte Flag { get; set; } |
| } |
| |
| //private class CopiedFlag |
| //{ |
| // internal int Row { get; set; } |
| // internal int Column { get; set; } |
| // internal Byte Flag { get; set; } |
| //} |
| |
| |
| internal ExcelRangeBase(ExcelWorksheet worksheet) { |
| _worksheet = worksheet; |
| _ws = _worksheet.Name; |
| _workbook = _worksheet.Workbook; |
| AddressChange += ExcelRangeBase_AddressChange; |
| SetDelegate(); |
| } |
| |
| private void ExcelRangeBase_AddressChange(object sender, EventArgs e) { |
| if (Table != null) { |
| SetRcFromTable(_workbook, null); |
| } |
| SetDelegate(); |
| } |
| |
| internal ExcelRangeBase(ExcelWorksheet worksheet, string address) |
| : base(worksheet == null ? "" : worksheet.Name, address) { |
| _worksheet = worksheet; |
| _workbook = worksheet.Workbook; |
| SetRcFromTable(_workbook, null); |
| if (string.IsNullOrEmpty(_ws)) { |
| _ws = _worksheet == null ? "" : _worksheet.Name; |
| } |
| AddressChange += ExcelRangeBase_AddressChange; |
| SetDelegate(); |
| } |
| |
| internal ExcelRangeBase(ExcelWorkbook wb, ExcelWorksheet xlWorksheet, string address, bool isName) |
| : base(xlWorksheet == null ? "" : xlWorksheet.Name, address, isName) { |
| SetRcFromTable(wb, null); |
| _worksheet = xlWorksheet; |
| _workbook = wb; |
| if (string.IsNullOrEmpty(_ws)) { |
| _ws = xlWorksheet?.Name; |
| } |
| AddressChange += ExcelRangeBase_AddressChange; |
| SetDelegate(); |
| } |
| |
| ~ExcelRangeBase() { |
| //this.AddressChange -= new EventHandler(ExcelRangeBase_AddressChange); |
| } |
| |
| private void SetDelegate() { |
| if (_fromRow == -1) { |
| _changePropMethod = SetUnknown; |
| } |
| //Single cell |
| else if (_fromRow == _toRow && _fromCol == _toCol && Addresses == null) { |
| _changePropMethod = SetSingle; |
| } |
| //Range (ex A1:A2) |
| else if (Addresses == null) { |
| _changePropMethod = SetRange; |
| } |
| //Multi Range (ex A1:A2,C1:C2) |
| else { |
| _changePropMethod = SetMultiRange; |
| } |
| } |
| |
| /// <summary> |
| /// We dont know the address yet. Set the delegate first time a property is set. |
| /// </summary> |
| /// <param name="valueMethod"></param> |
| /// <param name="value"></param> |
| private void SetUnknown(SetValueHandler valueMethod, object value) { |
| //Address is not set use, selected range |
| if (_fromRow == -1) { |
| SetToSelectedRange(); |
| } |
| SetDelegate(); |
| _changePropMethod(valueMethod, value); |
| } |
| |
| /// <summary> |
| /// Set a single cell |
| /// </summary> |
| /// <param name="valueMethod"></param> |
| /// <param name="value"></param> |
| private void SetSingle(SetValueHandler valueMethod, object value) { |
| valueMethod(value, _fromRow, _fromCol); |
| } |
| |
| /// <summary> |
| /// Set a range |
| /// </summary> |
| /// <param name="valueMethod"></param> |
| /// <param name="value"></param> |
| private void SetRange(SetValueHandler valueMethod, object value) { |
| SetValueAddress(this, valueMethod, value); |
| } |
| |
| /// <summary> |
| /// Set a multirange (A1:A2,C1:C2) |
| /// </summary> |
| /// <param name="valueMethod"></param> |
| /// <param name="value"></param> |
| private void SetMultiRange(SetValueHandler valueMethod, object value) { |
| SetValueAddress(this, valueMethod, value); |
| foreach (var address in Addresses) { |
| SetValueAddress(address, valueMethod, value); |
| } |
| } |
| |
| /// <summary> |
| /// Set the property for an address |
| /// </summary> |
| /// <param name="address"></param> |
| /// <param name="valueMethod"></param> |
| /// <param name="value"></param> |
| private void SetValueAddress(ExcelAddress address, SetValueHandler valueMethod, object value) { |
| IsRangeValid(""); |
| if (_fromRow == 1 |
| && _fromCol == 1 |
| && _toRow == ExcelPackage.MaxRows |
| && _toCol |
| == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging |
| { |
| throw (new ArgumentException( |
| "Can't reference all cells. Please use the indexer to set the range")); |
| } |
| for (int col = address.Start.Column; col <= address.End.Column; col++) { |
| for (int row = address.Start.Row; row <= address.End.Row; row++) { |
| valueMethod(value, row, col); |
| } |
| } |
| } |
| |
| private void Set_StyleID(object value, int row, int col) { |
| _worksheet._styles.SetValue(row, col, (int)value); |
| } |
| |
| private void Set_Value(object value, int row, int col) { |
| //ExcelCell c = _worksheet.Cell(row, col); |
| var sfi = _worksheet._formulas.GetValue(row, col); |
| if (sfi is int) { |
| SplitFormulas(_worksheet.Cells[row, col]); |
| } |
| if (sfi != null) { |
| _worksheet._formulas.SetValue(row, col, string.Empty); |
| } |
| _worksheet._values.SetValue(row, col, value); |
| } |
| |
| private void Set_Formula(object value, int row, int col) { |
| //ExcelCell c = _worksheet.Cell(row, col); |
| var f = _worksheet._formulas.GetValue(row, col); |
| if (f is int i && i >= 0) { |
| SplitFormulas(_worksheet.Cells[row, col]); |
| } |
| |
| string formula = (value == null ? string.Empty : value.ToString()); |
| if (formula == string.Empty) { |
| _worksheet._formulas.SetValue(row, col, string.Empty); |
| } else { |
| if (formula[0] == '=') { |
| value = formula.Substring(1, formula.Length - 1); // remove any starting equalsign. |
| } |
| _worksheet._formulas.SetValue(row, col, formula); |
| _worksheet._values.SetValue(row, col, null); |
| } |
| } |
| |
| /// <summary> |
| /// Handles shared formulas |
| /// </summary> |
| /// <param name="value">The formula</param> |
| /// <param name="address">The address of the formula</param> |
| /// <param name="isArray">If the forumla is an array formula.</param> |
| private void Set_SharedFormula(string value, ExcelAddress address, bool isArray) { |
| if (_fromRow == 1 |
| && _fromCol == 1 |
| && _toRow == ExcelPackage.MaxRows |
| && _toCol |
| == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging |
| { |
| throw (new InvalidOperationException("Can't set a formula for the entire worksheet")); |
| } |
| if (address.Start.Row == address.End.Row |
| && address.Start.Column == address.End.Column |
| && !isArray) //is it really a shared formula? Arrayformulas can be one cell only |
| { |
| //Nope, single cell. Set the formula |
| Set_Formula(value, address.Start.Row, address.Start.Column); |
| return; |
| } |
| //RemoveFormuls(address); |
| CheckAndSplitSharedFormula(address); |
| ExcelWorksheet.Formulas f = new ExcelWorksheet.Formulas(SourceCodeTokenizer.Default); |
| f.Formula = value; |
| f.Index = _worksheet.GetMaxShareFunctionIndex(isArray); |
| f.Address = address.FirstAddress; |
| f.StartCol = address.Start.Column; |
| f.StartRow = address.Start.Row; |
| f.IsArray = isArray; |
| |
| _worksheet._sharedFormulas.Add(f.Index, f); |
| //_worksheet.Cell(address.Start.Row, address.Start.Column).SharedFormulaID = f.Index; |
| //_worksheet.Cell(address.Start.Row, address.Start.Column).Formula = value; |
| |
| for (int col = address.Start.Column; col <= address.End.Column; col++) { |
| for (int row = address.Start.Row; row <= address.End.Row; row++) { |
| //_worksheet.Cell(row, col).SharedFormulaID = f.Index; |
| _worksheet._formulas.SetValue(row, col, f.Index); |
| _worksheet._values.SetValue(row, col, null); |
| } |
| } |
| } |
| |
| private void Set_HyperLink(object value, int row, int col) { |
| //_worksheet.Cell(row, col).Hyperlink = value as Uri; |
| if (value is Uri uri) { |
| _worksheet._hyperLinks.SetValue(row, col, uri); |
| |
| if (uri is ExcelHyperLink link) { |
| _worksheet._values.SetValue(row, col, link.Display); |
| } else { |
| _worksheet._values.SetValue(row, col, uri.OriginalString); |
| } |
| } else { |
| _worksheet._hyperLinks.SetValue(row, col, null); |
| _worksheet._values.SetValue(row, col, null); |
| } |
| } |
| |
| private void Set_IsArrayFormula(object value, int row, int col) { |
| _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.ArrayFormula); |
| } |
| |
| private void Set_IsRichText(object value, int row, int col) { |
| //_worksheet.Cell(row, col).IsRichText = (bool)value; |
| _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.RichText); |
| } |
| |
| private void Exists_Comment(object value, int row, int col) { |
| ulong cellId = GetCellId(_worksheet.SheetID, row, col); |
| if (_worksheet.Comments._comments.ContainsKey(cellId)) { |
| throw (new InvalidOperationException( |
| string.Format( |
| "Cell {0} already contain a comment.", |
| new ExcelCellAddress(row, col).Address))); |
| } |
| } |
| |
| private void SetToSelectedRange() { |
| if (_worksheet.View.SelectedRange == "") { |
| Address = "A1"; |
| } else { |
| Address = _worksheet.View.SelectedRange; |
| } |
| } |
| |
| private void IsRangeValid(string type) { |
| if (_fromRow <= 0) { |
| if (_address == "") { |
| SetToSelectedRange(); |
| } else { |
| if (type == "") { |
| throw (new InvalidOperationException( |
| string.Format("Range is not valid for this operation: {0}", _address))); |
| } |
| throw (new InvalidOperationException( |
| string.Format("Range is not valid for {0} : {1}", type, _address))); |
| } |
| } |
| } |
| |
| /// <summary> |
| /// The styleobject for the range. |
| /// </summary> |
| public ExcelStyle Style { |
| get { |
| IsRangeValid("styling"); |
| int s = 0; |
| if (!_worksheet._styles.Exists( |
| _fromRow, |
| _fromCol, |
| ref s)) //Cell exists |
| { |
| if (!_worksheet._styles.Exists( |
| _fromRow, |
| 0, |
| ref s)) //No, check Row style |
| { |
| var c = Worksheet.GetColumn(_fromCol); |
| if (c == null) { |
| s = 0; |
| } else { |
| s = c.StyleID; |
| } |
| } |
| } |
| return _worksheet.Workbook.Styles.GetStyleObject(s, _worksheet.PositionID, Address); |
| } |
| } |
| |
| /// <summary> |
| /// The named style |
| /// </summary> |
| public string StyleName { |
| get { |
| IsRangeValid("styling"); |
| int xfId; |
| if (_fromRow == 1 && _toRow == ExcelPackage.MaxRows) { |
| xfId = GetColumnStyle(_fromCol); |
| } else if (_fromCol == 1 && _toCol == ExcelPackage.MaxColumns) { |
| xfId = 0; |
| if (!_worksheet._styles.Exists(_fromRow, 0, ref xfId)) { |
| xfId = GetColumnStyle(_fromCol); |
| } |
| } else { |
| xfId = 0; |
| if (!_worksheet._styles.Exists(_fromRow, _fromCol, ref xfId)) { |
| if (!_worksheet._styles.Exists(_fromRow, 0, ref xfId)) { |
| xfId = GetColumnStyle(_fromCol); |
| } |
| } |
| } |
| int nsId; |
| if (xfId <= 0) { |
| nsId = Style.Styles.CellXfs[0].XfId; |
| } else { |
| nsId = Style.Styles.CellXfs[xfId].XfId; |
| } |
| foreach (var ns in Style.Styles.NamedStyles) { |
| if (ns.StyleXfId == nsId) { |
| return ns.Name; |
| } |
| } |
| |
| return ""; |
| } |
| set { |
| _styleID = _worksheet.Workbook.Styles.GetStyleIdFromName(value); |
| int col = _fromCol; |
| if (_fromRow == 1 |
| && _toRow |
| == ExcelPackage.MaxRows) //Full column |
| { |
| ExcelColumn column; |
| //Get the startcolumn |
| //ulong colID = ExcelColumn.GetColumnID(_worksheet.SheetID, column); |
| var c = _worksheet.GetValue(0, _fromCol); |
| if (c == null) { |
| column = _worksheet.Column(_fromCol); |
| //if (_worksheet._values.PrevCell(ref row, ref col)) |
| //{ |
| // var prevCol = (ExcelColumn)_worksheet._values.GetValue(row, col); |
| // column = prevCol.Clone(_worksheet, column); |
| // prevCol.ColumnMax = column - 1; |
| //} |
| } else { |
| column = (ExcelColumn)c; |
| } |
| |
| column.StyleName = value; |
| column.StyleID = _styleID; |
| |
| //var index = _worksheet._columns.IndexOf(colID); |
| var cols = new CellsStoreEnumerator<object>(_worksheet._values, 0, _fromCol + 1, 0, _toCol); |
| if (cols.Next()) { |
| col = _fromCol; |
| while (column.ColumnMin <= _toCol) { |
| if (column.ColumnMax > _toCol) { |
| var newCol = _worksheet.CopyColumn(column, _toCol + 1, column.ColumnMax); |
| column.ColumnMax = _toCol; |
| } |
| |
| column._styleName = value; |
| column.StyleID = _styleID; |
| |
| if (cols.Value == null) { |
| break; |
| } |
| var nextCol = (ExcelColumn)cols.Value; |
| if (column.ColumnMax < nextCol.ColumnMax - 1) { |
| column.ColumnMax = nextCol.ColumnMax - 1; |
| } |
| column = nextCol; |
| cols.Next(); |
| } |
| } |
| if (column.ColumnMax < _toCol) { |
| column.ColumnMax = _toCol; |
| } |
| //if (column.ColumnMin == column) |
| //{ |
| // column.ColumnMax = _toCol; |
| //} |
| //else if (column._columnMax < _toCol) |
| //{ |
| // var newCol = _worksheet.Column(column._columnMax + 1) as ExcelColumn; |
| // newCol._columnMax = _toCol; |
| |
| // newCol._styleID = _styleID; |
| // newCol._styleName = value; |
| //} |
| if (_fromCol == 1 |
| && _toCol |
| == ExcelPackage.MaxColumns) //FullRow |
| { |
| var rows = new CellsStoreEnumerator<object>( |
| _worksheet._values, |
| 1, |
| 0, |
| ExcelPackage.MaxRows, |
| 0); |
| rows.Next(); |
| while (rows.Value != null) { |
| _worksheet._styles.SetValue(rows.Row, 0, _styleID); |
| if (!rows.Next()) { |
| break; |
| } |
| } |
| } |
| } else if (_fromCol == 1 |
| && _toCol |
| == ExcelPackage.MaxColumns) //FullRow |
| { |
| for (int r = _fromRow; r <= _toRow; r++) { |
| _worksheet.Row(r)._styleName = value; |
| _worksheet.Row(r).StyleID = _styleID; |
| } |
| } |
| |
| if (!((_fromRow == 1 && _toRow == ExcelPackage.MaxRows) |
| || (_fromCol == 1 |
| && _toCol |
| == ExcelPackage.MaxColumns))) //Cell specific |
| { |
| for (int c = _fromCol; c <= _toCol; c++) { |
| for (int r = _fromRow; r <= _toRow; r++) { |
| _worksheet._styles.SetValue(r, c, _styleID); |
| } |
| } |
| } else //Only set name on created cells. (uncreated cells is set on full row or full column). |
| { |
| var cells = new CellsStoreEnumerator<object>( |
| _worksheet._values, |
| _fromRow, |
| _fromCol, |
| _toRow, |
| _toCol); |
| while (cells.Next()) { |
| _worksheet._styles.SetValue(cells.Row, cells.Column, _styleID); |
| } |
| } |
| //_changePropMethod(Set_StyleName, value); |
| } |
| } |
| |
| private int GetColumnStyle(int col) { |
| object c = null; |
| if (_worksheet._values.Exists(0, col, ref c)) { |
| return (c as ExcelColumn).StyleID; |
| } |
| int row = 0; |
| if (_worksheet._values.PrevCell(ref row, ref col)) { |
| var column = _worksheet._values.GetValue(row, col) as ExcelColumn; |
| if (column.ColumnMax >= col) { |
| return _worksheet._styles.GetValue(row, col); |
| } |
| } |
| return 0; |
| } |
| |
| /// <summary> |
| /// The style ID. |
| /// It is not recomended to use this one. Use Named styles as an alternative. |
| /// If you do, make sure that you use the Style.UpdateXml() method to update any new styles added to the workbook. |
| /// </summary> |
| public int StyleID { |
| get { |
| int s = 0; |
| if (!_worksheet._styles.Exists(_fromRow, _fromCol, ref s)) { |
| if (!_worksheet._styles.Exists(_fromRow, 0, ref s)) { |
| s = _worksheet._styles.GetValue(0, _fromCol); |
| } |
| } |
| return s; |
| } |
| set => _changePropMethod(Set_StyleID, value); |
| } |
| |
| /// <summary> |
| /// Set the range to a specific value |
| /// </summary> |
| public object Value { |
| get { |
| if (IsName) { |
| if (_worksheet == null) { |
| return _workbook._names[_address].NameValue; |
| } |
| return _worksheet.Names[_address].NameValue; |
| } |
| if (_fromRow == _toRow && _fromCol == _toCol) { |
| return _worksheet.GetValue(_fromRow, _fromCol); |
| } |
| return GetValueArray(); |
| } |
| set { |
| if (IsName) { |
| if (_worksheet == null) { |
| _workbook._names[_address].NameValue = value; |
| } else { |
| _worksheet.Names[_address].NameValue = value; |
| } |
| } else { |
| _changePropMethod(Set_Value, value); |
| } |
| } |
| } |
| |
| private object GetValueArray() { |
| ExcelAddressBase addr; |
| if (_fromRow == 1 |
| && _fromCol == 1 |
| && _toRow == ExcelPackage.MaxRows |
| && _toCol == ExcelPackage.MaxColumns) { |
| addr = _worksheet.Dimension; |
| if (addr == null) { |
| return null; |
| } |
| } else { |
| addr = this; |
| } |
| object[,] v = new object[addr._toRow - addr._fromRow + 1, addr._toCol - addr._fromCol + 1]; |
| |
| for (int col = addr._fromCol; col <= addr._toCol; col++) { |
| for (int row = addr._fromRow; row <= addr._toRow; row++) { |
| if (_worksheet._values.Exists(row, col)) { |
| if (_worksheet._flags.GetFlagValue(row, col, CellFlags.RichText)) { |
| v[row - addr._fromRow, col - addr._fromCol] = GetRichText(row, col).Text; |
| } else { |
| v[row - addr._fromRow, col - addr._fromCol] = _worksheet._values.GetValue(row, col); |
| } |
| } |
| } |
| } |
| return v; |
| } |
| |
| /// <summary> |
| /// Returns the formatted value. |
| /// </summary> |
| public string Text => GetFormattedText(false); |
| |
| private string GetFormattedText(bool forWidthCalc) { |
| object v = Value; |
| if (v == null) { |
| return ""; |
| } |
| var styles = Worksheet.Workbook.Styles; |
| var nfId = styles.CellXfs[StyleID].NumberFormatId; |
| ExcelNumberFormatXml.ExcelFormatTranslator nf = null; |
| for (int i = 0; i < styles.NumberFormats.Count; i++) { |
| if (nfId == styles.NumberFormats[i].NumFmtId) { |
| nf = styles.NumberFormats[i].FormatTranslator; |
| break; |
| } |
| } |
| |
| string format, |
| textFormat; |
| if (forWidthCalc) { |
| format = nf.NetFormatForWidth; |
| textFormat = nf.NetTextFormatForWidth; |
| } else { |
| format = nf.NetFormat; |
| textFormat = nf.NetTextFormat; |
| } |
| |
| return FormatValue(v, nf, format, textFormat); |
| } |
| |
| internal static string FormatValue( |
| object v, |
| ExcelNumberFormatXml.ExcelFormatTranslator nf, |
| string format, |
| string textFormat) { |
| if (v is decimal || v.GetType().IsPrimitive) { |
| double d; |
| try { |
| d = Convert.ToDouble(v); |
| } catch { |
| return ""; |
| } |
| |
| if (nf.DataType == ExcelNumberFormatXml.eFormatType.Number) { |
| if (string.IsNullOrEmpty(nf.FractionFormat)) { |
| return d.ToString(format, nf.Culture); |
| } |
| return nf.FormatFraction(d); |
| } |
| if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { |
| var date = DateTime.FromOADate(d); |
| return date.ToString(format, nf.Culture); |
| } |
| } else if (v is DateTime time) { |
| if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { |
| return time.ToString(format, nf.Culture); |
| } |
| double d = time.ToOADate(); |
| if (string.IsNullOrEmpty(nf.FractionFormat)) { |
| return d.ToString(format, nf.Culture); |
| } |
| return nf.FormatFraction(d); |
| } else if (v is TimeSpan span) { |
| if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime) { |
| return new DateTime(span.Ticks).ToString(format, nf.Culture); |
| } |
| double d = (new DateTime(span.Ticks)).ToOADate(); |
| if (string.IsNullOrEmpty(nf.FractionFormat)) { |
| return d.ToString(format, nf.Culture); |
| } |
| return nf.FormatFraction(d); |
| } else { |
| if (textFormat == "") { |
| return v.ToString(); |
| } |
| return string.Format(textFormat, v); |
| } |
| return v.ToString(); |
| } |
| |
| /// <summary> |
| /// Gets or sets a formula for a range. |
| /// </summary> |
| public string Formula { |
| get { |
| if (IsName) { |
| if (_worksheet == null) { |
| return _workbook._names[_address].NameFormula; |
| } |
| return _worksheet.Names[_address].NameFormula; |
| } |
| return _worksheet.GetFormula(_fromRow, _fromCol); |
| } |
| set { |
| if (IsName) { |
| if (_worksheet == null) { |
| _workbook._names[_address].NameFormula = value; |
| } else { |
| _worksheet.Names[_address].NameFormula = value; |
| } |
| } else { |
| if (value == null || value.Trim() == "") { |
| //Set the cells to null |
| Value = null; |
| } else if (_fromRow == _toRow && _fromCol == _toCol) { |
| Set_Formula(value, _fromRow, _fromCol); |
| } else { |
| Set_SharedFormula(value, this, false); |
| if (Addresses != null) { |
| foreach (var address in Addresses) { |
| Set_SharedFormula(value, address, false); |
| } |
| } |
| } |
| } |
| } |
| } |
| |
| /// <summary> |
| /// Gets or Set a formula in R1C1 format. |
| /// </summary> |
| public string FormulaR1C1 { |
| get { |
| IsRangeValid("FormulaR1C1"); |
| return _worksheet.GetFormulaR1C1(_fromRow, _fromCol); |
| } |
| set { |
| IsRangeValid("FormulaR1C1"); |
| if (value.Length > 0 && value[0] == '=') { |
| value = value.Substring(1, value.Length - 1); // remove any starting equalsign. |
| } |
| |
| if (value == null || value.Trim() == "") { |
| //Set the cells to null |
| _worksheet.Cells[TranslateFromR1C1(value, _fromRow, _fromCol)].Value = null; |
| } else if (Addresses == null) { |
| Set_SharedFormula(TranslateFromR1C1(value, _fromRow, _fromCol), this, false); |
| } else { |
| Set_SharedFormula( |
| TranslateFromR1C1(value, _fromRow, _fromCol), |
| new(WorkSheet, FirstAddress), |
| false); |
| foreach (var address in Addresses) { |
| Set_SharedFormula( |
| TranslateFromR1C1(value, address.Start.Row, address.Start.Column), |
| address, |
| false); |
| } |
| } |
| } |
| } |
| |
| /// <summary> |
| /// Gets or Set a formula in R1C1 format. |
| /// |
| public string FormulaR1C1_V1 { |
| get { |
| IsRangeValid("FormulaR1C1"); |
| return _worksheet.GetFormulaR1C1_V1(_fromRow, _fromCol); |
| } |
| } |
| |
| public string ArrayFormulaAddress { |
| get { |
| IsRangeValid("FormulaR1C1"); |
| return _worksheet.GetArrayFormulaAddress(_fromRow, _fromCol); |
| } |
| } |
| |
| /// <summary> |
| /// Set the hyperlink property for a range of cells |
| /// </summary> |
| public Uri Hyperlink { |
| get { |
| IsRangeValid("formulaR1C1"); |
| return _worksheet._hyperLinks.GetValue(_fromRow, _fromCol); |
| } |
| set => _changePropMethod(Set_HyperLink, value); |
| } |
| |
| /// <summary> |
| /// If the cells in the range are merged. |
| /// </summary> |
| public bool Merge { |
| get { |
| IsRangeValid("merging"); |
| for (int col = _fromCol; col <= _toCol; col++) { |
| for (int row = _fromRow; row <= _toRow; row++) { |
| if (_worksheet.MergedCells[row, col] == null) { |
| return false; |
| } |
| //if (!_worksheet._flags.GetFlagValue(row, col, CellFlags.Merged)) |
| //{ |
| // return false; |
| //} |
| } |
| } |
| return true; |
| } |
| set { |
| IsRangeValid("merging"); |
| //SetMerge(value, FirstAddress); |
| if (value) { |
| _worksheet.MergedCells.Add(new(FirstAddress), true); |
| if (Addresses != null) { |
| foreach (var address in Addresses) { |
| _worksheet.MergedCells.Add(address, true); |
| //SetMerge(value, address._address); |
| } |
| } |
| } else { |
| _worksheet.MergedCells.Clear(this); |
| if (Addresses != null) { |
| foreach (var address in Addresses) { |
| _worksheet.MergedCells.Clear(address); |
| ; |
| } |
| } |
| } |
| } |
| } |
| |
| //private void SetMerge(bool value, string address) |
| //{ |
| // if (!value) |
| // { |
| // if (_worksheet.MergedCells.List.Contains(address)) |
| // { |
| // SetCellMerge(false, address); |
| // _worksheet.MergedCells.List.Remove(address); |
| // } |
| // else if (!CheckMergeDiff(false, address)) |
| // { |
| // throw (new Exception("Range is not fully merged.Specify the exact range")); |
| // } |
| // } |
| // else |
| // { |
| // if (CheckMergeDiff(false, address)) |
| // { |
| // SetCellMerge(true, address); |
| // _worksheet.MergedCells.List.Add(address); |
| // } |
| // else |
| // { |
| // if (!_worksheet.MergedCells.List.Contains(address)) |
| // { |
| // throw (new Exception("Cells are already merged")); |
| // } |
| // } |
| // } |
| //} |
| /// <summary> |
| /// Set an autofilter for the range |
| /// </summary> |
| public bool AutoFilter { |
| get { |
| IsRangeValid("autofilter"); |
| ExcelAddressBase address = _worksheet.AutoFilterAddress; |
| if (address == null) { |
| return false; |
| } |
| if (_fromRow >= address.Start.Row |
| && _toRow <= address.End.Row |
| && _fromCol >= address.Start.Column |
| && _toCol <= address.End.Column) { |
| return true; |
| } |
| return false; |
| } |
| set { |
| IsRangeValid("autofilter"); |
| _worksheet.AutoFilterAddress = this; |
| if (_worksheet.Names.ContainsKey("_xlnm._FilterDatabase")) { |
| _worksheet.Names.Remove("_xlnm._FilterDatabase"); |
| } |
| var result = _worksheet.Names.Add("_xlnm._FilterDatabase", this); |
| result.IsNameHidden = true; |
| } |
| } |
| |
| /// <summary> |
| /// If the value is in richtext format. |
| /// </summary> |
| public bool IsRichText { |
| get { |
| IsRangeValid("richtext"); |
| return _worksheet._flags.GetFlagValue(_fromRow, _fromCol, CellFlags.RichText); |
| } |
| set => _changePropMethod(Set_IsRichText, value); |
| } |
| |
| /// <summary> |
| /// Is the range a part of an Arrayformula |
| /// </summary> |
| public bool IsArrayFormula { |
| get { |
| IsRangeValid("arrayformulas"); |
| return _worksheet._flags.GetFlagValue(_fromRow, _fromCol, CellFlags.ArrayFormula); |
| } |
| set => _changePropMethod(Set_IsArrayFormula, value); |
| } |
| |
| private ExcelRichTextCollection _rtc; |
| |
| /// <summary> |
| /// Cell value is richtext formatted. |
| /// Richtext-property only apply to the left-top cell of the range. |
| /// </summary> |
| public ExcelRichTextCollection RichText { |
| get { |
| IsRangeValid("richtext"); |
| if (_rtc == null) { |
| _rtc = GetRichText(_fromRow, _fromCol); |
| } |
| return _rtc; |
| } |
| } |
| |
| private ExcelRichTextCollection GetRichText(int row, int col) { |
| XmlDocument xml = new XmlDocument(); |
| var v = _worksheet._values.GetValue(row, col); |
| var isRt = _worksheet._flags.GetFlagValue(row, col, CellFlags.RichText); |
| if (v != null) { |
| if (isRt) { |
| XmlHelper.LoadXmlSafe( |
| xml, |
| "<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" >" |
| + v |
| + "</d:si>", |
| Encoding.UTF8); |
| } else { |
| xml.LoadXml( |
| "<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" ><d:r><d:t>" |
| + SecurityElement.Escape(v.ToString()) |
| + "</d:t></d:r></d:si>"); |
| } |
| } else { |
| xml.LoadXml("<d:si xmlns:d=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" />"); |
| } |
| var rtc = new ExcelRichTextCollection( |
| _worksheet.NameSpaceManager, |
| xml.SelectSingleNode("d:si", _worksheet.NameSpaceManager), |
| this); |
| return rtc; |
| } |
| |
| /// <summary> |
| /// returns the comment object of the first cell in the range |
| /// </summary> |
| public ExcelComment Comment { |
| get { |
| IsRangeValid("comments"); |
| ulong cellId = GetCellId(_worksheet.SheetID, _fromRow, _fromCol); |
| if (_worksheet.Comments._comments.ContainsKey(cellId)) { |
| return _worksheet._comments._comments[cellId] as ExcelComment; |
| } |
| return null; |
| } |
| } |
| |
| /// <summary> |
| /// WorkSheet object |
| /// </summary> |
| public ExcelWorksheet Worksheet => _worksheet; |
| |
| /// <summary> |
| /// Address including sheetname |
| /// </summary> |
| public string FullAddress { |
| get { |
| string fullAddress = GetFullAddress(_worksheet.Name, _address); |
| if (Addresses != null) { |
| foreach (var a in Addresses) { |
| fullAddress += "," + GetFullAddress(_worksheet.Name, a.Address); |
| ; |
| } |
| } |
| return fullAddress; |
| } |
| } |
| |
| /// <summary> |
| /// Address including sheetname |
| /// </summary> |
| public string FullAddressAbsolute { |
| get { |
| string wbwsRef = string.IsNullOrEmpty(_wb) ? _ws : "[" + _wb.Replace("'", "''") + "]" + _ws; |
| string fullAddress = GetFullAddress( |
| wbwsRef, |
| GetAddress(_fromRow, _fromCol, _toRow, _toCol, true)); |
| if (Addresses != null) { |
| foreach (var a in Addresses) { |
| fullAddress += |
| "," |
| + GetFullAddress( |
| wbwsRef, |
| GetAddress(a.Start.Row, a.Start.Column, a.End.Row, a.End.Column, true)); |
| ; |
| } |
| } |
| return fullAddress; |
| } |
| } |
| |
| /// <summary> |
| /// Address including sheetname |
| /// </summary> |
| internal string FullAddressAbsoluteNoFullRowCol { |
| get { |
| string wbwsRef = string.IsNullOrEmpty(_wb) ? _ws : "[" + _wb.Replace("'", "''") + "]" + _ws; |
| string fullAddress = GetFullAddress( |
| wbwsRef, |
| GetAddress(_fromRow, _fromCol, _toRow, _toCol, true), |
| false); |
| if (Addresses != null) { |
| foreach (var a in Addresses) { |
| fullAddress += |
| "," |
| + GetFullAddress( |
| wbwsRef, |
| GetAddress(a.Start.Row, a.Start.Column, a.End.Row, a.End.Column, true), |
| false); |
| ; |
| } |
| } |
| return fullAddress; |
| } |
| } |
| |
| /// <summary> |
| /// Set the value without altering the richtext property |
| /// </summary> |
| /// <param name="value">the value</param> |
| internal void SetValueRichText(object value) { |
| if (_fromRow == 1 |
| && _fromCol == 1 |
| && _toRow == ExcelPackage.MaxRows |
| && _toCol |
| == ExcelPackage.MaxColumns) //Full sheet (ex ws.Cells.Value=0). Set value for A1 only to avoid hanging |
| { |
| //_worksheet.Cell(1, 1).SetValueRichText(value); |
| SetValue(value, 1, 1); |
| } else { |
| //for (int col = _fromCol; col <= _toCol; col++) |
| //{ |
| // for (int row = _fromRow; row <= _toRow; row++) |
| // { |
| //_worksheet.Cell(row, col).SetValueRichText(value); |
| SetValue(value, _fromRow, _fromCol); |
| //} |
| //} |
| } |
| } |
| |
| private void SetValue(object value, int row, int col) { |
| _worksheet.SetValue(row, col, value); |
| // if (value is string) _worksheet._types.SetValue(row, col, "S"); else _worksheet._types.SetValue(row, col, ""); |
| _worksheet._formulas.SetValue(row, col, ""); |
| } |
| |
| internal void SetSharedFormulaId(int id) { |
| for (int col = _fromCol; col <= _toCol; col++) { |
| for (int row = _fromRow; row <= _toRow; row++) { |
| _worksheet._formulas.SetValue(row, col, id); |
| } |
| } |
| } |
| |
| private void CheckAndSplitSharedFormula(ExcelAddressBase address) { |
| for (int col = address._fromCol; col <= address._toCol; col++) { |
| for (int row = address._fromRow; row <= address._toRow; row++) { |
| var f = _worksheet._formulas.GetValue(row, col); |
| if (f is int i && i >= 0) { |
| SplitFormulas(address); |
| return; |
| } |
| } |
| } |
| } |
| |
| private void SplitFormulas(ExcelAddressBase address) { |
| List<int> formulas = []; |
| for (int col = address._fromCol; col <= address._toCol; col++) { |
| for (int row = address._fromRow; row <= address._toRow; row++) { |
| var f = _worksheet._formulas.GetValue(row, col); |
| if (f is int id) { |
| if (id >= 0 && !formulas.Contains(id)) { |
| if (_worksheet._sharedFormulas[id].IsArray |
| && Collide(_worksheet.Cells[_worksheet._sharedFormulas[id].Address]) |
| == eAddressCollition.Partly) // If the formula is an array formula and its on the inside the overwriting range throw an exception |
| { |
| throw (new InvalidOperationException("Can not overwrite a part of an array-formula")); |
| } |
| formulas.Add(id); |
| } |
| } |
| } |
| } |
| |
| foreach (int ix in formulas) { |
| SplitFormula(address, ix); |
| } |
| |
| ////Clear any formula references inside the refered range |
| //_worksheet._formulas.Clear(address._fromRow, address._toRow, address._toRow - address._fromRow + 1, address._toCol - address.column + 1); |
| } |
| |
| private void SplitFormula(ExcelAddressBase address, int ix) { |
| var f = _worksheet._sharedFormulas[ix]; |
| var fRange = _worksheet.Cells[f.Address]; |
| var collide = address.Collide(fRange); |
| |
| //The formula is inside the currenct range, remove it |
| if (collide == eAddressCollition.Equal || collide == eAddressCollition.Inside) { |
| _worksheet._sharedFormulas.Remove(ix); |
| return; |
| //fRange.SetSharedFormulaID(int.MinValue); |
| } |
| var firstCellCollide = address.Collide( |
| new(fRange._fromRow, fRange._fromCol, fRange._fromRow, fRange._fromCol)); |
| if (collide == eAddressCollition.Partly |
| && (firstCellCollide == eAddressCollition.Inside |
| || firstCellCollide |
| == eAddressCollition.Equal)) //Do we need to split? Only if the functions first row is inside the new range. |
| { |
| //The formula partly collides with the current range |
| bool fIsSet = false; |
| string formulaR1C1 = fRange.FormulaR1C1; |
| //Top Range |
| if (fRange._fromRow < _fromRow) { |
| f.Address = GetAddress(fRange._fromRow, fRange._fromCol, _fromRow - 1, fRange._toCol); |
| fIsSet = true; |
| } |
| //Left Range |
| if (fRange._fromCol < address._fromCol) { |
| if (fIsSet) { |
| f = new(SourceCodeTokenizer.Default); |
| f.Index = _worksheet.GetMaxShareFunctionIndex(false); |
| f.StartCol = fRange._fromCol; |
| f.IsArray = false; |
| _worksheet._sharedFormulas.Add(f.Index, f); |
| } else { |
| fIsSet = true; |
| } |
| if (fRange._fromRow < address._fromRow) { |
| f.StartRow = address._fromRow; |
| } else { |
| f.StartRow = fRange._fromRow; |
| } |
| if (fRange._toRow < address._toRow) { |
| f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, address._fromCol - 1); |
| } else { |
| f.Address = GetAddress(f.StartRow, f.StartCol, address._toRow, address._fromCol - 1); |
| } |
| f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol); |
| _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index); |
| } |
| //Right Range |
| if (fRange._toCol > address._toCol) { |
| if (fIsSet) { |
| f = new(SourceCodeTokenizer.Default); |
| f.Index = _worksheet.GetMaxShareFunctionIndex(false); |
| f.IsArray = false; |
| _worksheet._sharedFormulas.Add(f.Index, f); |
| } else { |
| fIsSet = true; |
| } |
| f.StartCol = address._toCol + 1; |
| if (address._fromRow < fRange._fromRow) { |
| f.StartRow = fRange._fromRow; |
| } else { |
| f.StartRow = address._fromRow; |
| } |
| |
| if (fRange._toRow < address._toRow) { |
| f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, fRange._toCol); |
| } else { |
| f.Address = GetAddress(f.StartRow, f.StartCol, address._toRow, fRange._toCol); |
| } |
| f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol); |
| _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index); |
| } |
| //Bottom Range |
| if (fRange._toRow > address._toRow) { |
| if (fIsSet) { |
| f = new(SourceCodeTokenizer.Default); |
| f.Index = _worksheet.GetMaxShareFunctionIndex(false); |
| f.IsArray = false; |
| _worksheet._sharedFormulas.Add(f.Index, f); |
| } |
| |
| f.StartCol = fRange._fromCol; |
| f.StartRow = _toRow + 1; |
| |
| f.Formula = TranslateFromR1C1(formulaR1C1, f.StartRow, f.StartCol); |
| |
| f.Address = GetAddress(f.StartRow, f.StartCol, fRange._toRow, fRange._toCol); |
| _worksheet.Cells[f.Address].SetSharedFormulaId(f.Index); |
| } |
| } |
| } |
| |
| private object ConvertData(ExcelTextFormat format, string v, int col, bool isText) { |
| if (isText && (format.DataTypes == null || format.DataTypes.Length < col)) { |
| return v; |
| } |
| |
| double d; |
| DateTime dt; |
| if (format.DataTypes == null |
| || format.DataTypes.Length <= col |
| || format.DataTypes[col] == eDataTypes.Unknown) { |
| string v2 = v.EndsWith("%") ? v.Substring(0, v.Length - 1) : v; |
| if (double.TryParse(v2, NumberStyles.Any, format.Culture, out d)) { |
| if (v2 == v) { |
| return d; |
| } |
| return d / 100; |
| } |
| if (DateTime.TryParse(v, format.Culture, DateTimeStyles.None, out dt)) { |
| return dt; |
| } |
| return v; |
| } |
| switch (format.DataTypes[col]) { |
| case eDataTypes.Number: |
| if (double.TryParse(v, NumberStyles.Any, format.Culture, out d)) { |
| return d; |
| } |
| return v; |
| case eDataTypes.DateTime: |
| if (DateTime.TryParse(v, format.Culture, DateTimeStyles.None, out dt)) { |
| return dt; |
| } |
| return v; |
| case eDataTypes.Percent: |
| string v2 = v.EndsWith("%") ? v.Substring(0, v.Length - 1) : v; |
| if (double.TryParse(v2, NumberStyles.Any, format.Culture, out d)) { |
| return d / 100; |
| } |
| return v; |
| |
| default: |
| return v; |
| } |
| } |
| |
| /// <summary> |
| /// Conditional Formatting for this range. |
| /// </summary> |
| public IRangeConditionalFormatting ConditionalFormatting => |
| new RangeConditionalFormatting(_worksheet, new(Address)); |
| |
| /// <summary> |
| /// Data validation for this range. |
| /// </summary> |
| public IRangeDataValidation DataValidation => new RangeDataValidation(_worksheet, Address); |
| |
| /// <summary> |
| /// Get the strongly typed value of the cell. |
| /// </summary> |
| /// <typeparam name="T">The type</typeparam> |
| /// <returns>The value. If the value can't be converted to the specified type, the default value will be returned</returns> |
| public T GetValue<T>() { |
| return _worksheet.GetTypedValue<T>(Value); |
| } |
| |
| /// <summary> |
| /// Get a range with an offset from the top left cell. |
| /// The new range has the same dimensions as the current range |
| /// </summary> |
| /// <param name="rowOffset">Row Offset</param> |
| /// <param name="columnOffset">Column Offset</param> |
| /// <returns></returns> |
| public ExcelRangeBase Offset(int rowOffset, int columnOffset) { |
| if (_fromRow + rowOffset < 1 |
| || _fromCol + columnOffset < 1 |
| || _fromRow + rowOffset > ExcelPackage.MaxRows |
| || _fromCol + columnOffset > ExcelPackage.MaxColumns) { |
| throw (new ArgumentOutOfRangeException("Offset value out of range")); |
| } |
| string address = GetAddress( |
| _fromRow + rowOffset, |
| _fromCol + columnOffset, |
| _toRow + rowOffset, |
| _toCol + columnOffset); |
| return new(_worksheet, address); |
| } |
| |
| /// <summary> |
| /// Get a range with an offset from the top left cell. |
| /// </summary> |
| /// <param name="rowOffset">Row Offset</param> |
| /// <param name="columnOffset">Column Offset</param> |
| /// <param name="numberOfRows">Number of rows. Minimum 1</param> |
| /// <param name="numberOfColumns">Number of colums. Minimum 1</param> |
| /// <returns></returns> |
| public ExcelRangeBase Offset( |
| int rowOffset, |
| int columnOffset, |
| int numberOfRows, |
| int numberOfColumns) { |
| if (numberOfRows < 1 || numberOfColumns < 1) { |
| throw (new("Number of rows/columns must be greater than 0")); |
| } |
| numberOfRows--; |
| numberOfColumns--; |
| if (_fromRow + rowOffset < 1 |
| || _fromCol + columnOffset < 1 |
| || _fromRow + rowOffset > ExcelPackage.MaxRows |
| || _fromCol + columnOffset > ExcelPackage.MaxColumns |
| || _fromRow + rowOffset + numberOfRows < 1 |
| || _fromCol + columnOffset + numberOfColumns < 1 |
| || _fromRow + rowOffset + numberOfRows > ExcelPackage.MaxRows |
| || _fromCol + columnOffset + numberOfColumns > ExcelPackage.MaxColumns) { |
| throw (new ArgumentOutOfRangeException("Offset value out of range")); |
| } |
| string address = GetAddress( |
| _fromRow + rowOffset, |
| _fromCol + columnOffset, |
| _fromRow + rowOffset + numberOfRows, |
| _fromCol + columnOffset + numberOfColumns); |
| return new(_worksheet, address); |
| } |
| |
| /// <summary> |
| /// Clear all cells |
| /// </summary> |
| public void Clear() { |
| Delete(this, false); |
| } |
| |
| /// <summary> |
| /// Creates an array-formula. |
| /// </summary> |
| /// <param name="arrayFormula">The formula</param> |
| public void CreateArrayFormula(string arrayFormula) { |
| if (Addresses != null) { |
| throw (new("An Arrayformula can not have more than one address")); |
| } |
| Set_SharedFormula(arrayFormula, this, true); |
| } |
| |
| //private void Clear(ExcelAddressBase Range) |
| //{ |
| // Clear(Range, true); |
| //} |
| internal void Delete(ExcelAddressBase range, bool shift) { |
| //DeleteCheckMergedCells(Range); |
| _worksheet.MergedCells.Clear(range); |
| //First find the start cell |
| int fromRow, |
| fromCol; |
| var d = Worksheet.Dimension; |
| if (d != null |
| && range._fromRow <= d._fromRow |
| && range._toRow |
| >= d._toRow) //EntireRow? |
| { |
| fromRow = 0; |
| } else { |
| fromRow = range._fromRow; |
| } |
| if (d != null |
| && range._fromCol <= d._fromCol |
| && range._toCol |
| >= d._toCol) //EntireRow? |
| { |
| fromCol = 0; |
| } else { |
| fromCol = range._fromCol; |
| } |
| |
| var rows = range._toRow - fromRow + 1; |
| var cols = range._toCol - fromCol + 1; |
| |
| _worksheet._values.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._types.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._styles.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._formulas.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._hyperLinks.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._flags.Delete(fromRow, fromCol, rows, cols, shift); |
| _worksheet._commentsStore.Delete(fromRow, fromCol, rows, cols, shift); |
| |
| //if(shift) |
| //{ |
| // _worksheet.AdjustFormulasRow(fromRow, rows); |
| //} |
| |
| //Clear multi addresses as well |
| if (Addresses != null) { |
| foreach (var sub in Addresses) { |
| Delete(sub, shift); |
| } |
| } |
| } |
| |
| public void Dispose() {} |
| |
| //int _index; |
| //ulong _toCellId; |
| //int _enumAddressIx; |
| private CellsStoreEnumerator<object> cellEnum; |
| |
| public IEnumerator<ExcelRangeBase> GetEnumerator() { |
| Reset(); |
| return this; |
| } |
| |
| IEnumerator IEnumerable.GetEnumerator() { |
| Reset(); |
| return this; |
| } |
| |
| /// <summary> |
| /// The current range when enumerating |
| /// </summary> |
| public ExcelRangeBase Current => new(_worksheet, GetAddress(cellEnum.Row, cellEnum.Column)); |
| |
| /// <summary> |
| /// The current range when enumerating |
| /// </summary> |
| object IEnumerator.Current => |
| new ExcelRangeBase(_worksheet, GetAddress(cellEnum.Row, cellEnum.Column)); |
| |
| private int _enumAddressIx = -1; |
| |
| public bool MoveNext() { |
| if (cellEnum.Next()) { |
| return true; |
| } |
| if (_addresses != null) { |
| _enumAddressIx++; |
| if (_enumAddressIx < _addresses.Count) { |
| cellEnum = new( |
| _worksheet._values, |
| _addresses[_enumAddressIx]._fromRow, |
| _addresses[_enumAddressIx]._fromCol, |
| _addresses[_enumAddressIx]._toRow, |
| _addresses[_enumAddressIx]._toCol); |
| return MoveNext(); |
| } |
| return false; |
| } |
| return false; |
| } |
| |
| public void Reset() { |
| _enumAddressIx = -1; |
| cellEnum = new(_worksheet._values, _fromRow, _fromCol, _toRow, _toCol); |
| } |
| |
| //private void GetNextIndexEnum(int fromRow, int fromCol, int toRow, int toCol) |
| //{ |
| // if (_index >= _worksheet._cells.Count) return; |
| // ExcelCell cell = _worksheet._cells[_index] as ExcelCell; |
| // while (cell.Column > toCol || cell.Column < fromCol) |
| // { |
| // if (cell.Column < fromCol) |
| // { |
| // _index = _worksheet._cells.IndexOf(ExcelAddress.GetCellID(_worksheet.SheetID, cell.Row, fromCol)); |
| // } |
| // else |
| // { |
| // _index = _worksheet._cells.IndexOf(ExcelAddress.GetCellID(_worksheet.SheetID, cell.Row + 1, fromCol)); |
| // } |
| |
| // if (_index < 0) |
| // { |
| // _index = ~_index; |
| // } |
| // if (_index >= _worksheet._cells.Count || _worksheet._cells[_index].RangeID > _toCellId) |
| // { |
| // break; |
| // } |
| // cell = _worksheet._cells[_index] as ExcelCell; |
| // } |
| //} |
| |
| //private void GetStartIndexEnum(int fromRow, int fromCol, int toRow, int toCol) |
| //{ |
| // _index = _worksheet._cells.IndexOf(ExcelCellBase.GetCellID(_worksheet.SheetID, fromRow, fromCol)); |
| // _toCellId = ExcelCellBase.GetCellID(_worksheet.SheetID, toRow, toCol); |
| // if (_index < 0) |
| // { |
| // _index = ~_index; |
| // } |
| // _index--; |
| //} |
| } |
