Excel导出

  /**
     * 根据批次号导出溯源码excel
     * 　2003
     *
     * @param pbCode
     * @return
     */
    @RequestMapping("exportTrQrcode")
    public synchronized void exportTrQrcode(String pbCode, HttpServletResponse response) throws IOException {
        //参数初始化
        startRow = -1;
        startCol = 0;
        patriarch = null;
        totalSheet = 0;
        sheet = null;

        ResultBase resultBase = null;
        BufferedImage bufferImg = null;
        String outFileName = null;
        HSSFWorkbook wb = null;
        try {
            if (StringUtils.isEmpty(pbCode)) {
                resultBase = new ResultBase("1014");
            } else if (batchService.getProBatchByPbCode(pbCode) == null) {
                resultBase = new ResultBase("1015");
            } else {
                List<TbTraceQrcode> qrcodeList = qrcodeService.getListByPbCode(pbCode);
                File model = new File(FilePathConsts.rootPath + "file/excel/export_trace_qrcode.xls");
                FileInputStream fis = new FileInputStream(model);
                POIFSFileSystem fs = new POIFSFileSystem(fis);
                wb = new HSSFWorkbook(fs);

                //HSSFSheet sheet = wb.createSheet("qrcode picture");
                sheet = wb.getSheetAt(0);
                //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                patriarch = sheet.createDrawingPatriarch();

                int width = sheet.getColumnWidth(0);
                int height = sheet.getRow(0).getHeight();
                totalSheet = 1;
                //溯源码图片生成  顺序为：溯源码-其对应的活动码们-下一个溯源码...
                for (TbTraceQrcode qrcode : qrcodeList) {
                    ByteArrayOutputStream byteArrayOut = QrCodeUtils.qrCode(FilePathConsts.qrcode_prefix + qrcode.getTqQrcode());

                    insertPicture(width, height, wb, byteArrayOut);

                    //活动码
                    List<TbDrawQrcode> drawQrcodeList = drawQrcodeService.getDrQrcodeListByTqQrcode(qrcode.getTqQrcode());
                    //活动码图片生成
                    for (TbDrawQrcode tbDrawQrcode : drawQrcodeList) {
                        ByteArrayOutputStream byteArrayOut2 = QrCodeUtils.qrCode(FilePathConsts.qrcode_prefix + tbDrawQrcode.getDqId());

                        insertPicture(width, height, wb, byteArrayOut2);

                    }
                }

            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        } finally {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + UUIDUtils.getUniquekey() + ".xls");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        }
    }

    /**
     * EXCEL插入图片
     *
     * @param width
     * @param height
     * @param wb
     * @param byteArrayOut
     */
    private void insertPicture(int width, int height, HSSFWorkbook wb, ByteArrayOutputStream byteArrayOut) {
        try {
            HSSFClientAnchor anchor;
            //anchor主要用于设置图片的属性
            if (startCol != 0 && startCol < 5) {
                anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) startCol, startRow, (short) startCol, startRow);
                sheet.setColumnWidth(startCol, width);
                //sheet.getRow(startRow).setHeight((short)height);
                startCol++;
            } else {
                startCol = 0;
                startRow++;
                anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) startCol, startRow, (short) startCol, startRow);
                sheet.setColumnWidth(startCol, width);
                if (sheet.getRow(startRow) == null) {
                    sheet.createRow(startRow);
                }
                sheet.getRow(startRow).setHeight((short) height);
                startCol++;
            }

            anchor.setAnchorType(3);
            //插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            //每页五行五列
            if (startRow == 5 && startCol == 5) {
                sheet = wb.createSheet("Sheet" + (++totalSheet));
                patriarch = sheet.createDrawingPatriarch();
                startCol = 0;
                startRow = -1;
            }
        } catch (Exception e) {
            throw e;
        }
    }