This project has moved and is read-only. For the latest updates, please go here.

Pivot Subtotals and Layout

Oct 15, 2013 at 8:45 PM
Hello everyone, first many thanks for the project it's amazing and save-me a lot of time doing my excel reports from my web.

So, I created a export of a datatable and created a PivotTable and it's working like a charm, but there three options that I trying to use but is not working:
pivot.AutofitColumns = true;
pivot.Subtotals = XLPivotSubtotals.DoNotShow;
pivot.Layout = XLPivotLayout.Tabular;
All of this options above there is no effect on the pivot result.

What I'm doing wrong? Bellow is my complete export code:
private void ExportPivot(String fileName, DataTable dataTable)
        {
            XLWorkbook workbook = new XLWorkbook();
            var sheet = workbook.Worksheets.Add("ModeloPontuacao");

            var source = sheet.Cell(1, 1).InsertTable(dataTable, "ModeloPontuacao", true);
            // Create a range that includes our table, including the header row
            var range = source.DataRange;
            var header = sheet.Range(1, 1, 1, 12);
            var dataRange = sheet.Range(header.FirstCell(), range.LastCell());

            // create the sheet and insert into them the new pivot
            var ptSheet = workbook.Worksheets.Add("PivotTable", 1);
            var pivot = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange);

            // define the rows and columns items
            pivot.RowLabels.Add("CodigoCliente", "Código");
            pivot.RowLabels.Add("RazaoSocial", "Cliente");
            pivot.RowLabels.Add("Endereco", "Endereço");
            pivot.RowLabels.Add("RCA", "RV");
            pivot.RowLabels.Add("Supervisor");
            pivot.RowLabels.Add("GA", "G.A.");
            pivot.RowLabels.Add("Coordenador");
            pivot.RowLabels.Add("Promotor");
            pivot.RowLabels.Add("Sistematica");
            pivot.ColumnLabels.Add("TipoPonto", "Tipo de Ponto");
            pivot.ColumnLabels.Add("Descricao", "Grupos");
            pivot.Values.Add("Pontuado");

            // set the pivot properties
            pivot.AllowMultipleFilters = true;
            pivot.DisplayCaptionsAndDropdowns = true;
            pivot.ShowGrandTotalsRows = false;
            pivot.ShowGrandTotalsColumns = false;
            pivot.ShowExpandCollapseButtons = false;
            pivot.ShowColumnStripes = false;
            pivot.ShowRowStripes = true;

            // properties that is not working 
            pivot.AutofitColumns = true;
            pivot.Subtotals = XLPivotSubtotals.DoNotShow;
            pivot.Layout = XLPivotLayout.Tabular;

            // Throw the file on the response
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("content-disposition", "attachment;filename=\"" + fileName + ".xlsx\"");
            // Flush the workbook to the Response.OutputStream
            using (MemoryStream memoryStream = new MemoryStream())
            {
                workbook.SaveAs(memoryStream);
                memoryStream.WriteTo(response.OutputStream);
                memoryStream.Close();
            }
            response.End();
        }