JXL POI 导出excel 包括图片

http://iehyou.iteye.com/blog/676893

主要的问题还是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;
	}