Java使用POI导出Excel表格

1.导入POI相关的依赖(注意版本需要一致)

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

2.导出Excel表格工具类代

package com.tgram.sboot.uitl;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 *<p> Description: 导出Excel工具类 </p>
 *<p> Copyright: Copyright(c) 2018/12/27 </p>
 *<p> Company: xxx </p>
 *
 *@author Jason
 *@Version 1.0 2018/12/27 11:30
 */
public class ExportExcelUtil
{
    
    public static void exportExcel(List<List<String>> dataList, OutputStream stream, boolean isNewVersion)
    {
        if (dataList == null || dataList.size() == 0)
        {
            return;
        }
        
        // 1.创建Workbook对象(excel的文档对象)
        Workbook workbook;
        if (isNewVersion)
        {
            workbook = new SXSSFWorkbook();
        }
        else
        {
            workbook = new HSSFWorkbook();
        }
        
        // 2.创建文档格式对象并建立新的sheet对象（excel的表单）
        CellStyle cellStyle = getCellStyle(workbook);
        Sheet sheet = workbook.createSheet();
        
        // 3.创建行对象,循环生成数据
        Row row;
        List<String> list;
        for (int i = 0; i < dataList.size(); i++)
        {
            // 循环遍历生成每一行数据,list保存每行对应的列数据
            row = sheet.createRow(i);
            list = dataList.get(i);
            for (int j = 0; j < list.size(); j++) {
                Cell cell = row.createCell(j);      // 通过行对象创建列对象
                cell.setCellValue(list.get(j));     // 设置列数据
                cell.setCellStyle(cellStyle);       // 设置列样式
            }
        }

        try {
            // 4.通过输出流写入数据到Excel文档对象中
            workbook.write(stream);
        } catch (IOException e) {
            throw new RuntimeException("系统内部错误");
        }

    }
    
    /**
     * 设置Excel的格式
     * @param workbook Workbook文档对象
     * @return CellStyle对象
     */
    private static CellStyle getCellStyle(Workbook workbook)
    {
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat dataFormat = workbook.createDataFormat();
        //文本类型
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
        //自动换行
        cellStyle.setWrapText(true);
        //上下居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        return cellStyle;
    }
}

POI DateFormat种类(cellStyle.setDataFormat(dataFormat.getFormat("@")))参考地址：https://blog.csdn.net/phoenixx123/article/details/12720431

3.设置表头整理数据导出Excel表格

/**
 *<p> Description: 控制层 </p>
 *<p> Copyright: Copyright(c) 2018/11/9 </p>
 *<p> Company: xxx </p>
 *
 *@author Jason
 *@Version 1.0 2018/11/9 14:22
 */
@RestController
public class StudentController
{  
    @Resource
    private StudentService studentService;
    
    /**
     * 将所有的学生信息到处到excel表格中
     * @param response
     */
    @RequestMapping("/allStudsExportExcle")
    public void allStudsExportExcle(HttpServletResponse response) throws IOException
    {
        // 设置请求头信息(使用String防止文件名中文乱码)
        response.setHeader("Content-Disposition","attachment;filename=" 
　　　　　　　　　　　　　　　　+ new String("学生信息表.xls".getBytes(),"ISO-8859-1"));
        // response.setHeader("Content-disposition", "attachment; filename=student.xls");
        
        // 获取输入流信息
        OutputStream outputStream = response.getOutputStream();
        
        // 获取数据并封装数据
        List<List<String>> dataList = new ArrayList<>();
        List<String> headList = Arrays.asList("学生编号", "学生姓名", "学生年龄");
        dataList.add(headList);

        // 获取数据
        List<Student> students = studentService.allStudentsList();
        students.forEach(student -> {
            List<String> rowList = new ArrayList<>();
            rowList.add(student.getStu_id() + "");
            rowList.add(student.getStu_name() + "");
            rowList.add(student.getStu_age() + "");
            dataList.add(rowList);
        });

        // 调用工具类到处Excel表格
        ExportExcelUtil.exportExcel(dataList,outputStream,false);

        // 刷新输出流
        outputStream.flush();
    }
}