java POI 导出 Excel

一、导入相关的依赖

<!-- POI start-->

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- POI end-->

二、导入表格的思路

1）创建工作簿

2）创建 sheet

3）创建 row 行对象

4）通过行对象，给每一个单元格设置值

所以 有一个双重for 循环，表格是二维的，两层循环

5）利用导出的工具类导出

三、工具类

package com.htb.beidawebpoi.demo06.utils;

import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        //保存的文件名,必须和页面编码一致,否则乱码
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
        response.addHeader("Content-Disposition","attachment;filename="+returnName);
        response.setContentLength(byteArrayOutputStream.size());
        response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
        //取得输出流
        ServletOutputStream outputstream = response.getOutputStream();
        //写到输出流
        byteArrayOutputStream.writeTo(outputstream);
        //关闭
        byteArrayOutputStream.close();
        //刷数据
        outputstream.flush();

    }
}

四、导出代码实现

package com.htb.beidawebpoi.demo06.controller;

import com.htb.beidawebpoi.demo06.entity.User;
import com.htb.beidawebpoi.demo06.service.UserService;
import com.htb.beidawebpoi.demo06.utils.DownloadUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author 16221
 * @Date 2020/6/20
 **/
@RestController
public class DownCtrl {
    @Resource
    private UserService userService;

    @GetMapping(value = "/down")
    public void down(HttpServletRequest request,HttpServletResponse response) throws IOException {


        List<User> allUser = userService.getAllUser();



        //1.创建工作簿
        Workbook wb = new XSSFWorkbook();
        //2.构造sheet
        Sheet sheet = wb.createSheet();
        //3.创建行
        //标题
        String[] titles = "编号,姓名,手机号,密码,出生日期".split(",");


        Row row = sheet.createRow(0);
        int  titleIndex = 0;
        for(String title:titles){
            Cell cell = row.createCell(titleIndex++);
            cell.setCellValue(title);
        }



        int rowIndex = 1;
        //4.创建单元格
        for(User u:allUser){
            row = sheet.createRow(rowIndex++);
            Cell cell = row.createCell(0);
            cell.setCellValue(u.getId());

            cell = row.createCell(1);
            cell.setCellValue(u.getName());

            cell = row.createCell(2);
            cell.setCellValue(u.getPhone());

            cell = row.createCell(3);
            cell.setCellValue(u.getPassword());

            cell = row.createCell(4);
            cell.setCellValue(u.getBirth());


        }

        ByteArrayOutputStream os = new ByteArrayOutputStream();

        wb.write(os);
        //思路3：导出
        new DownloadUtils().download(os,response,"人事报表.xlsx");
    }
}

五、测试

浏览器调用上面的接口即可

http://localhost:8080/down