/// <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); }