java - 需要将结果写入 Xls 中

Question

我需要在 xls 文件或 csv 文件中写入测试用例名称和结果。我已使用现有文件并将结果写入现有文件，它在行 * cell=row.getCell(0); 上返回 NULL 指针异常； *

下面给出我的代码..

  package com.testCases;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import com.test.utility.PrintResultinExcel;
import com.test.utility.ReadExcelSheet;
import com.test.utility.clearText;
import com.test.utility.clickEvent;
import com.test.utility.globalVariables;
import com.test.utility.selectCheckbox;
import com.test.utility.text;
import com.test.utility.verifyText;
import com.test.utility.writeExcel;

public class SQLInject extends globalVariables{
    public static void sqlinjection() throws InterruptedException, IOException{
        ArrayList<HashMap> data = ReadExcelSheet.readExcel("Sheet1");
        int size = data.size();     
        System.out.println("Total Number of Keywords: "+size);      
        for(int i=0;i<=size-1;i++){
        clickEvent.clickAt("allcustomers_linkText");
        Thread.sleep(2000);
        clickEvent.clickAt("sqlquery_xpath");       
        Thread.sleep(2000);
        String keyword = data.get(i).get("Keywords").toString();
        text.enterText("sql_id", keyword);
        clickEvent.clickAt("sqlsearchbutton_id");
        verifyText.verify("SQL statement does NOT contain valid  keywords.");
        String result="PASS";   
        PrintResultinExcel.excelLog(keyword, result, i);
        }
    }

}

实用程序文件:

package com.test.utility;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class PrintResultinExcel extends globalVariables {


    public static void excelLog(String keyword, String result, int rowNum) {
        try {
            FileInputStream file = new FileInputStream(new File("D:\\testexcel.xls"));

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            // Find empty cell
            HSSFRow row = sheet.getRow(rowNum);
            Cell cell = null;
            cell=row.getCell(0);
            cell.setCellValue(keyword);
            cell=row.getCell(1);
            cell.setCellValue(result);
            // Update the value of cell

            file.close();

            FileOutputStream outFile = new FileOutputStream(new File("D:\\testexcel.xls"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    }

Answer 1

将数据添加到WorkBook并将其保留在内存中。数据添加完成后，将其写入文件。

示例代码

package com.test.utility;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class writeExcel extends globalVariables {

    private static String dest = "D:\\testexcel.xls";
    private static HSSFWorkbook myWorkBook = new HSSFWorkbook();
    private static HSSFSheet mySheet = myWorkBook.createSheet();

    public static void excelLog(String keyword, String result, int rowNum) {

        HSSFRow myRow = null;
        HSSFCell myCell = null;

        myRow = mySheet.createRow(rowNum);

        myCell = myRow.createCell(0);
        myCell.setCellValue(keyword);
        myCell = myRow.createCell(1);
        myCell.setCellValue(result);
    }

    public static void main(String[] args) {
        for (int i = 1;i <= 100;i++) {
            excelLog("Exec" + i, "PASS" + i, i);
        }

        try {
            FileOutputStream out = new FileOutputStream(dest);
            myWorkBook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

编辑:

您的文件/工作簿/工作表/行/单元格可能不存在。所以有必要处理所有的情况。

查看 HSSFSheet.getRow(int rowNum) 的文档.

这里是执行相同操作的更新代码:

package com.test.utility;

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

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;


public class writeExcel {
    public static void main(String[] args) {
        for (int i = 0; i < 100; i++) {
            excelLog("Exec" + i, "PASS" + i, i);
        }
    }

    public static void excelLog(String keyword, String result, int rowNum) {
        String fileName = "testexcel.xls";
        try {
            File f = new File(fileName);
            HSSFWorkbook workbook = getWorkBook(f);
            HSSFSheet sheet = getSheet(workbook);

            HSSFRow row = getRow(sheet, rowNum);
            // Find empty cell
            Cell cell = null;
            cell = getCell(row, 0);
            cell.setCellValue(keyword);
            cell = getCell(row, 1);
            cell.setCellValue(result);
            // Update the value of cell

            workbook.close();

            FileOutputStream outFile = new FileOutputStream(new File(fileName));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    private static Cell getCell(HSSFRow row, int cellNum) {
        Cell cell = row.getCell(cellNum);
        if (cell == null) {
            cell = row.createCell(cellNum);
        }
        return cell;
    }

    private static HSSFRow getRow(HSSFSheet sheet, int rowNum) {
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        return row;
    }

    private static HSSFSheet getSheet(HSSFWorkbook workbook) {
        HSSFSheet sheet = null;
        try {
            sheet = workbook.getSheetAt(0);
        } catch (Exception e) {
            sheet = workbook.createSheet();
        }
        return sheet;
    }

    private static HSSFWorkbook getWorkBook(File f) throws IOException {
        FileInputStream file = null;
        HSSFWorkbook workbook = null;
        if (f.exists()) {
            file = new FileInputStream(f);
            workbook = new HSSFWorkbook(file);
        } else {
            workbook = new HSSFWorkbook();
        }

        return workbook;
    }
}