| /******************************************************************************* | |
| * 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.ComponentModel; | |
| using System.Data; | |
| using System.Globalization; | |
| using System.IO; | |
| using System.Linq; | |
| using System.Reflection; | |
| using System.Security; | |
| using System.Text; | |
| using System.Text.RegularExpressions; | |
| using System.Threading; | |
| using System.Xml; | |
| using OfficeOpenXml.ConditionalFormatting; | |
| using OfficeOpenXml.DataValidation; | |
| using OfficeOpenXml.FormulaParsing.LexicalAnalysis; | |
| using OfficeOpenXml.Style; | |
| using OfficeOpenXml.Style.XmlAccess; | |
| using OfficeOpenXml.Table; | |
| namespace OfficeOpenXml; | |
| /// <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 Set_Comment(object value, int row, int col) { | |
| string[] v = (string[])value; | |
| Worksheet.Comments.Add(new(_worksheet, GetAddress(_fromRow, _fromCol)), v[0], v[1]); | |
| // _worksheet.Cell(row, col).Comment = comment; | |
| } | |
| 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 = new List<int>(); | |
| 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> | |
| /// Load the data from the datareader starting from the top left cell of the range | |
| /// </summary> | |
| /// <param name="reader">The datareader to loadfrom</param> | |
| /// <param name="printHeaders">Print the column caption property (if set) or the columnname property if not, on first row</param> | |
| /// <param name="tableName">The name of the table</param> | |
| /// <param name="tableStyle">The table style to apply to the data</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromDataReader( | |
| IDataReader reader, | |
| bool printHeaders, | |
| string tableName, | |
| TableStyles tableStyle = TableStyles.None) { | |
| var r = LoadFromDataReader(reader, printHeaders); | |
| int rows = r.Rows - 1; | |
| if (rows >= 0 && r.Columns > 0) { | |
| var tbl = _worksheet.Tables.Add( | |
| new(_fromRow, _fromCol, _fromRow + (rows <= 0 ? 1 : rows), _fromCol + r.Columns - 1), | |
| tableName); | |
| tbl.ShowHeader = printHeaders; | |
| tbl.TableStyle = tableStyle; | |
| } | |
| return r; | |
| } | |
| /// <summary> | |
| /// Load the data from the datareader starting from the top left cell of the range | |
| /// </summary> | |
| /// <param name="reader">The datareader to load from</param> | |
| /// <param name="printHeaders">Print the caption property (if set) or the columnname property if not, on first row</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromDataReader(IDataReader reader, bool printHeaders) { | |
| if (reader == null) { | |
| throw (new ArgumentNullException("reader", "Reader can't be null")); | |
| } | |
| int fieldCount = reader.FieldCount; | |
| int col = _fromCol, | |
| row = _fromRow; | |
| if (printHeaders) { | |
| for (int i = 0; i < fieldCount; i++) { | |
| // If no caption is set, the ColumnName property is called implicitly. | |
| _worksheet._values.SetValue(row, col++, reader.GetName(i)); | |
| } | |
| row++; | |
| col = _fromCol; | |
| } | |
| while (reader.Read()) { | |
| for (int i = 0; i < fieldCount; i++) { | |
| _worksheet._values.SetValue(row, col++, reader.GetValue(i)); | |
| } | |
| row++; | |
| col = _fromCol; | |
| } | |
| return _worksheet.Cells[_fromRow, _fromCol, row - 1, _fromCol + fieldCount - 1]; | |
| } | |
| /// <summary> | |
| /// Load the data from the datatable starting from the top left cell of the range | |
| /// </summary> | |
| /// <param name="table">The datatable to load</param> | |
| /// <param name="printHeaders">Print the column caption property (if set) or the columnname property if not, on first row</param> | |
| /// <param name="tableStyle">The table style to apply to the data</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromDataTable( | |
| DataTable table, | |
| bool printHeaders, | |
| TableStyles tableStyle) { | |
| var r = LoadFromDataTable(table, printHeaders); | |
| int rows = (table.Rows.Count == 0 ? 1 : table.Rows.Count) + (printHeaders ? 1 : 0); | |
| if (rows >= 0 && table.Columns.Count > 0) { | |
| var tbl = _worksheet.Tables.Add( | |
| new(_fromRow, _fromCol, _fromRow + rows - 1, _fromCol + table.Columns.Count - 1), | |
| table.TableName); | |
| tbl.ShowHeader = printHeaders; | |
| tbl.TableStyle = tableStyle; | |
| } | |
| return r; | |
| } | |
| /// <summary> | |
| /// Load the data from the datatable starting from the top left cell of the range | |
| /// </summary> | |
| /// <param name="table">The datatable to load</param> | |
| /// <param name="printHeaders">Print the caption property (if set) or the columnname property if not, on first row</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromDataTable(DataTable table, bool printHeaders) { | |
| if (table == null) { | |
| throw (new ArgumentNullException("Table can't be null")); | |
| } | |
| int col = _fromCol, | |
| row = _fromRow; | |
| if (printHeaders) { | |
| foreach (DataColumn dc in table.Columns) { | |
| // If no caption is set, the ColumnName property is called implicitly. | |
| _worksheet._values.SetValue(row, col++, dc.Caption); | |
| } | |
| row++; | |
| col = _fromCol; | |
| } else if (table.Rows.Count == 0) { | |
| return null; | |
| } | |
| foreach (DataRow dr in table.Rows) { | |
| foreach (object value in dr.ItemArray) { | |
| if (value != null && value != DBNull.Value && !string.IsNullOrEmpty(value.ToString())) { | |
| _worksheet._values.SetValue(row, col++, value); | |
| } else { | |
| col++; | |
| } | |
| } | |
| row++; | |
| col = _fromCol; | |
| } | |
| return _worksheet.Cells[_fromRow, | |
| _fromCol, | |
| (row == _fromRow ? _fromRow : row - 1), | |
| _fromCol + table.Columns.Count - 1]; | |
| } | |
| /// <summary> | |
| /// Loads data from the collection of arrays of objects into the range, starting from | |
| /// the top-left cell. | |
| /// </summary> | |
| /// <param name="data">The data.</param> | |
| public ExcelRangeBase LoadFromArrays(IEnumerable<object[]> data) { | |
| //thanx to Abdullin for the code contribution | |
| if (data == null) { | |
| throw new ArgumentNullException("data"); | |
| } | |
| int column = _fromCol, | |
| row = _fromRow; | |
| foreach (var rowData in data) { | |
| column = _fromCol; | |
| foreach (var cellData in rowData) { | |
| _worksheet._values.SetValue(row, column, cellData); | |
| column += 1; | |
| } | |
| row += 1; | |
| } | |
| return _worksheet.Cells[_fromRow, _fromCol, row - 1, column - 1]; | |
| } | |
| /// <summary> | |
| /// Load a collection into a the worksheet starting from the top left row of the range. | |
| /// </summary> | |
| /// <typeparam name="T">The datatype in the collection</typeparam> | |
| /// <param name="collection">The collection to load</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> collection) { | |
| return LoadFromCollection( | |
| collection, | |
| false, | |
| TableStyles.None, | |
| BindingFlags.Public | BindingFlags.Instance, | |
| null); | |
| } | |
| /// <summary> | |
| /// Load a collection of T into the worksheet starting from the top left row of the range. | |
| /// Default option will load all public instance properties of T | |
| /// </summary> | |
| /// <typeparam name="T">The datatype in the collection</typeparam> | |
| /// <param name="collection">The collection to load</param> | |
| /// <param name="printHeaders">Print the property names on the first row. If the property is decorated with a <see cref="DisplayNameAttribute"/> or a <see cref="DescriptionAttribute"/> that attribute will be used instead of the reflected member name.</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> collection, bool printHeaders) { | |
| return LoadFromCollection( | |
| collection, | |
| printHeaders, | |
| TableStyles.None, | |
| BindingFlags.Public | BindingFlags.Instance, | |
| null); | |
| } | |
| /// <summary> | |
| /// Load a collection of T into the worksheet starting from the top left row of the range. | |
| /// Default option will load all public instance properties of T | |
| /// </summary> | |
| /// <typeparam name="T">The datatype in the collection</typeparam> | |
| /// <param name="collection">The collection to load</param> | |
| /// <param name="printHeaders">Print the property names on the first row. If the property is decorated with a <see cref="DisplayNameAttribute"/> or a <see cref="DescriptionAttribute"/> that attribute will be used instead of the reflected member name.</param> | |
| /// <param name="tableStyle">Will create a table with this style. If set to TableStyles.None no table will be created</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromCollection<T>( | |
| IEnumerable<T> collection, | |
| bool printHeaders, | |
| TableStyles tableStyle) { | |
| return LoadFromCollection( | |
| collection, | |
| printHeaders, | |
| tableStyle, | |
| BindingFlags.Public | BindingFlags.Instance, | |
| null); | |
| } | |
| /// <summary> | |
| /// Load a collection into the worksheet starting from the top left row of the range. | |
| /// </summary> | |
| /// <typeparam name="T">The datatype in the collection</typeparam> | |
| /// <param name="collection">The collection to load</param> | |
| /// <param name="printHeaders">Print the property names on the first row. Any underscore in the property name will be converted to a space. If the property is decorated with a <see cref="DisplayNameAttribute"/> or a <see cref="DescriptionAttribute"/> that attribute will be used instead of the reflected member name.</param> | |
| /// <param name="tableStyle">Will create a table with this style. If set to TableStyles.None no table will be created</param> | |
| /// <param name="memberFlags">Property flags to use</param> | |
| /// <param name="members">The properties to output. Must be of type T</param> | |
| /// <returns>The filled range</returns> | |
| public ExcelRangeBase LoadFromCollection<T>( | |
| IEnumerable<T> collection, | |
| bool printHeaders, | |
| TableStyles tableStyle, | |
| BindingFlags memberFlags, | |
| MemberInfo[] members) { | |
| var type = typeof(T); | |
| if (members == null) { | |
| members = type.GetProperties(memberFlags); | |
| } else { | |
| foreach (var t in members) { | |
| if (t.DeclaringType != null | |
| && t.DeclaringType != type | |
| && !t.DeclaringType.IsSubclassOf(type)) { | |
| throw new InvalidCastException( | |
| "Supplied properties in parameter Properties must be of the same type as T (or an assignable type from T"); | |
| } | |
| } | |
| } | |
| int col = _fromCol, | |
| row = _fromRow; | |
| if (members.Length > 0 && printHeaders) { | |
| foreach (var t in members) { | |
| var descriptionAttribute = | |
| t.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() | |
| as DescriptionAttribute; | |
| var header = string.Empty; | |
| if (descriptionAttribute != null) { | |
| header = descriptionAttribute.Description; | |
| } else { | |
| var displayNameAttribute = | |
| t.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault() | |
| as DisplayNameAttribute; | |
| if (displayNameAttribute != null) { | |
| header = displayNameAttribute.DisplayName; | |
| } else { | |
| header = t.Name.Replace('_', ' '); | |
| } | |
| } | |
| _worksheet._values.SetValue(row, col++, header); | |
| } | |
| row++; | |
| } | |
| if (!collection.Any() && (members.Length == 0 || printHeaders == false)) { | |
| return null; | |
| } | |
| if (members.Length == 0) { | |
| foreach (var item in collection) { | |
| _worksheet.Cells[row++, col].Value = item; | |
| } | |
| } else { | |
| foreach (var item in collection) { | |
| col = _fromCol; | |
| if (item is string || item is decimal || item is DateTime || item.GetType().IsPrimitive) { | |
| _worksheet.Cells[row, col++].Value = item; | |
| } else { | |
| foreach (var t in members) { | |
| if (t is PropertyInfo info) { | |
| _worksheet.Cells[row, col++].Value = info.GetValue(item, null); | |
| } else if (t is FieldInfo fieldInfo) { | |
| _worksheet.Cells[row, col++].Value = fieldInfo.GetValue(item); | |
| } else if (t is MethodInfo methodInfo) { | |
| _worksheet.Cells[row, col++].Value = methodInfo.Invoke(item, null); | |
| } | |
| } | |
| } | |
| row++; | |
| } | |
| } | |
| if (_fromRow == row - 1 && printHeaders) { | |
| row++; | |
| } | |
| var r = _worksheet.Cells[_fromRow, _fromCol, row - 1, members.Length == 0 ? col : col - 1]; | |
| if (tableStyle != TableStyles.None) { | |
| var tbl = _worksheet.Tables.Add(r, ""); | |
| tbl.ShowHeader = printHeaders; | |
| tbl.TableStyle = tableStyle; | |
| } | |
| return r; | |
| } | |
| /// <summary> | |
| /// Loads a CSV text into a range starting from the top left cell. | |
| /// Default settings is Comma separation | |
| /// </summary> | |
| /// <param name="text">The Text</param> | |
| /// <returns>The range containing the data</returns> | |
| public ExcelRangeBase LoadFromText(string text) { | |
| return LoadFromText(text, new()); | |
| } | |
| /// <summary> | |
| /// Loads a CSV text into a range starting from the top left cell. | |
| /// </summary> | |
| /// <param name="text">The Text</param> | |
| /// <param name="format">Information how to load the text</param> | |
| /// <returns>The range containing the data</returns> | |
| public ExcelRangeBase LoadFromText(string text, ExcelTextFormat format) { | |
| if (string.IsNullOrEmpty(text)) { | |
| var r = _worksheet.Cells[_fromRow, _fromCol]; | |
| r.Value = ""; | |
| return r; | |
| } | |
| if (format == null) { | |
| format = new(); | |
| } | |
| string splitRegex = String.Format( | |
| "{0}(?=(?:[^{1}]*{1}[^{1}]*{1})*[^{1}]*$)", | |
| format.EOL, | |
| format.TextQualifier); | |
| string[] lines = Regex.Split(text, splitRegex); | |
| int row = _fromRow; | |
| int col = _fromCol; | |
| int maxCol = col; | |
| int lineNo = 1; | |
| foreach (string line in lines) { | |
| if (lineNo > format.SkipLinesBeginning && lineNo <= lines.Length - format.SkipLinesEnd) { | |
| col = _fromCol; | |
| string v = ""; | |
| bool isText = false, | |
| isQualifier = false; | |
| int qCount = 0; | |
| int lineQCount = 0; | |
| foreach (char c in line) { | |
| if (format.TextQualifier != 0 && c == format.TextQualifier) { | |
| if (!isText && v != "") { | |
| throw (new(string.Format("Invalid Text Qualifier in line : {0}", line))); | |
| } | |
| isQualifier = !isQualifier; | |
| qCount += 1; | |
| lineQCount++; | |
| isText = true; | |
| } else { | |
| if (qCount > 1 && !string.IsNullOrEmpty(v)) { | |
| v += new string(format.TextQualifier, qCount / 2); | |
| } else if (qCount > 2 && string.IsNullOrEmpty(v)) { | |
| v += new string(format.TextQualifier, (qCount - 1) / 2); | |
| } | |
| if (isQualifier) { | |
| v += c; | |
| } else { | |
| if (c == format.Delimiter) { | |
| _worksheet.SetValue(row, col, ConvertData(format, v, col - _fromCol, isText)); | |
| v = ""; | |
| isText = false; | |
| col++; | |
| } else { | |
| if (qCount % 2 == 1) { | |
| throw (new(string.Format("Text delimiter is not closed in line : {0}", line))); | |
| } | |
| v += c; | |
| } | |
| } | |
| qCount = 0; | |
| } | |
| } | |
| if (qCount > 1) { | |
| v += new string(format.TextQualifier, qCount / 2); | |
| } | |
| if (lineQCount % 2 == 1) { | |
| throw (new(string.Format("Text delimiter is not closed in line : {0}", line))); | |
| } | |
| _worksheet._values.SetValue(row, col, ConvertData(format, v, col - _fromCol, isText)); | |
| if (col > maxCol) { | |
| maxCol = col; | |
| } | |
| row++; | |
| } | |
| lineNo++; | |
| } | |
| return _worksheet.Cells[_fromRow, _fromCol, row - 1, maxCol]; | |
| } | |
| /// <summary> | |
| /// Loads a CSV text into a range starting from the top left cell. | |
| /// </summary> | |
| /// <param name="text">The Text</param> | |
| /// <param name="format">Information how to load the text</param> | |
| /// <param name="tableStyle">Create a table with this style</param> | |
| /// <param name="firstRowIsHeader">Use the first row as header</param> | |
| /// <returns></returns> | |
| public ExcelRangeBase LoadFromText( | |
| string text, | |
| ExcelTextFormat format, | |
| TableStyles tableStyle, | |
| bool firstRowIsHeader) { | |
| var r = LoadFromText(text, format); | |
| var tbl = _worksheet.Tables.Add(r, ""); | |
| tbl.ShowHeader = firstRowIsHeader; | |
| tbl.TableStyle = tableStyle; | |
| return r; | |
| } | |
| /// <summary> | |
| /// Loads a CSV file into a range starting from the top left cell. | |
| /// </summary> | |
| /// <param name="textFile">The Textfile</param> | |
| /// <returns></returns> | |
| public ExcelRangeBase LoadFromText(FileInfo textFile) { | |
| return LoadFromText(File.ReadAllText(textFile.FullName, Encoding.ASCII)); | |
| } | |
| /// <summary> | |
| /// Loads a CSV file into a range starting from the top left cell. | |
| /// </summary> | |
| /// <param name="textFile">The Textfile</param> | |
| /// <param name="format">Information how to load the text</param> | |
| /// <returns></returns> | |
| public ExcelRangeBase LoadFromText(FileInfo textFile, ExcelTextFormat format) { | |
| return LoadFromText(File.ReadAllText(textFile.FullName, format.Encoding), format); | |
| } | |
| /// <summary> | |
| /// Loads a CSV file into a range starting from the top left cell. | |
| /// </summary> | |
| /// <param name="textFile">The Textfile</param> | |
| /// <param name="format">Information how to load the text</param> | |
| /// <param name="tableStyle">Create a table with this style</param> | |
| /// <param name="firstRowIsHeader">Use the first row as header</param> | |
| /// <returns></returns> | |
| public ExcelRangeBase LoadFromText( | |
| FileInfo textFile, | |
| ExcelTextFormat format, | |
| TableStyles tableStyle, | |
| bool firstRowIsHeader) { | |
| return LoadFromText( | |
| File.ReadAllText(textFile.FullName, format.Encoding), | |
| format, | |
| tableStyle, | |
| firstRowIsHeader); | |
| } | |
| /// <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> | |
| /// Adds a new comment for the range. | |
| /// If this range contains more than one cell, the top left comment is returned by the method. | |
| /// </summary> | |
| /// <param name="text"></param> | |
| /// <param name="author"></param> | |
| /// <returns>A reference comment of the top left cell</returns> | |
| public ExcelComment AddComment(string text, string author) { | |
| if (string.IsNullOrEmpty(author)) { | |
| author = Thread.CurrentPrincipal.Identity.Name; | |
| } | |
| //Check if any comments exists in the range and throw an exception | |
| _changePropMethod(Exists_Comment, null); | |
| //Create the comments | |
| _changePropMethod(Set_Comment, new[] { text, author }); | |
| return _worksheet.Comments[new ExcelCellAddress(_fromRow, _fromCol)]; | |
| } | |
| ///// <summary> | |
| ///// Copies the range of cells to an other range | |
| ///// </summary> | |
| ///// <param name="Destination">The start cell where the range will be copied.</param> | |
| public void Copy(ExcelRangeBase destination) { | |
| bool sameWorkbook = destination._worksheet.Workbook == _worksheet.Workbook; | |
| ExcelStyles sourceStyles = _worksheet.Workbook.Styles, | |
| styles = destination._worksheet.Workbook.Styles; | |
| Dictionary<int, int> styleCashe = new Dictionary<int, int>(); | |
| //Clear all existing cells; | |
| int toRow = _toRow - _fromRow + 1, | |
| toCol = _toCol - _fromCol + 1; | |
| string s = ""; | |
| int i = 0; | |
| object o = null; | |
| byte flag = 0; | |
| Uri hl = null; | |
| ExcelComment comment = null; | |
| var cse = new CellsStoreEnumerator<object>( | |
| _worksheet._values, | |
| _fromRow, | |
| _fromCol, | |
| _toRow, | |
| _toCol); | |
| var copiedValue = new List<CopiedCell>(); | |
| while (cse.Next()) { | |
| var row = cse.Row; | |
| var col = cse.Column; //Issue 15070 | |
| var cell = new CopiedCell { | |
| Row = destination._fromRow + (row - _fromRow), | |
| Column = destination._fromCol + (col - _fromCol), | |
| Value = cse.Value, | |
| }; | |
| //Destination._worksheet._values.SetValue(row, col, cse.Value); | |
| if (_worksheet._types.Exists(row, col, ref s)) { | |
| //Destination._worksheet._types.SetValue(row, col,s); | |
| cell.Type = s; | |
| } | |
| if (_worksheet._formulas.Exists(row, col, ref o)) { | |
| if (o is int) { | |
| // Destination._worksheet._formulas.SetValue(row, col, _worksheet.GetFormula(cse.Row, cse.Column)); //Shared formulas, set the formula per cell to simplify | |
| cell.Formula = _worksheet.GetFormula(cse.Row, cse.Column); | |
| } else { | |
| //Destination._worksheet._formulas.SetValue(row, col, o); | |
| cell.Formula = o; | |
| } | |
| } | |
| if (_worksheet._styles.Exists(row, col, ref i)) { | |
| if (sameWorkbook) { | |
| //Destination._worksheet._styles.SetValue(row, col, i); | |
| cell.StyleID = i; | |
| } else { | |
| if (styleCashe.ContainsKey(i)) { | |
| i = styleCashe[i]; | |
| } else { | |
| var oldStyleId = i; | |
| i = styles.CloneStyle(sourceStyles, i); | |
| styleCashe.Add(oldStyleId, i); | |
| } | |
| //Destination._worksheet._styles.SetValue(row, col, i); | |
| cell.StyleID = i; | |
| } | |
| } | |
| if (_worksheet._hyperLinks.Exists(row, col, ref hl)) { | |
| //Destination._worksheet._hyperLinks.SetValue(row, col, hl); | |
| cell.HyperLink = hl; | |
| } | |
| if (_worksheet._commentsStore.Exists(row, col, ref comment)) { | |
| cell.Comment = comment; | |
| } | |
| if (_worksheet._flags.Exists(row, col, ref flag)) { | |
| cell.Flag = flag; | |
| } | |
| copiedValue.Add(cell); | |
| } | |
| //Copy styles with no cell value | |
| var cses = new CellsStoreEnumerator<int>( | |
| _worksheet._styles, | |
| _fromRow, | |
| _fromCol, | |
| _toRow, | |
| _toCol); | |
| while (cses.Next()) { | |
| if (!_worksheet._values.Exists(cses.Row, cses.Column)) { | |
| var row = destination._fromRow + (cses.Row - _fromRow); | |
| var col = destination._fromCol + (cses.Column - _fromCol); | |
| var cell = new CopiedCell { | |
| Row = row, | |
| Column = col, | |
| Value = null, | |
| }; | |
| i = cses.Value; | |
| if (sameWorkbook) { | |
| cell.StyleID = i; | |
| } else { | |
| if (styleCashe.ContainsKey(i)) { | |
| i = styleCashe[i]; | |
| } else { | |
| var oldStyleId = i; | |
| i = styles.CloneStyle(sourceStyles, i); | |
| styleCashe.Add(oldStyleId, i); | |
| } | |
| //Destination._worksheet._styles.SetValue(row, col, i); | |
| cell.StyleID = i; | |
| } | |
| copiedValue.Add(cell); | |
| } | |
| } | |
| var copiedMergedCells = new Dictionary<int, ExcelAddress>(); | |
| //Merged cells | |
| var csem = new CellsStoreEnumerator<int>( | |
| _worksheet.MergedCells._cells, | |
| _fromRow, | |
| _fromCol, | |
| _toRow, | |
| _toCol); | |
| while (csem.Next()) { | |
| if (!copiedMergedCells.ContainsKey(csem.Value)) { | |
| var adr = new ExcelAddress(_worksheet.Name, _worksheet.MergedCells.List[csem.Value]); | |
| if (Collide(adr) == eAddressCollition.Inside) { | |
| copiedMergedCells.Add( | |
| csem.Value, | |
| new( | |
| destination._fromRow + (adr.Start.Row - _fromRow), | |
| destination._fromCol + (adr.Start.Column - _fromCol), | |
| destination._fromRow + (adr.End.Row - _fromRow), | |
| destination._fromCol + (adr.End.Column - _fromCol))); | |
| } else { | |
| //Partial merge of the address ignore. | |
| copiedMergedCells.Add(csem.Value, null); | |
| } | |
| } | |
| } | |
| destination._worksheet.MergedCells.Clear( | |
| new( | |
| destination._fromRow, | |
| destination._fromCol, | |
| destination._fromRow + toRow - 1, | |
| destination._fromCol + toCol - 1)); | |
| destination._worksheet._values.Clear(destination._fromRow, destination._fromCol, toRow, toCol); | |
| destination._worksheet._formulas.Clear( | |
| destination._fromRow, | |
| destination._fromCol, | |
| toRow, | |
| toCol); | |
| destination._worksheet._styles.Clear(destination._fromRow, destination._fromCol, toRow, toCol); | |
| destination._worksheet._types.Clear(destination._fromRow, destination._fromCol, toRow, toCol); | |
| destination._worksheet._hyperLinks.Clear( | |
| destination._fromRow, | |
| destination._fromCol, | |
| toRow, | |
| toCol); | |
| destination._worksheet._flags.Clear(destination._fromRow, destination._fromCol, toRow, toCol); | |
| destination._worksheet._commentsStore.Clear( | |
| destination._fromRow, | |
| destination._fromCol, | |
| toRow, | |
| toCol); | |
| foreach (var cell in copiedValue) { | |
| destination._worksheet._values.SetValue(cell.Row, cell.Column, cell.Value); | |
| if (cell.Type != null) { | |
| destination._worksheet._types.SetValue(cell.Row, cell.Column, cell.Type); | |
| } | |
| if (cell.StyleID != null) { | |
| destination._worksheet._styles.SetValue(cell.Row, cell.Column, cell.StyleID.Value); | |
| } | |
| if (cell.Formula != null) { | |
| cell.Formula = UpdateFormulaReferences( | |
| cell.Formula.ToString(), | |
| destination._fromRow - _fromRow, | |
| destination._fromCol - _fromCol, | |
| 0, | |
| 0, | |
| true); | |
| destination._worksheet._formulas.SetValue(cell.Row, cell.Column, cell.Formula); | |
| } | |
| if (cell.HyperLink != null) { | |
| destination._worksheet._hyperLinks.SetValue(cell.Row, cell.Column, cell.HyperLink); | |
| } | |
| if (cell.Comment != null) { | |
| //Destination._worksheet._commentsStore.SetValue(cell.Row, cell.Column, cell.Comment); | |
| } | |
| if (cell.Flag != 0) { | |
| destination._worksheet._flags.SetValue(cell.Row, cell.Column, cell.Flag); | |
| } | |
| } | |
| //Add merged cells | |
| foreach (var m in copiedMergedCells.Values) { | |
| if (m != null) { | |
| destination._worksheet.MergedCells.Add(m, true); | |
| } | |
| } | |
| //Clone the cell | |
| //var copiedCell = (_worksheet._cells[GetCellID(_worksheet.SheetID, cell._fromRow, cell.column)] as ExcelCell); | |
| //var newCell = copiedCell.Clone(Destination._worksheet, | |
| // Destination._fromRow + (copiedCell.Row - _fromRow), | |
| // Destination.column + (copiedCell.Column - column)); | |
| // newCell.MergeId = _worksheet.GetMergeCellId(copiedCell.Row, copiedCell.Column); | |
| // if (!string.IsNullOrEmpty(newCell.Formula)) | |
| // { | |
| // newCell.Formula = ExcelCell.UpdateFormulaReferences(newCell.Formula, newCell.Row - copiedCell.Row, (newCell.Column - copiedCell.Column), 1, 1); | |
| // } | |
| // //If its not the same workbook we must copy the styles to the new workbook. | |
| // if (!sameWorkbook) | |
| // { | |
| // if (styleCashe.ContainsKey(cell.StyleID)) | |
| // { | |
| // newCell.StyleID = styleCashe[cell.StyleID]; | |
| // } | |
| // else | |
| // { | |
| // newCell.StyleID = styles.CloneStyle(sourceStyles, cell.StyleID); | |
| // styleCashe.Add(cell.StyleID, newCell.StyleID); | |
| // } | |
| // } | |
| // newCells.Add(newCell); | |
| // if (newCell.Merge) mergedCells.Add(newCell.CellID, newCell); | |
| // } | |
| // //Now clear the destination. | |
| // Destination.Offset(0, 0, (_toRow - _fromRow) + 1, (_toCol - column) + 1).Clear(); | |
| // //And last add the new cells to the worksheet | |
| // foreach (var cell in newCells) | |
| // { | |
| // Destination.Worksheet._cells.Add(cell); | |
| // } | |
| // //Add merged cells | |
| // if (mergedCells.Count > 0) | |
| // { | |
| // List<ExcelAddressBase> mergedAddresses = new List<ExcelAddressBase>(); | |
| // foreach (var cell in mergedCells.Values) | |
| // { | |
| // if (!IsAdded(cell, mergedAddresses)) | |
| // { | |
| // int startRow = cell.Row, startCol = cell.Column, endRow = cell.Row, endCol = cell.Column + 1; | |
| // while (mergedCells.ContainsKey(ExcelCell.GetCellID(Destination.Worksheet.SheetID, endRow, endCol))) | |
| // { | |
| // ExcelCell next = mergedCells[ExcelCell.GetCellID(Destination.Worksheet.SheetID, endRow, endCol)]; | |
| // if (cell.MergeId != next.MergeId) | |
| // { | |
| // break; | |
| // } | |
| // endCol++; | |
| // } | |
| // while (IsMerged(mergedCells, Destination.Worksheet, endRow, startCol, endCol - 1, cell)) | |
| // { | |
| // endRow++; | |
| // } | |
| // mergedAddresses.Add(new ExcelAddressBase(startRow, startCol, endRow - 1, endCol - 1)); | |
| // } | |
| // } | |
| // Destination.Worksheet.MergedCells.List.AddRange((from r in mergedAddresses select r.Address)); | |
| // } | |
| //} | |
| //private bool IsAdded(ExcelCell cell, List<ExcelAddressBase> mergedAddresses) | |
| //{ | |
| // foreach (var address in mergedAddresses) | |
| // { | |
| // if (address.Collide(new ExcelAddressBase(cell.CellAddress)) == eAddressCollition.Inside) | |
| // { | |
| // return true; | |
| // } | |
| // } | |
| // return false; | |
| //} | |
| //private bool IsMerged(Dictionary<ulong, ExcelCell> mergedCells, ExcelWorksheet worksheet, int row, int startCol, int endCol, ExcelCell cell) | |
| //{ | |
| // for (int col = startCol; col <= endCol; col++) | |
| // { | |
| // if (!mergedCells.ContainsKey(ExcelCell.GetCellID(worksheet.SheetID, row, col))) | |
| // { | |
| // return false; | |
| // } | |
| // else | |
| // { | |
| // ExcelCell next = mergedCells[ExcelCell.GetCellID(worksheet.SheetID, row, col)]; | |
| // if (cell.MergeId != next.MergeId) | |
| // { | |
| // return false; | |
| // } | |
| // } | |
| // } | |
| // return true; | |
| } | |
| /// <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--; | |
| //} | |
| } |