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");
}
}
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:
Post a Comment