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
here is my excel sheet
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();
}
}
}
Aucun commentaire:
Enregistrer un commentaire