Học cách đọc excel, viết excel, đánh giá các ô công thức và áp dụng định dạng tùy chỉnh cho các tệp excel được tạo bằng thư viện Apache POI với các ví dụ
Nếu chúng tôi đang xây dựng phần mềm cho lĩnh vực Nhân sự hoặc Tài chính, thường có yêu cầu tạo báo cáo excel giữa các cấp quản lý. Ngoài các báo cáo, chúng tôi cũng có thể mong đợi một số dữ liệu đầu vào cho các ứng dụng ở dạng trang tính excel và ứng dụng được mong đợi sẽ hỗ trợ yêu cầu này
Apache POI là một thư viện đáng tin cậy trong số nhiều thư viện nguồn mở khác để xử lý các trường hợp sử dụng như vậy liên quan đến các tệp excel. Xin lưu ý rằng, ngoài ra, chúng tôi cũng có thể đọc và ghi các tệp MS Word và MS PowerPoint bằng thư viện Apache POI
Hướng dẫn Apache POI này sẽ thảo luận về một số thao tác excel hàng ngày trong các ứng dụng thực tế
Mục lục
1. Phụ thuộc Maven
Nếu chúng tôi đang làm việc trên một dự án maven, chúng tôi có thể bao gồm các phụ thuộc Apache POI trong tệp
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 data = new TreeMap[];
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 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[];
}
}
}
1 bằng cách sử dụng tệp này
org.apache.poi
poi
5.2.2
org.apache.poi
poi-ooxml
5.2.2
2. Các lớp quan trọng trong thư viện POI
Các lớp HSSF, XSSF và XSSF
Các lớp chính của Apache POI thường bắt đầu bằng HSSF, XSSF hoặc SXSSF
- HSSF – là triển khai Java thuần túy của Dự án POI ở định dạng tệp Excel 97[-2007]. e. g. , HSSFWorkbook, HSSFSheet
- XSSF – là triển khai Java thuần túy của Dự án POI cho Excel 2007 OOXML [. định dạng tệp xlsx]. e. g. , Sổ làm việc XSSF, Trang tính XSSF
- SXSSF [từ 3. 8-beta3] – là tiện ích phát trực tuyến tương thích với API của XSSF được sử dụng khi phải tạo bảng tính lớn và dung lượng lưu trữ bị hạn chế. e. g. , SXSSFWorkbook, SXSSFSheet. SXSSF đạt được dung lượng bộ nhớ thấp bằng cách giới hạn quyền truy cập vào các hàng trong cửa sổ trượt, trong khi XSSF cấp quyền truy cập vào tất cả các hàng trong tài liệu
Hàng và Ô
Ngoài các lớp trên, Hàng và Ô tương tác với một hàng cụ thể và một ô cụ thể trong một bảng excel
Các lớp liên quan đến tạo kiểu
Một loạt các lớp như CellStyle, BuiltinFormats, So sánhOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting, v.v. được sử dụng khi bạn phải thêm định dạng vào trang tính, chủ yếu dựa trên một số quy tắc
Trình đánh giá công thức
Một lớp FormulaEvaluator hữu ích khác được sử dụng để đánh giá các ô công thức trong một trang tính excel
3. Viết một tệp Excel
Tôi đang lấy ví dụ này trước để chúng ta có thể sử dụng lại bảng excel được tạo bởi mã này trong các ví dụ tiếp theo
Viết excel bằng POI rất đơn giản và bao gồm các bước sau
- Tạo sổ làm việc
- Tạo trang tính trong sổ làm việc
- Tạo một hàng trong trang tính
- Thêm ô vào trang tính
- Lặp lại bước 3 và 4 để ghi thêm dữ liệu
Nó có vẻ rất đơn giản, phải không?
Chương trình Java viết file excel sử dụng thư viện Apache POI
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 data = new TreeMap[];
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 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[];
}
}
}
Xem thêm. Nối các hàng vào Excel
4. Đọc một tệp Excel
Việc đọc một file excel bằng POI cũng rất đơn giản nếu chúng ta chia thành các bước
- Tạo phiên bản sổ làm việc từ một trang tính excel
- Đến trang tính mong muốn
- Tăng số hàng
- lặp lại trên tất cả các ô trong một hàng
- lặp lại bước 3 và 4 cho đến khi tất cả dữ liệu được đọc
Hãy xem tất cả các bước trên trong mã. Tôi đang viết mã để đọc tệp excel được tạo trong ví dụ trên. Nó sẽ đọc tất cả các tên cột và các giá trị trong đó – từng ô một
Chương trình Java để đọc tệp excel bằng thư viện Apache POI
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 rowIterator = sheet.iterator[];
while [rowIterator.hasNext[]]
{
Row row = rowIterator.next[];
//For each row, iterate through all the columns
Iterator 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[];
}
}
}
Đầu ra chương trình
ID NAME LASTNAME
1.0 Amit Shukla
2.0 Lokesh Gupta
3.0 John Adwards
4.0 Brian Schultz
Xem thêm. Apache POI – Đọc tệp Excel bằng Trình phân tích cú pháp SAX
5. Thêm và đánh giá các ô công thức
Khi làm việc trên các sheet excel phức tạp, chúng ta gặp nhiều ô có công thức để tính giá trị của chúng. Đây là những ô công thức. Apache POI cũng có hỗ trợ tuyệt vời để thêm các ô công thức và đánh giá các ô công thức đã có
Hãy xem một ví dụ về cách thêm các ô công thức trong excel?
Trang tính có bốn ô liên tiếp và ô thứ tư trong phép nhân của cả 3 hàng trước đó. Vậy công thức sẽ là. A2*B2*C2 [ở hàng thứ hai]
Chương trình Java để thêm công thức vào tệp excel bằng thư viện Apache POI
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[];
}
}
Tương tự, chúng tôi muốn đọc một tệp có các ô công thức và sử dụng logic sau để đánh giá các ô công thức
Chương trình Java để đánh giá công thức trong tệp excel bằng thư viện Apache POI
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 rowIterator = sheet.iterator[];
while [rowIterator.hasNext[]]
{
Row row = rowIterator.next[];
//For each row, iterate through all the columns
Iterator 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[] + "tt"];
break;
case Cell.CELL_TYPE_STRING:
System.out.print[cell.getStringCellValue[] + "tt"];
break;
case Cell.CELL_TYPE_FORMULA:
//Not again
break;
}
}
System.out.println[""];
}
file.close[];
}
catch [Exception e]
{
e.printStackTrace[];
}
}
Đầu ra chương trình
Pricipal RoI T Interest [P r t]
14500.0 9.25 3.0 402375.0
6. Định dạng ô
Cho đến nay chúng ta đã thấy các ví dụ về đọc/ghi và các tệp excel bằng cách sử dụng Apache POI. Tuy nhiên, khi tạo báo cáo trong tệp excel, điều cần thiết là thêm định dạng trên các ô phù hợp với bất kỳ tiêu chí được xác định trước nào
Định dạng này có thể có màu khác dựa trên phạm vi giá trị cụ thể, giới hạn ngày hết hạn, v.v.
Trong các ví dụ dưới đây, chúng tôi đang lấy một số ví dụ về định dạng ô như vậy cho các mục đích khác nhau
6. 1. Giá trị ô trong một phạm vi cụ thể
Mã này sẽ tô màu bất kỳ ô nào trong phạm vi có giá trị nằm giữa phạm vi đã định cấu hình. [e. g. , giữa 50 và 70]
________số 8_______6. 2. Làm nổi bật các giá trị trùng lặp
Đánh dấu tất cả các ô có giá trị trùng lặp trong các ô được quan sát
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["1 [Blue Font]"];
}
6. 3. Các hàng màu thay thế với các màu khác nhau
Một mã đơn giản để tô màu từng hàng thay thế bằng một màu khác
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]"];
}
6. 4. Số lượng màu sắp hết hạn trong 30 ngày tới
Một mã tiện dụng cho các dự án tài chính theo dõi thời hạn
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 data = new TreeMap[];
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 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[];
}
}
}
0Tôi kết thúc hướng dẫn poi apache này tại đây để giữ cho bài đăng trong giới hạn
7. Phần kết luận
Trong hướng dẫn này, chúng ta đã học cách đọc excel, viết excel, đặt và đánh giá các ô công thức cũng như định dạng các ô bằng mã màu bằng thư viện Apache POI