If you are building a software for HR or finance domain, there is usually requirement for generating excel reports which are usually across management levels. Apart from reports, you can expect input data for application coming in form of excel sheets and application is expected to support it. These are many open source APIs to handle such scenarios.
Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.
In this post, I am discussing some common activities required to do in real life application.
Sections in this post:
Apache POI runtime dependencies
Some useful common classes
Writing an excel file
Reading an excel file
Using formulas in excel sheet
Formatting the cells
Sourcecode download
Apache POI runtime dependencies
If you are working on a maven project, you can include the POI dependency in pom.xml file using this:
|
1
2
3
4
5
|
<
dependency
>
<
groupId
>org.apache.poi</
groupId
>
<
artifactId
>poi</
artifactId
>
<
version
>3.9</
version
>
</
dependency
>
|
If you are not using maven, then you can download maven jar files from POI download page. Include following jar files minimum to run the sample code:
- dom4j-1.6.1.jar
- poi-3.9-20121203.jar
- poi-ooxml-3.9-20121203.jar
- poi-ooxml-schemas-3.9-20121203.jar
- xmlbeans-2.3.0.jar
Some useful POI classes
Apache POI main classes usually start with either HSSF, XSSF or SXSSF.
- HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. e.g. HSSFWorkbook, HSSFSheet.
- XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook,XSSFSheet.
- SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.
Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.
Another useful class FormulaEvaluator is used to evaluate the formula cells in excel sheet.
A wide range of classes like CellStyle, BuiltinFormats, ComparisonOperator, ConditionalFormattingRule, FontFormatting,IndexedColors, PatternFormatting, SheetConditionalFormatting etc. are used when you have to add formatting in a sheet, mostly based on some rules.
We will see the usage of above classes in coming examples.
Writing an excel file
I am taking this example first so that we can reuse the excel sheet created by this code to read back in next example.
Writing a file using POI is very simple and involve following steps:
- Create a workbook
- Create a sheet in workbook
- Create a row in sheet
- Add cells in sheet
- Repeat step 3 and 4 to write more data
It seems very simple, right? Lets have a look at the code doing these steps:
|
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
|
package
com.howtodoinjava.demo.poi;
//import statements
public
class
WriteExcelDemo
{
public
static
void
main(String[] args)
{
//Blank workbook
XSSFWorkbook workbook =
new
XSSFWorkbook();
//Create a blank sheet
XSSFSheet sheet = workbook.createSheet(
"Employee Data"
);
//This data needs to be written (Object[])
Map<String, Object[]> data =
new
TreeMap<String, Object[]>();
data.put(
"1"
,
new
Object[] {
"ID"
,
"NAME"
,
"LASTNAME"
});
data.put(
"2"
,
new
Object[] {
1
,
"Amit"
,
"Shukla"
});
data.put(
"3"
,
new
Object[] {
2
,
"Lokesh"
,
"Gupta"
});
data.put(
"4"
,
new
Object[] {
3
,
"John"
,
"Adwards"
});
data.put(
"5"
,
new
Object[] {
4
,
"Brian"
,
"Schultz"
});
//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int
rownum =
0
;
for
(String key : keyset)
{
Row row = sheet.createRow(rownum++);
Object [] objArr = data.get(key);
int
cellnum =
0
;
for
(Object obj : objArr)
{
Cell cell = row.createCell(cellnum++);
if
(obj
instanceof
String)
cell.setCellValue((String)obj);
else
if
(obj
instanceof
Integer)
cell.setCellValue((Integer)obj);
}
}
try
{
//Write the workbook in file system
FileOutputStream out =
new
FileOutputStream(
new
File(
"howtodoinjava_demo.xlsx"
));
workbook.write(out);
out.close();
System.out.println(
"howtodoinjava_demo.xlsx written successfully on disk."
);
}
catch
(Exception e)
{
e.printStackTrace();
}
}
}
|
Reading an excel file
Reading an excel file is also very simple if we divide this in steps.
- Create workbook instance from excel sheet
- Get to the desired sheet
- Increment row number
- iterate over all cells in a row
- repeat step 3 and 4 until all data is read
Lets see all above steps in code. I am writing the code to read the excel file created in above example.
|
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
|
package
com.howtodoinjava.demo.poi;
//import statements
public
class
ReadExcelDemo
{
public
static
void
main(String[] args)
{
try
{
FileInputStream file =
new
FileInputStream(
new
File(
"howtodoinjava_demo.xlsx"
));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook =
new
XSSFWorkbook(file);
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(
0
);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while
(rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while
(cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch
(cell.getCellType())
{
case
Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() +
"\t"
);
break
;
case
Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() +
"\t"
);
break
;
}
}
System.out.println(
""
);
}
file.close();
}
catch
(Exception e)
{
e.printStackTrace();
}
}
}
Output:
ID NAME LASTNAME
1.0
Amit Shukla
2.0
Lokesh Gupta
3.0
John Adwards
4.0
Brian Schultz
|
Using formulas in excel sheet
When working on complex excel sheets, we encounter many cells which have formula to calculate their values. These are formula cells. Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.
Les see one example of how to set formula cells in excel?
In this code, there are four cells in a row and fourth one in multiplication of all previous 3 rows. So the formula will be : A2*B2*C2 (in second row)
|
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
static
void
main(String[] args)
{
XSSFWorkbook workbook =
new
XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(
"Calculate Simple Interest"
);
Row header = sheet.createRow(
0
);
header.createCell(
0
).setCellValue(
"Pricipal"
);
header.createCell(
1
).setCellValue(
"RoI"
);
header.createCell(
2
).setCellValue(
"T"
);
header.createCell(
3
).setCellValue(
"Interest (P r t)"
);
Row dataRow = sheet.createRow(
1
);
dataRow.createCell(
0
).setCellValue(14500d);
dataRow.createCell(
1
).setCellValue(
9.25
);
dataRow.createCell(
2
).setCellValue(3d);
dataRow.createCell(
3
).setCellFormula(
"A2*B2*C2"
);
try
{
FileOutputStream out =
new
FileOutputStream(
new
File(
"formulaDemo.xlsx"
));
workbook.write(out);
out.close();
System.out.println(
"Excel with foumula cells written successfully"
);
}
catch
(FileNotFoundException e) {
e.printStackTrace();
}
catch
(IOException e) {
e.printStackTrace();
}
}
|
Similarly, I you want to read a file which have formula cells in it, use following logic to evaluate the formula cells.
|
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
|
public
static
void
readSheetWithFormula()
{
try
{
FileInputStream file =
new
FileInputStream(
new
File(
"formulaDemo.xlsx"
));
//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook =
new
XSSFWorkbook(file);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(
0
);
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while
(rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while
(cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type after eveluating formulae
//If it is formula cell, it will be evaluated otherwise no change will happen
switch
(evaluator.evaluateInCell(cell).getCellType())
{
case
Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() +
"\t\t"
);
break
;
case
Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() +
"\t\t"
);
break
;
case
Cell.CELL_TYPE_FORMULA:
//Not again
break
;
}
}
System.out.println(
""
);
}
file.close();
}
catch
(Exception e)
{
e.printStackTrace();
}
}
Output:
Pricipal RoI T Interest (P r t)
14500.0
9.25
3.0
402375.0
|
Formatting the cells
So for we have seen the examples of reading/ writing and excel file using apache POI. But, when we are creating a report in excel file and it becomes utmost important to add formatting on cells which fit into any per-determined criteria. This formatting can be a different coloring based on certain value range, based on expiry date limit etc.
In below examples, I am taking couple of such formatting examples for various purposes.
1) Cell value is in between a certain range
This piece of code will color any cell in range whose value is between a configured range. [e.g. between 50 and 70]
|
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
|
static
void
basedOnValue(Sheet sheet)
{
//Creating some random values
sheet.createRow(
0
).createCell(
0
).setCellValue(
84
);
sheet.createRow(
1
).createCell(
0
).setCellValue(
74
);
sheet.createRow(
2
).createCell(
0
).setCellValue(
50
);
sheet.createRow(
3
).createCell(
0
).setCellValue(
51
);
sheet.createRow(
4
).createCell(
0
).setCellValue(
49
);
sheet.createRow(
5
).createCell(
0
).setCellValue(
41
);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
//Condition 1: Cell Value Is greater than 70 (Blue Fill)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT,
"70"
);
PatternFormatting fill1 = rule1.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
//Condition 2: Cell Value Is less than 50 (Green Fill)
ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT,
"50"
);
PatternFormatting fill2 = rule2.createPatternFormatting();
fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] regions = {
CellRangeAddress.valueOf(
"A1:A6"
)
};
sheetCF.addConditionalFormatting(regions, rule1, rule2);
}
|
2) Highlight duplicate values
Highlight all cells which have duplicate values in observed cells
|
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
|
static
void
formatDuplicates(Sheet sheet) {
sheet.createRow(
0
).createCell(
0
).setCellValue(
"Code"
);
sheet.createRow(
1
).createCell(
0
).setCellValue(
4
);
sheet.createRow(
2
).createCell(
0
).setCellValue(
3
);
sheet.createRow(
3
).createCell(
0
).setCellValue(
6
);
sheet.createRow(
4
).createCell(
0
).setCellValue(
3
);
sheet.createRow(
5
).createCell(
0
).setCellValue(
5
);
sheet.createRow(
6
).createCell(
0
).setCellValue(
8
);
sheet.createRow(
7
).createCell(
0
).setCellValue(
0
);
sheet.createRow(
8
).createCell(
0
).setCellValue(
2
);
sheet.createRow(
9
).createCell(
0
).setCellValue(
8
);
sheet.createRow(
10
).createCell(
0
).setCellValue(
6
);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// Condition 1: Formula Is =A2=A1 (White Font)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
"COUNTIF($A$2:$A$11,A2)>1"
);
FontFormatting font = rule1.createFontFormatting();
font.setFontStyle(
false
,
true
);
font.setFontColorIndex(IndexedColors.BLUE.index);
CellRangeAddress[] regions = {
CellRangeAddress.valueOf(
"A2:A11"
)
};
sheetCF.addConditionalFormatting(regions, rule1);
sheet.getRow(
2
).createCell(
1
).setCellValue(
"<== Duplicates numbers in the column are highlighted. "
+
"Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"
);
}
|
3) Color alternate rows in different colors
A simple code to color each alternate row in a different color
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
static
void
shadeAlt(Sheet sheet) {
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// Condition 1: Formula Is =A2=A1 (White Font)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
"MOD(ROW(),2)"
);
PatternFormatting fill1 = rule1.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] regions = {
CellRangeAddress.valueOf(
"A1:Z100"
)
};
sheetCF.addConditionalFormatting(regions, rule1);
sheet.createRow(
0
).createCell(
1
).setCellValue(
"Shade Alternating Rows"
);
sheet.createRow(
1
).createCell(
1
).setCellValue(
"Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"
);
}
|
4) Color amounts which are going to expire in next 30 days
A very useful code for financial projects which keep track of dead lines.
|
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
|
static
void
expiryInNext30Days(Sheet sheet)
{
CellStyle style = sheet.getWorkbook().createCellStyle();
style.setDataFormat((
short
)BuiltinFormats.getBuiltinFormat(
"d-mmm"
));
sheet.createRow(
0
).createCell(
0
).setCellValue(
"Date"
);
sheet.createRow(
1
).createCell(
0
).setCellFormula(
"TODAY()+29"
);
sheet.createRow(
2
).createCell(
0
).setCellFormula(
"A2+1"
);
sheet.createRow(
3
).createCell(
0
).setCellFormula(
"A3+1"
);
for
(
int
rownum =
1
; rownum <=
3
; rownum++) sheet.getRow(rownum).getCell(
0
).setCellStyle(style);
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// Condition 1: Formula Is =A2=A1 (White Font)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
"AND(A2-TODAY()>=0,A2-TODAY()<=30)"
);
FontFormatting font = rule1.createFontFormatting();
font.setFontStyle(
false
,
true
);
font.setFontColorIndex(IndexedColors.BLUE.index);
CellRangeAddress[] regions = {
CellRangeAddress.valueOf(
"A2:A4"
)
};
sheetCF.addConditionalFormatting(regions, rule1);
sheet.getRow(
0
).createCell(
1
).setCellValue(
"Dates within the next 30 days are highlighted"
);
}
|
I am ending this post here for keeping the post in limit. I will post some useful code samples in coming posts.
Sourcecode download
Click on below given link to download the source code of above examples.
Download Sourcecode
Happy Learning !!
102
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
