HOW TO ADD COLUMN AND SET STATUS AT RUN TIME TO COLUMN IN EXCEL

package com.rearrange;

import java.io.File;
import java.io.FileInputStream;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class addColumnAndAddCellvalueAtRunTime {
static String generatedFilePath = "E:\\EclipseWorkSpace_8thApril\\ReadExcelFile\\TestData26.xlsx";

public static void createExcelColumn(String filePath, int rowNo, String value, String generatedExcelPath)
throws IOException {
FileInputStream fsIP = new FileInputStream(new File(filePath));
XSSFWorkbook wb = new XSSFWorkbook(fsIP);
try {
XSSFSheet worksheet = wb.getSheetAt(0);
Row row = worksheet.getRow(rowNo);
int noOfColumns = worksheet.getRow(rowNo).getLastCellNum();

Cell column = row.getCell(noOfColumns);
if (column == null) {
column = row.createCell(noOfColumns);
}

column.setCellValue(value);
System.out.println("Column name created");

} catch (Exception e) {
e.printStackTrace();
} finally {
FileOutputStream out = new FileOutputStream(new File(generatedExcelPath));
wb.write(out);
wb.close();
System.out.println("Workbook is closed");
}
}

public static void insertValueToACell(String generatedFilePath,int sheetNo,int rowNo,int cellNo, String value) throws IOException {
FileInputStream fsIP = new FileInputStream(new File(generatedFilePath));
XSSFWorkbook wb = new XSSFWorkbook(fsIP);
XSSFSheet worksheet = wb.getSheetAt(sheetNo);
Row row = worksheet.getRow(rowNo);
try {
Cell cell = null;
if (cell == null) {
cell = row.createCell(cellNo);
}
cell = worksheet.getRow(rowNo).getCell(cellNo);
cell.setCellValue(value);
System.out.println("Able to set value to cell");
} finally {
FileOutputStream out = new FileOutputStream(new File(generatedFilePath));
wb.write(out);
wb.close();
System.out.println("Workbook is closed");
}
}

public static void main(String[] args) throws IOException {
createExcelColumn("E:\\EclipseWorkSpace_8thApril\\ReadExcelFile\\TestData.xlsx", 0, "STATUS",
generatedFilePath);
insertValueToACell(generatedFilePath,0,1,4,"PASS");
insertValueToACell(generatedFilePath,0,2,4,"FAIL");
insertValueToACell(generatedFilePath,0,3,4,"PASS");
insertValueToACell(generatedFilePath,0,4,4,"PASS");
insertValueToACell(generatedFilePath,0,5,4,"FAIL");
insertValueToACell(generatedFilePath,0,6,4,"YOU HAVE DONE IT");



}
}

No comments:

About Me

My photo
Pune, Maharastra, India
You can reach me out at : jimmiamrit@gmail.com