java定义excel表格的样式

骑猪看日落 2023-10-10 18:40 84阅读 0赞

package com.luxsan.lpms.utils;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.*;
    import java.awt.Color;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelTools {
    
        public static short computeRowHeight(Integer num) {
            return (short) (20 * num);
        }
    
        /**
         * 返回长度最大宽度不超过47个字符（过长的单元格影响美观）
         * */
        public static int computeColumnWidth(Integer num) {
            return (num > 47 ? 47 : num) * 256 + 184;
        }
    
        /**
         * 定义表头样式
         */
        public static XSSFCellStyle getHeaderStyle(XSSFWorkbook workbook) {
            XSSFCellStyle cs = workbook.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cs.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cs.setBorderBottom(BorderStyle.THIN);     //设置下边框
            cs.setBorderTop(BorderStyle.THIN);        //设置上边框
            cs.setBorderLeft(BorderStyle.THIN);       //设置左边框
            cs.setBorderRight(BorderStyle.THIN);      //设置右边框
            // 设置背景填充色
            cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cs.setFillForegroundColor(new XSSFColor(new Color(189, 215, 238)));
            // 设置字体
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            cs.setFont(font);
            return cs;
        }
    
        /**
         * 定义红色样式
         */
        public static XSSFCellStyle getRedStyle(XSSFWorkbook workbook) {
            XSSFCellStyle cs = workbook.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cs.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cs.setBorderBottom(BorderStyle.THIN);     //设置下边框
            cs.setBorderTop(BorderStyle.THIN);        //设置上边框
            cs.setBorderLeft(BorderStyle.THIN);       //设置左边框
            cs.setBorderRight(BorderStyle.THIN);      //设置右边框
            // 设置背景填充色
            cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cs.setFillForegroundColor(new XSSFColor(new Color(215, 14, 54)));
            // 设置字体
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            cs.setFont(font);
            return cs;
        }
    
        /**
         * 定义数据单元格样式
         */
        public static XSSFCellStyle getDataStyle(XSSFWorkbook workbook) {
            // 数据单元格样式
            XSSFCellStyle dataCS = workbook.createCellStyle();
            dataCS.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            dataCS.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            dataCS.setBorderBottom(BorderStyle.THIN);     //设置下边框
            dataCS.setBorderTop(BorderStyle.THIN);        //设置上边框
            dataCS.setBorderLeft(BorderStyle.THIN);       //设置左边框
            dataCS.setBorderRight(BorderStyle.THIN);      //设置右边框
            dataCS.setWrapText(true); // 设置自动换行显示
            return dataCS;
        }
    
        /**
         * 定义单元格样式
         */
        public static XSSFCellStyle getRedCellStyle(XSSFWorkbook workbook) {
            XSSFCellStyle cs = workbook.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cs.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cs.setBorderBottom(BorderStyle.THIN);     //设置下边框
            cs.setBorderTop(BorderStyle.THIN);        //设置上边框
            cs.setBorderLeft(BorderStyle.THIN);       //设置左边框
            cs.setBorderRight(BorderStyle.THIN);      //设置右边框
            // 设置背景填充色
            cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cs.setFillForegroundColor(new XSSFColor(new Color(255, 5, 5)));
            // 设置字体
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            cs.setFont(font);
            return cs;
        }
    
        public static XSSFCellStyle getYellowCellStyle(XSSFWorkbook workbook) {
            XSSFCellStyle cs = workbook.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            cs.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            cs.setBorderBottom(BorderStyle.THIN);     //设置下边框
            cs.setBorderTop(BorderStyle.THIN);        //设置上边框
            cs.setBorderLeft(BorderStyle.THIN);       //设置左边框
            cs.setBorderRight(BorderStyle.THIN);      //设置右边框
            // 设置背景填充色
            cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cs.setFillForegroundColor(new XSSFColor(new Color(255, 255, 0)));
            // 设置字体
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            cs.setFont(font);
            return cs;
        }
    
        /**
         * 定义 Total单元格样式
         */
        public static XSSFCellStyle getTotalStyle(XSSFWorkbook workbook) {
            // 汇总单元格样式
            XSSFCellStyle totalCS = workbook.createCellStyle();
            totalCS.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
            totalCS.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
            totalCS.setBorderBottom(BorderStyle.THIN);     //设置下边框
            totalCS.setBorderTop(BorderStyle.THIN);        //设置上边框
            totalCS.setBorderLeft(BorderStyle.THIN);       //设置左边框
            totalCS.setBorderRight(BorderStyle.THIN);      //设置右边框
            totalCS.setWrapText(true); // 设置自动换行显示
            totalCS.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置背景填充色
            totalCS.setFillForegroundColor(new XSSFColor(new Color(221, 235, 247)));
            return totalCS;
        }
    
        public static void writeSheet(List<Map<String, Object>> list,
                                      List<String> header,
                                      List<String> keys,
                                      List<String> dateArr,
                                      XSSFWorkbook workbook,
                                      XSSFSheet sheet,
                                      Integer headerStartRow, Integer beforeHeaderSize, Integer metaSize) {
            XSSFCellStyle headerStyle = getHeaderStyle(workbook);
            XSSFCellStyle totalStyle = getTotalStyle(workbook);
            XSSFCellStyle dataStyle = getDataStyle(workbook);
            XSSFCellStyle totalStyleRate = getTotalStyle(workbook);
            totalStyleRate.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
            XSSFCellStyle dataStyleRate = getDataStyle(workbook);
            dataStyleRate.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
    
            XSSFRow row = sheet.createRow(headerStartRow);
            row.setHeight(computeRowHeight(15));// 设置行高
            Integer[] columnWidth = new Integer[header.size()]; // 用于记录列宽
            for (int i = 0; i < header.size(); i++) {
                XSSFCell cell = row.createCell(i);
                if (dateArr.size() > 0 && i >= (header.size() - (metaSize * dateArr.size()))) {
                    cell.setCellValue(dateArr.get((i - (header.size() - (metaSize * dateArr.size()))) / metaSize));
                } else {
                    cell.setCellValue(header.get(i));
                }
                columnWidth[i] = header.get(i).length();
                cell.setCellStyle(headerStyle);
            }
            if (dateArr.size() != 0 && metaSize != 0) {
                row = sheet.createRow(headerStartRow + 1);
                row.setHeight(computeRowHeight(15));// 设置行高
                for (int i = 0; i < header.size(); i++) {
                    XSSFCell cell = row.createCell(i);
                    cell.setCellValue(header.get(i));
                    cell.setCellStyle(headerStyle);
                }
                // 合并表头单元格
                for (int i = 0; i < header.size(); i++) {
                    if (i >= beforeHeaderSize && i >= (header.size() - (metaSize * dateArr.size()))) {
                        if (metaSize != 1) {
                            sheet.addMergedRegion(new CellRangeAddress(headerStartRow, headerStartRow, i, i + metaSize - 1));
                        }
                        i += metaSize - 1;
                    } else {
                        sheet.addMergedRegion(new CellRangeAddress(headerStartRow, headerStartRow + 1, i, i));
                    }
                }
            } else {
                headerStartRow--; // 修正数据起始行
            }
            // 写入数据
            for (int i = 0; i < list.size(); i++) {
                Integer rowIndex = headerStartRow + 2 + i;
                row = sheet.createRow(rowIndex);
                row.setHeight(computeRowHeight(15));
                Map<String, Object> rowData = list.get(i);
                boolean flag = false; // 标记是否为汇总行
                for (int j = 0; j < keys.size(); j++) {
                    XSSFCell cell = row.createCell(j);
                    String value = rowData.get(keys.get(j)) == null ? "" : rowData.get(keys.get(j)).toString();
                    //cell.setCellValue(value);
                    if (header.get(j).indexOf("(#)") != -1) {
                        int num;
                        try {
                            num = Integer.parseInt(value);
                            cell.setCellValue(num);
                        } catch (Exception e) {
                            cell.setCellValue(value);
                        }
                        if (flag || checkTotalRow(value)) {
                            cell.setCellStyle(totalStyle);
                            flag = true;
                        } else {
                            cell.setCellStyle(dataStyle);
                        }
                    } else if (header.get(j).indexOf("(%)") != -1) {
                        float rate;
                        try {
                            if (value.indexOf("%") != -1) {
                                rate = Float.parseFloat(value.substring(0, value.length() - 1)) / 100;
                            } else {
                                rate = Float.parseFloat(value);
                            }
                            cell.setCellValue(rate);
                            if (flag || checkTotalRow(value)) {
                                cell.setCellStyle(totalStyleRate);
                                flag = true;
                            } else {
                                cell.setCellStyle(dataStyleRate);
                            }
                        } catch (Exception e) {
                            cell.setCellValue(value);
                            if (flag || checkTotalRow(value)) {
                                cell.setCellStyle(totalStyle);
                                flag = true;
                            } else {
                                cell.setCellStyle(dataStyle);
                            }
                        }
                    } else {
                        cell.setCellValue(value);
                        if (flag || checkTotalRow(value)) {
                            cell.setCellStyle(totalStyle);
                            flag = true;
                        } else {
                            cell.setCellStyle(dataStyle);
                        }
                    }
    
                    if (columnWidth[j] < value.length())
                        columnWidth[j] = value.length();
                }
            }
            // 设置列宽
            for (int i = 0; i < columnWidth.length; i++) {
                sheet.setColumnWidth(i, computeColumnWidth(columnWidth[i] + 2));
            }
            // 合并数据单元格
            Integer[] sls = new Integer[beforeHeaderSize];
            for (int i = 0; i < sls.length; i++) {
                sls[i] = headerStartRow + 2;
            }
            for (int i = 1; i < list.size(); i++) { // 行
                Map<String, Object> prd = list.get(i - 1);
                Map<String, Object> rd = list.get(i);
                for (int j = 0; j < beforeHeaderSize; j++) { // 前 beforeHeaderSize 列
                    if (!compareRowData(prd.get(keys.get(j)), rd.get(keys.get(j)))) {
                        while (j < beforeHeaderSize) {
                            if (sls[j] < i + headerStartRow + 2 - 1)
                                sheet.addMergedRegion(new CellRangeAddress(sls[j], i + headerStartRow + 2 - 1, j, j));
                            sls[j] = i + headerStartRow + 2;
                            j++;
                        }
                    }
                }
            }
            for (int j = 0; j < beforeHeaderSize; j++) {
                if (sls[j] < list.size() + headerStartRow + 2 - 1)
                    sheet.addMergedRegion(new CellRangeAddress(sls[j], list.size() + headerStartRow + 2 - 1, j, j));
            }
        }
    
        public static boolean compareRowData(Object o1, Object o2) {
            String v1 = o1 == null ? "" : o1.toString();
            String v2 = o2 == null ? "" : o2.toString();
            return v1.equals(v2);
        }
    
        /**
         * 判断是否为汇总行
         */
        private static boolean checkTotalRow(String value) {
            boolean flag = false;
            if ((value != null || value.length() > 0) && value.indexOf("Total") != -1) {
                flag = true;
            }
            return flag;
        }
    /*
        public static void createLineChart(XSSFSheet sheet, Map<String, Object> params) {
            // 创建绘图区
            CTPlotArea ctPlotArea = createCTPlotArea(sheet, (int[]) params.get("chartPosition"));
    //        // 绘制图表
    //        createCTLineChart(sheet, ctPlotArea, params);
        }
    
        *//**
         * create CTPlotArea
         *
         * @param chartPosition int[]{startCol, startRow, endCol, endRow,
         *                      xOffsetInStartCell, yOffsetInStartCell, xOffsetInEndCell, yOffsetInEndCell}
         *//*
        private static CTPlotArea createCTPlotArea(XSSFSheet sheet, int[] chartPosition) {
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor =
                    drawing.createAnchor(
                            chartPosition[4] * Units.EMU_PER_POINT, chartPosition[5] * Units.EMU_PER_POINT,
                            chartPosition[6] * Units.EMU_PER_POINT, chartPosition[7] * Units.EMU_PER_POINT,
                            chartPosition[0], chartPosition[1], chartPosition[2], chartPosition[3]);
            XSSFChart xssfChart = drawing.createChart(anchor);
            CTChart ctChart = xssfChart.getCTChart();
            CTPlotArea ctPlotArea = ctChart.getPlotArea();
            xssfChart.setTitleText("set xssfChart Title"); // set title
            ctChart.getTitle().addNewOverlay().setVal(false);// 图表标题位置（图标上方、居中覆盖）
            ctChart.addNewShowDLblsOverMax().setVal(true);
            ctChart.addNewDispBlanksAs().setVal(STDispBlanksAs.ZERO);
    
            return ctPlotArea;
        }*/
    }

for (int i = 0; i < arry.length; i++) {
        cell = row.createCell(i);
        cell.setCellValue(arry[i]);
        cell.setCellStyle(totalStyle);
        sheet.setColumnWidth(i,4000);
    }//定义列宽