/// <summary>
/// ''' can't touch already created pivot-table
/// ''' https://github.com/ClosedXML/ClosedXML/pull/124
/// ''' </summary>
/// ''' <param name="sheetSource"></param>
/// ''' <param name="sheetDest"></param>
/// ''' <param name="rows"></param>
/// ''' <param name="cols"></param>
/// ''' <returns></returns>
public ExcelHelper DoPivotTable(string sheetSource, string sheetDest, string[] rows = null, string[] cols = null, string[] datafields = null)
{
try
{
IXLWorksheet worksheetSource = null;
IXLTable sourceTable = null;
if (_workbook.Worksheets.Count < 1)
{
_workbook = new XLWorkbook(this._inputFile.FullName);
}
worksheetSource = _workbook.Worksheets.Worksheet(sheetSource);
sourceTable = worksheetSource.Table(0);
// TODO PRIMA CONTROLLARE SE ESISTE E nel caso cancella
this.RemoveSheet(sheetDest); // <<TODO invece di remove che in questo caso crea un errore cercare se è possibile chiamare REFRESH
IXLWorksheet pivotTableSheet = _workbook.Worksheets.Add(sheetDest);
IXLPivotTable pivoTable = pivotTableSheet.PivotTables.Add("PivotTable", pivotTableSheet.Cell(1, 1), sourceTable.AsRange());
foreach (string r in rows)
{
if (r.Trim() != "")
{
pivoTable.RowLabels.Add(r);
}
}
foreach (string c in cols)
{
if (c.Trim() != "")
{
pivoTable.ColumnLabels.Add(c);
}
}
foreach (string d in datafields)
{
if (d.Trim() != "")
{
pivoTable.Values.Add(d);
}
}
}
// i filtri non sono al momento supportati https://github.com/ClosedXML/ClosedXML/issues/218
catch (Exception ex)
{
//SD.Log(ex.Message, SD.LogLevel.Error, ex.StackTrace);
return(this);
}
return(this);
}