Create a blank word document with Apache POI

I try to create a blank word document, so I read tutorials about Apache POI, but the installation process failed.

I have added libs file in my project's lib folder, but I got an error of type Duplicate file exception.

The duplicated file was LICENCE.

As I read on other users' question, I add this in my build gradle :

packagingOptions {     exclude 'META-INF/LICENSE' } 

But now, I get this error :

Warning:warning: Ignoring InnerClasses attribute for an anonymous inner class (org.apache.xmlbeans.XmlBeans$1) that doesn't come with an associated EnclosingMethod attribute. This class was probably produced by a compiler that did not target the modern .class file format. The recommended solution is to recompile the class from source, using an up-to-date compiler and without specifying any "-target" type options. The consequence of ignoring this warning is that reflective operations on this class will incorrectly indicate that it is not an inner class.

Error:Uncaught translation error: java.lang.IllegalArgumentException: already added: Lorg/apache/xmlbeans/xml/stream/Location;

Thanks for your help !

0 Answers

Read More

Why can't I link one workbook to another in Apache POI?

I have one workbook that has some data in it. I am taking that workbook and creating another workbook with a line chart in it based off of the data in the other workbook. The code runs fine, but whenever I open up the graph file, I get the warning We can't update some of the links in your workbook right now. If I click the Edit Links... button in the warning menu, it shows that the data workbook cannot be found. If I click on Change Source..., and select the proper workbook, it then works fine. Why is this? Can POI not retain the link between the two files?

My code:

To create the data workbook:

public static XSSFWorkbook createDataSpreadsheet(String name, long[] data) {     XSSFWorkbook workbook = new XSSFWorkbook();     XSSFSheet sheet = workbook.createSheet(name);      int rowNumber = 0;     for(int i = 1; i < data.length + 1; i++) {         Row row = sheet.createRow(rowNumber++);          int columnNumber = 0;         row.createCell(columnNumber++).setCellValue(i);         row.createCell(columnNumber++).setCellValue(data[i - 1]);     }      return workbook; } 

To create the graph workbook:

public static XSSFWorkbook createLineChart(String name, XSSFWorkbook data) {     XSSFWorkbook workbook = new XSSFWorkbook();      XSSFSheet sheet = workbook.createSheet(name);      XSSFDrawing drawing = sheet.createDrawingPatriarch();     XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);     XSSFChart lineChart = drawing.createChart(anchor);      XSSFChartLegend legend = lineChart.getOrCreateLegend();     legend.setPosition(LegendPosition.BOTTOM);       LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();          ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);     ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);     leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);      XSSFSheet dataSheet = data.getSheetAt(0);     ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));     ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));      LineChartSeries chartSeries = chartData.addSeries(xData, yData);     chartSeries.setTitle("A title");      lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });      return workbook; } 

1 Answers

Answers 1

Creation of external links in XSSF is not well implemented until now. There is ExternalLinksTable but if you look at the Uses of this Class then you will see that there is only provided reading those external links but not creating and writing.

So we need working with the low level objects. And we need knowledge about the internal dependencies of this external links within the Office OpenXML *.xlsx ZIP-archive.

The following works as long both workbooks are stored in the same directory.

The code is mainly your provided code added with a method for creating a external link to a sheet in another workbook. This method is using low level objects and is not very general usable, but it should show the principle.

Other changings to your code are commented as well.

import java.io.*;  import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.charts.*; import org.apache.poi.ss.util.CellRangeAddress;  import org.apache.poi.xssf.usermodel.*; import org.apache.poi.xssf.model.ExternalLinksTable;  import org.apache.poi.openxml4j.opc.*; import org.apache.poi.POIXMLDocumentPart;  import org.openxmlformats.schemas.spreadsheetml.x2006.main.ExternalLinkDocument;  import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;  public class CreateExcelLineChartDataAnotherWorkbook {   private static String datawbname = "DataWB.xlsx";  private static String chartwbname = "ChartWB.xlsx";   public CreateExcelLineChartDataAnotherWorkbook() throws Exception {   Workbook datawb = createDataSpreadsheet("ChartDataSheet");   saveWorkbook(datawb, "/home/axel/Dokumente/"+datawbname);    Workbook chartwb = createLineChart("ChartSheet", (XSSFWorkbook)datawb);   saveWorkbook(chartwb, "/home/axel/Dokumente/"+chartwbname);  }   //your method only partially changed to have sample data  public XSSFWorkbook createDataSpreadsheet(String name) {   Workbook workbook = new XSSFWorkbook();   Sheet sheet = workbook.createSheet(name);    int rowNumber = 0;   for(int i = 0; i < 20; i++) {    Row row = sheet.createRow(rowNumber++);     int columnNumber = 0;    row.createCell(columnNumber++).setCellValue(Math.PI*i/10*2);    row.createCell(columnNumber++).setCellValue(Math.sin(Math.PI*i/10*2));   }    return (XSSFWorkbook)workbook;  }   //method for saving the workbooks  public void saveWorkbook(Workbook wb, String path) throws Exception {   wb.write(new FileOutputStream(path));   wb.close();  }   //your method changes are commented  public XSSFWorkbook createLineChart(String name, XSSFWorkbook data) throws Exception {   Workbook workbook = new XSSFWorkbook();    //create the external link to datawbname   int extwbid = 1;   createExternalLinkToWorksheet((XSSFWorkbook)workbook, datawbname, "ChartDataSheet", "rId"+extwbid);    Sheet sheet = workbook.createSheet(name);    Drawing drawing = sheet.createDrawingPatriarch();   ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);   Chart lineChart = drawing.createChart(anchor);    ChartLegend legend = lineChart.getOrCreateLegend();   legend.setPosition(LegendPosition.BOTTOM);     LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();        ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);   ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);    Sheet dataSheet = data.getSheetAt(0);   ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));   ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));    LineChartSeries chartSeries = chartData.addSeries(xData, yData);   chartSeries.setTitle("A title");    lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });    //since dataSheet is an external sheet, the formula in the org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef   //must be prefixed with [1], where 1 is the Id of the linked workbook    String catref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().getF();   ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().setF("[" + extwbid + "]" + catref);   String valref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().getF();   ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().setF("[" + extwbid + "]" + valref);    return (XSSFWorkbook)workbook;  }   //method for creating a external link to a sheet in another workbook  public void createExternalLinkToWorksheet(XSSFWorkbook workbook, String wbname, String sheetname, String rIdExtWb) throws Exception {   OPCPackage opcpackage = workbook.getPackage();    //creating /xl/externalLinks/externalLink1.xml having link to externalBook with external sheetName   PackagePartName partname = PackagingURIHelper.createPartName("/xl/externalLinks/externalLink1.xml");   PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml");   POIXMLDocumentPart externallinkstable = new POIXMLDocumentPart(part) {    @Override    protected void commit() throws IOException {     PackagePart part = getPackagePart();     OutputStream out = part.getOutputStream();     try {      ExternalLinkDocument doc = ExternalLinkDocument.Factory.parse(       "<externalLink xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"      +"<externalBook xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\""+ rIdExtWb + "\">"      +"<sheetNames><sheetName val=\"" + sheetname + "\"/></sheetNames>"      +"</externalBook>"      +"</externalLink>"      );      doc.save(out, DEFAULT_XML_OPTIONS);      out.close();     } catch (Exception ex) {      ex.printStackTrace();     };     }   };   //creating the relation to the external workbook in /xl/externalLinks/_rels/externalLink1.xml.rels   PackageRelationship packrelship = part.addRelationship(new java.net.URI(wbname), TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", rIdExtWb);    //creating the relation to /xl/externalLinks/externalLink1.xml in /xl/_rels/workbook.xml.rels   String rIdExtLink = "rId" + (workbook.getRelationParts().size()+1);   workbook.addRelation(rIdExtLink, XSSFRelation.EXTERNAL_LINKS, externallinkstable);    //creating the <externalReferences><externalReference .../> in /xl/workbook.xml   workbook.getCTWorkbook().addNewExternalReferences().addNewExternalReference().setId(rIdExtLink);   }   public static void main(String[] args) throws Exception {   CreateExcelLineChartDataAnotherWorkbook mainObject = new CreateExcelLineChartDataAnotherWorkbook();  }  } 

---

My new code provides a class MyXSSFWorkbook which extends XSSFWorkbook by a method for creating ExternalLinksTable for linked workbook and sheet. This code really creates a ExternalLinksTable and it uses reflection for adding this ExternalLinksTable to the list of ExternalLinksTables in the XSSFWorkbook. So it will be getable in further using the workbook.

The method only needs the names of the linked workbook and the linked sheet. It manages Ids itself. It returns the Id of the ExternalLinksTable (as the 1 in /xl/externalLinks/externalLink1.xml. So this Id can be used as external workbook reference in formulas (as the 1 in [1]ChartDataSheet!$A$1:$A$20).

import java.io.*;  import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.charts.*; import org.apache.poi.ss.util.CellRangeAddress;  import org.apache.poi.xssf.usermodel.*; import org.apache.poi.xssf.model.ExternalLinksTable;  import org.apache.poi.openxml4j.opc.*; import org.apache.poi.POIXMLDocumentPart;  import org.openxmlformats.schemas.spreadsheetml.x2006.main.ExternalLinkDocument; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReferences;  import static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;  import java.lang.reflect.Field;  import java.util.List; import java.util.ArrayList;  public class CreateExcelLineChartExternalLinksTable {   private static String datawbname = "DataWB.xlsx";  private static String chartwbname = "ChartWB.xlsx";   public CreateExcelLineChartExternalLinksTable() throws Exception {   Workbook datawb = createDataSpreadsheet("ChartDataSheet");   saveWorkbook(datawb, "/home/axel/Dokumente/"+datawbname);    Workbook chartwb = createLineChart("ChartSheet", (XSSFWorkbook)datawb);   saveWorkbook(chartwb, "/home/axel/Dokumente/"+chartwbname);  }   //your method only partially changed to have sample data  public XSSFWorkbook createDataSpreadsheet(String name) {   Workbook workbook = new XSSFWorkbook();   Sheet sheet = workbook.createSheet(name);    int rowNumber = 0;   for(int i = 0; i < 20; i++) {    Row row = sheet.createRow(rowNumber++);     int columnNumber = 0;    row.createCell(columnNumber++).setCellValue(Math.PI*i/10*2);    row.createCell(columnNumber++).setCellValue(Math.sin(Math.PI*i/10*2));   }    return (XSSFWorkbook)workbook;  }   //method for saving the workbooks  public void saveWorkbook(Workbook wb, String path) throws Exception {   wb.write(new FileOutputStream(path));   wb.close();  }   //your method changes are commented  public XSSFWorkbook createLineChart(String name, XSSFWorkbook data) throws Exception {   Workbook workbook = new MyXSSFWorkbook();    Sheet sheet = workbook.createSheet(name);    Drawing drawing = sheet.createDrawingPatriarch();   ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 15, 15);   Chart lineChart = drawing.createChart(anchor);    ChartLegend legend = lineChart.getOrCreateLegend();   legend.setPosition(LegendPosition.BOTTOM);     LineChartData chartData = lineChart.getChartDataFactory().createLineChartData();        ChartAxis bottomAxis = lineChart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);   ValueAxis leftAxis = lineChart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);    Sheet dataSheet = data.getSheetAt(0);   ChartDataSource<Number> xData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 0, 0));   ChartDataSource<Number> yData = DataSources.fromNumericCellRange(dataSheet, new CellRangeAddress(0, dataSheet.getLastRowNum(), 1, 1));    LineChartSeries chartSeries = chartData.addSeries(xData, yData);   chartSeries.setTitle("A title");    lineChart.plot(chartData, new ChartAxis[] { bottomAxis, leftAxis });    //create the ExternalLinksTable for the linked workbook and sheet   int extLinksId = ((MyXSSFWorkbook)workbook).createExternalLinksTableWbSheet(datawbname, "ChartDataSheet"); System.out.println(((XSSFWorkbook)workbook).getExternalLinksTable());    //since dataSheet is an external sheet, the formula in the org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef   //must be prefixed with [1], where 1 is the Id of the linked workbook    String catref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().getF();   ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getCat().getNumRef().setF("["+extLinksId+"]" + catref);   String valref = ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().getF();   ((XSSFChart)lineChart).getCTChart().getPlotArea().getLineChartArray(0).getSerArray(0).getVal().getNumRef().setF("["+extLinksId+"]" + valref);    return (XSSFWorkbook)workbook;  }   public static void main(String[] args) throws Exception {   CreateExcelLineChartExternalLinksTable mainObject = new CreateExcelLineChartExternalLinksTable();  }   //class which extends XSSFWorkbook and provides a method for creating ExternalLinksTable for linked workbook and sheet  private class MyXSSFWorkbook extends XSSFWorkbook {    //method for creating ExternalLinksTable for linked workbook and sheet   //returns the Id of this ExternalLinksTable   int createExternalLinksTableWbSheet(String wbname, String sheetname) throws Exception {     List<ExternalLinksTable> elternallinkstablelist = getExternalLinksTable();    int extLinksId = 1;    if (elternallinkstablelist != null) extLinksId = elternallinkstablelist.size()+1;     OPCPackage opcpackage = getPackage();     //creating /xl/externalLinks/externalLink1.xml having link to externalBook with external sheetName    PackagePartName partname = PackagingURIHelper.createPartName("/xl/externalLinks/externalLink"+extLinksId+".xml");    PackagePart part = opcpackage.createPart(partname, "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml");     OutputStream out = part.getOutputStream();    ExternalLinkDocument doc = ExternalLinkDocument.Factory.parse(      "<externalLink xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"     +"<externalBook xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" r:id=\"rId1\">"     +"<sheetNames><sheetName val=\"" + sheetname + "\"/></sheetNames>"     +"</externalBook>"     +"</externalLink>"    );    doc.save(out, DEFAULT_XML_OPTIONS);    out.close();     //creating the relation to the external workbook in /xl/externalLinks/_rels/externalLink1.xml.rels    PackageRelationship packrelship = part.addRelationship(new java.net.URI(wbname), TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath", "rId1");     ExternalLinksTable externallinkstable = new ExternalLinksTable(part);     //creating the relation to /xl/externalLinks/externalLink1.xml in /xl/_rels/workbook.xml.rels    String rIdExtLink = "rId" + (getRelationParts().size()+1);    addRelation(rIdExtLink, XSSFRelation.EXTERNAL_LINKS, externallinkstable);     //creating the <externalReferences><externalReference .../> in /xl/workbook.xml    CTExternalReferences externalreferences = getCTWorkbook().getExternalReferences();    if (externalreferences == null) externalreferences = getCTWorkbook().addNewExternalReferences();    externalreferences.addNewExternalReference().setId(rIdExtLink);     Field externalLinksField = XSSFWorkbook.class.getDeclaredField("externalLinks");     externalLinksField.setAccessible(true);    @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs    List<ExternalLinksTable> externalLinks = (ArrayList<ExternalLinksTable>)externalLinksField.get(this);    if (externalLinks == null) {     externalLinks = new ArrayList<ExternalLinksTable>();     externalLinks.add(externallinkstable);     externalLinksField.set(this, externalLinks);    } else {     externalLinks.add(externallinkstable);    }     return extLinksId;   }  } } 

Read More

How to iterate by all not empty rows without using break or continue statement?

I want to get data from Excel file. I'm using while loop, iterator and hasNext() method to go by all rows. My problem: sometimes after rows with data there are empty rows (propably with cell type string and value "" or null), which I don't want to iterate by. So I added method isCellEmpty():

public static boolean isCellEmpty(final Cell cell) {     if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {         return true;     }     if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty()) {         return true;     }     return false; } 

and added it after starring while loop in main method:

while (rowIterator.hasNext()) {     row = rowIterator.next();     if (isCellEmpty(row.getCell(2))) {         break;     }     // some code ... } 

But now I have a break statement. How can I iterate by all not empty rows without using break or continue? Now (with break) my algorithm is working properly - I'm getting data which I need. I'm just wondering if it's possible to write code without break or continue.

6 Answers

Answers 1

If you want to keep your while loop, and avoid a break, the easiest is probably a status boolean, eg

boolean inData = true; while (rowIterator.hasNext() && inData) {    row = rowIterator.next();    if (row == null || isCellEmpty(row.getCell(2))) {       inData = false;    } else {       // Use the row    } } 

Otherwise, I'd suggest reading the Apache POI documentation on iterating over rows and cells, there are other approaches you could take which might work even better!

Oh, and don't forget that rows can be null, so you need to check that before you try fetching the cell

Answers 2

Not sure if I get the question right, are you looking for something like this?

Row row; while (rowIterator.hasNext()            && !isCellEmpty((row = rowIterator.next()).getCell(2))) {     // do something with row } 

This would process all rows until it finds an empty cell and ends the loop.

Answers 3

Looks like POI have no enanchements or features to iterate just over non-empty rows.

They already mentioned this subject. Look at Apache POI HSSF+XSSF sections Iterate over rows and cells and Iterate over cells, with control of missing / blank cells

Instead of implementing whiles or loops I use Apache Commons. Due to POI works with Iterators, you could use Apache IteratorUtils.

This Utils comes with Apache-common-collections which probably is at your classpath because its very common to find it as 3th party lib's dependency.

In order to make your code clean and polite. This would be the solution

import org.apache.commons.collections.Predicate;  public class ValidRowPredicate implements Predicate{     @Override     public boolean evaluate(Object object) {        Row row = (Row) object;        Cell cell = row.getCell(2);        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {           return false;        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING &&                    cell.getStringCellValue().isEmpty()) {          return false;       }       return true;     } } 

Then in your main code:

Iterator<Row> rawIterator = rowIterator; Iterator<Row> cleanIterator = IteratorUtils.filteredIterator(rawIterator , new ValidRowPredicate());  while(cleanIterator.hasNext()){    Row row = cleanIterator.next();    // some code }  

You may thing that we are looping over the book 2 times. Yes we are. But look at its benefits. In this way we made portable the validation of empty cell 2 so we can reproduce it whenever is needed at any place in the code.

We also got a valid Iterator which have only valid rows so me don't need to we worry about blanks or empties. We can move this iterator through any other component or layer and we will be sure that target wont need to check it out again.

Predicates gives lot of possibilites. Like chaining Predicats by inheritance, execute predicates standalone, It can be parametrized, ...

Its cost is in infact, the first loop all over the main Iterator. But the result worth it.

IteratorUtils as CollectionUtils are really good utils and we often have'm into our classpath and nobody dare to used it. The question is Why not?

Hope it helps!

Answers 4

Change your function isCellEmpty() use switch than a nested if-else.

public static boolean isCellEmpty(final Cell cell) {    switch(cell.getCellType()){        case Cell.CELL_TYPE_BLANK :        case cell.CELL_TYPE_STRING :           if(StringUtils.isBlank(cell.getCellValue())                return true;           else                return false;          break;        default :                return false;      break;                   } 

}

Now use this code

boolean hasCellData= true; while (rowIterator.hasNext() && hasCellData) {    row = rowIterator.next();  //iterate through each rows.    if (row == null || isCellEmpty(row.getCell(2))) {       hasData = false;    } else {       //if row contains data then do your stuffs.    } } 

This while (rowIterator.hasNext() && hasCellData) loop will stop at point of time if a row contains null values. It never check whether beyond of this row have some data.

Example :- Suppose In your sheet data is filled from row 1 to 50 but in between there is a row number 30 which is blank then this will not Iterate after row number 30.

Thanks.

Answers 5

You have several options to exit a loop without using break:

• Use some other control flow, e.g. return, throw;

• Add an extra condition to the loop guard:

  boolean shouldContinue = true; while (shouldContinue && rowIterator.hasNext()) {   row = rowIterator.next();   if (isCellEmpty(...)) {     shouldContinue = false;   } } 

• Exhaust the iterator inside the loop body:

  while (rowIterator.hasNext()) {   row = rowIterator.next();   if (isCellEmpty(...)) {     while (rowIterator.hasNext()) rowIterator.next();   } } 

Or just use break. It's not so bad.

Answers 6

Might be too simplistic but wouldn't the following be enough?

while (rowIterator.hasNext()) {     row = rowIterator.next();     if (!isCellEmpty(row.getCell(2))) {         // some code ...     } } 

Read More