JAVA 导出Excel 带有多个公式函数

最新推荐文章于 2024-02-27 09:18:37 发布

怪咖基德

最新推荐文章于 2024-02-27 09:18:37 发布

阅读量3.7k

点赞数 2

分类专栏： Excel导出文章标签： java 后端 oracle

本文链接：https://blog.csdn.net/new_guaika/article/details/121560201

版权

Excel导出专栏收录该内容

2 篇文章 1 订阅

订阅专栏

导出样式
在这里插入图片描述
Controller层

@ApiOperation(value = "限额配置-导出Excel")
    @GetMapping("/exportExcel")
    public byte[] exportExcel(@ApiParam(value = "用户iv-user Code", required = true) @RequestHeader(value = "iv-user") String userCode,
                         @RequestParam @ApiParam(required = true, name = "targetId", value = "流程编号")String targetId,
                         @RequestParam @ApiParam(required = true, name = "startPeriodId", value = "开始日期") Integer startPeriodId,
                         @RequestParam @ApiParam(required = true, name = "endPeriodId", value = "结束日期") Integer endPeriodId,
                         HttpServletResponse response) throws IOException {

        log.info("配置部门规划-净资本预算表-导出Excel | DeptConfigExcelController.exportExcel | 任务开始,流程编号：targetId=={}, 当前操作人：userCode==={}", targetId, userCode);

        HSSFWorkbook hwb = new HSSFWorkbook();
        InputStream input = null;

        try {
            //根据用户code：userCode，获取该用户所属部门
            FlowUser userInfo = deptConfigService.getUserInfo(userCode);
            String depName = userInfo.getDepartName();
            log.info("配置部门规划-净资本预算表-导出Excel | 开始解析Excel, 流程编号：targetId=={}, 当前操作人所属部门：depName==={}", targetId, depName);

            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            OutputStream outputStream = response.getOutputStream();

            //业务及资本规划表: ERMP_DATA.CAPTIAL_DEP_PROJ_BUSINESS
            // 根据流程编码：targetId 和 部门名称：depName 查库里是否有数据，有，就导出库里；无，导出项目中的excel文件
            if(mapper.findProjCount("CAPTIAL_DEP_PROJ_BUSINESS", targetId, depName) > 0){
                exportExcelService.exportExcel(depName, targetId, startPeriodId, endPeriodId, hwb);
                hwb.write(outputStream);
            }else{
				//fileName：项目中的文件名称
                Resource resource = new ClassPathResource("file/" + fileName + ".xlsx");
                input = resource.getInputStream();
                Workbook wb = WorkbookFactory.create(input);
                exportExcelService.exportExcelTemplate(depName, startPeriodId, endPeriodId, wb);
                wb.write(outputStream);
            }
            outputStream.close();
        } catch (Exception e) {
            log.error("DeptConfigExcelController exportExcel fail.",e);
            throw new IOException("限额配置-净资本预算表-导出Excel | 导入Excel出现异常，异常信息：" + e.getMessage());
        } finally {
            if (input != null) {
                IOUtils.closeQuietly(input);
            }
        }
        return hwb.getBytes();
    }

Service层

@Slf4j
@Service
public class DeptConfigExportExcelServiceImpl implements DeptConfigExportExcelService {

    private static final String TABLE_TITLE = "tableTitle";
    private static final String BG_TABLE_CELL = "BGTableCell";
    private static final String ALIGN_TABLE_CELL = "alignTableCell";
    private static final String ALIGN_BOLD_TABLE_CELL = "alignBoldTableCell";
    private static final String ALIGN_LEFT_TABLE_CELL = "alignLeftTableCell";
    private static final String COLOR_YELLOW_TABLE_CELL = "colorYellowCell";
    //Excel 需要标黄的单元格
    private static final List<Integer> YELLOWCOLORS = Arrays.asList(8,13,26,35,47,54,59,63,64,65,69,76,80,83,84,90,96,100);
    //Excel 需要体积加粗的单元格
    private static final List<Integer> BOLDS = Arrays.asList(63,80,83,96,99,100);

    @Autowired
    private PeriodDimDao periodDimDao;

    @Autowired
    private CaptialDepProjMapper mapper;

    @Override
    public void exportExcel(String depName, String targetId, Integer startPeriodId, Integer endPeriodId, HSSFWorkbook wb) {

        log.info("配置部门规划-净资本预算表-导出Excel | 任务开始,流程编号：targetId=={}, 部门名称：depName==={}", targetId, depName);

        //定义报表数据中的行数
        int rownum = 0;

        //根据开始日期：startPeriodId 和结束日期：endPeriodId 获取月的个数以及所有月份
        int periodCount = periodDimDao.queryYearMonthCount(startPeriodId, endPeriodId);
        List<Integer> periodList = periodDimDao.queryYearMonthList(startPeriodId, endPeriodId);

        //定义总列数
        int columnNumber = 5 + periodCount*2;
        //给每一列设置大小值
        int[] columnWidth = {50,19,19,19,19,19,19,10,10,10,75};

        HSSFSheet sheet = wb.createSheet(depName);

        for(int i = 0; i < columnNumber; i++){
            sheet.setColumnWidth(i,columnWidth[i] * 256); // 单独设置每列的宽
        }

        //存储所有的样式
        Map<String, HSSFCellStyle> cellStyleMap = getCellStyleMap(wb);

        //第一部分：1-6行，需要设置自动计算
        int oneRowNum = getOneTable(rownum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);

        //第二部分 第7行表头
        int twoRowNum = getTwoTable(oneRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);

        //第三部分 第8行表头
        int threeRowNum = getThreeTable(twoRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);

        //第四部分 第9行开始读取库里数据
        getFourTable(threeRowNum, columnNumber, depName, targetId, wb, sheet, periodList, cellStyleMap);

        //设置第8行冻结
        sheet.createFreezePane(11,8);

        //单元格添加 函数 有效
        for(BusinessExcelEnum item : BusinessExcelEnum.values()) {
            HSSFRow row = sheet.getRow(item.getRow());
            HSSFCell cellS = row.getCell(item.getCell());
            //取得公式单元格的公式,重新设置
            cellS.setCellFormula(item.getFunction());
        }
        //强制让Excel Sheet 执行公式
        wb.setForceFormulaRecalculation(true);

    }

    private void getFourTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
                              List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
        //定义报表数据中的行数
        rownum = rownum + 1;

        HSSFRow rowSubTitle;

        //根据流程编码：targetId 和 部门名称：depName 查库里数据
        List<CaptialDepProjBusinessVO> projVOList = mapper.queryProjVOData(targetId, depName);

        for(int i = 0; i < projVOList.size(); i++){
            CaptialDepProjBusinessVO voData = projVOList.get(i);
            rowSubTitle = sheet.createRow(i + rownum);
            HSSFCell datacell = null;

            //9-63行没有合并单元格，之后有合并单元格
            //voData.getPx() 在 8-62之间没有单元格，大于62合并单元格
            if(voData.getPx() >= 8 && voData.getPx() <= 62){
                for (int j = 0; j < columnNumber; j++) {
                    //第9行设置函数以及月份设置底色
                    if(voData.getPx() == 8 || voData.getPx() == 26){
                        //字体加粗
                        if(j == 0){
                            datacell = rowSubTitle.createCell(j);
                            //设置单元格样式：字体加粗 靠左对齐
                            datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
                            datacell.setCellValue(getAllCellValue(voData, j));
                        }
                        //月份单元格设置
                        if(j != 0 && j <= periodList.size()*2){
                            datacell = rowSubTitle.createCell(j);
                            //设置单元格样式：添加底色 居中
                            datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
                            datacell.setCellValue(getAllCellValue(voData, j));
                        }
                        if(j > periodList.size()*2){
                            datacell = rowSubTitle.createCell(j);
                            if(j == columnNumber-1){
                                //设置单元格样式：字体非加粗 靠左对齐
                                datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
                            }else{
                                //设置单元格样式：字体非加粗 居中
                                datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
                            }
                            datacell.setCellValue(getAllCellValue(voData, j));
                        }

                    }else{
                        datacell = rowSubTitle.createCell(j);
                        if(j == 0 || j == columnNumber-1){
                            //设置单元格样式：字体非加粗 靠左对齐
                            datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
                        }else{
                            //设置单元格样式：字体非加粗 居中
                            datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
                        }
                        if(YELLOWCOLORS.contains(voData.getPx()) && j != 0 && j <= periodList.size()*2){
                            //设置单元格样式：添加底色 居中
                            datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
                        }
                        if(j > 0 && j <= periodList.size()*2 && !YELLOWCOLORS.contains(i + rownum)){
                            //函数 计算 需要返回数组类型
                            datacell.setCellValue(getDoubleCellValue(voData, j));
                        }else{
                            datacell.setCellValue(getAllCellValue(voData, j));
                        }

                    }
                }
            }else{
                //63行开始，月份部分有合并单元格
                for (int j = 0; j < columnNumber; j++) {
                    //合并单元格
                    if(j % 2 != 0 && j <= periodList.size()*2){
                        datacell = rowSubTitle.createCell(j);
                        //函数 计算 需要返回数组类型
                        if(YELLOWCOLORS.contains(voData.getPx()) && j <= periodList.size() * 2){
                            //设置单元格样式：添加底色 居中
                            datacell.setCellStyle(styleMap.get(COLOR_YELLOW_TABLE_CELL));
                        }else{
                            //设置单元格样式：字体非加粗 居中
                            datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
                        }

                        if(!YELLOWCOLORS.contains(i + rownum)){
                            //函数 计算 需要返回数组类型
                            datacell.setCellValue(getDoubleCellValue(voData, j));
                        }else{
                            datacell.setCellValue(getAllCellValue(voData, j));
                        }

                        CellRangeAddress region = new CellRangeAddress(i + rownum,i + rownum,j,j + 1);
                        sheet.addMergedRegion(region);
                        setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
                    }else{
                        datacell = rowSubTitle.createCell(j);
                        if(j == 0){
                            if(BOLDS.contains(voData.getPx())){
                                //设置单元格样式：字体加粗 靠左对齐
                                datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
                            }else{
                                //设置单元格样式：字体非加粗 靠左对齐
                                datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
                            }
                        }else if(j == columnNumber-1){
                            //设置单元格样式：字体非加粗 靠左对齐
                            datacell.setCellStyle(styleMap.get(ALIGN_LEFT_TABLE_CELL));
                        }else{
                            //设置单元格样式：字体非加粗 居中
                            datacell.setCellStyle(styleMap.get(ALIGN_BOLD_TABLE_CELL));
                        }
                        datacell.setCellValue(getAllCellValue(voData, j));
                    }
                }
            }
        }
    }

    private double getDoubleCellValue(CaptialDepProjBusinessVO voData, int cellIndex) {
        switch(cellIndex){
            case 1:
                return getDoubleValue(voData.getVal1());
            case 2:
                return getDoubleValue(voData.getVal2());
            case 3:
                return getDoubleValue(voData.getVal3());
            case 4:
                return getDoubleValue(voData.getVal4());
            case 5:
                return getDoubleValue(voData.getVal5());
            case 6:
                return getDoubleValue(voData.getVal6());
            default:
                return 0.00;
        }
    }

    private double getDoubleValue(String val) {
        return StringUtils.isNotEmpty(val) ? Double.parseDouble(val) : 0.00;
    }

    private String getAllCellValue(CaptialDepProjBusinessVO voData, int cellIndex) {
        switch(cellIndex){
            case 0:
                return voData.getProjName();
            case 1:
                return voData.getVal1();
            case 2:
                return voData.getVal2();
            case 3:
                return voData.getVal3();
            case 4:
                return voData.getVal4();
            case 5:
                return voData.getVal5();
            case 6:
                return voData.getVal6();
            case 7:
                return voData.getCoefficient();
            case 8:
                return voData.getStandard();
            case 9:
                return voData.getTakeUp();
            case 10:
                return voData.getRemark();
            default:
                return "";
        }
    }

    private int getThreeTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
                              List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
        //定义报表数据中的行数
        rownum = rownum + 1;

        // 创建表头单元格样式 以及表头的字体样式
        //第8行表头
        HSSFRow rowSubTitle = sheet.createRow(rownum);
        String[] subTitle = {"1. 市场风险资本准备", "未对冲", "已对冲", "未对冲", "已对冲", "未对冲", "已对冲", "", "\\", "", ""};
        // 创建表头的列
        for (int i = 0; i < columnNumber; i++) {

            HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
            cellRiskTitle.setCellValue(subTitle[i]);
            if(i == 0){
                //设置单元格样式：字体加粗 靠左对齐
                cellRiskTitle.setCellStyle(styleMap.get(BG_TABLE_CELL));
            }else{
                cellRiskTitle.setCellStyle(styleMap.get(ALIGN_TABLE_CELL));
            }
        }
        return rownum;
    }

    private int getTwoTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
                             List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
        // 创建表头单元格样式 以及表头的字体样式
        //第7行表头
        HSSFRow rowSubTitle = sheet.createRow(rownum);
        rowSubTitle.setHeightInPoints(40);
        String[] subTitle = {"业务项目（填报非标黄行）", getCellValue(String.valueOf(periodList.get(0))), "", getCellValue(String.valueOf(periodList.get(1))), "",
                getCellValue(String.valueOf(periodList.get(2))), "", "净资本计算系数", "计算标准", "本期净资本占用", "备注"};
        // 创建表头的列
        for (int i = 0; i < columnNumber; i++) {
            //合并单元格
            if(i % 2 != 0 && i < periodList.size()*2){
                HSSFCell cellChangeTitle = rowSubTitle.createCell(i);
                cellChangeTitle.setCellValue(subTitle[i]);
                cellChangeTitle.setCellStyle(styleMap.get(TABLE_TITLE));
                CellRangeAddress region = new CellRangeAddress(rownum,rownum,i,i + 1);
                sheet.addMergedRegion(region);
                setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
            }else{
                HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
                cellRiskTitle.setCellStyle(styleMap.get(TABLE_TITLE));
                if(i == 0){
                    //部分字体加红色
                    cellRiskTitle.setCellValue(setTableTitleRed(wb, subTitle[i]));
                }else{
                    cellRiskTitle.setCellValue(subTitle[i]);
                }
            }
        }
        return rownum;
    }

    private int getOneTable(int rownum, int columnNumber, String depName, String targetId, HSSFWorkbook wb, HSSFSheet sheet,
                            List<Integer> periodList, Map<String, HSSFCellStyle> styleMap) {
        // 创建表头单元格样式 以及表头的字体样式
        HSSFRow rowSubTitle = sheet.createRow(rownum);
        rowSubTitle.setHeightInPoints(20);
        String[] subTitle = {"业务规划结果（自动计算）", getCellValue(String.valueOf(periodList.get(0))), getCellValue(String.valueOf(periodList.get(1))),
                getCellValue(String.valueOf(periodList.get(2)))};
        // 创建表头的列
        for (int i = 0; i < columnNumber-4; i++) {
            if(i == 0){
                HSSFCell cellRiskTitle = rowSubTitle.createCell(i);
                cellRiskTitle.setCellStyle(styleMap.get(TABLE_TITLE));
                //部分字体加红色
                cellRiskTitle.setCellValue(setTableTitleRed(wb, subTitle[i]));

            }
            //合并单元格
            if(i % 2 != 0){
                HSSFCell cellChangeTitle = rowSubTitle.createCell(i);
                cellChangeTitle.setCellValue(subTitle[(i+1) / 2]);
                cellChangeTitle.setCellStyle(styleMap.get(TABLE_TITLE));
                CellRangeAddress region = new CellRangeAddress(rownum,rownum,i,i + 1);
                sheet.addMergedRegion(region);
                setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框
            }
        }

        //根据流程编码：targetId 和 部门名称：depName 查库里数据
        List<CaptialDepProjBusinessVO> projVOList = mapper.queryProjVOTopData(targetId, depName);

        //创建单元格，并设置值
        rownum = rownum + 1;

        for(int i = 0; i < projVOList.size(); i++){
            CaptialDepProjBusinessVO voData = projVOList.get(i);
            rowSubTitle = sheet.createRow(i + rownum);
            HSSFCell datacell = null;
            for (int j = 0; j < columnNumber-4; j++) {
                if(j == 0){
                    datacell = rowSubTitle.createCell(j);
                    //设置单元格样式：字体加粗 靠左对齐
                    datacell.setCellStyle(styleMap.get(BG_TABLE_CELL));
                    datacell.setCellValue(voData.getProjName());
                }
                //合并单元格
                if(j % 2 != 0){
                    datacell = rowSubTitle.createCell(j);
                    datacell.setCellStyle(styleMap.get(ALIGN_TABLE_CELL));
                    datacell.setCellValue(getAllCellValue(voData, j));
                    CellRangeAddress region = new CellRangeAddress(i + rownum,i + rownum,j,j + 1);
                    sheet.addMergedRegion(region);
                    setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, wb);   //给合并过的单元格加边框

                }
            }
        }
        return rownum + projVOList.size();
    }

    private HSSFRichTextString setTableTitleRed(HSSFWorkbook wb, String value) {
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 9); // 字体高度
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
        font.setFontName("宋体"); // 字体

        Font font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 9); // 字体高度
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
        font2.setFontName("宋体"); // 字体
        font2.setColor(HSSFColor.RED.index);  //颜色

        HSSFRichTextString ts= new HSSFRichTextString(value); //单元格内容
        ts.applyFont(0,7,font);  //从零开始，长度7
        ts.applyFont(7,ts.length()-1,font2);  //从7开始

        return ts;
    }

    //给合并后的单元格加样式
    public void setBorderStyle(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){
        RegionUtil.setBorderBottom(border, region, sheet, wb);   //下边框
        RegionUtil.setBorderLeft(border, region, sheet, wb);     //左边框
        RegionUtil.setBorderRight(border, region, sheet, wb);    //右边框
        RegionUtil.setBorderTop(border, region, sheet, wb);      //上边框
    }

    private Map<String, HSSFCellStyle> getCellStyleMap(HSSFWorkbook wb){
        Map<String, HSSFCellStyle> styleMap = new HashMap<>();
        styleMap.put(TABLE_TITLE, createTableTitleStyle(wb));
        styleMap.put(BG_TABLE_CELL, createBGTableCellStyle(wb));
        styleMap.put(ALIGN_TABLE_CELL, createAlignTableCellStyle(wb));
        styleMap.put(ALIGN_BOLD_TABLE_CELL, createAlignBoldTableCellStyle(wb));
        styleMap.put(ALIGN_LEFT_TABLE_CELL, createAlignLeftTableCellStyle(wb));
        styleMap.put(COLOR_YELLOW_TABLE_CELL, createYellowTitleStyle(wb));
        return styleMap;
    }

    //创建表头单元格样式 以及表头的字体样式
    private HSSFCellStyle createTableTitleStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.DARK_YELLOW.index);//添加背景色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        //自定义单元格颜色
        //拿到palette颜色板
        HSSFPalette palette = wb.getCustomPalette();
        //这个是重点，具体的就是把之前的颜色 HSSFColor.DARK_YELLOW.index
        //替换为 RGB(196,189,151)
        palette.setColorAtIndex(HSSFColor.DARK_YELLOW.index, (byte) 196, (byte) 189, (byte) 151);

        return style;
    }

    //创建数据单元格样式 以及字体样式
    private HSSFCellStyle createBGTableCellStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    //创建数据单元格样式 以及字体样式  数据居中
    private HSSFCellStyle createAlignTableCellStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        //保留两位小数
        HSSFDataFormat format= wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));

        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    //创建数据单元格样式 以及字体样式  字体不加粗居中
    private HSSFCellStyle createAlignBoldTableCellStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        //保留两位小数
        HSSFDataFormat format= wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));

        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    //创建单元格样式 单元格添加黄色
    private HSSFCellStyle createYellowTitleStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);//添加背景色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        //保留两位小数
        HSSFDataFormat format= wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));
        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    //创建数据单元格样式 以及字体样式  字体不加粗
    private HSSFCellStyle createAlignLeftTableCellStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true); //设置自动换行
        //保留两位小数
        HSSFDataFormat format= wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));

        HSSFFont titleFont = (HSSFFont) wb.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 9);
        style.setFont(titleFont);

        return style;
    }

    @Override
    public void exportExcelTemplate(String depName, Integer startPeriodId, Integer endPeriodId, Workbook wb) {

        log.info("限额配置-净资本预算表-导出Excel | 任务开始, 部门名称：depName==={}", depName);
        List<Integer> periodList = periodDimDao.queryYearMonthList(startPeriodId, endPeriodId);

        Sheet sheet = wb.getSheetAt(0);
        wb.setSheetName(0,depName);

        //重新设置单元格的值
        Cell cell1 = sheet.getRow(0).getCell(1); //第1行,第2列
        cell1.setCellValue(getCellValue(String.valueOf(periodList.get(0))));

        Cell cell2 = sheet.getRow(0).getCell(3); //第1行,第4列
        cell2.setCellValue(getCellValue(String.valueOf(periodList.get(1))));

        Cell cell3 = sheet.getRow(0).getCell(5); //第1行,第6列
        cell3.setCellValue(getCellValue(String.valueOf(periodList.get(2))));

        Cell cell4 = sheet.getRow(6).getCell(1); //第7行,第2列
        cell4.setCellValue(getCellValue(String.valueOf(periodList.get(0))));

        Cell cell5 = sheet.getRow(6).getCell(3); //第7行,第4列
        cell5.setCellValue(getCellValue(String.valueOf(periodList.get(1))));

        Cell cell6 = sheet.getRow(6).getCell(5); //第7行,第6列
        cell6.setCellValue(getCellValue(String.valueOf(periodList.get(2))));
    }

    //截取日期
    private String getCellValue(String value) {
        return value.substring(0,4) + "年" + getZero(value.substring(value.length()-2)) + "月底规划规模（单位：亿元）";
    }

    //日期如果前一位为0，去掉0
    private String getZero(String sub) {
        return "0".equals(sub.substring(0,1)) ?  sub.substring(1) : sub;
    }
}

动态获取公式

package com.hs.report.enums;
public enum BusinessExcelEnum {

    //1-净资本占用
    JZBZY_ONE(1, 1 , "SUMPRODUCT(B10:B26*H10:H26*I10:I26)+SUMPRODUCT(C10:C26*H10:H26*0.05)+SUMPRODUCT(B28:B63*H28:H63*I28:I63)+" +
            "SUMPRODUCT(C28:C63*H28:H63*0.01)+SUMPRODUCT(B67:C80*H67:H80*I67:I80)+SUMPRODUCT(B82:C83*H82:H83*I82:I83)+" +
            "SUMPRODUCT(B86:C96*H86:H96*I86:I96)+SUMPRODUCT(B98:C100*H98:H100*I98:I100)"),
    JZBZY_TWO(1,3,"SUMPRODUCT(D10:D26*H10:H26*I10:I26)+SUMPRODUCT(E10:E26*H10:H26*0.05)+SUMPRODUCT(D28:D63*H28:H63*I28:I63)+" +
            "SUMPRODUCT(E28:E63*H28:H63*0.01)+SUMPRODUCT(D67:E80*H67:H80*I67:I80)+SUMPRODUCT(D82:E83*H82:H83*I82:I83)+" +
            "SUMPRODUCT(D86:E96*H86:H96*I86:I96)+SUMPRODUCT(D98:E100*H98:H100*I98:I100)"),
    JZBZY_THREE(1,5,"SUMPRODUCT(F10:F26*H10:H26*I10:I26)+SUMPRODUCT(G10:G26*H10:H26*0.05)+SUMPRODUCT(F28:F63*H28:H63*I28:I63)+" +
            "SUMPRODUCT(G28:G63*H28:H63*0.01)+SUMPRODUCT(F67:G80*H67:H80*I67:I80)+SUMPRODUCT(F82:G83*H82:H83*I82:I83)+" +
            "SUMPRODUCT(F86:G96*H86:H96*I86:I96)+SUMPRODUCT(F98:G100*H98:H100*I98:I100)"),

    //2-自营权益类证券及其衍生品规模
    ZYQY_ONE(2,1,"SUM(B10:B26)+SUM(C10:C26)*0.05"),
    ZYQY_TWO(2,3,"SUM(D10:D26)+SUM(E10:E26)*0.05"),
    ZYQY_THREE(2,5,"SUM(F10:F26)+SUM(G10:G26)*0.05"),

    //3-自营非权益类证券及其衍生品规模
    ZYFQY_ONE(3,1,"SUM(B28:B63)+SUM(C28:C63)*0.01"),
    ZYFQY_TWO(3,3,"SUM(D28:D63)+SUM(E28:E63)*0.01"),
    ZYFQY_THREE(3,5,"SUM(F28:F63)+SUM(G28:G63)*0.01"),

    //4-融资（融券）业务规模
    RZRQ_ONE(4,1,"SUM(B67:C76)"),
    RZRQ_TWO(4,3,"SUM(D67:E76)"),
    RZRQ_THREE(4,5,"SUM(F67:G76)"),

    //5-表内外资产规模
    GENERAL_ONE(5,1,"B9+C9+B27+C27+SUM(B67:C80)+SUM(B82:C83)+SUM(B86:C96)*0.3%+B102*15%+B103*10%+B104*5%"),
    GENERAL_TWO(5,3,"D9+E9+D27+E27+SUM(D67:E80)+SUM(D82:E83)+SUM(D86:E96)*0.3%+D102*15%+D103*10%+D104*5%"),
    GENERAL_THREE(5,5,"F9+G9+F27+G27+SUM(F67:G80)+SUM(F82:G83)+SUM(F86:G96)*0.3%+F102*15%+F103*10%+F104*5%"),

    //其中：（1）权益类证券及其衍生品规模
    QYL_ONE(8,1,"SUM(B10:B26)"),
    QYL_TWO(8,2,"SUM(C10:C26)"),
    QYL_THREE(8,3,"SUM(D10:D26)"),
    QYL_FOUR(8,4,"SUM(E10:E26)"),
    QYL_FIVE(8,5,"SUM(F10:F26)"),
    QYL_SIX(8,6,"SUM(G10:G26)"),

    //非权益类证券及其衍生品规模
    FQYL_ONE(26,1,"SUM(B28:B63)"),
    FQYL_TWO(26,2,"SUM(C28:C63)"),
    FQYL_THREE(26,3,"SUM(D28:D63)"),
    FQYL_FOUR(26,4,"SUM(E28:E63)"),
    FQYL_SIX(26,6,"SUM(G28:G63)"),
    FQYL_FIVE(26,5,"SUM(F28:F63)"),

    //本期净资本占用 列
    JZB_TEN_TEN(9,9,"G10*H10*I10+F10*H10*0.05"),
    JZB_ELEVEN_TEN(10,9,"F11*H11*I11+G11*H11*0.05"),
    JZB_TWELVE_TEN(11,9,"F12*H12*I12+G12*H12*0.05"),
    JZB_THIRTEEN_TEN(12,9,"F13*H13*I13+G13*H13*0.05"),
    JZB_FIFTEEN_TEN(14,9,"F15*H15*I15+G15*H15*0.05"),
    JZB_SIXTEEN_TEN(15,9,"F16*H16*I16+G16*H16*0.05"),
    JZB_SEVENTEEN_TEN(16,9,"F17*H17*I17+G17*H17*0.05"),
    JZB_EIGHTEEN_TEN(17,9,"F18*H18*I18+G18*H18*0.05"),
    JZB_NINETEEN_TEN(18,9,"F19*H19*I19+G19*H19*0.05"),
    JZB_TWENTY_TEN(19,9,"F20*H20*I20+G20*H20*0.05"),
    JZB_TWENTY_ONE_TEN(20,9,"F21*H21*I21+G21*H21*0.05"),
    JZB_TWENTY_TWO_TEN(21,9,"F22*H22*I22+G22*H22*0.05"),
    JZB_TWENTY_THREE_TEN(22,9,"F23*H23*I23+G23*H23*0.05"),
    JZB_TWENTY_FOUR_TEN(23,9,"F24*H24*I24+G24*H24*0.05"),
    JZB_TWENTY_FIVE_TEN(24,9,"F25*H25*I25+G25*H25*0.05"),
    JZB_TWENTY_EIGHT_TEN(27,9,"F28*H28*I28+G28*H28*0.01"),
    JZB_TWENTY_NINE_TEN(28,9,"F29*H29*I29+G29*H29*0.01"),
    JZB_THIRTY_TEN(29,9,"F30*H30*I30+G30*H30*0.01"),
    JZB_THIRTY_ONE_TEN(30,9,"F31*H31*I31+G31*H31*0.01"),
    JZB_THIRTY_TWO_TEN(31,9,"F32*H32*I32+G32*H32*0.01"),
    JZB_THIRTY_THREE_TEN(32,9,"F33*H33*I33+G33*H33*0.01"),
    JZB_THIRTY_FOUR_TEN(33,9,"F34*H34*I34+G34*H34*0.01"),
    JZB_THIRTY_FIVE_TEN(34,9,"F35*H35*I35+G35*H35*0.01"),
    JZB_THIRTY_SIX_TEN(36,9,"F37*H37*I37+G37*H37*0.01"),
    JZB_THIRTY_EIGHT_TEN(37,9,"F38*H38*I38+G38*H38*0.01"),
    JZB_THIRTY_NINE_TEN(38,9,"F39*H39*I39+G39*H39*0.01"),
    JZB_FORTY_TEN(39,9,"F40*H40*I40+G40*H40*0.01"),
    JZB_FORTY_ONE_TEN(40,9,"F41*H41*I41+G41*H41*0.01"),
    JZB_FORTY_TWO_TEN(41,9,"F42*H42*I42+G42*H42*0.01"),
    JZB_FORTY_THREE_TEN(42,9,"F43*H43*I43+G43*H43*0.01"),
    JZB_FORTY_FOUR_TEN(43,9,"F44*H44*I44+G44*H44*0.01"),
    JZB_FORTY_FIVE_TEN(44,9,"F45*H45*I45+G45*H45*0.01"),
    JZB_FORTY_SIX_TEN(45,9,"F46*H46*I46+G46*H46*0.01"),
    JZB_FORTY_SEVEN_TEN(46,9,"F47*H47*I47+G47*H47*0.01"),
    JZB_FORTY_NINE_TEN(48,9,"F49*H49*I49+G49*H49*0.01"),
    JZB_FIFTY_TEN(49,9,"F50*H50*I50+G50*H50*0.01"),
    JZB_FIFTY_ONE_TEN(50,9,"F51*H51*I51+G51*H51*0.01"),
    JZB_FIFTY_TWO_TEN(51,9,"F52*H52*I52+G52*H52*0.01"),
    JZB_FIFTY_THREE_TEN(52,9,"F53*H53*I53+G53*H53*0.01"),
    JZB_FIFTY_FOUR_TEN(53,9,"F54*H54*I54+G54*H54*0.01"),
    JZB_FIFTY_SIX_TEN(55,9,"F56*H56*I56+G56*H56*0.01"),
    JZB_FIFTY_SEVEN_TEN(56,9,"F57*H57*I57+G57*H57*0.01"),
    JZB_FIFTY_EIGHT_TEN(57,9,"F58*H58*I58+G58*H58*0.01"),
    JZB_FIFTY_NINE_TEN(58,9,"F59*H59*I59+G59*H59*0.01"),
    JZB_SIXTY_ONE_TEN(60,9,"F61*H61*I61+G61*H61*0.01"),
    JZB_SIXTY_TWO_TEN(61,9,"F62*H62*I62+G62*H62*0.01"),
    JZB_SIXTY_SEVEN_TEN(66,9,"F67*H67*I67"),
    JZB_SIXTY_EIGHT_TEN(67,9,"F68*H68*I68"),
    JZB_SIXTY_NINE_TEN(68,9,"F69*H69*I69"),
    JZB_SEVENTY_ONE_TEN(70,9,"F71*H71*I71"),
    JZB_SEVENTY_TWO_TEN(71,9,"F72*H72*I72"),
    JZB_SEVENTY_THREE_TEN(72,9,"F73*H73*I73"),
    JZB_SEVENTY_FOUR_TEN(73,9,"F74*H74*I74"),
    JZB_SEVENTY_FIVE_TEN(74,9,"F75*H75*I75"),
    JZB_SEVENTY_SIX_TEN(75,9,"F76*H76*I76"),
    JZB_SEVENTY_EIGHT_TEN(77,9,"F78*H78*I78"),
    JZB_SEVENTY_NINE_TEN(78,9,"F79*H79*I79"),
    JZB_EIGHTY_TEN(79,9,"F80*H80*I80"),
    JZB_EIGHTY_TWO_TEN(81,9,"F82*H82*I82"),
    JZB_EIGHTY_THREE_TEN(82,9,"F83*H83*I83"),
    JZB_EIGHTY_SIX_TEN(85,9,"F86*H86*I86"),
    JZB_EIGHTY_SEVEN_TEN(86,9,"F87*H87*I87"),
    JZB_EIGHTY_EIGHT_TEN(87,9,"F88*H88*I88"),
    JZB_EIGHTY_NINE_TEN(88,9,"F89*H89*I89"),
    JZB_NINETY_TEN(89,9,"F90*H90*I90"),
    JZB_NINETY_TWO_TEN(91,9,"F92*H92*I92"),
    JZB_NINETY_THREE_TEN(92,9,"F93*H93*I93"),
    JZB_NINETY_FOUR_TEN(93,9,"F94*H94*I94"),
    JZB_NINETY_FIVE_TEN(94,9,"F95*H95*I95"),
    JZB_NINETY_SIX_TEN(95,9,"F96*H96*I96"),
    JZB_NINETY_EIGHT_TEN(97,9,"F98*H98*I98"),
    JZB_NINETY_NINE_TEN(98,9,"F99*H99*I99"),
    JZB_ONE_HUNDRED_TEN(99,9,"F100*H100*I100"),
    ;
    private final Integer row;
    private final Integer cell;
    private final String function;

    BusinessExcelEnum(Integer row, Integer cell, String function) {
        this.row = row;
        this.cell = cell;
        this.function = function;
    }

    public Integer getRow() {
        return row;
    }

    public Integer getCell() {
        return cell;
    }

    public String getFunction() {
        return function;
    }

    public static String getFunction(int row, int cell){
        for(BusinessExcelEnum item : BusinessExcelEnum.values()){
            if(row == item.row && cell == item.cell){
                return item.getFunction();
            }
        }
        return null;
    }
}

怪咖基德

关注

2
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
JAVA 导出Excel 带有多个公式函数

Controller层@ApiOperation(value = "限额配置-导出Excel") @GetMapping("/exportExcel") public byte[] exportExcel(@ApiParam(value = "用户iv-user Code", required = true) @RequestHeader(value = "iv-user") String userCode, @RequestParam @
复制链接

扫一扫