setFillPattern - Introduction
In this tutorial, we will describe how to set specific patterns to your worksheet cell, when creating the workbook with Java and Apache POI. Patterns are set to cell by using setFillPattern method . setFillPattern method is defined in org.apache.poi.hssf.usermodel.HSSFCellStyle. Let us see how to set both XLS and XLSX workbook cells with specific patterns.
XLS - setFillPattern - Java Example Program
The Java program to set specific fill patterns to an .XLS workbook is given below:
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
public class CellPattern {
public static void main(String[] args) throws Exception{
/* Create Workbook and Worksheet */
HSSFWorkbook my_workbook = new HSSFWorkbook();
HSSFSheet my_sheet = my_workbook.createSheet("Cell Font");
/*Let us define five cell patterns */
HSSFCellStyle my_style_1 = my_workbook.createCellStyle();
HSSFCellStyle my_style_2 = my_workbook.createCellStyle();
HSSFCellStyle my_style_3 = my_workbook.createCellStyle();
HSSFCellStyle my_style_4 = my_workbook.createCellStyle();
HSSFCellStyle my_style_5 = my_workbook.createCellStyle();
HSSFCellStyle my_style_6 = my_workbook.createCellStyle();
/* Dotted Cell Pattern */
my_style_1.setFillPattern(HSSFCellStyle.FINE_DOTS);
/* Wide dot pattern */
my_style_2.setFillPattern(HSSFCellStyle.ALT_BARS);
/* Sparse dot pattern */
my_style_3.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
/* Thick horizontal band pattern */
my_style_4.setFillPattern(HSSFCellStyle.THICK_HORZ_BANDS);
/* Thick vertical band pattern */
my_style_5.setFillPattern(HSSFCellStyle.THICK_VERT_BANDS);
/* Thick backward facing diagonals*/
my_style_6.setFillPattern(HSSFCellStyle.THICK_BACKWARD_DIAG);
/* Attach Pattern to Cells */
Row row = my_sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Dotted Cell Pattern");
cell.setCellStyle(my_style_1);
row = my_sheet.createRow(1);
cell = row.createCell(1);
cell.setCellValue("Wide Dot Pattern");
cell.setCellStyle(my_style_2);
row = my_sheet.createRow(2);
cell = row.createCell(2);
cell.setCellValue("Sparse dot pattern");
cell.setCellStyle(my_style_3);
row = my_sheet.createRow(3);
cell = row.createCell(3);
cell.setCellValue("Thick horizontal band pattern");
cell.setCellStyle(my_style_4);
row = my_sheet.createRow(4);
cell = row.createCell(4);
cell.setCellValue("Thick vertical band pattern");
cell.setCellStyle(my_style_5);
row = my_sheet.createRow(5);
cell = row.createCell(5);
cell.setCellValue("Thick backward facing diagonals");
cell.setCellStyle(my_style_6);
/* Write changes to the workbook */
FileOutputStream out = new FileOutputStream(new File("C:\\cell_pattern_example.xls"));
my_workbook.write(out);
out.close();
}
}
We have covered six different patterns that you can apply to your cells. An example output is shown below.
 |
| setFillPattern - Java POI Program Output Example - Set Fill Patterns to Excel Cell |
XLSX - Fill Cell with Pattern - Java Program Example
You can use the same method for working with XLSX sheets also. Let us try six other patterns with XLSX format and examine the output. The complete Java program is provided below:
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
public class CellPatternXLSX {
public static void main(String[] args) throws Exception{
/* Create Workbook and Worksheet XLSX Format */
XSSFWorkbook my_workbook = new XSSFWorkbook();
XSSFSheet my_sheet = my_workbook.createSheet("Cell Font");
/* Get access to XSSFCellStyle */
XSSFCellStyle my_style = my_workbook.createCellStyle();
/*Let us define six cell patterns */
XSSFCellStyle my_style_1 = my_workbook.createCellStyle();
XSSFCellStyle my_style_2 = my_workbook.createCellStyle();
XSSFCellStyle my_style_3 = my_workbook.createCellStyle();
XSSFCellStyle my_style_4 = my_workbook.createCellStyle();
XSSFCellStyle my_style_5 = my_workbook.createCellStyle();
XSSFCellStyle my_style_6 = my_workbook.createCellStyle();
/* Thick forward facing diagonals */
my_style_1.setFillPattern(XSSFCellStyle.THICK_FORWARD_DIAG);
/* Large spots pattern */
my_style_2.setFillPattern(XSSFCellStyle.BIG_SPOTS);
/* Brick like layout */
my_style_3.setFillPattern(XSSFCellStyle.BRICKS);
/* Thin horizontal band pattern */
my_style_4.setFillPattern(XSSFCellStyle.THIN_HORZ_BANDS);
/* Thin vertical band pattern */
my_style_5.setFillPattern(XSSFCellStyle.THIN_VERT_BANDS);
/* diamond pattern*/
my_style_6.setFillPattern(XSSFCellStyle.DIAMONDS);
/* Attach Pattern to Cells */
Row row = my_sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Thick forward facing diagonals");
cell.setCellStyle(my_style_1);
row = my_sheet.createRow(1);
cell = row.createCell(1);
cell.setCellValue("Large spots pattern");
cell.setCellStyle(my_style_2);
row = my_sheet.createRow(2);
cell = row.createCell(2);
cell.setCellValue("Brick like layout");
cell.setCellStyle(my_style_3);
row = my_sheet.createRow(3);
cell = row.createCell(3);
cell.setCellValue("Thin horizontal band pattern");
cell.setCellStyle(my_style_4);
row = my_sheet.createRow(4);
cell = row.createCell(4);
cell.setCellValue("Thin vertical band pattern");
cell.setCellStyle(my_style_5);
row = my_sheet.createRow(5);
cell = row.createCell(5);
cell.setCellValue("diamond pattern");
cell.setCellStyle(my_style_6);
/* Write changes to the workbook */
FileOutputStream out = new FileOutputStream(new File("C:\\Cell_Pattern_Example.xlsx"));
my_workbook.write(out);
out.close();
}
}
Would like to provide two set of outputs, to make the patterns clearly visible.
 |
| Excel Cell Patterns - XLSX - Java POI Example Output - 1 |
 | ||
| Horizontal - vertical - diamond pattern - XLSX - Java POI Example Output - 2 |
There are still some more patterns like SQUARES, THIN_FORWARD_DIAG and THIN_BACKWARD_DIAG. You can try these patterns on the same lines if you want to use them in your code. Hope we have covered setting patterns in Excel cells comprehensively. If you have a question, you can post it in the comments section. Otherwise, see you in a different formatting tip tutorial.
No comments:
Post a Comment