springboot实现数据库数据导出到Excel中并下载到浏览器本地

项目需求：写一个接口，实现点击该URL，可下载excel。excel内容为数据库表中内容

项目架构：SpringBoot+Mybatis

服务端实现：

• 第一步：导入POI依赖包

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

• 第二步：编写数据库表实体类（与数据库表一一对应）

import lombok.Data;

@Data
public class Company {
    private int id;
    private String companyId;
    private String companyName;
}

• 第三步：三层架构实现

1. Controller层

@GetMapping("/download")
    public void downloadExcel(HttpServletResponse response) throws IOException{
        //从数据库中获取表数据
        List<Company> list = authService.getAllCompany();
        //下载
        HSSFWorkbook wb = authService.downloadExcel(list);
        OutputStream output = response.getOutputStream();
// 文件名中文形式
        String fileName = "设置要导出的文件的名字"+".xls";//
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
// 文件名日期形式
//        response.setCharacterEncoding("UTF-8");
//        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//        response.addHeader("Content-Disposition", "attachment;filename=" + 
                + new SimpleDateFormat("yyyyMMdd_HHmmssSSS").format(new Date()) + ".xls");
// 文件名英文形式
//        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//        response.setContentType("application/msexcel");

//        response.setContentType("application/vnd.ms-excel");
        wb.write(output);
        output.close();
    }

2. service层

public interface AuthService {
    HSSFWorkbook downloadExcel(List<Company> list);
    List<Company> getAllCompany();
}

3. 实现类

@Override
    public HSSFWorkbook downloadExcel(List<Company> list) {
        String[] excelHeader = { "Id", "姓名"};
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("关系表");
        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            //设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
            sheet.setColumnWidth(cell.getColumnIndex(), 100 * 50);
        }

        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            Company company = list.get(i);
            row.createCell(0).setCellValue(company.getCompanyId());
            row.createCell(1).setCellValue(company.getCompanyName());
        }
        return wb;
    }

    /**
     * 获取数据
     * @return
     * @author 
     * @time 
     */
    @Override
    public List<Company> getAllCompany() {
        return authMapper.getAllCompany();
    }

• 第四步：测试（PostMan）

 

下载后的形式：

踩坑：

1. 下载文件名为中文出现乱码现象解决，也不明白为什么，反正很好用，上面代码也给出了中文、英文、和日期命名的不同代码。

try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);

2.测试使用PostMan，提供下载文件的功能