web_server/src/main/java/thyyxxk/webserver/entity/excel/ToolExcel.java

package thyyxxk.webserver.entity.excel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import lombok.Data;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ToolExcel {
    private final List<Integer> widthIndex = new ArrayList<>();
    private final BigExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
    private String format = "yyyy-mm-dd hh:mm:ss";

    public ToolExcel() {

    }


    public ToolExcel addTitle(String name, String alias, int width) {
        writer.addHeaderAlias(name, alias);
        widthIndex.add(width);
        return this;
    }

    public ToolExcel addTitle(String name, String alias) {
        writer.addHeaderAlias(name, alias);
        widthIndex.add(null);
        return this;
    }

    public ToolExcel setDataFormat(String format) {
        this.format = format;
        return this;
    }

    private void setCellDateFormat() {
        DataFormat dataFormat = writer.getWorkbook().createDataFormat();
        short format = dataFormat.getFormat(this.format);
        StyleSet styleSet = writer.getStyleSet();
        styleSet.getCellStyleForDate()
                .setDataFormat(format);
    }

    private void setCellWeight() {
        for (int i = 0; i < widthIndex.size(); i++) {
            Integer width = widthIndex.get(i);
            if (width != null) {
                writer.setColumnWidth(i, width);
            }
        }
    }

    public void exportExcel(Iterable<?> data, HttpServletResponse response) {

        setCellDateFormat();
        setCellWeight();

        writer.setOnlyAlias(true);
        writer.write(data, true);

        writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);

        ServletOutputStream out;
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=test.xls");

            out = response.getOutputStream();
            // 将Excel Workbook刷出到输出流
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("文件写入失败！");
        } finally {
            //关闭流
            writer.close();
        }
        IoUtil.close(out);
    }

}