FOR SETTING STYLE TO GENERATED 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.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**
* @param filePath
* @param sheetNo
* @param rowNo
* @param cellNo
* @param value
* @param generatedExcelPath
* @throws IOException
*/


@SuppressWarnings("deprecation")
public static void createExcelColumn(String filePath, int sheetNo, int rowNo, int cellNo, String newColumnName,
String generatedExcelPath) throws IOException {
FileInputStream fsIP = new FileInputStream(new File(filePath));
XSSFWorkbook my_workbook = new XSSFWorkbook(fsIP);

// For adding style to newly added column
XSSFCellStyle columnNameStyle = my_workbook.createCellStyle();
columnNameStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
columnNameStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font font = my_workbook.createFont();
font.setColor(IndexedColors.BLACK.getIndex());
columnNameStyle.setFont(font);
columnNameStyle.setAlignment(CellStyle.ALIGN_CENTER);
columnNameStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
columnNameStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
columnNameStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
columnNameStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
font.setFontHeightInPoints((short) 15);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
columnNameStyle.setFont(font);

try {
XSSFSheet my_sheet = my_workbook.getSheetAt(sheetNo);
Row row = my_sheet.getRow(rowNo);
row.setHeight((short) 600);
Cell cell = row.createCell(cellNo);
my_sheet.setColumnWidth(cellNo, 8000);
cell.setCellValue(newColumnName);
cell.setCellStyle(columnNameStyle);
} catch (Exception e) {
e.printStackTrace();
} finally {
FileOutputStream out = new FileOutputStream(new File(generatedExcelPath));
my_workbook.write(out);
my_workbook.close();
out.close();
System.out.println("Successfully generated a new excel sheet with newly added column");
}
}



@SuppressWarnings("deprecation")
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);

// For adding style to test result-PASS
XSSFCellStyle passStyle = wb.createCellStyle();
Font passFont = wb.createFont();
passFont.setColor(IndexedColors.GREEN.getIndex());
passStyle.setFont(passFont);
passStyle.setAlignment(CellStyle.ALIGN_CENTER);
passStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
passStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
passStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
passStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);

// For adding style to test result-FAIL
XSSFCellStyle failStyle = wb.createCellStyle();
Font failFont = wb.createFont();
failFont.setColor(IndexedColors.RED.getIndex());
failStyle.setFont(failFont);
failStyle.setAlignment(CellStyle.ALIGN_CENTER);
failStyle.setAlignment(CellStyle.ALIGN_CENTER);
failStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
failStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
failStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
failStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);

try {
Cell cell = null;
if (cell == null) {
cell = row.createCell(cellNo);
}
cell = worksheet.getRow(rowNo).getCell(cellNo);
if (value.equalsIgnoreCase("PASS")) {
cell.setCellValue(value);
cell.setCellStyle(passStyle);
}

if (value.equalsIgnoreCase("FAIL")) {
cell.setCellValue(value);
cell.setCellStyle(failStyle);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
FileOutputStream out = new FileOutputStream(new File(generatedFilePath));
wb.write(out);
System.out.println("Successfully updated status to new column added");
wb.close();
}
}



public static void main(String[] args) throws IOException {
createExcelColumn("E:\\EclipseWorkSpace_8thApril\\ReadExcelFile\\TestData.xlsx", 0, 0, 4,"TEST EXECUTION RESULT", 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");
}
}