java 使用jxl 操作Excel

首先需要下载jxl的jar包，下载地址为jxl下载

1、读取Excel的数据

Workbook readwb = null;
		InputStream in = null;
		try{
			//创建输入流
			in=new FileInputStream("f:/jxlrwtest.xls");
			//获取第一个工作表
			readwb = Workbook.getWorkbook(in);
			Sheet readsheet = readwb.getSheet(0);
			
			//这段可以获得表中的图片信息
			int num = readsheet.getNumberOfImages();
			//图片的个数
			System.out.println(num);
			byte[] temp;
			for(int i =0 ; i<num ;i++){
				if(readsheet.getDrawing(i).getImageData().length>3){
					temp = readsheet.getDrawing(i).getImageData();
					System.out.print(readsheet.getDrawing(i).getRow()+",");
					System.out.print(readsheet.getDrawing(i).getColumn()+",");
					System.out.print(readsheet.getDrawing(i).getWidth()+",");
					System.out.println(readsheet.getDrawing(i).getHeight());
					//生成图片并保存
					FileImageOutputStream put = new FileImageOutputStream(new File("f:/img"+i+".jpg"));
					put.write(temp, 0, temp.length);
					put.close();	
				}
			}
			
			//获得表格的行数列数
			int rsColumns = readsheet.getColumns();
			int rsRows = readsheet.getRows();
			
			//循环读取表格中的数据
			for(int i = 0; i<rsRows; i++){
				System.out.print(i+1+",");
				for(int j = 0;j<rsColumns;j++){
					Cell cell = readsheet.getCell(j,i);
					//获取单元格的内容和类型
					System.out.print(cell.getContents()+"是"+cell.getType()+"\t");
				}
				System.out.println();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
			readwb.close();
			try{
				if(in!=null){
					in.close();
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}

2、向Excel写入数据

WritableWorkbook book = null;
		try{
			//新建工作表
			book = Workbook.createWorkbook(new File("f:/writetest.xls"));
			WritableSheet sheet = book.createSheet("测试", 0);
			
			//合并单元格，设置行高列宽
			sheet.mergeCells(0, 0, 2, 2);
			sheet.setRowView(0, 1000);
			sheet.setColumnView(1, 10);
			
			//添加Number对象
			jxl.write.Number number = new jxl.write.Number(3,0,234.354);
			sheet.addCell(number);
			
			//格式化添加Number对象
			NumberFormat nf = new NumberFormat("#.##");
			WritableCellFormat wcf = new WritableCellFormat(nf);
			jxl.write.Number laberNF = new jxl.write.Number(3,1,234.354,wcf);
			sheet.addCell(laberNF);
			
			//添加boolean对象
			jxl.write.Boolean labelB = new jxl.write.Boolean(3,2,true);
			sheet.addCell(labelB);
			
			//添加datetime 对象
			sheet.addCell(new jxl.write.DateTime(4,4,new Date()));
			jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
			WritableCellFormat wcDF = new WritableCellFormat(df);
			jxl.write.DateTime labelDT = new jxl.write.DateTime(3,4,new Date(),wcDF);
			sheet.addCell(labelDT);
			
			//设置单元格样式
			WritableFont font1 = new WritableFont(WritableFont.createFont("隶书"),16,WritableFont.BOLD);
			WritableCellFormat format1 = new WritableCellFormat(font1);
			format1.setAlignment(Alignment.RIGHT);
			format1.setVerticalAlignment(VerticalAlignment.TOP);
			format1.setBorder(Border.ALL,BorderLineStyle.THIN);
			format1.setBackground(Colour.RED);
			
			Label label = new Label(0,0,"test",format1);
			sheet.addCell(label);
			
			//插入图片
			File imgfile = new File("f:/test.png");
			WritableImage img = new WritableImage(1,5,2,5,imgfile);
			sheet.addImage(img);
			
			//添加页眉页脚
			HeaderFooter hf = new HeaderFooter();
			hf.getLeft().append("左");
			hf.getCentre().append("中");
			hf.getRight().append("右");
			sheet.getSettings().setHeader(hf);
			sheet.getSettings().setFooter(hf);
			
			book.write();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
	}