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:
// GeneratePivotTables(workbookPart, worksheetPart, worksheet, context);
I'm guessing there's an issue with GeneratePivotTables. Anything I can do to help?
Mar 5, 2012 at 3: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 9:13 PM
Edited Dec 18, 2013 at 9:14 PM
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?
'Excel file definition
Dim wb As XLWorkbook = New XLWorkbook()
'Get data from dataset
Dim con = New SqlConnection(ConfigurationManager.ConnectionStrings("IPSdb").ConnectionString)
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()
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)
.SortFieldsAtoZ = True
.ShowGrandTotalsColumns = False
.ShowGrandTotalsRows = False
.Theme = XLPivotTableTheme.PivotStyleLight1
.Subtotals = XLPivotSubtotals.DoNotShow
.Layout = XLPivotLayout.Tabular
.PreserveCellFormatting = True
.AutofitColumns = True
'Adjust workbook and save
For Each ws As IXLWorksheet In wb.Worksheets
ws.ShowGridLines = False
ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left
'Save Excel file to ExportedFiles
wb.SaveAs(path & "EDIexport " & Day(Today()) & "." & Month(Today()) & "." & Year(Today()) & ".xlsx")
fileexists = True