Styjun

What was the plan for an abort of the Enola Gay's mission to drop the atomic bomb?

Hexagonal Grid Filling

spatiotemporal regression

How to get a ellipse shaped node in Tikz Network?

Has magnetic core memory been used beyond the Moon?

Is it a Munchausen Number?

Why should password hash verification be time consistent?

Can a surprised creature fall prone voluntarily on their turn?

What does formal training in a field mean?

When quoting someone, is it proper to change "gotta" to "got to" without modifying the rest of the quote?

Was there a contingency plan in place if Little Boy failed to detonate?

How to compare d20+x with advantage to d20+y without advantage (x < y)

What is the name of meteoroids which hit Moon, Mars, or pretty much anything that isn’t the Earth?

Examples where existence is harder than evaluation

What is wrong with my code? RGB potentiometer

How to make a language evolve quickly?

Renting a house to a graduate student in my department

Visual representation code coverage in VSCode

Which other programming languages apart from Python and predecessor are out there using indentation to define code blocks?

How to rename pi as another value only for y axis without affecting pi used in x axis?

Why use steam instead of just hot air?

Is it a good idea to copy a trader when investing?

Names of the Six Tastes

What does this quote in Small Gods refer to?

How can I select the elements of a XSSFPivotTable filter?

How do I efficiently iterate over each entry in a Java Map?How can I concatenate two arrays in Java?How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I read / convert an InputStream into a String in Java?How do I generate random integers within a specific range in Java?How can I create an executable JAR with dependencies using Maven?How can I convert a stack trace to a string?How do I convert a String to an int in Java?How do I fix android.os.NetworkOnMainThreadException?Pivot table top 10 filter is displaying more than 10 item

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

Recently, I created a pivot table with Apache POI and I want to select the elements of the filters I created.

For example if the filter pivotTable.addReportFilter( columnNumber("K")-1 ); generates a list of 5 items, select 2 items show the sum of quantities.

useSheet("Sales");

AreaReference source = new AreaReference("'Sales'!A1:" + columnLetter( getCurrentSheet().getRow(0).getLastCellNum() ) + (getCurrentSheet().getLastRowNum()+1), SpreadsheetVersion.EXCEL2007);
CellReference position = new CellReference("A1");

XSSFSheet table = (XSSFSheet)createSheet("Sales by Product Line");

XSSFPivotTable pivotTable = table.createPivotTable(source, position);

pivotTable.addRowLabel( columnNumber("J")-1 );
pivotTable.addRowLabel( columnNumber("F")-1 );

pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnNumber("U")-1, "Quantity" );

pivotTable.addReportFilter( columnNumber("K")-1 );
pivotTable.addReportFilter( columnNumber("Q")-1 );
pivotTable.addReportFilter( columnNumber("D")-1 );

try (FileOutputStream fileOut = new FileOutputStream("sales.xlsx")) 
 wbook.write(fileOut);
 catch (IOException ex) 
 Logger.getLogger(Stock.class.getName()).log(Level.SEVERE, null, ex);

At the moment it creates well the tables, only I would lack to select the elements of the filters. I hope you can help me. Thank you.

java excel apache-poi pivot-table

share|improve this question

asked Mar 23 at 9:39

user2551137user2551137

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Currently apache poi is not really rendering the pivot table from the given data. It only prepares the *.xlsx file so that Excel then renders the pivot table from the given data while opening the file. So apache poi's pivot cache is very rudimentary and does not know something about how the filters affect the data. So the only way to get this is analyzing the data using own Java code. Apache poi currently will not help for this.
  
  – Axel Richter
  Mar 23 at 10:59
  

  
  
  
  

add a comment | 

0

Recently, I created a pivot table with Apache POI and I want to select the elements of the filters I created.

For example if the filter pivotTable.addReportFilter( columnNumber("K")-1 ); generates a list of 5 items, select 2 items show the sum of quantities.

useSheet("Sales");

AreaReference source = new AreaReference("'Sales'!A1:" + columnLetter( getCurrentSheet().getRow(0).getLastCellNum() ) + (getCurrentSheet().getLastRowNum()+1), SpreadsheetVersion.EXCEL2007);
CellReference position = new CellReference("A1");

XSSFSheet table = (XSSFSheet)createSheet("Sales by Product Line");

XSSFPivotTable pivotTable = table.createPivotTable(source, position);

pivotTable.addRowLabel( columnNumber("J")-1 );
pivotTable.addRowLabel( columnNumber("F")-1 );

pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnNumber("U")-1, "Quantity" );

pivotTable.addReportFilter( columnNumber("K")-1 );
pivotTable.addReportFilter( columnNumber("Q")-1 );
pivotTable.addReportFilter( columnNumber("D")-1 );

try (FileOutputStream fileOut = new FileOutputStream("sales.xlsx")) 
 wbook.write(fileOut);
 catch (IOException ex) 
 Logger.getLogger(Stock.class.getName()).log(Level.SEVERE, null, ex);

At the moment it creates well the tables, only I would lack to select the elements of the filters. I hope you can help me. Thank you.

java excel apache-poi pivot-table

share|improve this question

asked Mar 23 at 9:39

user2551137user2551137

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Currently apache poi is not really rendering the pivot table from the given data. It only prepares the *.xlsx file so that Excel then renders the pivot table from the given data while opening the file. So apache poi's pivot cache is very rudimentary and does not know something about how the filters affect the data. So the only way to get this is analyzing the data using own Java code. Apache poi currently will not help for this.
  
  – Axel Richter
  Mar 23 at 10:59
  

  
  
  
  

add a comment | 

Recently, I created a pivot table with Apache POI and I want to select the elements of the filters I created.

For example if the filter pivotTable.addReportFilter( columnNumber("K")-1 ); generates a list of 5 items, select 2 items show the sum of quantities.

useSheet("Sales");

AreaReference source = new AreaReference("'Sales'!A1:" + columnLetter( getCurrentSheet().getRow(0).getLastCellNum() ) + (getCurrentSheet().getLastRowNum()+1), SpreadsheetVersion.EXCEL2007);
CellReference position = new CellReference("A1");

XSSFSheet table = (XSSFSheet)createSheet("Sales by Product Line");

XSSFPivotTable pivotTable = table.createPivotTable(source, position);

pivotTable.addRowLabel( columnNumber("J")-1 );
pivotTable.addRowLabel( columnNumber("F")-1 );

pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnNumber("U")-1, "Quantity" );

pivotTable.addReportFilter( columnNumber("K")-1 );
pivotTable.addReportFilter( columnNumber("Q")-1 );
pivotTable.addReportFilter( columnNumber("D")-1 );

try (FileOutputStream fileOut = new FileOutputStream("sales.xlsx")) 
 wbook.write(fileOut);
 catch (IOException ex) 
 Logger.getLogger(Stock.class.getName()).log(Level.SEVERE, null, ex);

At the moment it creates well the tables, only I would lack to select the elements of the filters. I hope you can help me. Thank you.

java excel apache-poi pivot-table

share|improve this question

asked Mar 23 at 9:39

user2551137user2551137

1

useSheet("Sales");

AreaReference source = new AreaReference("'Sales'!A1:" + columnLetter( getCurrentSheet().getRow(0).getLastCellNum() ) + (getCurrentSheet().getLastRowNum()+1), SpreadsheetVersion.EXCEL2007);
CellReference position = new CellReference("A1");

XSSFSheet table = (XSSFSheet)createSheet("Sales by Product Line");

XSSFPivotTable pivotTable = table.createPivotTable(source, position);

pivotTable.addRowLabel( columnNumber("J")-1 );
pivotTable.addRowLabel( columnNumber("F")-1 );

pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnNumber("U")-1, "Quantity" );

pivotTable.addReportFilter( columnNumber("K")-1 );
pivotTable.addReportFilter( columnNumber("Q")-1 );
pivotTable.addReportFilter( columnNumber("D")-1 );

try (FileOutputStream fileOut = new FileOutputStream("sales.xlsx")) 
 wbook.write(fileOut);
 catch (IOException ex) 
 Logger.getLogger(Stock.class.getName()).log(Level.SEVERE, null, ex);

share|improve this question

asked Mar 23 at 9:39

user2551137user2551137

1

share|improve this question

asked Mar 23 at 9:39

user2551137user2551137

1

asked Mar 23 at 9:39

user2551137user2551137

1

asked Mar 23 at 9:39

user2551137user2551137

1