这是我的课程,用于读写现有的excel文件。我一直在通过传递filePath和fileName在主类中调用这些函数。
public class NewExcelFile {
Workbook workbook;
/******* Methods *******/
// returns a workbook on giving the excel file's path and name
public Workbook readExcel(String filePath, String fileName) {
// Create object of File class to open xlsx file
File file = new File(filePath + "\\" + fileName);
// Create an object of FileInputStream class to read excel file
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
System.out.println("Error: Unable to find " + fileName + " in "
+ filePath);
e.printStackTrace();
}
Workbook workbook = null;
// Find the file extension by spliting file name in substring and
// getting only extension name
String fileExtensionName = fileName.substring(fileName.indexOf("."));
// Check condition if the file is xlsx file
if (fileExtensionName.equals(".xlsx")) {
// If it is xlsx file then create object of XSSFWorkbook class
try {
workbook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// Check condition if the file is xls file
else if (fileExtensionName.equals(".xls")) {
// If it is xls file then create object of XSSFWorkbook class
try {
workbook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
this.workbook = workbook;
return workbook;
}
public void writeExcel(String filePath, String fileName, String sheetName,
String dataToWrite, int rowno) {
System.out.println("WriteExcel" + filePath + " " + fileName + " "
+ sheetName + " " + dataToWrite + " " + rowno);
Workbook newWorkbook = readExcel(filePath, fileName);
Sheet sheet = newWorkbook.getSheet(sheetName);
System.out.println("Sheet: " + sheet.getSheetName());
Cell resultcell;
******resultcell = sheet.getRow(rowno).createCell(8);
resultcell.setCellType(Cell.CELL_TYPE_STRING);
resultcell.setCellValue(dataToWrite);
CellStyle style = workbook.createCellStyle();
if (dataToWrite == "P") {
style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
style.setFillPattern(CellStyle.ALIGN_FILL);
resultcell.setCellStyle(style);
} else if (dataToWrite == "F") {
style.setFillBackgroundColor(IndexedColors.RED.getIndex());
style.setFillPattern(CellStyle.ALIGN_FILL);
resultcell.setCellStyle(style);
}
// Create an object of FileOutputStream class to create write data in
// excel file
File file = new File(filePath + "\\" + fileName);
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(file);
} catch (FileNotFoundException e) {
System.out.println("File not found");
e.printStackTrace();
}
// write data in the excel file and close output stream
try {
workbook.write(outputStream);
outputStream.close();
} catch (IOException e) {
System.out.println("Error in writing to file");
e.printStackTrace();
}
}
当我使用readExcel在主体中获得工作簿并调用此函数时:
Row row = testScriptsSheet.getRow(24);
我得到正确的行并能够调用该行上的所有函数,但是对于writeExcel()中完全相同的工作表中的完全相同的行,我得到了一个空指针异常(代码中以***开头的行)以上)。 getRow()在这里给我空值。我在这里做错了什么?
此外,我应该将工作簿保留为数据成员,并在需要时执行
myNewExcelFile.workbook
还是将其保留为从主类中的readExcel返回的变量?我也想知道现在发生了什么,因为我没有在readExcel函数的末尾关闭inputStream。无论是否关闭inputStream,我都会遇到相同的错误。
编辑-添加主要功能
public class NewDriver {
public static void main(String[] args) {
System.out.println("Starting the framework");
// initialise the workbook
NewExcelFile testExecution = new NewExcelFile();
testExecution.readExcel(System.getProperty("user.dir") + "\\",
"abc.xlsx");
// initialise sheets of workbook
Sheet testSuiteSheet = testExecution.workbook.getSheet("TestSuite");
Sheet testScriptsSheet = testExecution.workbook.getSheet("TestCases");
Row row = testScriptsSheet.getRow(24);//gives the correct row
//calling writeExcel gives npe in that line
}
}
}
}
最佳答案
从docs的getRow(int)方法中:
返回从0开始的逻辑行(非物理行)。如果你要排
没有定义,您将得到一个空值。也就是说第4行代表
一张纸上的第五行。
因此,当未定义行时,必须首先创建该行,然后再创建该单元格。