Java导出Excel和图片

由于项目需要,需要导出数据和二维码到Excel,其实二维码就是图片

我这里excel的布局要求是四列,每个单元由数据+二维码组成

 public String exportCode(String id,EquEquipmentDetails equ,HttpServletResponse response,HttpServletRequest request) {
        String path;
        if(id!=null) {//这里是根据ID获取数据来源,ID不存在   数据就是equ 
            equ = equEquipmentDetailsService.selectByPrimaryKey(id);
            equ.setPurchaseDate(convert(equ.getPurchaseDate()));
            //预览二维码存放路径
            path = request.getServletContext().getRealPath("/")+"static\\qrCode\\";
        }
        //自定义二维码存放路径
        path = request.getServletContext().getRealPath("/")+"static\\customCode\\";
        //装备数量
        int  equipmentNum = Integer.valueOf(equ.getEquipmentNum()).intValue();

        String beginName=school.getOrganizationno()+equ.getBatchNumber()+equ.getEquipmentCode();
        //excel名称
        String sheetName=equ.getEquipmentName()+"-标签二维码";
        try {
            //创建工作薄
            /*Workbook wb = new SXSSFWorkbook(500);
            //创建工作表
            Sheet sheet = wb.createSheet(sheetName);
            Drawing patriarch = sheet.createDrawingPatriarch();*/

            // 创建一个工作薄  
            HSSFWorkbook wb = new HSSFWorkbook();  
            //创建一个sheet  
            HSSFSheet sheet = wb.createSheet();  

            CellStyle style = wb.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(titleFont);
            //创建第一行
            Row titleRow=sheet.createRow(0);
            titleRow.setHeightInPoints(40);//设置行高
            //创建单元格
            Cell titleCell=titleRow.createCell(0);
            titleCell.setCellValue(sheetName);
            titleCell.setCellStyle(style);
            //设置合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,3));
            int rowNum;//计算行数,规定一行4列
            if(equipmentNum%4==0) {
                rowNum = equipmentNum/4;
            }else {
                rowNum = equipmentNum/4+1;
            }
            int lastRowNum = equipmentNum%4;
            //设置4列宽度
            sheet.setColumnWidth(0, 42*256);
            sheet.setColumnWidth(1, 42*256);
            sheet.setColumnWidth(2, 42*256);
            sheet.setColumnWidth(3, 42*256);
            for(int i=0;i<rowNum;i++) {
                Row row = sheet.createRow(i+1);//创建行
                row.setHeightInPoints(100);//设置行高
                if(i==rowNum-1) {//最后一行时根据具体数量生成
                        for(int j=0;j<lastRowNum;j++) {
                            BufferedImage bufferImg=null;//图片
                            HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
                            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 
                            //创建单元格
                            Cell cell=row.createCell(j);
                            style = wb.createCellStyle();
                            style.setWrapText(true);//设置自动换行
                            //垂直居中
                            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                            //设置边线
                            style.setBorderBottom(CellStyle.BORDER_THIN);
                            style.setBorderTop(CellStyle.BORDER_THIN);
                            style.setBorderRight(CellStyle.BORDER_THIN);
                            //设置单元边框颜色
                            style.setTopBorderColor(HSSFColor.RED.index);
                            style.setRightBorderColor(HSSFColor.RED.index);
                            style.setBottomBorderColor(HSSFColor.RED.index);
                            StringBuilder sb=new StringBuilder();
                            sb.append("物品编号:"+equ.getEquipmentCode()+"-"+(4*i+j+1)+"\n");
                            sb.append("物品名称:"+equ.getEquipmentName()+"\n");
                            sb.append("购入日期:"+equ.getPurchaseDate());
                            cell.setCellValue(sb.toString());
                            cell.setCellStyle(style);
                            //获取二维码
                            bufferImg = ImageIO.read(new File(path+beginName+(4*i+j+1)+".jpg"));
                            System.out.println(path+beginName+(4*i+j+1)+".jpg");
                            ImageIO.write(bufferImg, "jpg", byteArrayOut);  
                            //插入Excel表格
                            HSSFClientAnchor anchor = new HSSFClientAnchor(500, 10,0, 0,  
                                     (short) j, (i+1), (short)(j+1) , (i+2)); 
                            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut  
                                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                     }
                  }else {
                        for(int j=0;j<4;j++) {
                            HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
                            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 
                            //创建单元格
                            Cell cell=row.createCell(j);
                            style = wb.createCellStyle();
                            style.setWrapText(true);//设置自动换行
                            //垂直居中
                            style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                            //设置边线
                            style.setBorderBottom(CellStyle.BORDER_THIN);
                            style.setBorderTop(CellStyle.BORDER_THIN);
                            style.setBorderRight(CellStyle.BORDER_THIN);
                            //设置单元边框颜色
                            style.setTopBorderColor(HSSFColor.RED.index);
                            style.setRightBorderColor(HSSFColor.RED.index);
                            style.setBottomBorderColor(HSSFColor.RED.index);
                            StringBuilder sb=new StringBuilder();
                            sb.append("物品编号:"+equ.getEquipmentCode()+"-"+(4*i+j+1)+"\n");
                            sb.append("物品名称:"+equ.getEquipmentName()+"\n");
                            sb.append("购入日期:"+equ.getPurchaseDate());
                            cell.setCellValue(sb.toString());
                            cell.setCellStyle(style);
                            BufferedImage bufferImg=null;//图片
                             System.out.println(path+beginName+(4*i+j+1)+".jpg");
                            //获取二维码
                            bufferImg = ImageIO.read(new File(path+beginName+(4*i+j+1)+".jpg"));
                            ImageIO.write(bufferImg, "jpg", byteArrayOut); 
                             /** 
                             * 该构造函数有8个参数 
                             * 前四个参数是控制图片在单元格的位置,分别是图片距离单元格left,top,right,bottom的像素距离 
                             * 后四个参数,前连个表示图片左上角所在的cellNum和 rowNum,后天个参数对应的表示图片右下角所在的cellNum和 rowNum, 
                             * excel中的cellNum和rowNum的index都是从0开始的 
                             *  
                             */ 
                            //插入Excel表格
                            HSSFClientAnchor anchor = new HSSFClientAnchor(500, 10,0,0,  
                                    (short) j, (i+1), (short)(j+1) , (i+2)); 
                            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut  
                                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                     }
                  }
            }
            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(
                    (sheetName+LocalDate.now()+".xls").getBytes("utf-8"), "iso-8859-1"));
            wb.write(response.getOutputStream()); 
        } catch (Exception e) {
            logger.debug(e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

下篇文章将介绍二维码生成

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/xujiangdong1992/article/details/78068760
个人分类: J2ee
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭