Data
- Example Pivot Table
- Break down
...
Intro
Code Block |
---|
ExcelApplication xla = new ExcelApplication();
//Example.xlsx has a worksheet 'Data' with 9 columns and 244 rows of data
Workbook wb = xla.Open("\\Example.xlsx");
Worksheet data_ws = wb.Worksheets["Data"];
Worksheet pivot_ws = wb.Worksheets.CreateWorksheet("Pivot");
|
Writing the code
Set up the data source
The data source needs to be a continuous block of cells with a header row with column names. The data source can be defined as an Area
or a NamedRange
. The column names become the
Here is a snapshot of the data for this tutorial:
<IMAGE>
2.
...
Info |
---|
Download Example.xlsx to see the full sample data set. |
In this case, the data source for the PivotTable will be a dynamically defined Area
on the data worksheet. Note that the row of column names is included in the area.
Code Block |
---|
Area data_area = data_ws.CreateArea(0, 0, 9, 244);
|
Create the PivotTable
CREATING A PIVOT TABLE
After creating the PivotTable, always set RefreshOnOpen
to true
.
...
ExcelWriter does not have the ability to render a PivotTable, so any modifications made to a PivotTable will not take affect until the output file is opened in Excel and the PivotTable is refreshed. If RefreshOnOpen
is true
, Excel will refresh the PivotTable when the workbook opens, which will re-render the PivotTable. 4.
Set PivotTableSettings
Other PivotTable properties - friendly error on value, empty value
Add PivotTableFields
Next, add PivotTableFields
to the PivotTable. There are four types of PivotTableField
: DataFields
, RowLabels
, ColumnLabels
, and PageFields
(or report filters).
To add
...
5. Adding data fields - calculated fields, naming conventions, changing the display name
3. The Pivot field properties are available for all four types of PivotTableField
, however some properties will not have an affect, depending on the type of field. These properties can be read and written to, but they will be ignored by Excel.
PageFields
Adding a page field - behavior
RowLabels and ColumnLabels
Adding a pivot table field - row labels / column labels, source fields, naming convention, changing the display name 4. Adding a page field - behavior
5. CHECK6. Other PivotTable properties - friendly error on value, empty value
7. Field properties, set row label
8. Summarize By
9.
DataFields
Adding data fields - calculated fields, naming conventions, changing the display name
DataField specific properties
Summarize By
Show Value As
Additional
...
Resources
- Answers post on CopySheet
- Modifying an existing PivotTable
- Documentation on creating an ExcelTemplate PivotTable
- PivotTable Glossary
- Answers post on how to set properties that aren't available
- Modifying an existing PivotTable