Pivot table and chart(Java)

 
top
Excel table

Create an Excel table and add new calculated column.
Use structured references in Excel table formulas

String tableDataInCSV = "Sales Person\tRegion\tSales Amount\t% Commission\n" +
"Joe\tNorth\t260\t10%\n" +
"Robert\tSouth\t660\t15%\n" +
"Michelle\tEast\t940\t15%\n" +
"Erich\tWest\t410\t12%\n" +
"Dafna\tNorth\t800\t15%\n" +
"Rob\tSouth\t900\t15%";
WorkBook workbook = new WorkBook();
//import table data
workbook.setCSVString(tableDataInCSV);

//add table to range A1:D7
Table table = workbook.addTable("DeptSales", 0, 0, 6, 3, TableBuiltInStyles.TableStyleMedium2);
//banded row
table.setRowStripes(true);
//enable total row
table.setTotalRow(true, "Total");

//add new column(with structured references formula) to the table
table.addCalculatedColumn("Commission Amount", "DeptSales[[#This Row],[Sales Amount]]*DeptSales[[#This Row],[% Commission]]");
//set the column's total func(1-average 2-count 3-countNums 4-max 5-min 6-sum 7-stdDev 8-var)
table.setColumnTotalsFunc("Commission Amount", 6);

workbook.setText(11,1,"Sales Total");
//using structured references formula in sheet
workbook.setFormula(11,2,"SUM(DeptSales[Sales Amount])");

for(int i=0;i<5;i++)
workbook.setColWidthAutoSize(i, true);

workbook.writeXLSX("Table.xlsx");

 
top
Pivot table

Create and set PivotTable Objects.

WorkBook workBook = new WorkBook();
String csv = "Who\tWeek\tWhat\tAmount\n" +
"Joe\t3\tBeer\t18\n" +
"Beth\t4\tFood\t17\n" +
"Janet\t5\tBeer\t14\n" +
"Joe\t3\tFood\t12\n" +
"Joe\t4\tBeer\t19\n" +
"Janet\t5\tCar\t12\n" +
"Joe\t3\tFood\t19\n" +
"Beth\t4\tBeer\t15\n" +
"Janet\t5\tBeer\t19\n" +
"Joe\t3\tCar\t20\n" +
"Joe\t4\tBeer\t16\n" +
"Beth\t5\tFood\t12\n" +
"Beth\t3\tBeer\t16\n" +
"Joe\t4\tFood\t17\n" +
"Joe\t5\tBeer\t14\n" +
"Janet\t3\tCar\t19\n" +
"Joe\t4\tFood\t17\n" +
"Beth\t5\tBeer\t20\n" +
"Janet\t3\tFood\t18\n" +
"Joe\t4\tBeer\t14\n" +
"Joe\t5\tFood\t12\n" +
"Janet\t3\tBeer\t18\n" +
"Janet\t4\tCar\t17\n" +
"Janet\t5\tFood\t12\n" +
"Test\t4\tTester\t4\n" +
"Stephen\t8\tPerson\t7";

//set to cell B1 active
workBook.setActiveCell(1, 1);
workBook.setCSVString(csv);

//this will create new sheet for pivot table
workBook.insertSheets(1, 1);

BookPivotRangeModel model = workBook.getPivotModel();

//set pivot table linked to range
model.setList("Sheet1!$B$2:$E$28");

//set pivot table in the top-left location Sheet2!C3
model.setLocation(1, 2, 2);

//select Sheet2
workBook.setSheet(1);

//make the pivot table active
workBook.setSelection("C3");

BookPivotRange pivotRange = model.getActivePivotRange();

BookPivotArea rowArea = pivotRange.getArea(BookPivotRange.row);
BookPivotArea columnArea = pivotRange.getArea(BookPivotRange.column);
BookPivotArea dataArea = pivotRange.getArea(BookPivotRange.data);

//add "Week" to RowArea
pivotRange.addField("Week", rowArea);
//add "Who" to RowArea
pivotRange.addField("Who", rowArea);
//add "What" to ColumnArea
pivotRange.addField("What", columnArea);
//add "Amount" to DataArea
pivotRange.addField("Amount", dataArea);

//get "Amount" from DataArea
BookPivotField dataField = dataArea.getField(0); //"Sum of Amount"
if(dataField.isSummarizeField())
{
String custom = dataField.getNumberFormatting();
//change the calc type to average
dataField.setSummarizeFieldCalcType(BookPivotField.SummarizeCalcAverage, "Avarage of Amount!!!");
dataField.setNumberFormatting("$#,##0_);($#,##0)");
}

//workBook.write("Pivot2.xls");
workBook.writeXLSX("Pivot2.xlsx");

 
top
Pivot table area formatting

Applied the formatting(font,fill,align etc) to specified area of the PivotTable.


BookPivotRange pivotRange = model.getActivePivotRange();

//...

RangeStyle rs = workBook.getRangeStyle();
rs.resetFormat();
rs.setFontBold(true);
rs.setFontItalic(true);
rs.setPattern((short)1);
rs.setPatternFG(java.awt.Color.green.getRGB());
int selection = 0;//0-BlankColumnHeader 2-Data 3-all 4-TopLeft
pivotRange.setTableFormat(rs, selection);

 
top
Pivot table conditional formatting

Applied the conditional formatting(color scales,data bar,iconset etc) to specified area of the PivotTable.


BookPivotRange pivotRange = model.getActivePivotRange();

//...
 //set the conditional formatting for the pivot table
ConditionFormat conditionFormat = workBook.CreateConditionFormat();

// 3 color scales(green-yellow-red)
conditionFormat.setColorScale((short)3, new java.awt.Color[]{java.awt.Color.green, java.awt.Color.yellow, java.awt.Color.red});

//data field index in the data area
int datafiled = 0;

// 0 - Selected cells 1- All cells showing "#DATAFIELD" values 3- All cells showing "#DATAFIELD" values for "#ROW_AREA_FIELDS" and "#COL_AREA_FIELDS"
int conditionType = 0;

pivotRange.setConditionalFormat(conditionFormat, datafiled, conditionType);

 
top
Pivot chart

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..

WorkBook workBook = new WorkBook();
workBook.read("PivotTable.xls");

BookPivotRangeModel model = workBook.getPivotModel();

//select the sheet and range
workBook.setSheet(0);
workBook.setSelection(0, 0, 0, 0);
BookPivotRange pivotRange = model.getActivePivotRange();
//fresh the pivot table
model.refreshRange(pivotRange);


ChartShape chart = workBook.addChart(5, 10, 12.6, 29.5);
chart.setChartType(ChartShape.Line);

//link the chart to the pivot table
chart.setPivotSource(pivotRange);

workBook.writeXLSX("PivotChart.xlsx");

 

 
top
Pivot table with external data source

Create a pivot table and then use that pivot table as the datasource to create a pivot chart..

PivotExDSSample.java
public class PivotExDSSample
{

private static Connection getConnection()
throws Exception
{
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:Northwind";
String username = "";
String password = "";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}

private static SXQuery getQuery()
{
try
{
Connection conn = getConnection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM Orders");
ResultSetMetaData rsMetaData = rs.getMetaData();
return new SXQuery(st, rs);
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}


public static void main(String args[])
{
WorkBook workBook = new WorkBook();
try
{
workBook.setSheet(0);

BookPivotRange pivotRange = workBook.addPivotRange(getQuery(), 0, 7, 5);
pivotRange.setDataOnRow(false);
pivotRange.refreshRange();

RangeArea rangeArea = pivotRange.getRangeArea();

BookPivotArea rowArea = pivotRange.getArea(BookPivotRange.row);
BookPivotArea columnArea = pivotRange.getArea(BookPivotRange.column);
BookPivotArea dataArea = pivotRange.getArea(BookPivotRange.data);
BookPivotArea pageArea = pivotRange.getArea(BookPivotRange.page);

BookPivotField rowField = pivotRange.addField("OrderID", rowArea);
rowField = pivotRange.addField("CustomerID", rowArea);

BookPivotField columnField = pivotRange.addField("OrderDate", columnArea);

BookPivotField dataField = pivotRange.addField("Freight", dataArea);

//BookPivotField pageField = pivotRange.addField("ShipRegion", pageArea);

//workBook.write(".\\PivotTableDS.xls");
workBook.writeXLSX(".\\PivotTableDS.xlsx");
}
catch (Exception e)
{
}
}
}

 

 
top
format Excel Table(or Pivot table) with custom style

Create a table style(or load from template) and then apply it to the table(or the pivot table)

WorkBook workbook = new WorkBook();
workbook.readXLSX(".\\SampleData.xlsx");

java.util.HashMap<com.smartxls.enums.TableStyleElementType, RangeStyle> tableStyleElements = new HashMap<TableStyleElementType, RangeStyle>();

RangeStyle rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0xdeebec);
rangeStyle.setHorizontalInsideBorder(RangeStyle.BorderThin);
rangeStyle.setHorizontalInsideBorderColor(0xffffff);
rangeStyle.setVerticalInsideBorder(RangeStyle.BorderThin);
rangeStyle.setVerticalInsideBorderColor(0xffffff);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.wholeTable, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setFontBold(true);
rangeStyle.setFontItalic(true);
rangeStyle.setFontColor(0xffffff);
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0x5b9ea4); // 91 158 164
rangeStyle.setBottomBorder(RangeStyle.BorderThin);
rangeStyle.setBottomBorderColor(0xffffff);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.headerRow, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setFontBold(true);
rangeStyle.setFontColor(0xffffff);
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0x5b9ea4);
rangeStyle.setTopBorder(RangeStyle.BorderThin);
rangeStyle.setTopBorderColor(0xffffff);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.totalRow, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setFontBold(true);
rangeStyle.setFontColor(0xffffff);
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0x5b9ea4);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.firstColumn, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setFontBold(true);
rangeStyle.setFontColor(0xffffff);
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0x5b9ea4);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.lastColumn, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0xbdd9db);
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.firstRowStripe, rangeStyle);

rangeStyle = workbook.getRangeStyle();
rangeStyle.resetFormat();
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(0xbdd9db); // 189 217 219
tableStyleElements.put(com.smartxls.enums.TableStyleElementType.firstColumnStripe, rangeStyle);

workbook.CreateTableStyle("My-TableStyleMedium11", tableStyleElements);

// Table table = workbook.addTable("DeptSales", 0, 0, 6, 3, TableBuiltInStyles.TableStyleMedium2); //create new table
Table table = workbook.getTable(0, 0); //get an pre-exist table
table.setTableCustomStyle("My-TableStyleMedium11");


// BookPivotRange pivotRange = workbook.addPivotRange("Sheet1!$A$1:$D$27", "Sheet2!C3"); //create new pivot table
// BookPivotRange pivotRange = workbook.addPivotRange(0, 0, 0, 26, 3, 1, 2, 2); //create new pivot table
BookPivotRange pivotRange = workbook.getActivePivotRange(2, 2); //get an pre-exist table

pivotRange.setTableCustomStyle("My-TableStyleMedium11");

workbook.writeXLSX("PivotTable.xlsx");