使用Apache POI - Excel将数据写入xlsx文件时遇到的问题随后会被破坏

I have removed a lot of code, mainly try-catch blocks and pasted here what I believe would be easy for you to look at and advise me on resolving the issue I am facing. I am running the below script in a Groovy Script step in SoapUI tool. Most of the variable datatypes are following the Groovy language syntax.

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

When I run this code, a xlsx file with file name mentioned in the destPath variable is created but with a size of 0 KB. When I try to open the file, I am seeing the message "Excel cannot open the file "filename" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." I could not see where I am making mistake.

当我运行此代码时，将创建一个带有destPath变量中提到的文件名的xlsx文件，但大小为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";
}

1 个解决方案

#1

Looks like your mixing XSSFWorkbook and Workbook. You should also check to see if the rows and cells you're reading from your source file actually have those rows/cells and create them if they don't. Here's a simplified version of your code for creating an Excel workbook from another workbook and writing to it:

看起来你的混合XSSFWorkbook和工作簿。您还应该检查从源文件中读取的行和单元格是否实际拥有这些行/单元格，如果没有，则创建它们。这里有一个简化版的代码，用于从另一个工作簿创建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();