spring boot 后端 springBoot对接Apache POI 实现excel下载和上传

搭建springboot项目

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

配置Apache POI 依赖

org.apache.poi

poi

5.2.2

org.apache.poi

poi-ooxml

5.2.2

创建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 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 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等工具，输入请求地址和对应的文件，查看控制台打印，和导入的表格内容一致

文章链接

 您阅读本篇文章共花了：