Apache poi - 在现有 .xlsx 文件中添加新页面

Question

有一个测试产品列表页面的脚本。在此脚本期间，网页中的数据（以包含名称和价格的两个列表的形式）应两次传输到 .xlsx 文件，每次传输到新工作表。问题是 xlsx 文件在第二次调用后被覆盖。SmartsPopular 工作表消失，取而代之的是 Smarts 3-6 K。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();    }

Answer 1

代码行Workbook wb = new XSSFWorkbook();总是创建一个新的空工作簿。然后您的代码在其中创建一张工作表并将包含一张工作表的工作簿写入文件。所以很明显，结果总是一个文件，其中包含一个工作簿，其中有一个工作表。

您需要检查是否已经有一个文件。如果是这样，则从Workbook该文件创建。然后您将拥有部分填写的工作簿。当然你还需要检查工作簿中是否已经存在工作表名称，因为不能创建两个具有相同名称的工作表。

...

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);

...

因为我认为您的代码中还有其他问题。我不会命名一个main不是真正的主要方法的方法Java。并且创建单元格内容只需要一个循环。所以我将提供一个完整的例子：

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();

 }

}