导出Excel预览

1.准备环境

        
        
            cn.hutool
            hutool-all
            5.4.5
        
//测试接口导出
        
            org.springframework.boot
            spring-boot-starter-web
        
//poi包
        
            org.apache.poi
            poi-ooxml
            4.1.2
        
//测试包
        
            junit
            junit
            4.12
        
//lombok
        
            org.projectlombok
            lombok
            true
        

2.Demo

package com.example.demo.excel;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author pengyifeng
 * @version 2.0
 * @ClassName ExcelUser.java
 * @Description 导出类
 * @createTime 2021年11月15日 10:54:00
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelUser {
    private String name;
    private String age;
    private Date birthDay;
}

package com.example.demo.excel;

import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author pengyifeng
 * @version 2.0
 * @ClassName excelDemo.java
 * @Description 基础excel导出
 * @createTime 2021年11月15日 10:49:00
 */
//浏览器请求下载解除注释
//@RestController
public class ExcelDemo {
    //@RequestMapping("/export")
    //void export(HttpServletResponse response) throws FileNotFoundException {
    @Test
    public void export() throws FileNotFoundException {
        List list = new ArrayList<>();
        list.add(new ExcelUser("zhangsan", "1231", new Date()));
        list.add(new ExcelUser("zhangsan1", "1232", new Date()));
        list.add(new ExcelUser("zhangsan2", "1233", new Date()));
        list.add(new ExcelUser("zhangsan3", "1234", new Date()));
        list.add(new ExcelUser("zhangsan4", "1235", new Date()));
        list.add(new ExcelUser("zhangsan5", "1236", DateUtil.date(new Date())));
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        // 自定义标题别名
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("birthDay", "生日");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(2, "申请人员信息");
        //设置默认高度
        writer.setDefaultRowHeight(30);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //自适应列宽（必须放在writer.write(list, true)写入后）
        ExcelUtils.setSizeColumn(writer.getSheet(),6);

        //浏览器导出
        //ExcelUtils.downloadExcel(response,"excelDemo",writer);

        //本地测试下载
        FileOutputStream outputStream = new FileOutputStream("D:/home/excel/excelDemo.xlsx");
        writer.flush(outputStream, true);
        writer.close();
    }
}

package com.honghe.hours.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.extern.slf4j.Slf4j;
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.xssf.usermodel.XSSFCell;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;

/**
 * @author pengyifeng
 * @version 2.0
 * @ClassName ExcelUtils.java
 * @Description TODO
 * @createTime 2021年11月03日 16:48:00
 */
@Slf4j
public class ExcelUtils {
    /**
     * 方法描述: 下载excel文件
     *
     * @param response 响应
     * @param fileName 文件名称
     * @param writer   writer
     * @return void
     * @author pyf
     * @date 2021/11/3 16:20
     */
    public static void downloadExcel(HttpServletResponse response, String fileName, ExcelWriter writer) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        ServletOutputStream out = null;
        try {
            // 设置请求头属性
           // response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.ISO_8859_1));
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), StandardCharsets.UTF_8));
            out = response.getOutputStream();
            // 写出到文件
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            // 此处记得关闭输出Servlet流
            IoUtil.close(out);
        } catch (IOException e) {
            log.error(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size 因为for循环从0开始，size值为 列数-1
     */
    public static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
}