POI导出excel

最新推荐文章于 2022-02-22 16:48:52 发布

置顶 forever__vi

最新推荐文章于 2022-02-22 16:48:52 发布

阅读量149

点赞数

文章标签： java

本文链接：https://blog.csdn.net/forever__vi/article/details/110481101

版权

POI导出excel合并单元格，多个sheet,excel中插入图片

最终结果
在这里插入图片描述
每个sheet都是一样的
实体类

在这里插入图片描述
excel核心代码

 HSSFWorkbook wb = new HSSFWorkbook();
        for(ExamRoundDaoVO examRoundDaoVO:list){
            //设置sheet页码
            HSSFSheet sheet = wb.createSheet(examRoundDaoVO.getCreate_by());
            //设置每一列的宽度
            sheet.setColumnWidth(0,60*55);
            sheet.setColumnWidth(1,300*55);
            sheet.setColumnWidth(2,143*55);
            sheet.setColumnWidth(3,175*55);
            sheet.setColumnWidth(4,120*55);
            sheet.setColumnWidth(5,120*55);
            sheet.setColumnWidth(6,220*55);
            //设置样式以及字体样式
            HSSFCellStyle titleStyle = ExcelUtil.createTitleCellStyle(wb);//标题
            HSSFCellStyle headerStyle = ExcelUtil.createHeadCellStyle(wb);//表头
            HSSFCellStyle contentStyle = ExcelUtil.createContentCellStyle(wb);//数据内容
            HSSFCellStyle remrakCellStyle = ExcelUtil.createRemrakCellStyle(wb);//备注样式
            HSSFCellStyle sencondCellStyle = ExcelUtil.createSencondCellStyle(wb);//第二行样式

            //行号
            int rowNum=0;
            //创建第一行
            HSSFRow row0 = sheet.createRow(rowNum++);
            //设置行高
            row0.setHeight((short) 500);
            HSSFCell c00 = row0.createCell(0);
            String title = "2020年10月自考试卷保密室值守情况巡查表";
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//标题合并单元格操作，总列数为7

            //第二行
            HSSFRow row1 = sheet.createRow(rowNum++);
            row1.setHeight((short)2000);
            HSSFCell c16 = row1.createCell(6);
            c16.setCellStyle(sencondCellStyle);
            c16.setCellValue("巡查员：");
            // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 0, 5));//标题合并单元格操作，总列数为7

            //插入图片
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferedImage =examRoundDaoVO.getInvestigator();
            try{
                ImageIO.write(bufferedImage, "jpg", byteArrayOut);
            }catch (Exception e){
               e.getMessage();
            }
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(175, 35, 1023, 187,(short) 6, 1, (short) 6, 1);
            anchor.setAnchorType(3);
            //插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));


            HSSFCell c1 = row1.createCell(0);
            c1.setCellStyle(sencondCellStyle);
            HSSFCell c2 = row1.createCell(1);
            c2.setCellStyle(sencondCellStyle);
            HSSFCell c3 = row1.createCell(2);
            c3.setCellStyle(sencondCellStyle);
            HSSFCell c4 = row1.createCell(3);
            c4.setCellStyle(sencondCellStyle);
            HSSFCell c5 = row1.createCell(4);
            c5.setCellStyle(sencondCellStyle);
            HSSFCell c6 = row1.createCell(5);
            c6.setCellStyle(sencondCellStyle);

            //创建第三行表头
            HSSFRow row2 = sheet.createRow(rowNum++);
            String[] row_first={"序号","区县名称","考点名称","考试秩序是否良好","监考员是否履职到位","视频是否存在异常","考场异常情况记录"};
            for(int i=0;i<row_first.length;i++){
                HSSFCell tempCell = row2.createCell(i);
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(headerStyle);
            }

            List<ExamRoundDaoVO.ExamInfo> examInfoList = examRoundDaoVO.getExamInfoList();
            //区县移动的量
            int z=0;
            //考点偏移量
            int x=0;
            //插入内容
            for(int i=0;i<examInfoList.size();i++){
                HSSFRow tempRow = sheet.createRow(rowNum++);
                tempRow.setHeight((short)500);
                //循环单元格插入数据
                for(int j=0;j<=6;j++){
                    HSSFCell tempCell = tempRow.createCell(j);
                    tempCell.setCellStyle(contentStyle);
                    String cellValue = "";
                    switch (j){
                        case 0:
                            cellValue=String.valueOf(i+1);
                            break;
                        case 1:
                            //区县名称(合并区县相同的)
                            cellValue=examInfoList.get(i).getCounty();
                            if(i>0){

                                String old = examInfoList.get(i-1).getCounty();
                                if(cellValue.equals(old)){
                                    cellValue="";
                                    //int start=rowNum-1;
                                    z++;
                                    // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
                                   // sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum, 1, 1));//标题合并单元格操作，总列数为7
                                }
                                if(i!=examInfoList.size()-1&&!"".equals(cellValue)&&z>0){
                                    sheet.addMergedRegion(new CellRangeAddress(rowNum-2-z, rowNum-2, 1, 1));
                                    z=0;
                                }
                                //最后一行
                                if (i==examInfoList.size()-1&&z>0){
                                    // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
                                     sheet.addMergedRegion(new CellRangeAddress(rowNum-1-z, rowNum-1, 1, 1));//标题合并单元格操作，总列数为7
                                }
                            }
                            break;
                        case 2:
                            //考点(合并考点相同的)
                            cellValue=examInfoList.get(i).getExamSite();
                            //第二行
                            if(i>0){

                                String old = examInfoList.get(i-1).getExamSite();
                                if(cellValue.equals(old)){
                                    cellValue="";
                                    x++;
                                    // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
                                   // sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum, 2, 2));//标题合并单元格操作，总列数为7
                                }
                                if(i!=examInfoList.size()-1&&!"".equals(cellValue)&&x>0){
                                    sheet.addMergedRegion(new CellRangeAddress(rowNum-2-x, rowNum-2, 2, 2));
                                    x=0;
                                }
                                //最后一行
                                if(i==examInfoList.size()-1&&x>0) {
                                    // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
                                    sheet.addMergedRegion(new CellRangeAddress(rowNum-1-x, rowNum-1, 2, 2));//标题合并单元格操作，总列数为7
                                }
                            }
                            break;
                        case 3:
                            //考场秩序是否良好
                            String examOrderStatus = examInfoList.get(i).getExamOrderStatus();
                            if("1".equals(examOrderStatus)){
                                cellValue="良好";
                            }
                            if("0".equals(examOrderStatus)){
                                cellValue=examInfoList.get(i).getExamRoom()+"秩序不良";
                            }
                            break;
                        case 4:
                            //监考员是否履职到位
                            String proctorDutyStatus = examInfoList.get(i).getProctorDutyStatus();
                            if("1".equals(proctorDutyStatus)){
                                cellValue="到位";
                            }
                            if("0".equals(proctorDutyStatus)){
                                cellValue=examInfoList.get(i).getProctorDutyNote();
                            }
                            break;
                        case 5:
                            //视频是否存在异常
                            String videoStatus = examInfoList.get(i).getVideoStatus();
                            if("1".equals(videoStatus)){
                                cellValue="正常";
                            }
                            if("0".equals(videoStatus)){
                                String room=   examInfoList.get(i).getExamRoom();
                                cellValue=room+examInfoList.get(i).getVideoStatusNote();
                            }
                            break;
                        case 6:
                            cellValue = examInfoList.get(i).getExamRoomInfo();
                            break;
                    }
                    tempCell.setCellValue(cellValue);
                }

            }

            HSSFRow endRow = sheet.createRow(rowNum);
            //设置行高
            endRow.setHeight((short) 1550);
            HSSFCell c = endRow.createCell(0);
            String remark = "备注：巡视员对所负责区域试卷保密室进行全覆盖巡视，重点对人员值守情况（保密室值守应为包含2名公安或武警人员在内的4名以上工作人员）、视频情况（视频质量、是否有盲区等）进行巡视，对巡视中无问题的县区选（√），对存在问题的勾选（×），并及时通知整改，对整改情况做好登记。";
            c.setCellValue(remark);
            c.setCellStyle(remrakCellStyle);
            // 合并单列，结元格，参数依次为起始行，结束行，起始束列 （索引0开始）
            sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 6));//标题合并单元格操作，总列数为16
        }
        String fileName = "2020年10月自考视频巡查登记表.xlsx";
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
            //  System.out.println(e.getMessage());
        }

Util

 /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //背景颜色
      //  cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

        HSSFFont headerFont1 = (HSSFFont) wb.createFont();
        //字体加粗
        headerFont1.setBold(true);
        //字体类型
        headerFont1.setFontName("宋体");
        //字体大小
        headerFont1.setFontHeightInPoints((short)20);
        cellStyle.setFont(headerFont1);
        return cellStyle;
    }

    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置背景颜色
     //   cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);

        //创建字体样式
        HSSFFont headerFont = (HSSFFont)wb.createFont();
        //字体加粗
     //   headerFont.setBold(true);
        //字体类型
        headerFont.setFontName("仿宋");
        //字体大小
        headerFont.setFontHeightInPoints((short)13);
        //为标题样式添加字体样式
        cellStyle.setFont(headerFont);

        return cellStyle;
    }

    /**
     * 设置表格内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置字体
        HSSFFont font = (HSSFFont)wb.createFont();
       // font.setColor((short)8);
        font.setFontName("仿宋");
        font.setFontHeightInPoints((short)13);

        return cellStyle;
    }
    /**
     * 设置备注内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createRemrakCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置字体
        HSSFFont font = (HSSFFont)wb.createFont();
       // font.setColor((short)8);
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short)11);
        cellStyle.setFont(font);
        return cellStyle;
    }
    /**
     * 设置备注内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createSencondCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居左
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

        //设置字体
        HSSFFont font = (HSSFFont)wb.createFont();
        // font.setColor((short)8);
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short)11);
        cellStyle.setFont(font);
        return cellStyle;
    }

合并：主要是合并内容相同的数据，x,z用来标注需要合并的行数，同一单元格不允许合并
插入图片的方法主要
HSSFClientAnchor anchor = new HSSFClientAnchor(175, 35, 1023, 187,(short) 6, 1, (short) 6, 1);
主要是这几个参数的作用设置图片的位置，网上都有参数解释，自行百度

forever__vi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
POI导出excel

POI导出excel合并单元格，多个sheet,CEL中插入图片最终结果每个sheet都是一样的实体类excel核心代码 HSSFWorkbook wb = new HSSFWorkbook(); for(ExamRoundDaoVO examRoundDaoVO:list){ //设置sheet页码 HSSFSheet sheet = wb.createSheet(examRoundDaoVO.getCreate_by());
复制链接

扫一扫