Here is my code read is working correctly, but when cell value set and updating changes its giving error. Please Help.
这里是我的代码读取正常工作，但是当单元格值设置和更新更改时，它会发生错误。请帮助。
I want to read each row and want to add Status at the and of each status
我希望读取每一行，并希望在每个状态的and处添加状态
Here is my Excel sheet:
这是我的Excel表格:
First Name Last name
vinay kumar
Vijay Sharma
Rahul Jain
Navin Jain
Here is my Code
这是我的代码
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
public class test extends config
{
public static String cellDataString = null;
public static boolean cellDataBoolean = false;
public static double cellDataInteger = 0;
public static String cellvalue = null;
public static int row, col;
public static void test()
{
try
{
String fileNameWithPath = "E:\\FrameworkDocumentation.xlsx";
// Specify the path of file
File src=new File(fileNameWithPath);
// File src=new File(fileName);
// load file
FileInputStream fileInputStream = new FileInputStream(src);
// Load workbook
XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream);
// Load sheet- Here we are loading first sheetonly
XSSFSheet sheet1= workBook.getSheetAt(0);
Cell cell = null;
for( row=0; row<=sheet1.getPhysicalNumberOfRows(); row++)
{
Row currentRow = sheet1.getRow(row);
for(col=0; col<currentRow.getLastCellNum(); col++)
{
Cell currentCell = currentRow.getCell(col, Row.RETURN_NULL_AND_BLANK);
/* if(currentCell==null)
{
System.out.print("\t null");
continue;
}
*/
switch(currentCell.getCellType())
{
case Cell.CELL_TYPE_STRING:
cellvalue = currentCell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellvalue = Double.toString(currentCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
cellvalue = String.valueOf(currentCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
cellvalue = null;
break;
}
System.out.print("\t"+cellvalue);
if(col==2)
{
keyword = cellvalue;
System.out.print("("+keyword+")");
}
else if(col==3)
{
locator_type = cellvalue;
System.out.print("("+locator_type+")");
}
else if(col==4)
{
locator = cellvalue;
System.out.print("("+locator+")");
}
else if (col==5)
{
data = cellvalue;
System.out.print("("+data+")");
}
}
//Update the value of cell
XSSFRow sheetrow = sheet1.getRow(row);
// System.out.println(sheetrow);
if(sheetrow == null)
{
sheetrow = sheet1.createRow(row);
}
//Update the value of cell
cell = sheetrow.getCell(col);
if(cell == null)
{
cell = sheetrow.createCell(col);
}
cell.setCellValue("Pass");
System.out.println("\n current cell value : "+ cell.getStringCellValue());
}
fileInputStream.close();
FileOutputStream outFile =new FileOutputStream(new File(fileNameWithPath));
workBook.write(outFile);
outFile.close();
System.out.println("Write Complete");
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
Error code
错误代码
FAILED: test
java.lang.NullPointerException
at test.test.test(test.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:85)
at org.testng.internal.Invoker.invokeMethod(Invoker.java:639)
at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:816)
at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1124)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:124)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:108)
at org.testng.TestRunner.privateRun(TestRunner.java:773)
at org.testng.TestRunner.run(TestRunner.java:623)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:359)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:354)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:312)
at org.testng.SuiteRunner.run(SuiteRunner.java:261)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1185)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1110)
at org.testng.TestNG.run(TestNG.java:1018)
at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:112)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:205)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:176)
Here is Same code that is running fine
下面是运行良好的代码
public class updateExcel
{
public static void main(String[] args)
{
try {
FileInputStream file = new FileInputStream("E:\\TechartifactExcel.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;
for (int i=0;i<4;i++)
{
for(int j=2; j<4;j++)
{
//Update the value of cell
XSSFRow sheetrow = sheet.getRow(i);
if(sheetrow == null)
{
sheetrow = sheet.createRow(i);
}
//Update the value of cell
cell = sheetrow.getCell(j);
if(cell == null)
{
cell = sheetrow.createCell(j);
}
cell.setCellValue("Pass");
}
}
file.close();
FileOutputStream outFile =new FileOutputStream(new File("E:\\TechartifactExcel.xlsx"));
workbook.write(outFile);
outFile.close();
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
1 个解决方案
#1
1
Simply replace sheet1.getPhysicalNumberOfRows() with sheet1.getLastRowNum() or change the test of your first for loop for a strict comparison.
只需将sheet1.getPhysicalNumberOfRows()替换为sheet1.getLastRowNum()，或者更改第一个for循环的测试，以便进行严格的比较。
for( row=0; row <= sheet1.getLastRowNum(); row++)
or
或
for( row=0; row < sheet1.getPhysicalNumberOfRows(); row++)
#1
1
Simply replace sheet1.getPhysicalNumberOfRows() with sheet1.getLastRowNum() or change the test of your first for loop for a strict comparison.
只需将sheet1.getPhysicalNumberOfRows()替换为sheet1.getLastRowNum()，或者更改第一个for循环的测试，以便进行严格的比较。
for( row=0; row <= sheet1.getLastRowNum(); row++)
or
或
for( row=0; row < sheet1.getPhysicalNumberOfRows(); row++)