java - 使用 Apache POI 将数据写入 xlsx 文件时遇到问题 - Excel 之后会损坏

Question

我删除了很多代码，主要是 try-catch block ，并粘贴在这里，我相信您会很容易查看并建议我解决我面临的问题。我正在 SoapUI 工具的 Groovy 脚本步骤中运行以下脚本。大多数变量数据类型都遵循 Groovy 语言语法。

当我运行此代码时，会创建一个 xlsx 文件，其文件名在 destPath 变量中提到，但大小为 0 KB。当我尝试打开该文件时，我看到消息“Excel 无法打开文件“文件名”，因为文件格式或文件扩展名无效。请验证文件是否已损坏，并且文件扩展名与以下格式匹配:文件。”我看不出我在哪里犯了错误。

def srcPath = "C:\\Test Data\\Test Data2.xlsx"
def destPath = "C:\\Test Data\\Results\\destSheet.xlsx"

def setData = new SetData(log,srcPath,destPath)

log.info setData.setCellData("Result",0,"Testing123")

class  SetData{
def log; //log variable to print values on the console
String srcPath; 
String destPath;
XSSFWorkbook workbook;
OPCPackage pkg;

SetData(log,srcPath,destPath){
this.log = log; 
this.srcPath =srcPath;
this.destPath = destPath}

public String setCellData(String colName,int rowNum, String data){

OPCPackage pkg = OPCPackage.open(srcPath);
Workbook workbook = WorkbookFactory.create(pkg);        
if(rowNum<0)
return "false"; 
int colNum=-1;
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
if(row.getCell(i).getStringCellValue().trim().equals(colName))
colNum=i;           
}
sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum+1);
log.info "Row data " + row  //log.info is equivalent to System.out.Println
in Java,to print values on the console.

XSSFCell cell = row.getCell(colNum);

// cell style
CellStyle cs = workbook.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);
log.info data; //prints Testing123
cell.setCellValue(data); // Set the cell data
log.info "raw cell Value" + "***" +  
cell.getRichStringCellValue().getString() //Prints "Testing123"
log.info "Column index "+ cell.getColumnIndex() // Prints 3
log.info cell.getRowIndex() // Prints 0
fileOut = new FileOutputStream(new File(destPath)); 
workbook.write(fileOut);
fileout.flush();
fileOut.close();
fileOut=null;
pkg.close()
return "true";
}

Answer 1

看起来像是您混合的 XSSFWorkbook 和 Workbook。您还应该检查从源文件中读取的行和单元格是否确实具有这些行/单元格，如果没有则创建它们。以下是用于从另一个工作簿创建 Excel 工作簿并向其写入内容的代码的简化版本:

    String srcPath = "C:\\projects\\source.xlsx";
    String destPath = "C:\\projects\\destSheet.xlsx";

    XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new File(srcPath));        

    XSSFSheet sheet = workbook.getSheetAt(0);

    XSSFRow row = sheet.getRow(1);
    if(row == null) { row = sheet.createRow(1); }

    XSSFCell cell = row.getCell(0);
    if(cell == null) { cell = row.createCell(0); }

    cell.setCellValue("Testing123");

    FileOutputStream fileOut = new FileOutputStream(new File(destPath)); 
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();