Pivot table and chart(Java)
- Excel table.
- Pivot table.
- Pivot table area formatting.
- Pivot table conditional formatting.
- Pivot chart.
- Pivot table with external data source.
- format Excel Table(or Pivot table) with custom style.
- Excel table
-
Create an Excel table and add new calculated column.
Use structured references in Excel table formulasString 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");
- 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");
- 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);
- 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);
- 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");
- 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)
{
}
}
}
- 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");