JXL POI 导出excel 包括图片

主要的问题还是JXL只支持PNG格式的图片,没辙,只有转换格式再处理.附件是结果图,这里只是贴出来方法.,还有转换图片的时候可能出异常.需要另外处理

	private WritableWorkbook saveXlS(OutputStream os,List items) throws Exception{
		ICItemManager itemMgnt = (ICItemManager) this.getBean("icItemManager");
		LazyDynaBean item = null;
		LazyDynaBean orderContent = null;
		if(items.size()>0){
			item=(LazyDynaBean) items.get(0);
		}
		jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
		// 文档格式名称
		jxl.write.WritableSheet sheet = wwb.createSheet("销售出库", 0);
		//加入文字,改变其字体和底色
		jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,20, WritableFont.BOLD, false,
		UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		
		WritableCellFormat format=new WritableCellFormat(wfc); 
		//把水平对齐方式指定为居中 
		format.setAlignment(jxl.format.Alignment.CENTRE); 
		//把垂直对齐方式指定为居中 
		format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
		//设置自动换行
		format.setWrap(true);
		//
		jxl.write.WritableFont wfc2 = new jxl.write.WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		WritableCellFormat contentFormat=new WritableCellFormat(wfc2); 
		contentFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
		contentFormat.setAlignment(jxl.format.Alignment.CENTRE); 
		contentFormat.setWrap(true);
		/**
		 * 第一行
		 */
		//合并单元格
		sheet.mergeCells(0,0,13,1); 
		// 加入文字 0,0是坐标位置
		jxl.write.Label title = new jxl.write.Label(0, 0, "销售出库单");
		title.setCellFormat(format);
		sheet.addCell(title);
		
		jxl.write.Label content =null;
		/**
		 * 第二行
		 */
		sheet.mergeCells(0,2,1,2);
		content = new jxl.write.Label(0, 2, "单据编号");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(2,2,6,2);
		content=new jxl.write.Label(2, 2, item.get("fbillno").toString());content.setCellFormat(contentFormat);
		sheet.addCell(content);
		
		sheet.mergeCells(7,2,8,2);
		content = new jxl.write.Label(7, 2, "日期");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(9,2,13,2);
		content=new jxl.write.Label(9, 2,  (String)item.get("fdate").toString().substring(0,10));content.setCellFormat(contentFormat);
		sheet.addCell(content);
		
		
		/**
		 * 第三行
		 */
		sheet.mergeCells(0,3,1,3);
		content = new jxl.write.Label(0, 3, "审核标志");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(2,3,6,3);
		content=new jxl.write.Label(2, 3, item.get("fcheck").toString());content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(7,3,8,3);
		content = new jxl.write.Label(7, 3, "仓库");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(9,3,13,3);
		content=new jxl.write.Label(9,3, item.get("fstockname").toString());content.setCellFormat(contentFormat);
		sheet.addCell(content);
		
		/**
		 * 第四行
		 */
		sheet.mergeCells(0,4,1,4);
		content = new jxl.write.Label(0, 4, "业务单位");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(2,4,6,4);
		content=new jxl.write.Label(2, 4,item.get("fdepartment").toString());content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(7,4,8,4);
		content = new jxl.write.Label(7, 4, "业务员");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(9,4,13,4);
		content=new jxl.write.Label(9,4, item.get("empFname").toString());content.setCellFormat(contentFormat);
		sheet.addCell(content);
		
		NumberFormat nFormat=NumberFormat.getNumberInstance();
		nFormat.setMaximumFractionDigits(2);//设置小数点后面尾数为2
		
		/**
		 * 第四行
		 */
		sheet.mergeCells(0,5,1,5);
		content = new jxl.write.Label(0, 5, "日期");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(2,5,4,5);
		content = new jxl.write.Label(2, 5, "物料名称");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(5,5,5,5);
		content = new jxl.write.Label(5, 5, "单位");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(6,5,7,5);
		content = new jxl.write.Label(6, 5, "单价");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(8,5,8,5);
		content = new jxl.write.Label(8, 5, "数量");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(9,5,10,5);
		content = new jxl.write.Label(9, 5, "金额");content.setCellFormat(contentFormat);
		sheet.addCell(content);
		sheet.mergeCells(11,5,13,5);
		content = new jxl.write.Label(11, 5, "图片");content.setCellFormat(contentFormat);
		sheet.addCell(content);	
		/**
		 * 第五行
		 */
		int j=items.size();
		for (int i = 0; i < j; i++) {
			orderContent=(LazyDynaBean)items.get(i);
			
			sheet.setRowView(i+6, 1000);
			sheet.setColumnView(i+6, 10);
			
			sheet.mergeCells(0,i+6,1,i+6);
			content = new jxl.write.Label(0, i+6, orderContent.get("fdate").toString().substring(0,10));content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(2,i+6,4,i+6);
			content = new jxl.write.Label(2, i+6, orderContent.get("fitemname").toString());content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(5,i+6,5,i+6);
			content = new jxl.write.Label(5, i+6, orderContent.get("funitname").toString());content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(6,i+6,7,i+6);
			Double fauxprice=Double.parseDouble(orderContent.get("fauxprice").toString());
			content = new jxl.write.Label(6, i+6, nFormat.format(fauxprice));content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(8,i+6,8,i+6);
			Double fauxqty=Double.parseDouble(orderContent.get("fauxqty").toString());
			content = new jxl.write.Label(8, i+6,nFormat.format(fauxqty));content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(9,i+6,10,i+6);
			Double famount=Double.parseDouble(orderContent.get("famount").toString());
			content = new jxl.write.Label(9, i+6,nFormat.format(famount));content.setCellFormat(contentFormat);
			sheet.addCell(content);
			sheet.mergeCells(11,i+6,13,i+6);
			
			//插入图片,由于只支持png格式,需要转换成png.
			List icItemPics = itemMgnt.getICItemPics(Integer.valueOf((String)orderContent.get("pfitemid")));
			File outputFile=null;
			ICItemAttach ica = null;
			if(icItemPics.size()>0){
				ica = (ICItemAttach) icItemPics.get(0);
				File imgFile =new File(this.getServlet().getServletContext().getRealPath("repository") + File.separator + ica.getParentNumber() + File.separator
						+ ica.getFNumber() + File.separator + ica.getFfilename());
				System.err.println(imgFile.getCanonicalPath());
				if(imgFile.exists()&&imgFile.length()>0){
					BufferedImage input=null;
					try {
						input = ImageIO.read(imgFile);
					} catch (Exception e) {
						e.printStackTrace();
					}
					if(input!=null){
						String path=imgFile.getAbsolutePath();
						outputFile = new File(path.substring(0,path.lastIndexOf('.')+1)+"png");
						ImageIO.write(input, "PNG", outputFile);
						if(outputFile.exists()&&outputFile.length()>0){
							WritableImage image = new WritableImage(11, i+6, 3, 1, outputFile);
							sheet.addImage(image);
						}
					}
					
				}
			}
		}
		return wwb;
	}

POI :

public static void main(String[] args)throws Exception {
        //声明一个工作薄
        HSSFWorkbook wb=new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet=wb.createSheet("表格1");
        //生成一个列
        HSSFRow row=sheet.createRow(0);
        //生成一个样式
        HSSFCellStyle style=wb.createCellStyle();
        //设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成一个字体
        HSSFFont font=wb.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short)16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //把字体应用到当前的样式
        style.setFont(font);
        //声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //填充单元格
        for(short i=0;i<5;i++){
            //声明一个单元格
            HSSFCell cell=row.createCell(i);
            switch(i){
                case 0:
                    //设置普通文本
                    cell.setCellValue(new HSSFRichTextString("普通文本"));
                    break;
                case 1:
                    //设置为形状
                    HSSFClientAnchor a1 = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
                    HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
                    //这里可以设置形状的样式
                    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
                    
                    break;
                case 2:
                    //设置为布尔量
                    cell.setCellValue(true);
                    break;
                case 3:
                    //设置为double值
                    cell.setCellValue(12.5);
                    break;
                case 4:
                    //设置为图片]
                    URL url=POITest.class.getResource("hello.jpg");
                    if(url!=null){
                    	insertImage(wb,patriarch,getImageData(ImageIO.read(url)),0,4,1);
                        break;
                    }
                    
                    
            }
            
            //设置单元格的样式
            cell.setCellStyle(style);
        }
        FileOutputStream fout=new FileOutputStream("我的第一个EXCEL.xls");
        //输出到文件
        wb.write(fout);
        fout.close();
    }
    //自定义的方法,插入某个图片到指定索引的位置
    private static void insertImage(HSSFWorkbook wb,HSSFPatriarch pa,byte[] data,int row,int column,int index){
    	//单元格为标,以左上为起点,想右移,范围0-1023  dx1 must be between 0 and 1023
        int x1=0;
        //单元格为标,以左上为起点,想下移,范围0-1023  dy1 must be between 0 and 255
        int y1=0;
        //单元格为标,以右上为起点,想左移,范围0-1023  dx1 must be between 0 and 1023
        int x2=100;
        //单元格为标,以右下为起点,想上移,范围0-1023  dy1 must be between 0 and 255
        int y2=22;
        HSSFClientAnchor anchor = new HSSFClientAnchor(x1,y1,x2,y2,(short)column,row,(short)column,row);
        
        anchor.setAnchorType(3);
        pa.createPicture(anchor , wb.addPicture(data,HSSFWorkbook.PICTURE_TYPE_JPEG));
    }
    //从图片里面得到字节数组
    private static  byte[] getImageData(BufferedImage bi){
        try{
        	//转化成PNG
            ByteArrayOutputStream bout=new ByteArrayOutputStream();
            ImageIO.write(bi,"PNG",bout);
            return bout.toByteArray();
        }catch(Exception exe){
            exe.printStackTrace();
            return null;
        }
    }

评论

2 楼 522823979 2016-11-14  

      

1 楼 贝塔ZQ 2016-09-21  

实现导出excel文件和图片，感觉用poi和jxl代码量好多，还容易出现问题，用插件么，简单方便，PageOffice插件就是专门操作处理office文档的，集成起来也方便，官网上他们也提供了不少事例代码，看起来也方便，可以了解了解。