526互联

报表导出工具

发布时间 2023-09-21 14:04:05作者: 黄河大道东

目录

示例一

  • 依赖
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.5</version>
</dependency>

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

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

注意：放在resource目录下的xls与xlsx文件在编译打包时会被转码压缩，导致客户端在下载到资源后无法正常打卡，解决方法https://www.cnblogs.com/mr-yang-localhost/p/9702577.html

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelWriter;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;


/**
 * excel工具
 */
public class ExcelUtil extends cn.hutool.poi.excel.ExcelUtil {

    private static final String XLS_SUFFIX = ".xls";
    private static final String EXCEL_XLS_CONTENT_TYPE = "application/vnd.ms-excel;charset=utf-8";

    private static final String XLSX_SUFFIX = ".xlsx";
    private static final String EXCEL_XLSX_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8";


    /**
     * 导出excel文件
     *
     * @param resp 响应对象
     * @param data 文件数据
     * @param isXlsxType excel类型
     */
    public static void exportExcelFile(HttpServletResponse resp, ExcelData data, Boolean isXlsxType) throws IOException {
        ExcelWriter writer;
        StringBuilder fileName = new StringBuilder("attachment;filename=" + URLEncoder.encode(data.getFileName(), StandardCharsets.UTF_8));
        if (isXlsxType) {
            writer = getWriter(isXlsxType);
            fileName = fileName.append(XLSX_SUFFIX);
            resp.setContentType(EXCEL_XLSX_CONTENT_TYPE);
        } else {
            writer = getWriter();
            fileName = fileName.append(XLS_SUFFIX);
            resp.setContentType(EXCEL_XLS_CONTENT_TYPE);
        }
        resp.setHeader("Content-Disposition", fileName.toString());
        writer.renameSheet(data.getSheetName());
        if (ObjectUtil.isNotNull(data.getTitle())) {
            writer.merge(data.getRows().get(0).size() - 1, data.getTitle());
        }
        writer.write(data.getRows(), true);
        writer.flush(resp.getOutputStream());
        writer.close();
    }
}

import lombok.Data;
import lombok.experimental.Accessors;
import java.util.List;
import java.util.Map;

/**
 * excel 数据对象
 */
@Data
@Accessors(chain = true)
public class ExcelData {
    private String fileName;
    private String sheetName;
    private String title;
    private List<Map<String, ?>> rows;
}

示例二

注意：放在resource目录下的xls与xlsx文件在编译打包时会被转码压缩，导致客户端在下载到资源后无法正常打卡，解决方法https://www.cnblogs.com/mr-yang-localhost/p/9702577.html

pom依赖

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

导出工具

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class ExportExcelTool {

    /**
     * 导出excel 包含多个sheet
     *
     * @param response 响应信息
     * @param dataList 需导出的数据
     * @param fileName 文件名称.xls
     */
    public static void exportSheet(HttpServletResponse response, List<ExcelData> dataList, String fileName) {
        try {
            // 创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            for (int k = 0; k < dataList.size(); k++) {
                ExcelData data = dataList.get(k);
                // 创建工作表
                createSheet(workbook, data);
            }
            if (workbook != null) {
                try {
                    response.setHeader("content-Type", "application/vnd.ms-excel");
                    // 下载文件的默认名称
                    response.setHeader("Content-Disposition", "attachment;filename="
                            + URLEncoder.encode(fileName, "utf-8"));
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建表格sheet
     *
     * @param workbook 工作簿
     * @param data     sheet数据
     * @return 封装完成后的sheet
     */
    public static HSSFSheet createSheet(HSSFWorkbook workbook, ExcelData data) {
        // 创建工作表
        HSSFSheet sheet = workbook.createSheet(data.getName());
        // 获取列头样式对象
        HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
        // 单元格样式对象
        HSSFCellStyle style = getStyle(workbook);
        // 定义所需列数
        int columnNum = data.getTitles().size();
        // 在索引0的位置创建行(最顶端的行开始)
        HSSFRow rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            Map<String, String> titleMap = data.getTitles().get(n);
            Iterator<Map.Entry<String, String>> iterator = titleMap.entrySet().iterator();
            if (iterator.hasNext()) {
                Map.Entry<String, String> entry = iterator.next();
                // 创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                // 设置列头单元格的数据类型
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString text = new HSSFRichTextString(entry.getKey());
                // 设置列头单元格的值
                cellRowName.setCellValue(text);
                // 设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
                if (StrUtil.isNotBlank(entry.getValue())) {
                    // 创建批注
                    HSSFPatriarch patr = sheet.createDrawingPatriarch();
                    // 创建批注位置
                    HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, n,
                            1, n + 3, 3);
                    HSSFComment comment = patr.createCellComment(anchor);
                    // 设置批注内容
                    comment.setString(new HSSFRichTextString(entry.getValue()));
                    cellRowName.setCellComment(comment);
                }
            }

        }

        if (CollUtil.isNotEmpty(data.getRows())) {
            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < data.getRows().size(); i++) {
                // 遍历每个对象
                List<Object> objList = data.getRows().get(i);
                if (CollUtil.isNotEmpty(objList)) {
                    // 创建所需的行数
                    HSSFRow row = sheet.createRow(i + 1);
                    for (int j = 0; j < objList.size(); j++) {
                        HSSFCell cell = row.createCell(j);
                        if (objList.get(j) != null && !"".equals(objList.get(j))) {
                            // 设置单元格的值
                            cell.setCellValue(objList.get(j).toString());
                        } else {
                            cell.setCellValue("");
                        }
                        // 设置单元格样式
                        cell.setCellStyle(style);
                    }
                }
            }
        }
        return sheet;
    }


    /**
     * 导出Excel
     *
     * @param response 响应信息
     * @param data     需导出的数据
     * @throws Exception 异常
     */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        try {
            // 创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建工作表
            createSheet(workbook, data);
            if (workbook != null) {
                try {
                    String fileName = data.getName() + ".xls";
                    response.setHeader("content-Type", "application/vnd.ms-excel");
                    // 下载文件的默认名称
                    response.setHeader("Content-Disposition", "attachment;filename="
                            + URLEncoder.encode(fileName, "utf-8"));
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出Excel
     *
     * @param data 需导出的数据
     * @throws Exception 异常
     */
    public static void exportExcel(OutputStream out, ExcelData data) {
        try {
            // 创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建工作表
            createSheet(workbook, data);
            if (workbook != null) {
                try {
                    workbook.write(out);
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 列头单元格样式
     *
     * @param workbook 工作簿
     * @return 单元格样式
     */
    public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * 列数据信息单元格样式
     *
     * @param workbook 工作簿
     * @return 单元格样式
     */
    public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
}

导出数据实体

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelData {
    /**
     * 名称
     */
    private String name;

    /**
     * 标题 key 标题 value：备注
     */
    private List<Map<String, String>> titles;

    /**
     * 行数据
     */
    private List<List<Object>> rows = new ArrayList<>();

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Map<String, String>> getTitles() {
        return titles;
    }

    public void setTitles(List<Map<String, String>> titles) {
        this.titles = titles;
    }

    public List<List<Object>> getRows() {
        return rows;
    }

    public void setRows(List<List<Object>> rows) {
        this.rows = rows;
    }
}

示例三

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.5</version>
</dependency>

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

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

    public static void exportForCarRecord(HttpServletResponse response, String titleTxt, List<String> headers, String fileName, LinkedList<LinkedList<Object>> data) throws IOException {
        BigExcelWriter bigWriter = ExcelUtil.getBigWriter();
        // 设置自动换行
        bigWriter.getStyleSet().getCellStyle().setWrapText(true);
        // 垂直对齐
        bigWriter.getStyleSet().getCellStyle().setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平对齐
        bigWriter.getStyleSet().getCellStyle().setAlignment(HorizontalAlignment.LEFT);
        // 设置字体
        Font font = bigWriter.getWorkbook().createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 12);
        bigWriter.getStyleSet().getCellStyle().setFont(font);
        // 合并单元格,并往这个单元格写指定内容,不是用默认格式
        bigWriter.merge(5, titleTxt, false);
        // 设置第一行的行高
        bigWriter.setRowHeight(0, 40);
        // 默认行高
        bigWriter.setDefaultRowHeight(20);
        // 设置当前行号
        bigWriter.setCurrentRow(1);
        // 设置列宽
        bigWriter.setColumnWidth(0, 30);
        bigWriter.setColumnWidth(1, 30);
        bigWriter.setColumnWidth(2, 30);
        bigWriter.setColumnWidth(3, 30);
        bigWriter.setColumnWidth(4, 30);
        bigWriter.setColumnWidth(5, 30);
        // 写表格的title的内容
        bigWriter.writeHeadRow(headers);
        // 写具体的数据体
        bigWriter.write(data);
        // 导出到输出流中
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        bigWriter.flush(outputStream, true);
        bigWriter.close();
        // 配置响应对象
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.getOutputStream().write(outputStream.toByteArray());
    }

调用示例

    @ApiOperation("查询车辆出入场记录")
    @AnonymousGetMapping("download")
    public void downList(CarRecordQueryCriteria criteria, Pageable pageable, HttpServletResponse response) throws IOException {
        Dict result = carRecordService.queryAll(criteria, pageable, true);
        Map<String, Object> upAreaInfo = areaService.queryAll().get(0);
        LinkedList<CarRecord> type = null;
        LinkedList<CarRecord> records = result.get("content", type);
        LinkedList<LinkedList<Object>> data = new LinkedList<>();
        for (CarRecord carRecord : records) {
            LinkedList<Object> objects = new LinkedList<>();
            objects.add(carRecord.getSn());
            objects.add(carRecord.getInTime());
            objects.add(carRecord.getOutTime());
            objects.add(carRecord.getStayHour());
            if (MapUtil.getStr(upAreaInfo, "location").equals(carRecord.getLocation())) {
                objects.add(MapUtil.getStr(upAreaInfo, "start_point") + " 至 " + MapUtil.getStr(upAreaInfo, "end_point"));
            } else {
                objects.add(MapUtil.getStr(upAreaInfo, "end_point") + " 至 " + MapUtil.getStr(upAreaInfo, "start_point"));
            }
            if ("0".equals(carRecord.getTimeoutFlag())) {
                objects.add("是");
            } else {
                objects.add("否");
            }
            data.add(objects);
        }
        String fileName = "过车记录" + DateUtil.date().toString().replaceAll("\\s|:|-", "_") + ".xlsx";
        String name = MapUtil.getStr(upAreaInfo, "parent_name");
        String begin = null;
        if (!CollectionUtils.isEmpty(records)) {
            CarRecord carRecord = records.get(0);
            begin = DateUtil.date(carRecord.getCreateTime().getTime()).toString();
        } else {
            begin = DateUtil.now().toString();
        }
        String end = DateUtil.now().toString();
        String titleTxt = StrUtil.format("{}{}至{}过车记录\r\n导出时间：{}", name, begin, end, end);
        List<String> headers = new ArrayList<>();
        headers.add("车牌号");
        headers.add("入场时间");
        headers.add("出场时间");
        headers.add("停留时间");
        headers.add("进入区域");
        headers.add("是否停留超时");
        ExcelUtils.exportForCarRecord(response, titleTxt, headers, fileName, data);
    }

效果
image