public void TranslateC1FullColumnWithSheet()
{
const string formula = "SUM(Sheet1!A:A)";
var formulaR1C1 = ExcelCellBase.TranslateToR1C1(formula, 1, 2);
Assert.AreEqual("SUM(Sheet1!C[-1])", formulaR1C1); // fails: formulaR1C1 == "Sum(C[-1])"
}
private void SetSerieFunction(string value)
{
CreateNode(_seriesPath, true);
CreateNode(_numCachePath, true);
if (ExcelCellBase.IsValidAddress(value))
{
SetXmlNodeString(_seriesPath, ExcelCellBase.GetFullAddress(_chart.WorkSheet.Name, value));
}
else
{
SetXmlNodeString(_seriesPath, value);
}
if (_chart.PivotTableSource != null)
{
XmlNode cache = TopNode.SelectSingleNode(string.Format("{0}/c:numRef/c:numCache", _seriesTopPath), NameSpaceManager);
if (cache != null)
{
cache.ParentNode.RemoveChild(cache);
}
SetXmlNodeString(string.Format("{0}/c:numRef/c:numCache", _seriesTopPath), "General");
}
XmlNode lit = TopNode.SelectSingleNode(_seriesNumLitPath, NameSpaceManager);
if (lit != null)
{
lit.ParentNode.RemoveChild(lit);
}
}
public void TranslateRCFullColumnWithSheet()
{
const string formulaR1C1 = "SUM(Sheet1!C[-1])";
var formula = ExcelCellBase.TranslateFromR1C1(formulaR1C1, 1, 2);
Assert.AreEqual("SUM(Sheet1!A:A)", formula); // also fails: formula == "Sum(A:A)"
}
public void TranslateFromR1C1Test1()
{
const string formulaR1C1 = "SUM(Sheet1!C[-1])";
var formula = ExcelCellBase.TranslateFromR1C1(formulaR1C1, 1, 2);
Assert.AreEqual("SUM(Sheet1!A:A)", formula);
}
public void TranslateFromR1C1Test2()
{
const string formulaR1C1 = "SUM(Sheet1!R[-1])";
var formula = ExcelCellBase.TranslateFromR1C1(formulaR1C1, 2, 1);
Assert.AreEqual("SUM(Sheet1!1:1)", formula);
}
public void IsValidCellAdress()
{
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("A1"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("A1048576"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("XFD1"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("XFD1048576"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("Table1!A1"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("Table1!A1048576"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("Table1!XFD1"));
Assert.IsTrue(ExcelCellBase.IsValidCellAddress("Table1!XFD1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("A"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("A"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("XFD"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("XFD"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("1"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("1"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("A1:A1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("A1:XFD1"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("A1048576:XFD1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("XFD1:XFD1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("Table1!A1:A1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("Table1!A1:XFD1"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("Table1!A1048576:XFD1048576"));
Assert.IsFalse(ExcelCellBase.IsValidCellAddress("Table1!XFD1:XFD1048576"));
}
public void ConvertSubFormulaToR1C1()
{
const string formula = "INDEX(Сводка!$A:$K,MATCH($A$1,Сводка!$A:$A,0)+Таблица2[[#This Row],[№п/п]]-1,2)";
var formulaR1C1 = ExcelCellBase.TranslateToR1C1(formula, 1, 2);
Assert.AreEqual("INDEX(СВОДКА!C1:C11,MATCH(R1C1,СВОДКА!C1,0)+ТАБЛИЦА2[[#THIS ROW],[№П/П]]-1,2)", formulaR1C1);
}
private static void GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)
{
var ws = name.Worksheet;
var id = ExcelCellBase.GetCellID(ws == null?0:ws.SheetID, name.Index, 0);
if (!depChain.index.ContainsKey(id))
{
var f = new FormulaCell()
{
SheetID = ws == null ? 0 : ws.SheetID, Row = name.Index, Column = 0, Formula = name.NameFormula
};
if (!string.IsNullOrEmpty(f.Formula))
{
f.Tokens = lexer.Tokenize(f.Formula, (ws == null ? null : ws.Name)).ToList();
if (ws == null)
{
name._workbook._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
else
{
ws._formulaTokens.SetValue(name.Index, 0, f.Tokens);
}
depChain.Add(f);
FollowChain(depChain, lexer, name._workbook, ws, f, options);
}
}
}
private byte[] GenerarExcel(List <DataTable> lstDt)
{
byte[] bytes;
using (var pack = new ExcelPackage())
{
lstDt.ForEach(dt =>
{
var ws = pack.Workbook.Worksheets.Add(dt.TableName);
ws.Cells["A1"].LoadFromDataTable(dt, true);
//Format the header for column
using (var rng = ws.Cells[1, 1, 1, dt.Columns.Count])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
if (dt.TableName != Productos)
{
var totalColumnas = dt.Columns.Count;
var totalRenglones = dt.Rows.Count;
var final = ExcelCellBase.GetAddress(totalRenglones + 1, totalColumnas);
var rangoTotal = "A1:" + final;
ws.Cells[rangoTotal].AutoFitColumns();
}
});
var ms = new MemoryStream();
pack.SaveAs(ms);
bytes = ms.ToArray();
ms.Close();
}
return(bytes);
}
/// <summary>
/// Deletes one or more rows at the specified position in the table.
/// </summary>
/// <param name="position">The position in the table where the row will be deleted. 0 will delete the first row. </param>
/// <param name="rows">Number of rows to delete.</param>
/// <returns></returns>
public ExcelRangeBase DeleteRow(int position, int rows = 1)
{
if (position < 0)
{
throw new ArgumentException("position", "position can't be negative");
}
if (rows < 0)
{
throw new ArgumentException("position", "rows can't be negative");
}
if (_address._fromRow + position + rows > _address._toRow)
{
throw new InvalidOperationException("Delete will exceed the number of rows in the table");
}
var subtract = ShowTotal ? 2 : 1;
if (position == 0 && rows + subtract >= _address.Rows)
{
throw new InvalidOperationException("Can't delete all table rows. A table must have at least one row.");
}
position++; //Header row should not be deleted.
var address = ExcelCellBase.GetAddress(_address._fromRow + position, _address._fromCol, _address._fromRow + position + rows - 1, _address._toCol);
var range = new ExcelRangeBase(WorkSheet, address);
range.Delete(eShiftTypeDelete.Up);
return(range);
}
/// <summary>
/// Inserts one or more rows before the specified position in the table.
/// </summary>
/// <param name="position">The position in the table where the row will be inserted. Default is in the end of the table. 0 will insert the row at the top. Any value larger than the number of rows in the table will insert a row at the bottom of the table.</param>
/// <param name="rows">Number of rows to insert.</param>
/// <returns>The inserted range</returns>
public ExcelRangeBase InsertRow(int position, int rows = 1)
{
if (position < 0)
{
throw new ArgumentException("position", "position can't be negative");
}
if (rows < 0)
{
throw new ArgumentException("position", "rows can't be negative");
}
var subtact = ShowTotal ? 2 : 1;
if (position >= ExcelPackage.MaxRows || position > _address._fromRow + position + rows - subtact)
{
position = _address.Rows - subtact;
}
if (_address._fromRow + position + rows > ExcelPackage.MaxRows)
{
throw new InvalidOperationException("Insert will exceed the maximum number of rows in the worksheet");
}
position++;
var address = ExcelCellBase.GetAddress(_address._fromRow + position, _address._fromCol, _address._fromRow + position + rows - 1, _address._toCol);
var range = new ExcelRangeBase(WorkSheet, address);
WorksheetRangeInsertHelper.Insert(range, eShiftTypeInsert.Down, false);
if (range._toRow > _address._toRow)
{
Address = _address.AddRow(_address._toRow, rows);
}
return(range);
}
public static bool IsValidName(string name)
{
if (string.IsNullOrEmpty(name))
{
return(false);
}
var fc = name[0];
if (!(char.IsLetter(fc) || fc == '_' || (fc == '\\' && name.Length > 2)))
{
return(false);
}
if (name.IndexOfAny(NameInvalidChars, 1) > 0)
{
return(false);
}
if (ExcelCellBase.IsValidAddress(name))
{
return(false);
}
//TODO:Add check for functionnames.
return(true);
}
private void InsertColumns(int colFrom, int cols, ExcelSparkline sl)
{
if (cols > 0)
{
if (sl.Cell.Column >= colFrom)
{
sl.Cell = new ExcelCellAddress(sl.Cell.Row, sl.Cell.Column + cols);
}
var address = sl.GetRangeAddress(_ws.Names);
if (!(address is ExcelNamedRange))
{
if (colFrom <= address.Start.Column)
{
var newAddress = ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column + cols, address.End.Row, address.End.Column + cols);
sl.RangeAddress = new ExcelAddress(BuildNewAddress(address, newAddress));
}
else if (colFrom <= address.End.Column && address.End.Column + cols < ExcelPackage.MaxColumns)
{
var newAddress = ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column + cols);
sl.RangeAddress = new ExcelAddress(BuildNewAddress(address, newAddress));
}
}
}
}
private void InsertRows(int rowFrom, int rows, ExcelSparkline sl)
{
if (rows > 0)
{
if (sl.Cell.Row >= rowFrom)
{
sl.Cell = new ExcelCellAddress(sl.Cell.Row + rows, sl.Cell.Column);
}
var address = sl.GetRangeAddress(_ws.Names);
if (!(address is ExcelNamedRange))
{
if (rowFrom <= address.Start.Row)
{
var newAddress = ExcelCellBase.GetAddress(address.Start.Row + rows, address.Start.Column, address.End.Row + rows, address.End.Column);
var ttt = new ExcelAddress(BuildNewAddress(address, newAddress));
sl.RangeAddress = ttt;
}
else if (rowFrom <= address.End.Row && address.End.Row + rows <= ExcelPackage.MaxRows)
{
var newAddress = ExcelCellBase.GetAddress(address.Start.Row, address.Start.Column, address.End.Row + rows, address.End.Column);
sl.RangeAddress = new ExcelAddress(BuildNewAddress(address, newAddress));
}
}
}
}
private static void InsertColumnFormulas(ExcelWorksheet ws, int columnFrom, int columns)
{
foreach (var f in ws._sharedFormulas.Values)
{
if (f.StartCol >= columnFrom)
{
f.StartCol += columns;
}
var a = new ExcelAddressBase(f.Address);
if (a._fromCol >= columnFrom)
{
a._fromCol += columns;
a._toCol += columns;
}
else if (a._toCol >= columnFrom)
{
a._toCol += columns;
}
f.Address = ExcelCellBase.GetAddress(a._fromRow, a._fromCol, a._toRow, a._toCol);
f.Formula = ExcelCellBase.UpdateFormulaReferences(f.Formula, 0, columns, 0, columnFrom, ws.Name, ws.Name);
}
var cse = new CellStoreEnumerator <object>(ws._formulas);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), 0, columns, 0, columnFrom, ws.Name, ws.Name);
}
}
}
/// <summary>
/// 构建Excel模板
/// </summary>
protected virtual void StructureExcel(ExcelPackage excelPackage)
{
var worksheet =
excelPackage.Workbook.Worksheets.Add(typeof(T).GetDisplayName() ??
ExcelImporterAttribute.SheetName ?? "导入数据");
if (!ParseImporterHeader())
{
return;
}
//设置列头
for (var i = 0; i < ImporterHeaderInfos.Count; i++)
{
//忽略
if (ImporterHeaderInfos[i].ExporterHeader.IsIgnore)
{
continue;
}
worksheet.Cells[ExcelImporterAttribute.HeaderRowIndex, i + 1].Value =
ImporterHeaderInfos[i].ExporterHeader.Name;
if (!string.IsNullOrWhiteSpace(ImporterHeaderInfos[i].ExporterHeader.Description))
{
worksheet.Cells[ExcelImporterAttribute.HeaderRowIndex, i + 1].AddComment(
ImporterHeaderInfos[i].ExporterHeader.Description,
ImporterHeaderInfos[i].ExporterHeader.Author);
}
//如果必填，则列头标红
if (ImporterHeaderInfos[i].IsRequired)
{
worksheet.Cells[ExcelImporterAttribute.HeaderRowIndex, i + 1].Style.Font.Color.SetColor(Color.Red);
}
if (ImporterHeaderInfos[i].MappingValues.Count > 0)
{
//针对枚举类型和Bool类型添加数据约束
var range = ExcelCellBase.GetAddress(ExcelImporterAttribute.HeaderRowIndex + 1, i + 1,
ExcelPackage.MaxRows, i + 1);
var dataValidations = worksheet.DataValidations.AddListValidation(range);
foreach (var mappingValue in ImporterHeaderInfos[i].MappingValues)
{
dataValidations.Formula.Values.Add(mappingValue.Key);
}
}
}
worksheet.Cells.AutoFitColumns();
worksheet.Cells.Style.WrapText = true;
worksheet.Cells[worksheet.Dimension.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[worksheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[worksheet.Dimension.Address].Style.Fill.BackgroundColor.SetColor(Color.DarkSeaGreen);
}
/// <summary>
/// 构建Excel模板
/// </summary>
protected virtual void StructureExcel(ExcelPackage excelPackage)
{
var worksheet =
excelPackage.Workbook.Worksheets.Add(typeof(T).GetDisplayName() ??
ExcelImporterAttribute.SheetName ?? "导入数据");
if (!ParseImporterHeader(out var enumColumns, out var boolColumns))
{
return;
}
//设置列头
for (var i = 0; i < ImporterHeaderInfos.Count; i++)
{
worksheet.Cells[1, i + 1].Value = ImporterHeaderInfos[i].ExporterHeader.Name;
if (!string.IsNullOrWhiteSpace(ImporterHeaderInfos[i].ExporterHeader.Description))
{
worksheet.Cells[1, i + 1].AddComment(ImporterHeaderInfos[i].ExporterHeader.Description,
ImporterHeaderInfos[i].ExporterHeader.Author);
}
//如果必填，则列头标红
if (ImporterHeaderInfos[i].IsRequired)
{
worksheet.Cells[1, i + 1].Style.Font.Color.SetColor(Color.Red);
}
}
worksheet.Cells.AutoFitColumns();
worksheet.Cells.Style.WrapText = true;
worksheet.Cells[worksheet.Dimension.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells[worksheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells[worksheet.Dimension.Address].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[worksheet.Dimension.Address].Style.Fill.BackgroundColor.SetColor(Color.DarkSeaGreen);
//枚举处理
foreach (var enumColumn in enumColumns)
{
var range = ExcelCellBase.GetAddress(1, enumColumn.Key, ExcelPackage.MaxRows, enumColumn.Key);
var dataValidations = worksheet.DataValidations.AddListValidation(range);
foreach (var displayName in enumColumn.Value)
{
dataValidations.Formula.Values.Add(displayName.Key);
}
}
//Bool类型处理
foreach (var boolColumn in boolColumns)
{
var range = ExcelCellBase.GetAddress(1, boolColumn, ExcelPackage.MaxRows, boolColumn);
var dataValidations = worksheet.DataValidations.AddListValidation(range);
dataValidations.Formula.Values.Add("是");
dataValidations.Formula.Values.Add("否");
}
}
private static string BuildNewAddress(ExcelAddressBase namedRange, string newAddress)
{
if (namedRange.FullAddress.Contains("!"))
{
var worksheet = namedRange.FullAddress.Split('!')[0];
worksheet = worksheet.Trim('\'');
newAddress = ExcelCellBase.GetFullAddress(worksheet, newAddress);
}
return(newAddress);
}
protected static void SetValues(ExcelWorksheet ws, int rowcols)
{
for (int r = 1; r <= rowcols; r++)
{
for (int c = 1; c <= rowcols; c++)
{
ws.Cells[r, c].Value = ExcelCellBase.GetAddress(r, c);
}
}
}
public void Format <TProperty>(Expression <Func <T, TProperty> > expression, string format)
{
var body = (MemberExpression)expression.Body;
string name = body.Member.Name;
var p = _props.Select((x, i) => new { prop = x, index = i }).FirstOrDefault(x => x.prop.Name == name);
var c = p.index + 1;
var column = ExcelCellBase.GetAddress(2, c, _rows, c);
_ws.Cells[column].Style.Numberformat.Format = format;
}
public void ConvertingIndexesAndAddresses()
{
Assert.That(ExcelCellBase.GetAddress(1, 1), Is.EqualTo("A1"));
Assert.That(ExcelCellBase.IsValidCellAddress("A5"), Is.True);
Assert.That(ExcelCellBase.GetFullAddress("MySheet", "A1:A3"), Is.EqualTo("'MySheet'!A1:A3"));
Assert.That(ExcelCellBase.TranslateToR1C1("AB23", 0, 0), Is.EqualTo("R[23]C[28]"));
Assert.That(ExcelCellBase.TranslateFromR1C1("R23C28", 0, 0), Is.EqualTo("$AB$23"));
}
/// <summary>
/// Returns a new <see cref="ExcelCellBase"/> reference containing the initial range expanded by minichart size.
/// </summary>
/// <param name="range">Target range</param>
/// <param name="size">Minichart size</param>
/// <returns>
/// A <see cref="ExcelAddressBase"/> containing the range address.
/// </returns>
public static ExcelCellBase Expand(this XlsxBaseRange range, XlsxMiniChartSize size)
{
int offsetY = size.VerticalCells == 1 ? 0 : size.VerticalCells - 1;
int offsetX = size.HorizontalCells == 1 ? 0 : size.HorizontalCells - 1;
var targetAddress = range.ToEppExcelAddress();
var address = ExcelCellBase.GetAddress(targetAddress.Start.Row, targetAddress.Start.Column, targetAddress.End.Row + offsetY, targetAddress.End.Column + offsetX);
return(new ExcelAddressBase(address));
}
public void GetRowColFullAddressAbsolute()
{
int row, col;
bool fixedRow, fixedCol;
ExcelCellBase.GetRowCol("$C$3", out row, out col, true, out fixedRow, out fixedCol);
Assert.AreEqual(3, row);
Assert.AreEqual(3, col);
Assert.IsTrue(fixedRow);
Assert.IsTrue(fixedCol);
}
public void GetRowColJustColumn()
{
int row, col;
bool fixedRow, fixedCol;
ExcelCellBase.GetRowCol("C", out row, out col, true, out fixedRow, out fixedCol);
Assert.AreEqual(0, row);
Assert.AreEqual(3, col);
Assert.IsFalse(fixedRow);
Assert.IsFalse(fixedCol);
}
public void GetRowColJustRowAbsolute()
{
int row, col;
bool fixedRow, fixedCol;
ExcelCellBase.GetRowCol("$3", out row, out col, true, out fixedRow, out fixedCol);
Assert.AreEqual(3, row);
Assert.AreEqual(0, col);
Assert.IsTrue(fixedRow);
Assert.IsFalse(fixedCol);
}
internal Worksheet(ExcelWorksheet ws, IEnumerable <T> items)
{
_ws = ws;
_items = items;
_props = typeof(T).GetProperties();
_cols = 1;
_rows = 1;
foreach (var p in _props)
{
var cell = ws.Cells[_rows, _cols];
cell.Value = p.Name;
_cols++;
}
_header = ExcelCellBase.GetAddress(1, 1, 1, _cols - 1);
_ws.Cells[_header].Style.Fill.PatternType = ExcelFillStyle.Solid;
_ws.Cells[_header].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#5078b3"));
_ws.Cells[_header].Style.Font.Color.SetColor(Color.White);
_ws.Cells[_header].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
_ws.Cells[_header].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
_ws.Cells[_header].Style.Border.Left.Style = ExcelBorderStyle.Thin;
_ws.Cells[_header].Style.Border.Left.Color.SetColor(Color.Black);
_ws.Cells[_header].Style.Font.Bold = true;
_rows = 2;
foreach (var i in _items)
{
for (int c = 0; c < _cols - 1; c++)
{
var prop = _props[c];
var cell = ws.Cells[_rows, c + 1];
var value = prop.GetValue(i);
cell.Value = value;
}
if (_rows % 2 != 0)
{
_ws.Cells[_rows, 1, _rows, _cols - 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
_ws.Cells[_rows, 1, _rows, _cols - 1].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#e0e8ee"));
}
_ws.Cells[_rows, 1, _rows, _cols - 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, ColorTranslator.FromHtml("#808080"));
_ws.Cells[_rows, 1, _rows, _cols - 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
_ws.Cells[_rows, 1, _rows, _cols - 1].Style.Border.Left.Color.SetColor(ColorTranslator.FromHtml("#808080"));
_rows++;
}
AutoFit();
}
/// <summary>
/// Translate an address from R1C1 to A1
/// </summary>
/// <param name="r1C1Address">The address</param>
/// <param name="row">The row of the cell to calculate from</param>
/// <param name="col">The column of the cell to calculate from</param>
/// <returns>The address in A1 notation</returns>
public static string FromR1C1(string r1C1Address, int row, int col)
{
var addresses = ExcelAddressBase.SplitFullAddress(r1C1Address);
var ret = "";
foreach (var address in addresses)
{
ret += ExcelCellBase.GetFullAddress(address[0], address[1], FromR1C1SingleAddress(address[2], row, col)) + ",";
}
return(ret.Length == 0?"":ret.Substring(0, ret.Length - 1));
}
internal protected string ToFullAddress(string value)
{
if (ExcelCellBase.IsValidAddress(value))
{
return(ExcelCellBase.GetFullAddress(_chart.WorkSheet.Name, value));
}
else
{
return(value);
}
}
/// <summary>
///
/// </summary>
/// <param name="tempNamedRange"></param>
public string GetExportRowAddress(ExcelNamedRange tempNamedRange)
{
var startRow = RowIndex;
var startColumn = tempNamedRange.Start.Column;
var endRow = startRow + tempNamedRange.Rows - 1;
var endColumn = tempNamedRange.Columns;
var address = ExcelCellBase.GetAddress(startRow, startColumn, endRow, endColumn);
RowIndex += tempNamedRange.Rows;
ColumnIndex = 1;
return(address);
}
private static ExcelAddressBase DeleteSplitIndividualAddress(ExcelAddressBase address, ExcelAddressBase range, ExcelAddressBase effectedAddress, eShiftTypeDelete shift)
{
if (address.CollideFullRowOrColumn(range))
{
if (range.CollideFullColumn(address._fromCol, address._toCol))
{
return(address.DeleteColumn(range._fromCol, range.Columns));
}
else
{
return(address.DeleteRow(range._fromRow, range.Rows));
}
}
else
{
var collide = effectedAddress.Collide(address);
if (collide == ExcelAddressBase.eAddressCollition.Partly)
{
var addressToShift = effectedAddress.Intersect(address);
var shiftedAddress = ShiftAddress(addressToShift, range, shift);
var newAddress = "";
if (address._fromRow < addressToShift._fromRow)
{
newAddress = ExcelCellBase.GetAddress(address._fromRow, address._fromCol, addressToShift._fromRow - 1, address._toCol) + ",";
}
if (address._fromCol < addressToShift._fromCol)
{
var fromRow = Math.Max(address._fromRow, addressToShift._fromRow);
newAddress += ExcelCellBase.GetAddress(fromRow, address._fromCol, address._toRow, addressToShift._fromCol - 1) + ",";
}
if (shiftedAddress != null)
{
newAddress += $"{shiftedAddress.Address},";
}
if (address._toRow > addressToShift._toRow)
{
newAddress += ExcelCellBase.GetAddress(addressToShift._toRow + 1, address._fromCol, address._toRow, address._toCol) + ",";
}
if (address._toCol > addressToShift._toCol)
{
newAddress += ExcelCellBase.GetAddress(address._fromRow, addressToShift._toCol + 1, address._toRow, address._toCol) + ",";
}
return(new ExcelAddressBase(newAddress.Substring(0, newAddress.Length - 1)));
}
else if (collide != ExcelAddressBase.eAddressCollition.No)
{
return(ShiftAddress(address, range, shift));
}
}
return(address);
}