Apache poi - adding a new page in existing .xlsx file

Question

There is a script to test the product listing page. During this script, the data from the web page (in the form of two List with the name and price) should be transferred twice to the .xlsx file, each time to a new sheet.

The problem is that the xlsx file is overwritten after the second call. The SmartsPopular sheet disappears and Smarts 3-6 K appears instead.

public class Script
    @Test
    public void script3() throws IOException {
    openSmartphones();
    moreGoodsClick();
    moreGoodsClick();

    FileExcelCreating.main("SmartsPopular", goodsNamesListCreating, goodsPricesListCreating);

    moreGoodsClick();
    moreGoodsClick();

    FileExcelCreating.main("Smarts 3-6 K", goodsNamesListCreating, goodsPricesListCreating);
---------------------------------------------------------------------------------------------------------
public class FileExcelCreating 
    public static void main(String sheetName, List<String> goodsNames, List<String> goodsPrices) throws IOException {

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);

        Row r0 = sheet.createRow(0);
        Cell c0 = r0.createCell(0);
        c0.setCellValue("Name");
        Cell c1 = r0.createCell(1);
        c1.setCellValue("Price");

        Row a;

        List<Integer> goodsPricesInt = new ArrayList<>();
        for(String s : goodsPrices) goodsPricesInt.add(Integer.valueOf(s));

        for (int i = 0; i < goodsNames.size(); i++) {
            a = sheet.createRow(i+1);
            String name = goodsNames.get(i);
            a.createCell(0).setCellValue(name);
        }

        for (int j = 0; j < goodsPricesInt.size(); j++) {
            a = sheet.getRow(j+1);
            Integer price = goodsPricesInt.get(j);
            a.createCell(1).setCellValue(price);
        }

        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:B" + (goodsPricesInt.size())));

        FileOutputStream outputStream = new FileOutputStream  ("/FilesTXT/Smartphones.xlsx");

        wb.write(outputStream);
        outputStream.close();
    }

Axel Richter · Accepted Answer · 2019-08-19T06:56:24.463

The code line Workbook wb = new XSSFWorkbook(); always creates a new empty workbook. Then your code creates one sheet in it and writes that workbook having one sheet into the file. So it is pretty clear, that the result always will be a file having a workbook having one sheet in it.

You needs check whether there is already a file. If so, then create the Workbook from that file. Then you will have the partially filled workbook. Of course then you needs also check whether the sheet name already exists in the workbook because one cannot creating two sheets having the same name.

...
private static final String fileName = "./FilesTXT/Smartphones.xlsx";
...

...
Workbook wb = null;
File file = new File(fileName);
if(file.exists()) {
 wb = WorkbookFactory.create(new FileInputStream(file));
} else {
 wb = new XSSFWorkbook();
}  

Sheet sheet = wb.getSheet(sheetName); if(sheet == null) sheet = wb.createSheet(sheetName);
...

Since there are other issues in your code in my opinion. I would not name a method main which is not really a main method in terms of Java. And there is only one loop necessary for creating the cell contents. So I will provide a complete example:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.List;
import java.util.ArrayList;

public class TestScript {
 public static void main(String[] args) throws Exception {

  List<String> goodsNamesListCreating = new ArrayList<String>();
  goodsNamesListCreating.add("SmartsPopular Name 1");
  goodsNamesListCreating.add("SmartsPopular Name 2");
  goodsNamesListCreating.add("SmartsPopular Name 3");

  List<String> goodsPricesListCreating = new ArrayList<String>();
  goodsPricesListCreating.add("123");
  goodsPricesListCreating.add("456");
  goodsPricesListCreating.add("789");

  FileExcelCreating.create("SmartsPopular", goodsNamesListCreating, goodsPricesListCreating);

  goodsNamesListCreating = new ArrayList<String>();
  goodsNamesListCreating.add("Smarts 3-6 K Name 1");
  goodsNamesListCreating.add("Smarts 3-6 K Name 2");
  goodsNamesListCreating.add("Smarts 3-6 K Name 3");
  goodsNamesListCreating.add("Smarts 3-6 K Name 4");

  goodsPricesListCreating = new ArrayList<String>();
  goodsPricesListCreating.add("321");
  goodsPricesListCreating.add("654");
  goodsPricesListCreating.add("987");

  FileExcelCreating.create("Smarts 3-6 K", goodsNamesListCreating, goodsPricesListCreating);

 }
}

class FileExcelCreating {

 private static final String fileName = "./FilesTXT/Smartphones.xlsx";

 public static void create(String sheetName, List<String> goodsNames, List<String> goodsPrices) throws Exception {

  Workbook wb = null;
  File file = new File(fileName);
  if(file.exists()) {
   wb = WorkbookFactory.create(new FileInputStream(file));
  } else {
   wb = new XSSFWorkbook();
  }  

  Sheet sheet = wb.getSheet(sheetName); if(sheet == null) sheet = wb.createSheet(sheetName);

  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Name");
  cell = row.createCell(1);
  cell.setCellValue("Price");

  List<Integer> goodsPricesInt = new ArrayList<>();
  for(String s : goodsPrices) goodsPricesInt.add(Integer.valueOf(s));

  for (int i = 0; i < goodsNames.size(); i++) {
   row = sheet.createRow(i+1);
   String name = goodsNames.get(i);
   row.createCell(0).setCellValue(name);
   Integer price = (i < goodsPricesInt.size())?goodsPricesInt.get(i):null;
   if (price != null) row.createCell(1).setCellValue(price);
  }

  sheet.setAutoFilter(CellRangeAddress.valueOf("A1:B" + goodsNames.size()));

  FileOutputStream outputStream = new FileOutputStream(file);

  wb.write(outputStream);
  outputStream.close();
  wb.close();
 }
}

Apache poi - adding a new page in existing .xlsx file

1 Answers1