SpringBoot操作Excel实现导入和导出功能（详细讲解+Gitee源码）

前言：在日常的开发中，避免不了操作Excel，比如从系统当中导出一个报表，或者通过解析客户上传的Excel文件进行批量解析数据入库等等，本篇博客主要汇总日常开发中如何使用开源的Apache提供的POI流操作Excel进行导入导出功能详解。

一、导入pom.xml依赖

这边我使用的是Apache提供的POI流来操作Excel。

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- apache工具操作poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

二、准备数据

1、学生实体类

@Data
public class Student {

    private int id;

    private String name;

    private String sex;

    private String address;

    private String phone;
}

2、初始化数据

    public List<Student> initData(){
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        student.setId(1);
        student.setName("张三");
        student.setSex("男");
        student.setAddress("江苏");
        student.setPhone("19991198111");
        list.add(student);
        student = new Student();
        student.setId(2);
        student.setName("李四");
        student.setSex("女");
        student.setAddress("南京");
        student.setPhone("1887119121");
        list.add(student);
        return list;
    }

    public void export(HttpServletResponse response) throws IOException {

        //获取学生数据
        List<Student> students = initData();
        //获取标题
        String[] titles = {"编号","姓名","性别","地址","电话"};
         
        //导出Excel的代码
        ...

    }

三、导出功能

这边主要讲解关于遍历数据、设置单元格样式、合并单元格和插入批注一些常用的功能。

3.1、新建文件

这边提供了2种Excel格式的文件，选一个即可，为了更好的兼容性，这边我采用的是.xls格式。

//.xls格式
HSSFWorkbook workbook = new HSSFWorkbook();
//.xlsx格式
//XSSFWorkbook workbook = new XSSFWorkbook();

3.2、新建工作簿

这行代码就是新建一个名为学生表的工作簿

HSSFSheet sheet = workbook.createSheet("学生表");

3.3、自定义样式

实例化出来的cellStyle提供给了我们很多关于设置样式的方法，这边我就提供一些常用的设置单元格样式的代码，把它封装成了一个方法，每行代码上面都有注释。

HSSFCellStyle cellStyle = workbook.createCellStyle();

常用样式代码：

/**
     * 单元格样式
     * @param workbook
     * @param fontBold 是否加粗
     * @param isBorder 是否带边框
     * @param horizontalAlignment 水平对齐格式
     * @param verticalAlignment 垂直对齐格式
     * @return
     */
    private HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,
                                         boolean fontBold,
                                         boolean isBorder,
                                         HorizontalAlignment horizontalAlignment,
                                         VerticalAlignment verticalAlignment) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //设置字体名字
        font.setFontName("宋体");
        font.setBold(fontBold);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 是设置前景色不是背景色
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        if (isBorder) {
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
        }
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(verticalAlignment);
        return style;
    }

3.4、创建行和列并填充数据

首先我们需要先通过HSSFRow创建单元行，其次再通过HSSFRow实例化的对象去创建单元列，最后通过HSSFCell实例化的对象去填充数据，这边我创建了一个名为学生信息表的标题。

//创建单元行
HSSFRow header = sheet.createRow(0);
//创建单元列
HSSFCell headerCell = header.createCell(0);
//填充信息
headerCell.setCellValue("学生信息表");

再通过同样的代码遍历我们的字段标题

//创建第一行
HSSFRow titleRow = sheet.createRow(1);

//样式
HSSFCellStyle cellStyle = this.getColumnStyle(workbook, true, true, HorizontalAlignment.CENTER,VerticalAlignment.CENTER);

//写入标题
for(int i = 0 ; i < titles.length ; i++ ){
    HSSFCell cell = titleRow.createCell(i);
    cell.setCellValue(titles[i]);
    cell.setCellStyle(cellStyle);
}

3.5、合并单元格

首先创建 CellRangeAddress对象，表示要合并的单元格范围，然后使用 addMergedRegion()方法合并单元格，以下这是CellRangeAddress的参数详解。
startRow开始行0，表示第1行endRow结束行1，表示第2行startColumn开始列2，表示第3列endColumn结束列3，表示第4列
代码如下：

CellRangeAddress region = new CellRangeAddress(0, 0 , 0, titles.length-1);
sheet.addMergedRegion(region);

3.6、创建批注

首先如果要在工作表上添加批注/图片/图形等元素，需要先调用createDrawingPatriarch()来获取工作表的绘制对象Drawing。

其次在工作表中创建单元格批注的对象，HSSFClientAnchor用于创建一个新的端锚,并设置锚的左下和右下坐标，用于图片插入，画线等操作。

HSSFClientAnchor的参数顺序如下：

左边距、上边距、右边距、下边距 、 开始列、开始行 、结束列、 结束行。

Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
    (short) headerCell.getColumnIndex(),
    headerCell.getRowIndex(),
    (short) (headerCell.getColumnIndex() + 5),
    headerCell.getRowIndex() + 6));
    comment.setString(new HSSFRichTextString("这是标题信息哦！"));
headerCell.setCellComment(comment);

所以这段代码的意思是：在工作表上，创建了一个单元格批注comment，这个批注的锚点设置在从单元格headerCell开始，延伸5列6行。

3.7、遍历数据

最后我们通过循环来遍历我们集合当中的数据，上面已经讲过，这边不再多做讲解。

         for(int i = 0 ; i < students.size() ; i++ ){

            Student student = students.get(i);

            HSSFRow row = sheet.createRow(i+2);

            for (int j = 0; j < titles.length; j++) {
                HSSFCell cell = null;
                switch (j) {
                    case 0:
                        cell = row.createCell(j);
                        cell.setCellValue(i);
                        break;
                    case 1:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getName());
                        break;
                    case 2:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getSex());
                        break;
                    case 3:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getAddress());
                        break;
                    case 4:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getPhone());
                        break;
                }
            }
        }

3.8、设置响应下载

1、定义Excel文件名

2、把文件名编码为 ISO-8859-1格式,从而支持中文下载文件名

3、设置 Response为附件应答

4、设置附件名称为codeFileName，告诉浏览器这是一个附件应答

5、从workbook中获取输出流，写入内容到response输出流

6、关闭workbook，释放资源

String fileName = "学生表统计";
String codeFileName = new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1");
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + codeFileName);
OutputStream out = response.getOutputStream();
workbook.write(response.getOutputStream());
out.flush();
out.close();
workbook.close();

3.9、运行截图

浏览器访问：http://localhost:8080/student/export

四、导入功能

4.1、获取单元格值方法

如果当前单元格的格式是时间格式，则需要特殊处理为时间格式，否则会报错。

String dataFormatString = cell.getCellStyle().getDataFormatString();
if(dataFormatString.equals("m/d/yy")){
    cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
    return cellValue;
}

如果当前单元格是数字，比如1，那么读出来会默认是1.0，所以要把数字当成String来读。

if(cell.getCellType() == CellType.NUMERIC){
    cell.setCellType(CellType.STRING);
}

先判断读取的类型，来通过switch结构来获取单元格值。

//判断数据的类型
switch (cell.getCellType()){
    //数字
    case NUMERIC:
        cellValue = String.val
        break;
    //字符串
    case STRING:
        cellValue = String.val
        break;
    //Boolean
    case BOOLEAN:
        cellValue = String.val
        break;
    //公式
    case FORMULA:
        cellValue = String.val
        break;
    //空值
    case BLANK:
        cellValue = "";
        break;
    //故障
    case ERROR:
        cellValue = "非法字符";
        break;
    default:
        cellValue = "未知类型";
        break;
}

4.2、获取文件流

FileInputStream in = new FileInputStream("F:\\学生表统计.xls");

4.3、获取Excel文件对象

我这边我获取的是.xls后缀格式的文件

Workbook workbook = new HSSFWorkbook(in);

这个是获取.xlsx格式的对象

Workbook workbook = new XSSFWorkbook(in);

4.4、获取工作簿

默认通过工作簿名称来获取工作簿对象

Sheet sheet = workbook.getSheet("学生表");

也可以通过下标来进行获取

Sheet sheet = workbook.getSheetAt(0);

4.5、获取首尾行下标

获取首行下标

int firstRowNum = sheet.getFirstRowNum();

获取尾行下标

int lastRowNum = sheet.getLastRowNum();

4.6、读取数据

使用for循环读取每行的第一列单元格的数据，获取数据的方法就是我上面封装好的方法。

for(int i = firstRowNum ; i <=lastRowNum ; i++){
    
    Row row = sheet.getRow(i);
    Cell cell = null;
    if(row != null){
        cell = row.getCell(0);
    }

    System.out.println(getCellValue(cell));

}

4.7、测试运行

我准备了7种不同类型的数据进行读取

单元测试结果如下，读取一切正常。

五、完整代码

导入和导出的完整代码：

package com.example.excel.service;

import com.example.excel.domain.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author HTT
 */
@Service
public class StudentService {

    public List<Student> initData(){
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        student.setId(1);
        student.setName("张三");
        student.setSex("男");
        student.setAddress("江苏");
        student.setPhone("19991198111");
        list.add(student);
        student = new Student();
        student.setId(2);
        student.setName("李四");
        student.setSex("女");
        student.setAddress("南京");
        student.setPhone("1887119121");
        list.add(student);
        return list;
    }

    public void export(HttpServletResponse response) throws IOException {

        //获取学生数据
        List<Student> students = initData();
        //获取标题
        String[] titles = {"编号","姓名","性别","地址","电话"};
        //.xls格式
        HSSFWorkbook workbook = new HSSFWorkbook();

        //.xlsx格式
        //XSSFWorkbook workbook = new XSSFWorkbook();

        //新增工作表
        HSSFSheet sheet = workbook.createSheet("学生表");

        //单元格样式对象
        HSSFCellStyle cellStyle = this.getColumnStyle(workbook, true, true, HorizontalAlignment.CENTER,VerticalAlignment.CENTER);

        //创建第一行
        HSSFRow header = sheet.createRow(0);
        HSSFCell headerCell = header.createCell(0);
        headerCell.setCellValue("学生信息表");
        CellRangeAddress region = new CellRangeAddress(0, 0 , 0, titles.length-1);
        sheet.addMergedRegion(region);

        /**
         * - 左边距
         * - 上边距
         * - 右边距
         * - 下边距
         * - 开始列
         * - 开始行
         * - 结束列
         * - 结束行
         * 在工作表`drawing`上,
         * 创建了一个单元格批注 comment。
         * 这个批注的锚点设置在从单元格 headerCell 开始,延伸 5 列 6 行。
         */
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
                (short) headerCell.getColumnIndex(),
                headerCell.getRowIndex(),
                (short) (headerCell.getColumnIndex() + 5),
                headerCell.getRowIndex() + 6));
        comment.setString(new HSSFRichTextString("这是标题信息哦！"));

        headerCell.setCellComment(comment);

        //创建第一行
        HSSFRow titleRow = sheet.createRow(1);

        //写入标题
        for(int i = 0 ; i < titles.length ; i++ ){
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
        }

        for(int i = 0 ; i < students.size() ; i++ ){

            Student student = students.get(i);

            HSSFRow row = sheet.createRow(i+2);

            for (int j = 0; j < titles.length; j++) {
                HSSFCell cell = null;
                switch (j) {
                    case 0:
                        cell = row.createCell(j);
                        cell.setCellValue(i);
                        break;
                    case 1:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getName());
                        break;
                    case 2:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getSex());
                        break;
                    case 3:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getAddress());
                        break;
                    case 4:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getPhone());
                        break;
                }
            }
        }

        String fileName = "学生表统计";
        String codeFileName = new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1");
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + codeFileName);
        OutputStream out = response.getOutputStream();
        workbook.write(response.getOutputStream());
        out.flush();
        out.close();
        workbook.close();

    }

    /**
     * 单元格样式
     * @param workbook
     * @param fontBold 是否加粗
     * @param isBorder 是否带边框
     * @param horizontalAlignment 水平对齐格式
     * @param verticalAlignment 垂直对齐格式
     * @return
     */
    private HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,
                                         boolean fontBold,
                                         boolean isBorder,
                                         HorizontalAlignment horizontalAlignment,
                                         VerticalAlignment verticalAlignment) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //设置字体名字
        font.setFontName("宋体");
        font.setBold(fontBold);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 是设置前景色不是背景色
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        if (isBorder) {
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
        }
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(verticalAlignment);
        return style;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            //数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public void analysis() throws IOException {
        // 通过输入流，读取excel文件
        FileInputStream in = new FileInputStream("F:\\学生表统计.xls");

        // excel文件对象
        Workbook workbook = new HSSFWorkbook(in);

        // 通过工作簿的名字获取工作簿
        Sheet sheet = workbook.getSheet("学生表");

        // 通过工作簿的下标获取工作簿
        //Sheet sheet = workbook.getSheetAt(0);

        // 获取首行下标
        int firstRowNum = sheet.getFirstRowNum();

        // 获取尾行下标
        int lastRowNum = sheet.getLastRowNum();

        for(int i = firstRowNum ; i <=lastRowNum ; i++){
            
            Row row = sheet.getRow(i);

            Cell cell = null;
            if(row != null){
                cell = row.getCell(0);
            }

            System.out.println(getCellValue(cell));

        }

    }
}

请求层

package com.example.excel.controller;

import com.example.excel.service.StudentService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@RestController
@RequestMapping("/student")
public class StudentController {

    @Resource
    private StudentService service;

    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        service.export(response);
    }
}

六、Gitee源码

SpringBoot使用Apache操作Excel导入与导出: 主要汇总日常开发中常用的关于一些关于Excel导入导出功能详解。