请关注微信公众号：拾荒的小海螺
1、简述
Apache POI是Apache软件基金会的顶级项目之一，它允许Java开发人员读取和写入Microsoft Office格式的文档，包括Excel、Word和PowerPoint文件。通过POI，开发人员可以创建、修改和读取Excel电子表格，从而实现各种各样的用例，例如数据导入、报表生成和数据分析等。
2、应用
Apache POI是一个强大的Java库，用于处理Microsoft Office格式文件，特别是Excel电子表格。在本文中，我们将深入探讨Apache POI库，并提供详细的应用实例，以展示其在处理Excel文件时的强大功能。
首先，确保在Maven中引入POI的依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
2.1 创建和读取
创建一个简单的Excel文件：
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelCreationExample {
public static void main(String[] args) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Hello, Apache POI!");
FileOutputStream fileOut = new FileOutputStream("example.xlsx");
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
读取和修改现有的Excel文件：
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelModificationExample {
public static void main(String[] args) {
try (FileInputStream fis = new FileInputStream("example.xlsx");
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String cellValue = cell.getStringCellValue();
System.out.println("Original Cell Value: " + cellValue);
cell.setCellValue("Modified by Apache POI!");
FileOutputStream fos = new FileOutputStream("example.xlsx");
workbook.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2.2 图片读取和插入
使用Apache POI库，你可以方便地插入图片到Excel文件中，并读取已经插入的图片。下面是插入图片和读取图片的示例代码：
插入图片到Excel文件中：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
public class InsertImageExample {
public static void main(String[] args) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
// 读取图片文件
FileInputStream fis = new FileInputStream("image.jpg");
byte[] imageData = fis.readAllBytes();
fis.close();
// 将图片插入到Excel文件中
int pictureIdx = workbook.addPicture(imageData, Workbook.PICTURE_TYPE_JPEG);
CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(1); // 图片起始列
anchor.setRow1(1); // 图片起始行
Picture picture = drawing.createPicture(anchor, pictureIdx);
FileOutputStream fos = new FileOutputStream("output.xlsx");
workbook.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
从Excel文件中读取图片：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ReadImageExample {
public static void main(String[] args) {
try (Workbook workbook = new XSSFWorkbook(new FileInputStream("output.xlsx"))) {
Sheet sheet = workbook.getSheetAt(0);
Drawing drawing = sheet.getDrawingPatriarch();
// 读取图片
for (Shape shape : drawing) {
if (shape instanceof Picture) {
Picture picture = (Picture) shape;
byte[] imageData = picture.getData();
BufferedImage bufferedImage = ImageIO.read(new ByteArrayInputStream(imageData));
// 将图片保存到文件
String filename = "image_from_excel.jpg";
FileOutputStream fos = new FileOutputStream(filename);
ImageIO.write(bufferedImage, "jpg", fos);
fos.close();
System.out.println("Image saved to: " + filename);
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
2.3 条件格式
添加条件格式规则：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ConditionalFormattingExample {
public static void main(String[] args) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
XSSFSheetConditionalFormatting cf = (XSSFSheetConditionalFormatting) sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule = cf.createConditionalFormattingRule(ComparisonOperator.GT, "0");
FontFormatting fontFmt = rule.createFontFormatting();
fontFmt.setFontStyle(true, false);
fontFmt.setFontColorIndex(IndexedColors.RED.index);
CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A10")};
cf.addConditionalFormatting(regions, rule);
FileOutputStream fos = new FileOutputStream("conditional_formatting_example.xlsx");
workbook.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
读取当前单元格是否满足条件格式设定的规则：
/**
* 获取当前单元格是否满足校验规则，并返回满足的规则对象
* @Author shdxhl
* @Method getMatchingConditionalFormattingForCell
* @Date 17:10 2023-10-14
* @Param [cell]
* @return List<EvaluationConditionalFormatRule>
**/
private List<EvaluationConditionalFormatRule> getMatchingConditionalFormattingForCell(XSSFCell cell) {
Sheet sheet = cell.getSheet();
Workbook workbook = sheet.getWorkbook();
WorkbookEvaluatorProvider workbookEvaluatorProvider =
(WorkbookEvaluatorProvider)workbook.getCreationHelper().createFormulaEvaluator();
ConditionalFormattingEvaluator conditionalFormattingEvaluator =
new ConditionalFormattingEvaluator(workbook, workbookEvaluatorProvider);
List<EvaluationConditionalFormatRule> matchingCFRulesForCell =
conditionalFormattingEvaluator.getConditionalFormattingForCell(cell);
return matchingCFRulesForCell;
}
/**
* 获取当前单元格匹配规则的颜色
* @Author shdxhl
* @Method getMatchingConditionalColor
* @Date 17:10 2023-10-14
* @Param [cell]
* @return XSSFColor
**/
private XSSFColor getMatchingConditionalColor(XSSFCell cell){
List<EvaluationConditionalFormatRule> formatRuleList = getMatchingConditionalFormattingForCell(cell);
if(Objects.nonNull(formatRuleList) && formatRuleList.size() > 0){
ConditionalFormattingRule cFRule = formatRuleList.get(0).getRule();
XSSFColor fontColor = (XSSFColor) cFRule.getFontFormatting().getFontColor();
if (fontColor != null) {
return fontColor;
}
}
return null;
}
3、优化
优化Apache POI代码可以提高处理Excel文件时的性能和效率。以下是一些优化技巧以及对应的实例：
3.1 批量操作
避免逐个单元格进行读写操作，而是批量处理单元格数据。这可以通过缓存或预加载数据来实现。
// 逐行读取数据并存储在内存中
List<List<String>> data = new ArrayList<>();
for (Row row : sheet) {
List<String> rowData = new ArrayList<>();
for (Cell cell : row) {
rowData.add(cell.getStringCellValue());
}
data.add(rowData);
}
3.2 使用缓存
合理使用POI提供的缓存机制，例如使用CellStyleCache来避免重复创建CellStyle对象。
CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 使用缓存
CellStyle cachedStyle = CellStyleCache.getOrAdd(style);
3.3 使用SXSSFWorkbook进行大数据量处理
当处理大量数据时，可以使用SXSSFWorkbook来避免内存溢出问题，因为它支持将数据写入临时文件而不是全部加载到内存中。
try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
SXSSFSheet sheet = workbook.createSheet("Sheet1");
for (int rownum = 0; rownum < 10000; rownum++) {
Row row = sheet.createRow(rownum);
for (int cellnum = 0; cellnum < 10; cellnum++) {
Cell cell = row.createCell(cellnum);
cell.setCellValue("Data" + cellnum);
}
}
try (FileOutputStream fos = new FileOutputStream("large_data.xlsx")) {
workbook.write(fos);
}
}
3.4 避免频繁的文件IO操作
尽量减少对文件系统的访问次数，可以通过缓存或批量写入操作来减少文件IO操作。
try (FileOutputStream fos = new FileOutputStream("output.xlsx");
BufferedOutputStream bos = new BufferedOutputStream(fos);
Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Sheet1");
// 批量写入数据
// ...
workbook.write(bos);
}
3.5 使用事件模型
如果只需要读取Excel文件的数据而不需要修改，可以使用事件驱动的模型（如SAX）来逐行读取数据，以减少内存消耗。
try (OPCPackage pkg = OPCPackage.open(new File("input.xlsx"))) {
XSSFReader reader = new XSSFReader(pkg);
StylesTable styles = reader.getStylesTable();
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) reader.getSheetsData();
while (iter.hasNext()) {
try (InputStream stream = iter.next()) {
processSheet(styles, strings, stream);
}
}
}
通过以上优化方法，可以提高Apache POI代码在处理Excel文件时的性能和效率。
4、结论
Apache POI是处理Excel文件的强大工具，可以满足各种各样的需求，从简单的数据导入到复杂的报表生成。通过本文提供的应用实例，你可以开始探索Apache POI，并利用其丰富的功能来处理Excel文件。
