We can create custom cell style in excel sheet using POI. We can change text alignment, font color, size etc. Here is my sample design:
Steps to create CellStyle
1. Create Row and column (Cell) using XSSFWorkBook.
2. Create CellStyle using XSSFWorkBook.
3. Create Font and set color, size etc.
4. Set CellStyle into Cell.
1. Create Row and column (Cell) using XSSFWorkBook.
Get the excel file from your system path and create workbook object. You can call this method where ever you need:
public void excelDesignWork() {
try {
FileInputStream input =
new FileInputStream(new File("D:\\TestFile.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(input);
XSSFSheet worksheet = workbook.getSheetAt(0);
createCellStyle(workbook, worksheet);
input.close(); //Close the InputStream
System.out.println("Excel Updated.");
//Override the sheet
exportExcelSheet(workbook);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
}
Create XSSFRow and Cell
public void createCellStyle(XSSFWorkbook workbook, XSSFSheet worksheet) {
Int rowIndex =3;//index of row where you want to create row.
Int cellIndex =2;//index of cell where you want to create cell.
XSSFRow row = worksheet.createRow(rowIndex );
Cell myCell = row.createCell(cellIndex );
}
2. Create CellStyle using XSSFWorkBook.
Creating cellStyle, set alignment and forground color:
//Create Cell Style
CellStyle cellStyleName = workbook.createCellStyle();
cellStyleName.setAlignment(CellStyle.ALIGN_LEFT);
cellStyleName.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyleName.setFillPattern(CellStyle.SOLID_FOREGROUND);
3. Create Font and set color, size etc.
Creating custom font for cellStyle:
Font font = workbook.createFont();
font.setFontName("Calibri Light");
font.setFontHeight((short)225);//equivalent to size 11 in MS sheet.
Color color =new Color(1,169,219);//RGB color
((XSSFFont) font).setColor(new XSSFColor(color));
4. Set CellStyle into Cell.
cellStyleName.setFont(font);
myCell.setCellStyle(cellStyleName);
myCell.setCellValue("My Custom Style");
Complete code:
public void excelDesignWork() {
try {
FileInputStream input =
new FileInputStream(new File("D:\\TestFile.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(input);
XSSFSheet worksheet = workbook.getSheetAt(0);
createCellStyle(workbook, worksheet);
input.close(); //Close the InputStream
System.out.println("Excel Updated.");
//Override the sheet
exportExcelSheet(workbook);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* Design CellStyle
*
*/
public void createCellStyle(XSSFWorkbook workbook, XSSFSheet worksheet) {
Int rowIndex =3;//index of row where you want to create row.
Int cellIndex =2;//index of cell where you want to create cell.
XSSFRow row = worksheet.createRow(rowIndex );
Cell myCell = row.createCell(cellIndex );
//Create Cell Style
CellStyle cellStyleName = workbook.createCellStyle();
cellStyleName.setAlignment(CellStyle.ALIGN_LEFT);
cellStyleName.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyleName.setFillPattern(CellStyle.SOLID_FOREGROUND);
//Create Font
Font font = workbook.createFont();
font.setFontName("Calibri Light");
font.setFontHeight((short)225);//equivalent to size 11 in MS sheet.
Color color =new Color(1,169,219);//RGB color
((XSSFFont) font).setColor(new XSSFColor(color));
//Set cellStyle
cellStyleName.setFont(font);
myCell.setCellStyle(cellStyleName);
myCell.setCellValue("My Custom Style");
}
/**
* Export excel sheet into system location
* @param workbook
*/
public void exportExcelSheet(XSSFWorkbook workbook) {
try {
FileOutputStream out =
new FileOutputStream(new File("D:\\TestFile.xlsx"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
Note: You need to use below jars for above integration :
2. Commons-io-2.4.jar
Please feel free to comment for any clarification or suggestion.
***
No comments:
Post a Comment