Using ClosedXML-0.76, I'm trying to create a basic Pivot Table with no luck so far. The code is in PowerShell:
# ClosedXML assembly and dependencies
Add-Type -Path "path\to\DocumentFormat.OpenXml.2.5\lib\DocumentFormat.OpenXml.dll"
Add-Type -Path "path\to\closedXML-0.76\ClosedXML.dll"
# Adding data from a DataTable
$dt = New-Object System.Data.DataTable
$dt.TableName = "test"
$dt.Columns.Add("col1")
$dt.Columns.Add("col2")
$dt.Columns.Add("col3")
$dt.Rows.Add(@(1,2,3))
$dt.Rows.Add(@(4,5,6))
$workbook = New-Object -TypeName ClosedXML.Excel.XLWorkbook
$ws = $workbook.AddWorksheet($dt)
# creating the Pivot Table on another worksheet
$dataRange = $ws.RangeUsed()
# Add a new sheet for our pivot table
$ptSheet = $workbook.Worksheets.Add("Pivot Table");
#Create the pivot table, using the data from the "PastrySalesData" table
$pt = $ptSheet.PivotTables.AddNew("Pivot Table", $ptSheet.Cell(1, 1), $dataRange);
# Simplest Pivot Table with Row Labels
$pt.RowLabels.Add("col1");
$pt.RowLabels.Add("col2")
$workbook.SaveAs("C:/temp/test.xlsx")
The Excel file is corrupted with no recovery. Do I forget something? Found this related post with no answer unfortunately (https://stackoverflow.com/questions/33437738/closedxml-pivot-table-creation-results-in-corrupt-spreadsheet).
