Pivot tables

// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument workbook = new ExcelDocument(2);

// Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");

// Add data to the first sheet, the data source of the pivot table
...

// Create pivot table
ExcelPivotTable xlsPivotTable = new ExcelPivotTable();
xlsPivotTable.setName("Sales");
xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook);
xlsPivotTable.setLocation("A3:G15");
xlsPivotTable.addFieldToRowLabels("Sale agent");
xlsPivotTable.addFieldToColumnLabels("Year");
xlsPivotTable.addFieldToValues("Sale amount", "Sale amount per year", 
        PivotTable.SUBTOTAL_SUM);
xlsPivotTable.addFieldToReportFilter("Sale country");
xlsPivotTable.setOutlineForm();
xlsPivotTable.setStyle(PivotTable.PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
((ExcelWorksheet)workbook.easy_getSheet(
        "Second tab")).easy_addPivotTable(xlsPivotTable);

// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx");

' Create an instance of the class that exports Excel files, having two sheets
Dim workbook As New ExcelDocument(2)

' Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")

' Add data to the first sheet, the data source of the pivot table
...
  
' Create pivot table
Dim xlsPivotTable As New ExcelPivotTable
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook)
xlsPivotTable.setLocation("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues("Sale amount", "Sale amount per year", _
        PivotTable.SUBTOTAL_SUM)
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(PivotTable.PIVOT_STYLE_DARK_11)

' Add the pivot table to the second sheet
Dim xlsWorksheet As ExcelWorksheet = workbook.easy_getSheet("Second tab")
xlsWorksheet.easy_addPivotTable(xlsPivotTable)

' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Excel pivot table.xlsx")

C++

// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelDocument),
		      (void**) &workbook) ;
		
// Create two sheets
workbook->easy_addWorksheet_2("First tab");
workbook->easy_addWorksheet_2("Second tab");

// Add data to the first sheet, the data source of the pivot table
...
		
// Create pivot table
EasyXLS::IExcelPivotTablePtr xlsPivotTable;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelPivotTable),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelPivotTable),
		      (void**) &xlsPivotTable);
xlsPivotTable->setName("Sales");
xlsPivotTable->setSourceRange("First tab!$A$1:$E$9",_variant_t((IDispatch*)workbook));
xlsPivotTable->setLocation_2("A3:G15");
xlsPivotTable->addFieldToRowLabels("Sale agent");
xlsPivotTable->addFieldToColumnLabels("Year");
xlsPivotTable->addFieldToValues("Sale amount", "Sale amount per year",
        PIVOTTABLE_SUBTOTAL_SUM);
xlsPivotTable->addFieldToReportFilter("Sale country");
xlsPivotTable->setOutlineForm();
xlsPivotTable->setStyle(PIVOTTABLE_PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
EasyXLS::IExcelWorksheetPtr xlsWorksheet = 
        (EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheet("Second tab");
xlsWorksheet->easy_addPivotTable(xlsPivotTable);

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx");

Click here to see Continuous Code Listing


C++.NET

// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument ^workbook = gcnew ExcelDocument(2);

// Set the sheet names
workbook->easy_getSheetAt(0)->setSheetName("First tab");
workbook->easy_getSheetAt(1)->setSheetName("Second tab");

// Add data to the first sheet, the data source of the pivot table
...

// Create pivot table
ExcelPivotTable ^xlsPivotTable = gcnew ExcelPivotTable();
xlsPivotTable->setName("Sales");
xlsPivotTable->setSourceRange("First tab!$A$1:$E$9", workbook);
xlsPivotTable->setLocation("A3:G15");
xlsPivotTable->addFieldToRowLabels("Sale agent");
xlsPivotTable->addFieldToColumnLabels("Year");
xlsPivotTable->addFieldToValues("Sale amount", "Sale amount per year", 
        PivotTable::SUBTOTAL_SUM);
xlsPivotTable->addFieldToReportFilter("Sale country");
xlsPivotTable->setOutlineForm();
xlsPivotTable->setStyle(PivotTable::PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
ExcelWorksheet ^xlsWorksheet = 
        safe_cast<ExcelWorksheet^>(workbook->easy_getSheet("Second tab"));
xlsWorksheet->easy_addPivotTable(xlsPivotTable);

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx");

Click here to see Continuous Code Listing

// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument workbook = new ExcelDocument(2);

// Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");

// Add data to the first sheet, the data source of the pivot table
...

// Create pivot table
ExcelPivotTable xlsPivotTable = new ExcelPivotTable();
xlsPivotTable.setName("Sales");
xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook);
xlsPivotTable.setLocation("A3:G15");
xlsPivotTable.addFieldToRowLabels("Sale agent");
xlsPivotTable.addFieldToColumnLabels("Year");
xlsPivotTable.addFieldToValues("Sale amount", "Sale amount per year", 
        PivotTable.SUBTOTAL_SUM);
xlsPivotTable.addFieldToReportFilter("Sale country");
xlsPivotTable.setOutlineForm();
xlsPivotTable.setStyle(PivotTable.PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
((ExcelWorksheet)workbook.easy_getSheet(
        "Second tab")).easy_addPivotTable(xlsPivotTable);

// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx");

.NET:

// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");

// Create two sheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");

// Add data to the first sheet, the data source of the pivot table
...
	
// Create pivot table
$xlsPivotTable = new COM("EasyXLS.PivotTables.ExcelPivotTable");
$xlsPivotTable->setName("Sales");
$xlsPivotTable->setSourceRange("First tab!\$A\$1:\$E\$9", $workbook);
$xlsPivotTable->setLocation_2("A3:G15");
$xlsPivotTable->addFieldToRowLabels("Sale agent");
$xlsPivotTable->addFieldToColumnLabels("Year");
$xlsPivotTable->addFieldToValues("Sale amount", "Sale amount per year",
        $PIVOTTABLE_SUBTOTAL_SUM);
$xlsPivotTable->addFieldToReportFilter("Sale country");
$xlsPivotTable->setOutlineForm();
$xlsPivotTable->setStyle($PIVOTTABLE_PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
$xlsWorksheet = $workbook->easy_getSheet("Second tab");
$xlsWorksheet->easy_addPivotTable($xlsPivotTable);

// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel pivot table.xlsx");

Click here to see Continuous Code Listing


Java:

// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
	
// Create two worksheets
$workbook->easy_addWorksheet("First tab");
$workbook->easy_addWorksheet("Second tab");

// Add data to the first sheet, the data source of the pivot table
...

// Create pivot table
$xlsPivotTable = new java("EasyXLS.PivotTables.ExcelPivotTable");
$xlsPivotTable->setName("Sales");
$xlsPivotTable->setSourceRange("First tab!\$A\$1:\$E\$9", $workbook);
$xlsPivotTable->setLocation("A3:G15");
$xlsPivotTable->addFieldToRowLabels("Sale agent");
$xlsPivotTable->addFieldToColumnLabels("Year");
$xlsPivotTable->addFieldToValues("Sale amount","Sale amount per year",
                                 $PIVOTTABLE_SUBTOTAL_SUM);  
$xlsPivotTable->addFieldToReportFilter("Sale country");
$xlsPivotTable->setOutlineForm(); 
$xlsPivotTable->setStyle($PIVOTTABLE_PIVOT_STYLE_DARK_11);
			
// Add the pivot table to the second sheet
$xlsWorksheet = $workbook->easy_getSheet("Second tab");
$xlsWorksheet->easy_addPivotTable($xlsPivotTable);
			
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel pivot table.xlsx");

Click here to see Continuous Code Listing

' Create an instance of the class that exports Excel files
set workbook = Server.CreateObject("EasyXLS.ExcelDocument")

' Create two sheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")

' Add data to the first sheet, the data source of the pivot table
...

' Create pivot table
set xlsPivotTable = Server.CreateObject("EasyXLS.PivotTables.ExcelPivotTable")
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange "First tab!$A$1:$E$9", workbook
xlsPivotTable.setLocation_2("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues "Sale amount", "Sale amount per year", _
        PIVOTTABLE_SUBTOTAL_SUM 
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(PIVOTTABLE_PIVOT_STYLE_DARK_11)

' Add the pivot table to the second sheet
workbook.easy_getSheet("Second tab").easy_addPivotTable(xlsPivotTable)

' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel pivot table.xlsx")

' Create an instance of the class that exports Excel files
Set workbook = CreateObject("EasyXLS.ExcelDocument")
    
' Create two sheets
workbook.easy_addWorksheet_2 ("First tab")
workbook.easy_addWorksheet_2 ("Second tab")
    
' Add data to the first sheet, the data source of the pivot table
...

' Create pivot table
Set xlsPivotTable = CreateObject("EasyXLS.PivotTables.ExcelPivotTable")
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange "First tab!$A$1:$E$9", workbook
xlsPivotTable.setLocation_2("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues "Sale amount", "Sale amount per year", _
        PivotTable.PIVOTTABLE_SUBTOTAL_SUM 
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(PivotTable.PIVOTTABLE_PIVOT_STYLE_DARK_11)

' Add the pivot table to the second sheet
Set xlsWorksheet = workbook.easy_getSheet("Second tab")
xlsWorksheet.easy_addPivotTable (xlsPivotTable)

' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel pivot table.xlsx")

' Create an instance of the class that exports Excel files
Set workbook = CreateObject("EasyXLS.ExcelDocument")
    
' Create two sheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
    
' Add data to the first sheet, the data source of the pivot table
...
  
' Create pivot table
Set xlsPivotTable = CreateObject("EasyXLS.PivotTables.ExcelPivotTable")
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange "First tab!$A$1:$E$9", workbook
xlsPivotTable.setLocation_2("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues "Sale amount", "Sale amount per year", _
        PIVOTTABLE_SUBTOTAL_SUM 
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(PIVOTTABLE_PIVOT_STYLE_DARK_11)

' Add the pivot table to the second sheet
Set xlsWorksheet = workbook.easy_getSheet("Second tab")
xlsWorksheet.easy_addPivotTable(xlsPivotTable)

' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel pivot table.xlsx")

<!-- Create an instance of the class that exports Excel files -->
<cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE">

<!-- Create two sheets -->
<cfset ret = workbook.easy_addWorksheet("First tab")>
<cfset ret = workbook.easy_addWorksheet("Second tab")>

<!-- Add data to the first sheet, the data source of the pivot table -->
...

<!-- Create pivot table -->
<cfobject type="java" class="EasyXLS.PivotTables.ExcelPivotTable" 
        name="xlsPivotTable" action="CREATE">
<cfset xlsPivotTable.setName("Sales")>
<cfset xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook)>
<cfset xlsPivotTable.setLocation("A3:G15")>
<cfset xlsPivotTable.addFieldToRowLabels("Sale agent")>
<cfset xlsPivotTable.addFieldToColumnLabels("Year")>
<cfset xlsPivotTable.addFieldToValues("Sale amount", "Sale amount per year",
        PivotTable.SUBTOTAL_SUM)>
<cfset xlsPivotTable.addFieldToReportFilter("Sale country")>
<cfset xlsPivotTable.setOutlineForm()>
<cfset xlsPivotTable.setStyle(PivotTable.PIVOT_STYLE_DARK_11)>

<!-- Add the pivot table to the second sheet -->
<cfset xlsWorksheet = workbook.easy_getSheet("Second tab")>
<cfset xlsWorksheet.easy_addPivotTable(xlsPivotTable)>

<!-- Export Excel file -->
<cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Excel pivot table.xlsx")>

.NET:

# Create an instance of the class that exports Excel files, having two sheets
workbook = ExcelDocument(2)

# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")

# Add data to the first sheet, the data source of the pivot table
...

# Create pivot table
xlsPivotTable = ExcelPivotTable()
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook)
xlsPivotTable.setLocation("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues("Sale amount","Sale amount per year", PivotTable.SUBTOTAL_SUM)
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(PivotTable.PIVOT_STYLE_DARK_11)

# Add the pivot table to the second sheet
workbook.easy_getSheet("Second tab").easy_addPivotTable(xlsPivotTable)

# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx")

Click here to see Continuous Code Listing


Java:

# Create an instance of the class that exports Excel files, having two sheets
workbook = gateway.jvm.ExcelDocument(2)

# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")

# Add data to the first sheet, the data source of the pivot table
...

# Create pivot table
xlsPivotTable = gateway.jvm.ExcelPivotTable()
xlsPivotTable.setName("Sales")
xlsPivotTable.setSourceRange("First tab!$A$1:$E$9", workbook)
xlsPivotTable.setLocation("A3:G15")
xlsPivotTable.addFieldToRowLabels("Sale agent")
xlsPivotTable.addFieldToColumnLabels("Year")
xlsPivotTable.addFieldToValues("Sale amount","Sale amount per year", gateway.jvm.PivotTable.SUBTOTAL_SUM)
xlsPivotTable.addFieldToReportFilter("Sale country")
xlsPivotTable.setOutlineForm()
xlsPivotTable.setStyle(gateway.jvm.PivotTable.PIVOT_STYLE_DARK_11)

# Add the pivot table to the second sheet
workbook.easy_getSheet("Second tab").easy_addPivotTable(xlsPivotTable)

# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel pivot table.xlsx")

Click here to see Continuous Code Listing