简言之,Apache POI 是Java 领域中可以操作World,Excel,PPT文件的类库,可以用于生成报表,数据处理等。
值得注意的是,Apache POI 从4.0.1版本开始,需要JDK 8 或更高版本支持。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
package com.shenmazong.zg2.controller;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.UUID;
/**
* @author 军哥
* @version 1.0
* @description: POI导入导出
* @date 2023/10/17 9:29
*/
@RestController
@Slf4j
@RequestMapping(value = "/poi")
public class PoiController {
/***
* @description POI导出
* @params
* @return java.lang.String
* @author 军哥
* @date 2023/10/17 9:32
*/
@GetMapping(value = "/exports")
public String exports() throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
// 创建sheet表
HSSFSheet hssfSheet = hssfWorkbook.createSheet("通讯录");
// 创建行
HSSFRow row = hssfSheet.createRow(0);
// 创建单元格
HSSFCell cell = row.createCell(0);
// 给单元格赋值
cell.setCellValue("2106通讯录");
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
// 设置居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置背景色
cellStyle.setFillForegroundColor(IndexedColors.RED.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置字体颜色
HSSFFont font = hssfWorkbook.createFont();
font.setColor(IndexedColors.YELLOW.index);
// 设置字体大小
font.setFontHeightInPoints((short) 100);
font.setBold(true);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
// 合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 1);
if (cellRangeAddress.getNumberOfCells() > 1) {
hssfSheet.addMergedRegionUnsafe(cellRangeAddress);
}
FileOutputStream fileOutputStream = new FileOutputStream("D:\\var\\docs\\"+ UUID.randomUUID()+".xls");
hssfWorkbook.write(fileOutputStream);
hssfWorkbook.close();
fileOutputStream.close();
return "yes";
}
/***
* @description POI导入
* @params
* @return java.lang.String
* @author 军哥
* @date 2023/10/17 9:34
*/
@GetMapping("imports")
public String imports() throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream("D:\\javaCode\\2106a\\upload\\5080e6da-7b28-4f11-a637-52f3ebdf06e1.xls"));
HSSFSheet hssfSheet = hssfWorkbook.getSheet("通讯录");
int lastRowNum = hssfSheet.getLastRowNum();
for (int i=0; i < lastRowNum; i++){
HSSFRow row = hssfSheet.getRow(i);
// 获取值
HSSFCell cell = row.getCell(0);
// 获取类型
cell.getCellType();
// TODO 写入数据库或者输出到前端
}
return "yes";
}
}