springBoot对接Apache POI 实现excel下载和上传

搭建springboot项目

此处可以参考 搭建最简单的SpringBoot项目_Steven-Russell的博客-CSDN博客

配置Apache POI 依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version>
</dependency>

创建controller

package com.wd.controller;import com.wd.utils.ExcelUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;@RestController
@RequestMapping(value = "excel")
public class ExcelController {@GetMapping(value = "download")public String download(HttpServletResponse httpServletResponse) throws IOException {List<String[]> dataList = new ArrayList<>();dataList.add(new String[]{"aaa", "add"});dataList.add(new String[]{"bbb", "add"});dataList.add(new String[]{"ccc", "delete"});dataList.add(new String[]{"ddd", "add"});dataList.add(new String[]{"eee", "delete"});try (SXSSFWorkbook workbook = ExcelUtils.parseInfo2ExcelWorkbook(dataList);OutputStream os = httpServletResponse.getOutputStream()){httpServletResponse.reset();httpServletResponse.setContentType("application/vnd.ms-excel");httpServletResponse.setHeader("Content-disposition","attachment;filename=data_excel_" + System.currentTimeMillis() + ".xlsx");workbook.write(os);workbook.dispose();}return "download excel success.";}@PostMapping(value = "upload")public String upload(@RequestParam(value = "file") MultipartFile file) {// 获取输入流 注意：SXSSFWorkbook需要关闭流try (InputStream inputStream = file.getInputStream();XSSFWorkbook workbook = ExcelUtils.parseExcelFile(inputStream)){XSSFSheet sheet = workbook.getSheetAt(0);for (int i = 0; i < sheet.getLastRowNum(); i++) {XSSFRow row = sheet.getRow(i + 1);String data = row.getCell(0).getStringCellValue();String opr = row.getCell(1).getStringCellValue();System.out.println("data : " + data + " <==> " + "opr : " + opr);}} catch (IOException e) {e.printStackTrace();return "upload excel failed.";}return "upload excel success.";}}

创建excel工具类 

package com.wd.utils;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;
import java.io.InputStream;
import java.util.List;public class ExcelUtils {/*** 解析数据到excel中** @param dataList 数据list信息* @return excel对象*/public static SXSSFWorkbook parseInfo2ExcelWorkbook(List<String[]> dataList) {SXSSFWorkbook workbook = new SXSSFWorkbook();SXSSFSheet sheet = workbook.createSheet("数据");// 配置保护当前sheet页不被修改sheet.protectSheet("aaa");// 此处使用行的变量进行迭代，避免后续行创建出错int rows = 0;// 表头SXSSFRow head = sheet.createRow(rows++);CellStyle headCellStyle = createHeadCellStyle(workbook);createCell4Head(head, headCellStyle);// 表内容填充CellStyle bodyCellStyle = createBodyCellStyle(workbook);for (String[] dataArr : dataList) {SXSSFRow row = sheet.createRow(rows++);;createCell4Body(row, bodyCellStyle, dataArr[0], dataArr[1]);}return workbook;}private static CellStyle createBodyCellStyle(SXSSFWorkbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);return cellStyle;}private static CellStyle createHeadCellStyle(SXSSFWorkbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return cellStyle;}private static void createCell4Body(SXSSFRow row, CellStyle bodyCellStyle, String data, String opr) {SXSSFCell dataCell = row.createCell(0);dataCell.setCellStyle(bodyCellStyle);dataCell.setCellValue(data);SXSSFCell oprCell = row.createCell(1);oprCell.setCellStyle(bodyCellStyle);oprCell.setCellValue(opr);}private static void createCell4Head(SXSSFRow head, CellStyle cellStyle) {SXSSFCell dataCell = head.createCell(0);dataCell.setCellValue("data");dataCell.setCellStyle(cellStyle);SXSSFCell oprCell = head.createCell(1);oprCell.setCellValue("opr");oprCell.setCellStyle(cellStyle);}/*** 将输入流封装为 XSSFWorkbook 对象** @param inputStream excel 输入流* @return XSSFWorkbook 对象* @throws IOException 异常信息*/public static XSSFWorkbook parseExcelFile(InputStream inputStream) throws IOException {return new XSSFWorkbook(inputStream);}
}

启动项目

测试

下载excel

浏览器输入 http://localhost:8888/excel/download

打开下载内容，和代码中的内容进行对比，发现和预期一致

上传excel

打开postman或者Insomnia等工具，输入请求地址和对应的文件，查看控制台打印，和导入的表格内容一致