基于pio实现的java excel导出、合并单元格导出

最新推荐文章于 2024-08-28 09:00:00 发布

ssssssssssy1

最新推荐文章于 2024-08-28 09:00:00 发布

阅读量1k

点赞数 21

文章标签： excel

本文链接：https://blog.csdn.net/ssssssssssy1/article/details/140178552

版权

再复杂的excel导出也是基于最朴实的,设置单元格样式,填充数据,设置表头,单元格合并几个语法,然后我再说点要注意的,单元格是用坐标轴表示位置的,是从(0,0)开始的。还有个要注意的就是设置样式，合并指定单元格，设置行宽行高这些语法，都是要指定某一格单元格，在使用这些语法的时候首先要创建单元格，这个单元格单元格要是不存在你给这个单元格创建样式和合并或者设置行宽行高就会报空指针

完整代码我放文章末尾我先描述一下语法

// 创建新的Excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();

// 在工作簿中创建新的工作表
XSSFSheet sheet = workbook.createSheet(sheetName);

// 创建表头行
XSSFRow headerRow = sheet.createRow(0);

// 设置第一大行标题
XSSFCell cell0 = headerRow.createCell(0);
cell0.setCellValue(title);

// 合并第一行的单元格,使标题占满整行
//参数说明：1：开始行 2：结束行 3：开始列 4：结束列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnTextArray.length - 1));

//设置样式
cell.setCellStyle(getTitleCellStyle(workbook));

//设置表格样式
private static CellStyle getTitleCellStyle(XSSFWorkbook workbook) {
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setFont(createBoldFont(workbook));
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    return cellStyle;
}

//设置指定列宽度

sheet.setColumnWidth(i, maxColumnCount * 256);

//可以搭配这个方法

private static void autoAdColumnWidth(XSSFSheet sheet, String[] columnTextArray) {
    int maxColumnCount = 0;
    for (String columnText : columnTextArray) {
        maxColumnCount = Math.max(maxColumnCount, columnText.length());
    }
    maxColumnCount = Math.max(maxColumnCount, 25); // 设置最小宽度为25个字符

    for (int i = 0; i < columnTextArray.length; i++) {
        sheet.setColumnWidth(i, maxColumnCount * 256); // 设置列宽
    }
}

//设置指定行高数据集有多少行设置比遍历多少行用fori循环就好

sheet.getRow(i).setHeightInPoints(25);
private static void autoAdjustRowHeight(XSSFSheet sheet, int rowCount) {

    for (int i = 0; i < rowCount; i++) {
        XSSFRow row = sheet.getRow(i);
        if (row == null) {
            row = sheet.createRow(i);
        }
        row.setHeightInPoints(25);
    }
}

//如果需要小数点位数的话可以用这个设置
row1.createCell(1).setCellValue(String.valueOf(String.format("%.6f", dto.getNumPrice())));

基本上就是这些语法然后我再附上工具类我写的工具类以及使用方法有单元格直接导出的, 还有的是按主数据id合并单元格导出的

package com.xtql.SmartProduceManager.utils;

import com.vqor.core.exception.BusinessException;
import com.vqor.core.util.BeanUtil;
import com.vqor.core.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

public class ExcUtil {

   

    public static List<Map<String, Object>> processRowData(List<?> list) {


        List<Map<String, Object>> dataList = new ArrayList<>();

        Map<String, String> rowMap = new LinkedHashMap<>();

        for (Object dto : list) {
            Map<String, Object> map = BeanUtil.getPMap(dto);
            String id = (String) map.get("id");
            if (!rowMap.containsKey(id)) {
                rowMap.put(id, String.valueOf(rowMap.size() + 1));
            }
            map.put("row", rowMap.get(id));
            dataList.add(map);
        }

        return dataList;
    }

    /**
     * //单元格合并 mergeColumn 传入的是一个int数组 传入那一列合并那一列指定合并列
     *  合并规则是  字段名称id相同指定列合并   如果不传数组 就是普通的excel导出
     *   需要序号的话 加入参数实体类加入row字段  数据库返回row字段(只要是数字都行方法内会重新处理row值)  参数数组中 columnTextArray  columnNames   加入 序号 , row  作为第一列就能自动处理
     *
     * @Date：2024-06-22 11:41
     * @Author：yechendong
     */


    public void exportMergeExcel(String sheetName, String title, HttpServletResponse response, List list, String[] columnTextArray, String[] columnNames, int[] mergeColumn) {

        List<Map<String, Object>> dataList = processRowData(list);
        try {

            // 创建新的Excel工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();

            // 在工作簿中创建新的工作表
            XSSFSheet sheet = workbook.createSheet(sheetName);

            // 创建表头行
            XSSFRow headerRow = sheet.createRow(0);

            // 设置第一大行标题
            XSSFCell cell0 = headerRow.createCell(0);
            cell0.setCellValue(title);

            // 合并第一行的单元格,使标题占满整行
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnTextArray.length - 1));


            // 设置标题单元格的样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            XSSFFont titleFont = workbook.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 20);
            titleStyle.setFont(titleFont);
            // 设置标题行边框样式

            cell0.setCellStyle(titleStyle);


            // s设置表头
            XSSFRow firstRow = sheet.createRow(1);
            // s设置表头


            for (int i = 0; i < columnTextArray.length; i++) {
                // 创建数据行
                XSSFCell cell = firstRow.createCell(i);
                cell.setCellValue(columnTextArray[i]);
                cell.setCellStyle(getTitleCellStyle(workbook));

            }
            // 遍历数据列表,为每一行填充单元格值
            for (int i = 0; i < dataList.size(); i++) {

                // 获取当前行的 Map 数据
                Map<String, Object> rowData = dataList.get(i);

                // 创建新的数据行
                XSSFRow dataRow = sheet.createRow(i + 2);

                // 遍历属性名称数组,设置单元格值
                for (int j = 0; j < columnNames.length; j++) {
                    // 根据列名从 Map 中获取对应的值
                    Object cellValue = rowData.get(columnNames[j]);

                    // 创建单元格并设置值
                    XSSFCell cell = dataRow.createCell(j);
                    if (cellValue instanceof Date) {
                        cell.setCellValue((Date) cellValue);
                    } else if (cellValue instanceof Number) {
                        cell.setCellValue(((Number) cellValue).doubleValue());
                    } else {
                        cell.setCellValue(cellValue != null ? cellValue.toString() : "");
                    }
                    // 设置单元格样式
                    cell.setCellStyle(getDataCellStyle(workbook));
                }
            }
            //单元格合并
            merge(dataList, mergeColumn, sheet);
            // 自动调整列宽和行高
            // sheet.addMergedRegion(new CellRangeAddress(2, 7, 0, 0));
            autoAdColumnWidth(sheet, columnTextArray);
            autoAdjustRowHeight(sheet, dataList.size() + 2);
            //sheet.getRow(i).setHeightInPoints(25);
            // 设置响应头信息以触发文件下载

            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;Filename=" + "downloadFileName");


            // 将工作簿写入响应输出流
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("文件下载失败,请重试!");
        }
    }

    private static XSSFFont createBoldFont(XSSFWorkbook workbook) {
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        return font;
    }

    //设置表头表格样式
    private static CellStyle getTitleCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(createBoldFont(workbook));
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return cellStyle;
    }

    // 自动调整列宽和行高
    private static void autoAdjustRowHeight(XSSFSheet sheet, int rowCount) {
        for (int i = 0; i < rowCount; i++) {
            sheet.getRow(i).setHeightInPoints(25);
        }
    }

    //设置内容样式
    private static CellStyle getDataCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //合并单元格
    public static void merge(List<Map<String, Object>> dataList, int[] mergeColumn, Sheet sheet) {

        Map<String, int[]> idToMinMax = new TreeMap<>();

        for (int i = 0; i < dataList.size(); i++) {
            Map<String, Object> rowData = dataList.get(i);
            //主表数据id相同的 合并指定列
            String id = String.valueOf(rowData.get("id"));
            //id相同的组成一个map  然后记录开始行 和结束行 作为合并的规则
            if (!idToMinMax.containsKey(id)) {
                idToMinMax.put(id, new int[]{i + 2, i + 2}); // 从第 3 行开始
            } else {
                int[] minMax = idToMinMax.get(id);
                minMax[1] = i + 2; // 更新最大行号
                idToMinMax.put(id, minMax);
            }
        }
        //根据map 合并指定列
        for (Map.Entry<String, int[]> entry : idToMinMax.entrySet()) {
            int[] minMax = entry.getValue();
            System.out.println(minMax[0] + " " + minMax[1]);
            if (minMax[0] == minMax[1]) {
                continue;
            }
            for (int i = 0; i < mergeColumn.length; i++) {

                sheet.addMergedRegion(new CellRangeAddress(minMax[0], minMax[1], mergeColumn[i] - 1, mergeColumn[i] - 1));
            }

        }
    }

    // 自动调整列宽
    private static void autoAdColumnWidth(XSSFSheet sheet, String[] columnTextArray) {
        int maxColumnCount = 0;
        for (String columnText : columnTextArray) {
            maxColumnCount = Math.max(maxColumnCount, columnText.length());
        }
        maxColumnCount = Math.max(maxColumnCount, 25); // 设置最小宽度为25个字符

        for (int i = 0; i < columnTextArray.length; i++) {
            sheet.setColumnWidth(i, maxColumnCount * 256); // 设置列宽
        }
    }

}

  
//需要合并的列在合并数组中 要合并的列 id值要相同才能合并
public void exportGroupStatement(DailyCondition dailyManagerDto, HttpServletResponse response) {
        if (dailyManagerDto.getId() != null && !dailyManagerDto.getId().isEmpty()) {
            String[] ids = dailyManagerDto.getId().split(",");
            dailyManagerDto.setIds(ids);
        }

        List dailyDetailExcelDtos = super.getJdbcBaseDao().queryForList("dailyManager.selectDailyDetailExcel", dailyManagerDto, DailyDetailExcelDto.class);
        String[] columnTextArray = {
                "序号",
                "人员",
                "日期",
                "逾期状态",
                "总工时",
                "工时单价",
                "点工工时",
                "工资总额",
                "计时金额",
                "备注",
                "协作人",
                "制造数量",
                "构件编码",
                "图号/标准号",
                "构件名称",
                "规格型号",
                "喷涂面积(㎡)",
                "构件单重(kg)",
                "总重(kg)",
                "工序单价",
                "计件金额",
                "计件工资分配",
                "制造工序",
                "产品分类",
                "制造类别",
                "项目名称",

        };
        String[] columnNames = {
                "row",
                "realName", "dailyDate", "overdueStatus", "totalHour", "workPrice", "workHour", "allPrice", "timePrice", "remark", "collaborators", "productNum",
                "cInvCode", "standardCode", "cInvName", "cInvStd", "sprayArea", "iInvWeight",
                "totalWeight", "numPrice", "pieceworkAmount", "pieceworkWageDistribution",
                "processName", "inventoryClassName",
                "methodName", "projectName"
        };

        int[] mergeColumn = {
                1, 2, 3, 4, 5, 6, 7, 8, 9, 10
        };
        exportService.exportMergeExcel("a报表", "a报表", response, dailyDetailExcelDtos, columnTextArray, columnNames, mergeColumn);
    }

一般只支持两层的数据合并 , 不需要合并单元格这个也能直接导出功能还是蛮强大的