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 No comments:
Labels:

JSF2 and Apache POI Export Excel File

For this post, we’re going to make a simple webpage in JSF which takes data from the user and converts them to excel file once submitted. We’ll be utilizing different html form elements in our page that will be written in JSF tags. The following form elements will be used:
  • Text Field
  • Dropdown List
  • Radio Button
  • Check Box
  • File uploader
Below is the sample completed webpage.

Note that the Apache POI version on this tutorial is 3.8.

You can access the source code of this tutorial by clicking the links below:

Creating the view

First, let's create the webpage. As stated, we'll be using different html elements. We'll be saving the file as .xhtml

Below is the head part of the page, we'll stylize the table a little bit. The table will be the container of the form elements.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"
 xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
 xmlns:h="http://xmlns.jcp.org/jsf/html"
 xmlns:f="http://xmlns.jcp.org/jsf/core">

<h:head>
 <title>Dave Export To Excel</title>
 <style>
  table
  {
   border: 1px solid black;
  }
  td
  {
   padding: 10px;
  }
 </style>
</h:head>

The code snippet below are the rest.

 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
<h:body>
 <ui:param name ="_BEAN" value ="#{DaveExportTest}"/>
 <h:form enctype ="multipart/form-data">
  <h2>Export to Excel (Apache POI)</h2>
  <table>
   <tr>
    <td>First Name</td>
    <td colspan ="3"><h:inputText label ="First Name" id ="fname" value ="#{_BEAN.fname}"></h:inputText></td>
   </tr>
   <tr>
    <td>Last Name</td>
    <td colspan ="3"><h:inputText label ="Last Name" id ="lname" value ="#{_BEAN.lname}"></h:inputText></td>
   </tr>
   <tr>
    <td>Birthdate</td>
    <td>
     Day
     <h:selectOneMenu value ="#{_BEAN.day}">
      <f:selectItems value ="#{_BEAN.days}"/>
     </h:selectOneMenu>
    </td>
    <td>
     Month
     <h:selectOneMenu value ="#{_BEAN.month}">
      <f:selectItems value ="#{_BEAN.months}"/>
     </h:selectOneMenu>
    </td>
    <td>
     Year
     <h:selectOneMenu value ="#{_BEAN.year}">
      <f:selectItems value ="#{_BEAN.years}"/>
     </h:selectOneMenu>
    </td>
   </tr>
   <tr>
    <td>Gender</td>
    <td colspan ="3">
     <h:selectOneRadio value ="#{_BEAN.gender}">
      <f:selectItem itemValue ="Male" itemLabel ="Male"/>
      <f:selectItem itemValue ="Female" itemLabel ="Female"/>
     </h:selectOneRadio>
    </td>
   </tr>
   <tr>
    <td>Has Dependent(s)?</td>
    <td colspan ="2">
     <h:selectBooleanCheckbox value ="#{_BEAN.hasDependents}"/>
    </td>
   </tr>
   <tr>
    <td>Upload Image</td>
    <td colspan ="3"><h:inputFile value ="#{_BEAN.image}"/></td>
   </tr>
   <tr>
    <td colspan ="4"><h:commandButton value ="Export Data" action ="#{_BEAN.acceptInfo}"></h:commandButton></td>
   </tr>
  </table>
  
 </h:form>
 
</h:body>
</html>

Don't forget to change the value of param based from your own bean name later: 
<ui:param name ="_BEAN" value ="#{DaveExportTest}"/>


The BEAN

Below are the packages that we'll be needing in our bean. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
import javax.faces.bean.ManagedBean;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.faces.view.ViewScoped;
import javax.servlet.http.Part;

import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Let's make the bean accessible in our view by defining the scope and managed bean name. Place these before the class declaration.
1
2
@ViewScoped
@ManagedBean (name = "DaveExportTest")

After which, it should look like this: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
import java.io.*;

import javax.faces.bean.ManagedBean;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.faces.view.ViewScoped;
import javax.servlet.http.Part;

import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@ViewScoped
@ManagedBean (name = "DaveExportTest")
public class DaveExportTest {

Our Java bean will have 8 properties, with it's appropriate getter and setter methods. These properties and methods corresponds to each of the html form elements we have in our view. Declare the following properties:

1
2
3
4
5
6
7
8
private String fname;
private String lname;
private int day;
private int month;
private int year;
private String gender;
private boolean hasDependents;
private Part image;

Next, we create a method that will generate data for the dropdown (birthdate) we what you see below:





We'll supply 1-31 values for the day, 1-12 values for the month, and 1901-2020 values for the year. To do this, we're going to create a method in the our bean that will return the values as an array. Below are the methods for the dropdowns: 
 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
public int[] getDays() 
{
 int[] days = new int[31];
 for(int i=1; i<=31; i++)
 {
  days[i-1] = i;
 }
 return days;
}

public int[] getMonths()
{
 int[] months = new int[12];
 for(int i=1; i<=12; i++)
 {
  months[i-1] = i;
 }
 return months;
}

public int[] getYears()
{
 int[] years = new int[120];
 for(int i=1; i<=120 ; i++)
 {
  years[i-1] = i + 1900;
 }
 return years;
}

Next, we create the method that will fetch the data from the view when the 'submit' button was pressed. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public void acceptInfo() 
{
 // get uploaded file/image
 String fileName = null;
 InputStream input = null;
 
 try 
 {
  input = image.getInputStream();
  fileName = image.getSubmittedFileName();
 }
 catch(IOException e)
 {
  System.err.println(e);
 }
 
 // debug log if all data were passed successfully
 System.out.println(this.fname + " " + this.lname + " " + this.day + "/" + this.month + "/" + this.year
   + " " + this.gender + " " + this.hasDependents + " " + fileName);
 
 // create excel file and download
 this.createExcelFile(fname, lname, day, month, year, gender, hasDependents, input);
}

Next, let's create the createExcelFile. The method as you can see, will take 8 parameters. Also, we'll going to declare 2 classes as global variable, we can have them above the method. XSSFRow and XSSFCell. See the code below: 
 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
// method for creating and downloading the excel file
XSSFRow row; XSSFCell cell;
private void createExcelFile(String fname, String lname, int day, int month, int year, String gender, boolean hasDependents, InputStream input)
{
 XSSFWorkbook wb = new XSSFWorkbook();
 XSSFSheet sh = wb.createSheet("Page 1");
 
 // first name, last name
 row = sh.createRow(1);
 cell = row.createCell(0);
 cell.setCellValue(fname);
 cell = row.createCell(1);
 cell.setCellValue(lname);
 // birthdate
 row = sh.createRow(2);
 String birthdate = day + "/" + month + "/" + year;
 cell = row.createCell(0);
 cell.setCellValue("Birthdate:");
 cell = row.createCell(1);
 cell.setCellValue(birthdate);
 // gender
 row = sh.createRow(3);
 cell = row.createCell(0);
 cell.setCellValue("Gender:");
 cell = row.createCell(1);
 cell.setCellValue(gender);
 // dependents
 row = sh.createRow(4);
 cell = row.createCell(0);
 cell.setCellValue("Has Dependents?");
 cell = row.createCell(1);
 String dep = hasDependents ? "Yes" : "None";
 cell.setCellValue(dep);
 // place image to sheet
 try
 {
  byte[] bytes = IOUtils.toByteArray(input); // convert image to bytes, needed for the getting the image index method
  int imageIndex = wb.addPicture(bytes, wb.PICTURE_TYPE_JPEG); // get image index, needed for create picture method
  input.close(); // close inputstream
  XSSFDrawing drawing = sh.createDrawingPatriarch(); // create drawing container
  XSSFClientAnchor anchor = new XSSFClientAnchor(); // define top left corner
  anchor.setCol1(5); 
  anchor.setRow1(6);
  anchor.setCol2(17);
  anchor.setRow2(18);
  XSSFPicture pic = drawing.createPicture(anchor, imageIndex); // invoke createPicture and pass the anchor point and image id
  // pic.resize(); // resize the image to its original size
 }
 catch(IOException e)
 {
  System.err.print(e);
 }
 
 // code to download file
 FacesContext facesContext = FacesContext.getCurrentInstance();
 ExternalContext externalContext = facesContext.getExternalContext();
 externalContext.setResponseContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"davefileexport.xlsx\"");
 
 try {
  wb.write(externalContext.getResponseOutputStream());
  facesContext.responseComplete();
  System.out.println("Workbook created");
 }
 catch(IOException e) {
  System.err.println(e);
 }
 
}

Last but not the least, we declare the mandatory getters and setters for our properties.

 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
// property GETTERS
public String getFname()
{
 return fname;
}

public String getLname()
{
 return lname;
}

public int getDay()
{
 return day;
}

public int getMonth()
{
 return month;
}

public int getYear()
{
 return year;
}

public String getGender()
{
 return this.gender;
}

public boolean getHasDependents()
{
 return this.hasDependents;
}

public Part getImage()
{
 return this.image;
}

// property SETTERS
public void setFname(String fname)
{
 this.fname = fname;
}

public void setLname(String lname)
{
 this.lname = lname;
}

public void setDay(int day)
{
 this.day = day;
}

public void setMonth(int month)
{
 this.month = month;
}

public void setYear(int year)
{
 this.year = year;
}

public void setGender(String gender)
{
 this.gender = gender;
}

public void setHasDependents(boolean hasDependents)
{
 this.hasDependents = hasDependents;
}

public void setImage(Part image)
{
 this.image = image;
}

output

That is all! I provided a link below if you wanted to download the source code.
If you find this helpful, consider donating below!

Thank you and have a great day!
at No comments:
Labels:
Subscribe to: Posts (Atom)