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;    }

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