利用POI SXSSFWorkbook 导出数据到Excel，读取Excel数据到后台

来一碗剔尖儿

已于 2022-11-29 16:01:44 修改

阅读量7k

点赞数 1

文章标签： java 开发语言

于 2022-05-25 16:09:37 首次发布

本文链接：https://blog.csdn.net/weixin_60589038/article/details/124968017

版权

下载

此类中将需要导入excel中的数据存放在List集合，实际应用中应该是从数据库取出放到List集合

同时将表头参数（可查看ExcelCol类）放入集合，然后调用Excel工具类

最后将返回的SXSSFWorkbook写到输出流中，完成下载功能

上传

将文件与excel表头结构传入Excel工具类，返回List数组（具体看代码注释）

package com.lx.excel;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@RestController
@RequestMapping("/excel")
public class Test {

    @RequestMapping("/download")
    public void export(HttpServletRequest request , HttpServletResponse response){
        //临时数据
        ArrayList<Student> students = new ArrayList<>();
        students.add(new Student("刘德华","22222","男"));
        students.add(new Student("彭于晏","33333","男"));
        students.add(new Student("林青霞","44444","女"));

        //表格结构数据
        String title = "表主标题";
        ArrayList<ExcelCol> excelCols = new ArrayList<>();
        excelCols.add(new ExcelCol("姓名","name",20));
        excelCols.add(new ExcelCol("学号","id",20));
        excelCols.add(new ExcelCol("性别","gender",20));

        SXSSFWorkbook workbook = null;
        try {
            //设置响应头
            response.setHeader("Content-disposition",
                    "attachment; filename="+ URLEncoder.encode("测试表.xlsx","utf-8"));
            response.setContentType("application/octet-stream;charset=UTF-8");
            ServletOutputStream outputStream = response.getOutputStream();
            //调用工具类
            workbook = ExcelUtile.initWorkbook("工作簿名称", title, excelCols, students);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (workbook!=null){
                workbook.dispose();
            }
        }
    }

    @PostMapping("/upload")
    public void upload(@RequestParam MultipartFile excel){
        //表格结构数据
        ArrayList<ExcelCol> excelCols = new ArrayList<>();
        excelCols.add(new ExcelCol("姓名","name",20));
        excelCols.add(new ExcelCol("学号","id",20));
        excelCols.add(new ExcelCol("性别","gender",20));

        List<HashMap> upload = null;
        ArrayList<Student> students = null;
        try {
            upload = ExcelUtile.upload(excel, excelCols);
            students = new ArrayList<>();
            for (HashMap map:upload) {
                //将list中的map数据，转换为字符串，再转为Student对象
                Student student = JSONObject.parseObject(JSONObject.toJSONString(map), Student.class);
                students.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        for (Student student:students) {
            System.out.println(student);
        }
    }
}

下载

此功能实现应用了 POI SXSSFWorkbook （在实现excel导出时，在数据量过大的情况下，总是容易发生内存溢出的情况。我们可以使用POI提供的 SXSSFWorkbook 类来避免内存溢出）

主要思路先创建对应的行，再通过行创建对应的单元格，将参数放到单元格中

对应关系：excel->sheet->row->col(cell)

1.先创建工作簿

2.创建标题行，然后合并单元格

3.创建表头行，通过遍历创建对应单元格，将表头名称放入单元格

4.通过遍历，依次创建行，列，单元格，将数据放入单元格

大致流程如上，可查看代码中注释

CellStyle在此demo中只设置了加粗，居中，其他样式可自行验证

上传

1.此demo中只展示读取一个工作簿，实际需求中，读取数据时需要先遍历工作簿

2.此demo读取数据时是按照string类型读取的，实际中需要先判断cell中的数据类型

可用getCellType()获取类型，然后switch case去依次匹配

package com.lx.excel;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/*
* 利用POI SXSSFWorkbook 导出数据到Excel
* */
public class ExcelUtile {
    //下载
    public static <T> SXSSFWorkbook initWorkbook(String sheetName , String title , List<ExcelCol> excelCol ,List<T> data){
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        int colSize = excelCol.size();

        //创建Sheet（工作簿）
        Sheet sheet = null;
        if (!StringUtils.hasText(sheetName)){
            sheet = workbook.createSheet();
        }else{
            sheet = workbook.createSheet(sheetName);
        }
        //创建主标题行(第一行)
        Row sheetTitleRow = sheet.createRow(0);
        Cell titleCell = sheetTitleRow.createCell(0);//创建第一行第一个单元格
        titleCell.setCellValue(title);//传值
        titleCell.setCellStyle(getHeaderFont(sheet.getWorkbook()));//设置样式
        //主标题行合并单元格
        CellRangeAddress cellAddresses = new CellRangeAddress(0, 0, 0, colSize - 1);
        sheet.addMergedRegion(cellAddresses);
        //创建表头行（第二行）
        Row sheetHeadRow = sheet.createRow(1);
        //遍历表头名称，创建表头单元格
        for(int i = 0 ; i < colSize ; i++){
            sheet.setColumnWidth(i,(excelCol.get(i).getWidth())*256);//宽度单位是字符的256分之一
            Cell headCell = sheetHeadRow.createCell(i);
            headCell.setCellValue(excelCol.get(i).getTitle());//传值
            headCell.setCellStyle(getHeaderFont(sheet.getWorkbook()));//设置样式
        }

        //将data中的值填充到excel
        int rowNum = sheet.getLastRowNum()+1;
        Iterator<T> iterator = data.iterator();
        //遍历数据
        for (;iterator.hasNext();){
            Row dataRow = sheet.createRow(rowNum);//创建行
            T obj = iterator.next();//获取当前行对应的数据
            JSONObject jsonObject = JSONObject.parseObject(JSONObject.toJSONString(obj));
            for (int i = 0 ; i < colSize ; i++ ){
                Cell dataCell = dataRow.createCell(i);
                dataCell.setCellStyle(getDataFont(workbook));
                dataCell.setCellValue(getValue(jsonObject.get(excelCol.get(i).getField())));
            }
            iterator.remove();
            rowNum++;
        }
        return workbook;
    }
    //上传
    public static List<HashMap> upload(MultipartFile excel , List<ExcelCol> excelCol) throws Exception {
        if (excel.isEmpty()){
            throw new Exception("文件为空");
        }
        ArrayList<HashMap> data = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = excel.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);//利用输入流获取Workbook对象
            Sheet sheet = workbook.getSheetAt(0);//获取第一个工作簿
            int numberOfRows = sheet.getPhysicalNumberOfRows();//总行数
            for (int i = 2; i < numberOfRows; i++) {//从第三行正文开始
                Row row = sheet.getRow(i);//获取遍历到的行
                int colNumber = excelCol.size();//获取列数
                HashMap<Object, Object> hashMap = new HashMap<>();//将每一行的数据存到一个map中
                for (int j = 0; j < colNumber; j++) {
                    Cell cell1 = row.getCell(j);
                    String field = excelCol.get(j).getField();
                    String value = cell1.getStringCellValue();
                    hashMap.put(field,value);
                }
                data.add(hashMap);//将每行的内容分别以map格式存到list中
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (inputStream!=null){
                inputStream.close();
            }
        }
        return data;
    }
    //标题样式
    public static CellStyle getHeaderFont(Workbook workbook){
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 16);//字体大小
        font.setBold(true);//加粗
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        return cellStyle;
    }

    //表头样式
    public static CellStyle getTitleFont(Workbook workbook){
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);//字体大小
        font.setBold(true);//加粗
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        return cellStyle;
    }

    //内容样式
    public static CellStyle getDataFont(Workbook workbook){
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);//字体大小
        font.setBold(false);//不加粗
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        return cellStyle;
    }

    //处理数据
    public static String getValue(Object object){
        if (object==null){
            return "";
        }else {
            return object.toString();
        }
    }
}

package com.lx.excel;

public class ExcelCol {
    private String title;//表头名称
    private String field;//内容名称(与数据库传回的参数字段对应)
    private int width;//单元格宽度

    public ExcelCol(String title, String field, int width) {
        this.title = title;
        this.field = field;
        this.width = width;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public int getWidth() {
        return width;
    }

    public void setWidth(int width) {
        this.width = width;
    }
}

package com.lx.excel;

import java.io.Serializable;

public class Student implements Serializable {
    private String name ;
    private String id;
    private String gender;

    public Student(String name, String id, String gender) {
        this.name = name;
        this.id = id;
        this.gender = gender;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }
}

前端form表单

<!DOCTYPE html>
<html>
	<head>
		<meta http-equiv="content-type" content="text/html; application/form-data;charset=UTF-8">
		<title></title>
	</head>
	<body>
		<form action="http://localhost:8080/excel/upload" enctype="multipart/form-data" method="post">
			上传文件:<input type="file" name="excel"/>
			        <input type="submit" value="上传" />
		</form>		
	</body>
</html>

实际效果如下

下载