POI导出Excel 含一维码图片

最新推荐文章于 2022-12-06 09:50:10 发布

liuyuhua0066

最新推荐文章于 2022-12-06 09:50:10 发布

阅读量2.5k

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/will_awoke/article/details/9358659

版权

Java 专栏收录该内容

136 篇文章 0 订阅

订阅专栏

代码如下：

/**
     * 创建excel
     * @return 
     * @throws InvalidAtributeException 
     * @throws IOException 
     * @see
     */
    private InputStream createExcel(List<Dealer> dealerList)
        throws IOException, InvalidAtributeException
    {
        //创建Excel工作簿对象
        HSSFWorkbook wb = new HSSFWorkbook();

        //创建Excel工作表
        HSSFSheet sheet = wb.createSheet("sheet1");

        //设置列宽
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 10 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 13 * 3 * 256);

        //单元格样式
        HSSFCellStyle wrapTextAlignLeft = wb.createCellStyle();
        wrapTextAlignLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        wrapTextAlignLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        wrapTextAlignLeft.setWrapText(true);

        //单元格样式
        HSSFCellStyle wrapTextAlignCenter = wb.createCellStyle();
        wrapTextAlignCenter.setWrapText(true);
        wrapTextAlignCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        wrapTextAlignCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //单元格图片处理对象
        HSSFPatriarch patriarch;

        //单元格图片锚点
        HSSFClientAnchor barcodeAnchor;

        int rowNum = 0;

        //创建标题行
        HSSFRow dataRow = sheet.createRow(rowNum);
        dataRow.setHeight((short)(2 * 255));
        HSSFCell nameCell = dataRow.createCell(0);
        nameCell.setCellStyle(wrapTextAlignCenter);
        nameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        nameCell.setCellValue("经销商名称");

        HSSFCell codeCell = dataRow.createCell(1);
        codeCell.setCellStyle(wrapTextAlignCenter);
        codeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        codeCell.setCellValue("经销商编号");

        HSSFCell areaCell = dataRow.createCell(2);
        areaCell.setCellStyle(wrapTextAlignCenter);
        areaCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        areaCell.setCellValue("区域");

        HSSFCell barcodeCell = dataRow.createCell(3);
        barcodeCell.setCellStyle(wrapTextAlignCenter);
        barcodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        barcodeCell.setCellValue("一维码");

        rowNum++ ;

        //一维码对象初始化
        JBarcode barcode = new JBarcode(Code39Encoder.getInstance(),
            WideRatioCodedPainter.getInstance(), BaseLineTextPainter.getInstance());

        //不需要校验位
        barcode.setCheckDigit(false);

        //一维码图片宽度
        barcode.setXDimension(0.26458333299999998D * 2.2);

        //一维码图片高度
        barcode.setBarHeight(0.26458333299999998D * 120);

        //一维码图片对象
        BufferedImage barcodeImage;

        //一维码图片字节流
        ByteArrayOutputStream barcodeByteArray;

        String tempAreaName;

        //POI图片操作对象
        patriarch = sheet.createDrawingPatriarch();

        for (Dealer tempDealer : dealerList)
        {

            //创建行
            dataRow = sheet.createRow(rowNum);

            //设置行高
            dataRow.setHeight((short)(7.5 * 255));

            //初始化区域
            tempAreaName = "";

            //名称单元格
            nameCell = dataRow.createCell(0);
            nameCell.setCellStyle(wrapTextAlignCenter);
            nameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            nameCell.setCellValue(tempDealer.getDealerName());

            //编码单元格
            codeCell = dataRow.createCell(1);
            codeCell.setCellStyle(wrapTextAlignCenter);
            codeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            codeCell.setCellValue(tempDealer.getDealerCode());

            //区域单元格
            areaCell = dataRow.createCell(2);
            areaCell.setCellStyle(wrapTextAlignCenter);
            areaCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            tempAreaName = DBCommon.getAreaAndParentString(tempDealer.getAreaId());
            areaCell.setCellValue(tempAreaName);

            //一维码单元格
            barcodeCell = dataRow.createCell(3);

            //初始化图片锚点
            barcodeAnchor = new HSSFClientAnchor(10, 10, 1013, 245, (short)3, rowNum, (short)3,
                rowNum);

            //图片锚点样式
            barcodeAnchor.setAnchorType(HSSFClientAnchor.DONT_MOVE_AND_RESIZE);

            //一维码图片初始化
            barcodeImage = barcode.createBarcode(tempDealer.getDealerCode());

            //初始化图片字节流
            barcodeByteArray = new ByteArrayOutputStream();

            //图片对象写入字节流
            ImageUtil.encodeAndWrite(barcodeImage, ImageUtil.JPEG, barcodeByteArray, 96, 96);
            
            barcodeByteArray.close();

            //添加图片字节流到Excel
            patriarch.createPicture(barcodeAnchor,
                wb.addPicture(barcodeByteArray.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            //行数自增
            rowNum++ ;

        }

        //excel输出流
        ByteArrayOutputStream excelByteArrayOutputStream = new ByteArrayOutputStream();

        //excel对象写入输出流
        wb.write(excelByteArrayOutputStream);
        
        return new ByteArrayInputStream(excelByteArrayOutputStream.toByteArray());
    }

    public static String getAreaAndParentString(String areaId)
    {
        Connection con = null;
        Statement pstmt = null;
        ResultSet rs = null;
        String area = "";

        try
        {
            con = DBManager.getDBConnection();
            pstmt = con.createStatement();           
            rs = pstmt.executeQuery("select t.area_name from tqs_area_info t start with t.area_id="
                                    + areaId
                                    + " connect by t.area_id= prior t.parent_id order by t.area_id");


            while (rs.next())
            {
                area += rs.getString(1);
            }


            rs.close();
        }
        catch (Exception e)
        {
            logger.error(e.getMessage());
        }
        finally
        {
            try
            {
                if (pstmt != null)
                {
                    pstmt.close();
                }
                if (con != null)
                {
                    con.close();
                }
            }
            catch (SQLException ex)
            {
                logger.error(ex.getMessage());
            }
        }


        return area;
    }