springboot整合poi读取数据库数据和图片动态导出excel

springboot整合poi读取数据库数据和图片动态导出excel

第一次操作
话不多说就直接上代码

实现代码

需要的依赖

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

代码片

// An highlighted block
  public void upFile(HttpServletRequest request, HttpServletResponse response, String user, String projectName, String filename) throws Exception {
        SimpleDateFormat ft = new SimpleDateFormat("yyyyMMdd");
        System.out.println("正常");
        List<SwitchOptInfo> switchOptInfo= speedSwitchOptInfoService.selectByProj(user,projectName) ;//项目场景
        if(switchOptInfo.size()<1)
            return;
        String language=RegexUtil.getLanguageType(switchOptInfo.get(0).getProject_scene());//获取语言
        // 创建一个工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建一个sheet
        HSSFSheet sheet = wb.createSheet(projectName);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wb.createCellStyle();
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);
        //加边框
        /*style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);*/
        /*-----------------配置字体颜色☟-----------------*/
        String str = "#FFFFFF";
        //处理把它转换成十六进制并放入一个数
        int[] color=new int[3];
        color[0]=Integer.parseInt(str.substring(1, 3), 16);
        color[1]=Integer.parseInt(str.substring(3, 5), 16);
        color[2]=Integer.parseInt(str.substring(5, 7), 16);
        //自定义颜色HSSFFont
        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex(HSSFColor.BLACK.index,(byte)color[0], (byte)color[1], (byte)color[2]);
        /*----------------配置字体颜色☝-----------------*/
        HSSFFont font = wb.createFont();
        //字体颜色
        font.setColor(HSSFColor.BLACK.index);
        //设置字体大小
        font.setFontHeightInPoints((short) 20);
        //字体
        font.setFontName("宋体");



        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 7); // 起始行, 终止行, 起始列, 终止列
        sheet.addMergedRegion(cra);
        String biaoti="UTEPO XXXSecurity monitoring equipment list";
       String Total="Total";
        String[] excelHeader= {"Type", "Picture", "Model","Features","Application area","Num","Price","Subtotal"};//列名
        String fileName=ft.format(switchOptInfo.get(0).getCreateTime())+switchOptInfo.get(0).getProject_scene()+"  product  list.xls";//文件名
        if("ZH".equals(language)){
            biaoti="UTEPO xxx安全监控设备清单";
            Total="总计";
            excelHeader[0]="交换机类型";excelHeader[1]="图片";excelHeader[2]="交换机名";excelHeader[3]="详情";excelHeader[4]="应用区域";
            excelHeader[5]="数量";excelHeader[6]="单价";excelHeader[7]="小计";
            fileName=ft.format(switchOptInfo.get(0).getCreateTime())+"  "+switchOptInfo.get(0).getProject_scene()+"项目产品清单.xls";
        }
        OutputStream outputStream = response.getOutputStream();
        HSSFCellStyle styl = wb.createCellStyle();
        Row row9 = sheet.createRow(0);
        try {
            String[] excelHeader1={biaoti};
            for (int j = 0; j< excelHeader1.length; j++) {

                row9.setHeight((short) (30.7 * 40));
                styl.setFont(font);
                //设置水平对齐的样式为居中对齐;
                styl.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                //设置垂直对齐的样式为居中对齐;
                styl.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                styl.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());// 设置背景色
                styl.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                Cell cell = row9.createCell(j);

                cell.setCellValue(excelHeader1[j]);
                cell.setCellStyle(styl);
                //sheet.autoSizeColumn(i);
                //设置列宽
                sheet.setColumnWidth(j, 306 * 30 + 184);
            }
            //标题
            for (int i = 0; i < excelHeader.length; i++) {
                row = sheet.createRow(1);
                row.setHeight((short) (20.7 * 30));
                //设置样式 每列都是水平垂直居中
                HSSFCell cel = row.createCell(0);
                cel.setCellValue(excelHeader[0]);
                cel.setCellStyle(style);

                HSSFCell cel0 = row.createCell(1);
                cel0.setCellValue(excelHeader[1]);
                cel0.setCellStyle(style);

                HSSFCell cel1 = row.createCell(2);
                cel1.setCellValue(excelHeader[2]);
                cel1.setCellStyle(style);

                HSSFCell cel2 = row.createCell(3);
                cel2.setCellValue(excelHeader[3]);
                cel2.setCellStyle(style);

                HSSFCell cel3 = row.createCell(4);
                cel3.setCellValue(excelHeader[4]);
                cel3.setCellStyle(style);


                HSSFCell cel4 = row.createCell(5);
                cel4.setCellValue(excelHeader[5]);
                cel4.setCellStyle(style);
                HSSFCell cel5 = row.createCell(6);
                cel5.setCellValue(excelHeader[6]);
                cel5.setCellStyle(style);
                HSSFCell cel6 = row.createCell(7);
                cel6.setCellValue(excelHeader[7]);
                cel6.setCellStyle(style);
                sheet.setColumnWidth(i, 156 * 50 + 184);
            }

            /**用for循环遍历标题
             * for (int i = 0; i < excelHeader.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader[i]);
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i);
                //设置列宽
                sheet.setColumnWidth(i, 246 * 30 + 184);
                // sheet.SetColumnWidth(i, 100 * 256);
            }*/

            List<SwitchListExcel> list = speedSceneInfoService.getSwitchlist(user, projectName, language);
            SwitchListExcel slx;
            //归类
            for (int i=0;i<list.size();i++){
                for(int j=i+1;j<list.size();j++) {
                    //类型与交换机名相同
                    if (list.get(i).getModel().equals(list.get(j).getModel()) && list.get(i).getType().equals(list.get(j).getType())) {
                        slx = list.get(j);
                        //重新设置场景，数量和小计
                        list.get(i).setNum(slx.getNum() + list.get(i).getNum());
                        list.get(i).setScene(list.get(i).getScene() + slx.getScene());
                        list.get(i).setSubtotal(list.get(i).getNum()*list.get(i).getPrice());
                        list.remove(j);
                        j--;
                    }else
                        break;
                }
            }

            int geshu=0;
            int tupianweiz=0;
            Double total=0.0;
            //循环插入excel
            for (int i = 0; i < list.size(); i++) {
                geshu=i + 2;
                tupianweiz=i + 1;
                row = sheet.createRow(geshu);
                row.setHeight((short) (39.7 * 50));
                SwitchListExcel excelVo = list.get(i);
                //设置样式 每列都是水平垂直居中
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(excelVo.getType());
                cell.setCellStyle(style);

                HSSFCell cell1 = row.createCell(2);
                cell1.setCellValue(excelVo.getModel());
                cell1.setCellStyle(style);

                HSSFCell cell2 = row.createCell(3);
                cell2.setCellValue(excelVo.getFeatures());
                cell2.setCellStyle(style);

                HSSFCell cell3 = row.createCell(4);
                cell3.setCellValue(excelVo.getScene());
                cell3.setCellStyle(style);

                HSSFCell cell4 = row.createCell(5);
                cell4.setCellValue(excelVo.getNum());
                cell4.setCellStyle(style);

                HSSFCell cell5 = row.createCell(6);
                cell5.setCellValue("¥ "+excelVo.getPrice());
                cell5.setCellStyle(style);

                HSSFCell cell6 = row.createCell(7);
                cell6.setCellValue("¥ "+excelVo.getSubtotal());
                total+=excelVo.getSubtotal();
                cell6.setCellStyle(style);
                /**
                 * 该构造函数有8个参数
                 * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                 * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
                 * excel中的cellNum和rowNum的index都是从0开始的
                 * 要传的json数据格式
                 * [{"type":"10001","model":"酒店","features": "2","num":"1","picture":"C:/Users/hjx/Pictures/python/lofter/litreily/1.png"},{"type":"10001","model":"酒店","features": "2","num":"1","picture":"C:/Users/hjx/Pictures/python/lofter/litreily/2.png"}]
                 */
                if (i > 2) {
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    BufferedImage bufferImg = null;//图片一
                    System.out.println("5");
                    bufferImg = ImageIO.read(new File(excelVo.getPicture()));
                    ImageIO.write(bufferImg, "png", byteArrayOut);
                    System.out.println(bufferImg);
                    System.out.println(excelVo.getPicture());
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                            (short) 1, (tupianweiz + 1), (short) 1, (i + 2));
                    anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
                    // 插入图片
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)).resize(0.5);
                } else {
                    ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                    BufferedImage bufferImg1 = null;//图片一
                    System.out.println("15");
                    bufferImg1 = ImageIO.read(new File(excelVo.getPicture()));
                    ImageIO.write(bufferImg1, "png", byteArrayOut1);
                    System.out.println(bufferImg1);
                    System.out.println(excelVo.getPicture());
                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 0, 0,
                            (short) 1, (tupianweiz + 1), (short) 1, (i + 2));

                    anchor1.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
                    // 插入图片
                    patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)).resize(0.5);
                }
            }
            row = sheet.createRow(geshu+1);
            HSSFCell cel21 = row.createCell(7);
            cel21.setCellValue(Total+": ￥"+total);
            cel21.setCellStyle(style);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //设置文件名称
            response.setHeader("Content-Disposition", "attachment; filename=" + ExcelUtil.transferName(request,fileName));//解决中文乱码
            //response.setHeader("Content-disposition", "attachment;filename="+ft.format(date)+""+switchOptInfo+"product list.xls");

            wb.write(outputStream);
            //try catch这些关闭流略
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            outputStream.flush();
            outputStream.close();
        }
    }

效果图

页面截图：

excel截图：