Wednesday, December 4, 2019

Apache POI Conditional Formatting and Formula

For this post, we're going to make a program that applies conditional formatting and vlookup formula at the same time. We're gonna use a sample spreadsheet below called students.xlsx. You can download the sample spreadsheet by clicking on this link.

student.xlsx


Using vlookup, we're going to display the scores of value h3 which is currently 'Tom' on cells h4:h7. Also we're going to change the font color and the cell background color of those scores that are 75 and above.

Create a new project and class; name it anything you want. For this tutorial, we're going to name our class 'Formulas'. The following libraries are needed for this demo: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule;
import org.apache.poi.xssf.usermodel.XSSFFontFormatting;
import org.apache.poi.xssf.usermodel.XSSFPatternFormatting;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Next, declare the following class properties inside the class: 
1
2
3
4
5
6
7
XSSFWorkbook wb;
XSSFSheet sh;
XSSFCell cell;
XSSFRow row;

File f;
FileInputStream fis;

Next, let's create a constructor for the class just to avoid writing the properties and methods as static. Then instantiate the class inside the main method.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package apachepoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule;
import org.apache.poi.xssf.usermodel.XSSFFontFormatting;
import org.apache.poi.xssf.usermodel.XSSFPatternFormatting;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Formulas {
 
 XSSFWorkbook wb;
 XSSFSheet sh;
 XSSFCell cell;
 XSSFRow row;
 
 File f;
 FileInputStream fis;
 
 // constructor
 Formulas(){
  
 }
 
 public static void main(String[] args) {
  new Formulas();
 }
 
}

Let's create a new method that will open the excel file. Make sure to place the excel file on the same directory of your project. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// method to create and open existing excel file
void openWorkBook() {
 try {
  f = new File("students.xlsx");
  fis = new FileInputStream(f);
  wb = new XSSFWorkbook(fis);
  
  if(f.isFile() && f.exists()) System.out.println("file loaded");
  else System.out.println("unable to load file");
 }
 catch(Exception e){
  System.err.println(e);
 }
}

Next, we're going to create a method that will read the contents of the first sheet. 
1
2
3
4
// method to read the sheet
void readSheet(){
 sh = wb.getSheetAt(0);
}

Next, we make the method that will apply vlookup formula to cells h4:h7. This will get the scores of any student name that are in cell h3. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
// method to apply formula (vlookup)
void applyFormula() {
 row = sh.getRow(3);
 cell = row.createCell(7);
 cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
 cell.setCellFormula("VLOOKUP(H3,A3:E10,2,FALSE)");
 
 row = sh.getRow(4);
 cell = row.createCell(7);
 cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
 cell.setCellFormula("VLOOKUP(H3,A3:E10,3,FALSE)");
 
 row = sh.getRow(5);
 cell = row.createCell(7);
 cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
 cell.setCellFormula("VLOOKUP(H3,A3:E10,4,FALSE)");
 
 row = sh.getRow(6);
 cell = row.createCell(7);
 cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
 cell.setCellFormula("VLOOKUP(H3,A3:E10,5,FALSE)");
}

Next, we create the method that will apply the conditional formatting on cells h4:h7. If the score of the student is greater than 74, we'll make the font green and change the background color of that cell to aqua blue. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
// method to apply conditional formatting
void applyConditionalFormatting() {
 // create condition
 XSSFSheetConditionalFormatting scf = sh.getSheetConditionalFormatting();
 XSSFConditionalFormattingRule cfr = scf.createConditionalFormattingRule(ComparisonOperator.GT, "74");
 // create font pattern
 XSSFFontFormatting fontPattern = cfr.createFontFormatting();
 fontPattern.setFontColorIndex(IndexedColors.GREEN.getIndex());
 // create background color pattern
 XSSFPatternFormatting colorPattern = cfr.createPatternFormatting();
 colorPattern.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
 // specify cell range
 CellRangeAddress[] dataRange = {CellRangeAddress.valueOf("H4:H7")};
 scf.addConditionalFormatting(dataRange,cfr);
}

Last but not the least, we write and finalize the work book. Also, don't for get to call all the methods you just wrote in the constructor in order.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// method to write work book
void writeWorkBook() {
 try {
  FileOutputStream fos = new FileOutputStream(new File("students.xlsx"));
  wb.write(fos);
  fos.close();
 }
 catch(Exception e) {
  System.err.println(e);
 }
}

.... and that's all of it! Below is the whole source code.

Complete Source Code


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
package apachepoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule;
import org.apache.poi.xssf.usermodel.XSSFFontFormatting;
import org.apache.poi.xssf.usermodel.XSSFPatternFormatting;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Formulas {
 
 XSSFWorkbook wb;
 XSSFSheet sh;
 XSSFCell cell;
 XSSFRow row;
 
 File f;
 FileInputStream fis;
 
 // constructor
 Formulas(){
  openWorkBook();
  readSheet();
  applyFormula();
  applyConditionalFormatting();
  writeWorkBook();
 }
 
 // method to create and open existing excel file
 void openWorkBook() {
  try {
   f = new File("students.xlsx");
   fis = new FileInputStream(f);
   wb = new XSSFWorkbook(fis);
   
   if(f.isFile() && f.exists()) System.out.println("file loaded");
   else System.out.println("unable to load file");
  }
  catch(Exception e){
   System.err.println(e);
  }
 }
 
 // method to read the sheet
 void readSheet(){
  sh = wb.getSheetAt(0);
 }
 
 // method to apply formula (vlookup)
 void applyFormula() {
  row = sh.getRow(3);
  cell = row.createCell(7);
  cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  cell.setCellFormula("VLOOKUP(H3,A3:E10,2,FALSE)");
  
  row = sh.getRow(4);
  cell = row.createCell(7);
  cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  cell.setCellFormula("VLOOKUP(H3,A3:E10,3,FALSE)");
  
  row = sh.getRow(5);
  cell = row.createCell(7);
  cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  cell.setCellFormula("VLOOKUP(H3,A3:E10,4,FALSE)");
  
  row = sh.getRow(6);
  cell = row.createCell(7);
  cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
  cell.setCellFormula("VLOOKUP(H3,A3:E10,5,FALSE)");
 }
 
 // method to apply conditional formatting
 void applyConditionalFormatting() {
  // create condition
  XSSFSheetConditionalFormatting scf = sh.getSheetConditionalFormatting();
  XSSFConditionalFormattingRule cfr = scf.createConditionalFormattingRule(ComparisonOperator.GT, "74");
  // create font pattern
  XSSFFontFormatting fontPattern = cfr.createFontFormatting();
  fontPattern.setFontColorIndex(IndexedColors.GREEN.getIndex());
  // create background color pattern
  XSSFPatternFormatting colorPattern = cfr.createPatternFormatting();
  colorPattern.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
  // specify cell range
  CellRangeAddress[] dataRange = {CellRangeAddress.valueOf("H4:H7")};
  scf.addConditionalFormatting(dataRange,cfr);
 }
 
 // method to write work book
 void writeWorkBook() {
  try {
   FileOutputStream fos = new FileOutputStream(new File("students.xlsx"));
   wb.write(fos);
   fos.close();
  }
  catch(Exception e) {
   System.err.println(e);
  }
 }
 
 public static void main(String[] args) {
  new Formulas();
 }
 
}

If you find this helpful, consider donating below!


Thank you and have a great day!
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)