EPPlus
ExcelAddress.cs
/*******************************************************************************
* 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 https://github.com/JanKallman/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 Added 18-MAR-2010
* Jan Källman License changed GPL-->LGPL 2011-12-16
*******************************************************************************/
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
namespace OfficeOpenXml
{
public clast ExcelTableAddress
{
public string Name { get; set; }
public string ColumnSpan { get; set; }
public bool IsAll { get; set; }
public bool IsHeader { get; set; }
public bool IsData { get; set; }
public bool IsTotals { get; set; }
public bool IsThisRow { get; set; }
}
///
/// A range address
///
/// Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5"
public clast ExcelAddressBase : ExcelCellBase
{
internal protected int _fromRow=-1, _toRow, _fromCol, _toCol;
protected internal bool _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed;
internal protected string _wb;
internal protected string _ws;
internal protected string _address;
internal enum eAddressCollition
{
No,
Partly,
Inside,
Equal
}
internal enum eShiftType
{
Right,
Down,
EntireRow,
EntireColumn
}
#region "Constructors"
internal ExcelAddressBase()
{
}
///
/// Creates an Address object
///
/// start row
/// start column
/// End row
/// End column
public ExcelAddressBase(int fromRow, int fromCol, int toRow, int toColumn)
{
_fromRow = fromRow;
_toRow = toRow;
_fromCol = fromCol;
_toCol = toColumn;
Validate();
_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
}
///
/// Creates an Address object
///
/// Worksheet Name
/// start row
/// start column
/// End row
/// End column
public ExcelAddressBase(string worksheetName, int fromRow, int fromCol, int toRow, int toColumn)
{
_ws = worksheetName;
_fromRow = fromRow;
_toRow = toRow;
_fromCol = fromCol;
_toCol = toColumn;
Validate();
_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
}
///
/// Creates an Address object
///
/// start row
/// start column
/// End row
/// End column
/// start row fixed
/// start column fixed
/// End row fixed
/// End column fixed
public ExcelAddressBase(int fromRow, int fromCol, int toRow, int toColumn, bool fromRowFixed, bool fromColFixed, bool toRowFixed, bool toColFixed)
{
_fromRow = fromRow;
_toRow = toRow;
_fromCol = fromCol;
_toCol = toColumn;
_fromRowFixed = fromRowFixed;
_fromColFixed = fromColFixed;
_toRowFixed = toRowFixed;
_toColFixed = toColFixed;
Validate();
_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol, _fromRowFixed, fromColFixed, _toRowFixed, _toColFixed );
}
///
/// Creates an Address object
///
/// Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5"
/// The Excel Address
public ExcelAddressBase(string address)
{
SetAddress(address);
}
///
/// Creates an Address object
///
/// Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5"
/// The Excel Address
/// Reference to the package to find information about tables and names
/// The address
public ExcelAddressBase(string address, ExcelPackage pck, ExcelAddressBase referenceAddress)
{
SetAddress(address);
SetRCFromTable(pck, referenceAddress);
}
internal void SetRCFromTable(ExcelPackage pck, ExcelAddressBase referenceAddress)
{
if (string.IsNullOrEmpty(_wb) && Table != null)
{
foreach (var ws in pck.Workbook.Worksheets)
{
foreach (var t in ws.Tables)
{
if (t.Name.Equals(Table.Name, StringComparison.OrdinalIgnoreCase))
{
_ws = ws.Name;
if (Table.IsAll)
{
_fromRow = t.Address._fromRow;
_toRow = t.Address._toRow;
}
else
{
if (Table.IsThisRow)
{
if (referenceAddress == null)
{
_fromRow = -1;
_toRow = -1;
}
else
{
_fromRow = referenceAddress._fromRow;
_toRow = _fromRow;
}
}
else if (Table.IsHeader && Table.IsData)
{
_fromRow = t.Address._fromRow;
_toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
}
else if (Table.IsData && Table.IsTotals)
{
_fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
_toRow = t.Address._toRow;
}
else if (Table.IsHeader)
{
_fromRow = t.ShowHeader ? t.Address._fromRow : -1;
_toRow = t.ShowHeader ? t.Address._fromRow : -1;
}
else if (Table.IsTotals)
{
_fromRow = t.ShowTotal ? t.Address._toRow : -1;
_toRow = t.ShowTotal ? t.Address._toRow : -1;
}
else
{
_fromRow = t.ShowHeader ? t.Address._fromRow + 1 : t.Address._fromRow;
_toRow = t.ShowTotal ? t.Address._toRow - 1 : t.Address._toRow;
}
}
if (string.IsNullOrEmpty(Table.ColumnSpan))
{
_fromCol = t.Address._fromCol;
_toCol = t.Address._toCol;
return;
}
else
{
var col = t.Address._fromCol;
var cols = Table.ColumnSpan.Split(':');
foreach (var c in t.Columns)
{
if (_fromCol 1 && _fromCol > 0 && cols[1].Equals(c.Name, StringComparison.OrdinalIgnoreCase)) //Issue15063 Add invariant igore case
{
_toCol = col;
return;
}
col++;
}
}
}
}
}
}
}
///
/// Address is an defined name
///
/// the name
/// Should always be true
internal ExcelAddressBase(string address, bool isName)
{
if (isName)
{
_address = address;
_fromRow = -1;
_fromCol = -1;
_toRow = -1;
_toCol = -1;
_start = null;
_end = null;
}
else
{
SetAddress(address);
}
}
protected internal void SetAddress(string address)
{
address = address.Trim();
if (Utils.ConvertUtil._invariantCompareInfo.IsPrefix(address, "'") || Utils.ConvertUtil._invariantCompareInfo.IsPrefix(address, "["))
{
//int pos = address.IndexOf("'", 1);
//while (pos < address.Length && address[pos + 1] == '\'')
//{
// pos = address.IndexOf("'", pos + 2);
//}
//var wbws = address.Substring(1, pos - 1).Replace("''", "'");
SetWbWs(address);
//_address = address.Substring(pos + 2);
}
else
{
_address = address;
}
_addresses = null;
if (_address.IndexOfAny(new char[] {',','!', '['}) > -1)
{
//Advanced address. Including Sheet or multi or table.
ExtractAddress(_address);
}
else
{
//Simple address
GetRowColFromAddress(_address, out _fromRow, out _fromCol, out _toRow, out _toCol, out _fromRowFixed, out _fromColFixed, out _toRowFixed, out _toColFixed);
_start = null;
_end = null;
}
_address = address;
Validate();
}
internal protected virtual void ChangeAddress()
{
}
private void SetWbWs(string address)
{
int pos;
if (address[0] == '[')
{
pos = address.IndexOf("]");
_wb = address.Substring(1, pos - 1);
_ws = address.Substring(pos + 1);
}
else
{
_wb = "";
_ws = address;
}
if(_ws.StartsWith("'"))
{
pos = _ws.IndexOf("'",1);
while(pos>0 && pos+1ix)
{
return address.Substring(ix, ixEnd-ix);
}
else
{
return defaultWorkSheet;
}
}
}
else
{
return defaultWorkSheet;
}
}
internal static string GetAddressPart(string address)
{
var ix=0;
GetWorksheetPart(address, "", ref ix);
if(ix -1)
{
prevStrIx = strIx;
strIx = address.IndexOf("''");
}
endIx = address.IndexOf("'");
return address.Substring(ix, endIx - ix).Replace("''","'");
}
internal bool IsValidRowCol()
{
return !(_fromRow > _toRow ||
_fromCol > _toCol ||
_fromRow < 1 ||
_fromCol < 1 ||
_toRow > ExcelPackage.MaxRows ||
_toCol > ExcelPackage.MaxColumns);
}
}
///
/// Range address with the address property readonly
///
public clast ExcelAddress : ExcelAddressBase
{
internal ExcelAddress()
: base()
{
}
public ExcelAddress(int fromRow, int fromCol, int toRow, int toColumn)
: base(fromRow, fromCol, toRow, toColumn)
{
_ws = "";
}
public ExcelAddress(string address)
: base(address)
{
}
internal ExcelAddress(string ws, string address)
: base(address)
{
if (string.IsNullOrEmpty(_ws)) _ws = ws;
}
internal ExcelAddress(string ws, string address, bool isName)
: base(address, isName)
{
if (string.IsNullOrEmpty(_ws)) _ws = ws;
}
public ExcelAddress(string Address, ExcelPackage package, ExcelAddressBase referenceAddress) :
base(Address, package, referenceAddress)
{
}
///
/// The address for the range
///
/// Examples of addresses are "A1" "B1:C2" "A:A" "1:1" "A1:E2,G3:G5"
public new string Address
{
get
{
if (string.IsNullOrEmpty(_address) && _fromRow>0)
{
_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol);
}
return _address;
}
set
{
SetAddress(value);
ChangeAddress();
}
}
}
public clast ExcelFormulaAddress : ExcelAddressBase
{
internal ExcelFormulaAddress()
: base()
{
}
public ExcelFormulaAddress(int fromRow, int fromCol, int toRow, int toColumn)
: base(fromRow, fromCol, toRow, toColumn)
{
_ws = "";
}
public ExcelFormulaAddress(string address)
: base(address)
{
SetFixed();
}
internal ExcelFormulaAddress(string ws, string address)
: base(address)
{
if (string.IsNullOrEmpty(_ws)) _ws = ws;
SetFixed();
}
internal ExcelFormulaAddress(string ws, string address, bool isName)
: base(address, isName)
{
if (string.IsNullOrEmpty(_ws)) _ws = ws;
if(!isName)
SetFixed();
}
private void SetFixed()
{
if (Address.IndexOf("[") >= 0) return;
var address=FirstAddress;
if(_fromRow==_toRow && _fromCol==_toCol)
{
GetFixed(address, out _fromRowFixed, out _fromColFixed);
}
else
{
var cells = address.Split(':');
GetFixed(cells[0], out _fromRowFixed, out _fromColFixed);
GetFixed(cells[1], out _toRowFixed, out _toColFixed);
}
}
private void GetFixed(string address, out bool rowFixed, out bool colFixed)
{
rowFixed=colFixed=false;
var ix=address.IndexOf('$');
while(ix>-1)
{
ix++;
if(ix < address.Length)
{
if(address[ix]>='0' && address[ix]0)
{
_address = GetAddress(_fromRow, _fromCol, _toRow, _toCol, _fromRowFixed, _toRowFixed, _fromColFixed, _toColFixed);
}
return _address;
}
set
{
SetAddress(value);
ChangeAddress();
SetFixed();
}
}
internal new List _addresses;
public new List Addresses
{
get
{
if (_addresses == null)
{
_addresses = new List();
}
return _addresses;
}
}
internal string GetOffset(int row, int column)
{
int fromRow = _fromRow, fromCol = _fromCol, toRow = _toRow, tocol = _toCol;
var isMulti = (fromRow != toRow || fromCol != tocol);
if (!_fromRowFixed)
{
fromRow += row;
}
if (!_fromColFixed)
{
fromCol += column;
}
if (isMulti)
{
if (!_toRowFixed)
{
toRow += row;
}
if (!_toColFixed)
{
tocol += column;
}
}
else
{
toRow = fromRow;
tocol = fromCol;
}
string a = GetAddress(fromRow, fromCol, toRow, tocol, _fromRowFixed, _fromColFixed, _toRowFixed, _toColFixed);
if (Addresses != null)
{
foreach (var sa in Addresses)
{
a+="," + sa.GetOffset(row, column);
}
}
return a;
}
}
}
