/// <summary>
/// ''' TODO AGGIUNGERE PARAMETRO ROUNDING OPPURE CELLFORMAT
/// ''' </summary>
/// ''' <param name="sheetName"></param>
/// ''' <param name="labelCell"></param>
/// ''' <param name="labelCellValue"></param>
/// ''' <param name="excludeHeaders"></param>
/// ''' <returns></returns>
public ExcelHelper AddRowTotal(string sheetName, string labelCell = "A", string labelCellValue = "TOT.", bool excludeHeaders = true)
{
IXLRange range = this.GetRangeUsed(sheetName);
if (range == null)
{
//SD.Log("range not found in " + sheetName, SD.LogLevel.Error);
return(this);
}
int rowsNumber = range.RowCount();
IXLCell lastCellUsed = range.LastCellUsed();
IXLColumn lastColUsed = lastCellUsed.WorksheetColumn();
IXLRow lastRowUsed = lastCellUsed.WorksheetRow();
string lastColLetter = lastColUsed.ColumnLetter();
int lastRowNumber = lastRowUsed.RowNumber();
IXLRows rows = lastRowUsed.InsertRowsBelow(1);
//IXLRow newRow = rows.Last();
var ws = this._workbook.Worksheets.Worksheet(sheetName);
IXLRow newRow = ws.LastRowUsed().RowBelow();
if (labelCell.Trim() != "")
{
newRow.Cell(labelCell).Value = labelCellValue;
newRow.Cell(labelCell).Style.Font.Bold = true;
}
var firstTotalCellAddress = newRow.FirstCell().CellRight().Address;
var lastTotalCellAddress = newRow.Cell(lastColLetter).Address;
IXLRange rangeTotal = this.GetRangeUsed(sheetName, firstTotalCellAddress, lastTotalCellAddress);
//int i = rangeTotal.Cells().Count() + 1;
int i = rangeTotal.ColumnCount() + 1;
int firstDataRowIndex = 0;
// escludo la riga delle intestazioni
if (excludeHeaders)
{
firstDataRowIndex = 2;
}
for (int k = 1; k <= i; k++)
{
XLDataType colDataType = newRow.Cell(k).CellAbove(1).DataType;
if (colDataType == XLDataType.Number)
{
string colLetter = newRow.Cell(k).Address.ColumnLetter;
string formula = "=SUM(" + colLetter + firstDataRowIndex.ToString() + ":" + colLetter + rowsNumber.ToString() + ")";
this.AddFormula(sheetName, newRow.Cell(k).Address, formula);
}
}
newRow.AsRange().RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.Thick;
return(this);
}