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

PivotTable

Mar 4, 2012 at 10:21 PM

I was wondering why PivotTables are not support in this great project.

I was browsing the source code and noticed that most (if not all) the necessary code seems to be already written in XLWorkbook_Save.cs but the call to the code has been commented out:

                //if (worksheet.PivotTables.Any())
                //{
                //    GeneratePivotTables(workbookPart, worksheetPart, worksheet, context);
                //}

I'm guessing there's an issue with GeneratePivotTables. Anything I can do to help?

 

Mar 5, 2012 at 4:40 PM

The functionality is rather limited and crude. I also need to review and test the API exposed for that functionality. The reason it's commented out is because I believe strongly in creating half a product, not a half assed one.

If you want to help we can probably work something out. Give me a PM if you're interested...

Dec 18, 2013 at 10:13 PM
Edited Dec 18, 2013 at 10:14 PM
Hello,
Aren´t pivot tables still not fully supported? I got some pivot table working but the behaviour is more than strange. After hours of investigation my pivot table with aprox. 15k of rows is crashing when workbook is opened (message: Feature removed....).

Pivot table is feeded from dataset and what´s strange the crash does not appear evertime workbook is opened, e.g. when I limit dataset to TOP 20 rows everything works fine, sometimes even 15 rows are not working. I´m getting really crazy because of this.

Can you give me some guide what should I be focused on? ...empty cells, data types in my database?

The code:
'Excel file definition
                Dim wb As XLWorkbook = New XLWorkbook()
                'Get data from dataset
                Dim con = New SqlConnection(ConfigurationManager.ConnectionStrings("IPSdb").ConnectionString)
                con.Open()
                Dim cmd As New SqlCommand
                Dim cmdstring As String = "SELECT Partnumber, Qty, Week, Year, Line, CBID FROM tblEDI"
                cmd.CommandType = CommandType.Text
                Dim objAdp = New SqlDataAdapter(cmdstring, con)
                Dim ds = New DataSet()
                objAdp.Fill(ds, "tblEDI")

                Dim sheet1 = wb.Worksheets.Add("EDI raw data")
                sheet1.Cell(1, 1).InsertTable(ds.Tables("tblEDI"), False, False)
                Dim dataRange = sheet1.RangeUsed

                'Add a new sheet for our pivot table
                Dim sheet2 = wb.Worksheets.Add("PivotTable")

                'Create the pivot table, using the data from the table
                Dim pt = sheet2.PivotTables.AddNew("PivotTable", sheet2.Cell(1, 1), dataRange)

                pt.RowLabels.Add("Partnumber")
                pt.RowLabels.Add("Line")
                pt.RowLabels.Add("CBID")
                pt.ColumnLabels.Add("Year")
                pt.ColumnLabels.Add("Week")
                pt.Values.Add("Qty")

                'Pivot format
                With pt
                    .SortFieldsAtoZ = True
                    .ShowGrandTotalsColumns = False
                    .ShowGrandTotalsRows = False
                    .Theme = XLPivotTableTheme.PivotStyleLight1
                    .Subtotals = XLPivotSubtotals.DoNotShow
                    .Layout = XLPivotLayout.Tabular
                    .PreserveCellFormatting = True
                    .AutofitColumns = True
                End With
               
                'Adjust workbook and save
                For Each ws As IXLWorksheet In wb.Worksheets
                    ws.ShowGridLines = False
                    ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left
                    ws.Columns.AdjustToContents()
                Next

                'Save Excel file to ExportedFiles
                wb.SaveAs(path & "EDIexport " & Day(Today()) & "." & Month(Today()) & "." & Year(Today()) & ".xlsx")
                fileexists = True