Java实现Excel的导入和导出

秒速五厘米 2022-07-12 09:27 243阅读 0赞

一、下载jxl.jar包导入到工程中

[http://download.csdn.net/detail/lrici/9758600  
][http_download.csdn.net_detail_lrici_9758600]

二、新建Book.java

package com.cc.reflection;
    
    public class Book {
    	
    	private int id;
    	private String name;
    	private String type;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getType() {
    		return type;
    	}
    	public void setType(String type) {
    		this.type = type;
    	}
    	
    
    }

三、新建ExcelBook

package com.cc.reflection;
    
    import java.io.File;
    import java.io.IOException;
    import java.util.ArrayList;
    
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    
    public class ExcelBook {
    
    	//将数据导出到Excel
    	public void excelOut(ArrayList<Book> arrayList) {
    		WritableWorkbook bWorkbook = null;
    		try {
    			// 创建Excel对象
    			bWorkbook = Workbook.createWorkbook(new File("D:/book.xls"));
    			// 通过Excel对象创建一个选项卡对象
    			WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
    			//使用循环将数据读出
    			for (int i = 0; i < arrayList.size(); i++) {
    				Book book=arrayList.get(i);
    				Label label=new Label(0,i,String.valueOf(book.getId()));
    				Label label1=new Label(1,i,String.valueOf(book.getName()));
    				Label label2=new Label(2,i,String.valueOf(book.getType()));
    				sheet.addCell(label);
    				sheet.addCell(label1);
    				sheet.addCell(label2);
    			}
    			
    			
    			// 创建一个单元格对象，第一个为列，第二个为行，第三个为值
    			Label label = new Label(0, 2, "test");
    			// 将创建好的单元格放入选项卡中
    			//sheet.addCell(label);
    			// 写如目标路径
    			bWorkbook.write();
    
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			try {
    				bWorkbook.close();
    			} catch (WriteException | IOException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    
    	}
    	
    	
    	//将Excel中的数据导入
    	public ArrayList<Book> ExcelIn(){
    		ArrayList<Book>arrayList=new ArrayList<Book>();
    		Workbook bWorkbook=null;
    		try {
    			bWorkbook=Workbook.getWorkbook(new File("D:/book.xls"));
    			Sheet sheet=bWorkbook.getSheet(0);
    			for (int i = 0; i < sheet.getRows(); i++) {
    				Book book=new Book();
    				//获取单元格对象
    				Cell cell =sheet.getCell(0,i);
    				//获取单元格的值
    				book.setId(Integer.valueOf(cell.getContents()));
    				book.setName(sheet.getCell(1,i).getContents());
    				book.setType(sheet.getCell(2, i).getContents() );
    				arrayList.add(book);
    				
    			}
    			
    		} catch (BiffException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally {
    			bWorkbook.close();
    		}
    		return arrayList;
    	}
    	
    
    	public static void main(String[] args) {
    		
    		//将数据导出到Excel中
    		ExcelBook book = new ExcelBook();
    //		ArrayList<Book> arrayList = new ArrayList<Book>();
    //		Book bo = new Book();
    //		bo.setId(1);
    //		bo.setName("酒馆");
    //		bo.setType("生活");
    //
    //		Book bo1 = new Book();
    //		bo1.setId(2);
    //		bo1.setName("酒馆1");
    //		bo1.setType("生活1");
    //
    //		arrayList.add(bo);
    //		arrayList.add(bo1);
    //		book.excelOut(arrayList);
    		
    		//将数据从Excel中导入
    		ArrayList<Book> arrayList1 = book.ExcelIn();
    		for(Book bo2:arrayList1){
    			System.out.println(bo2.getName()+bo2.getType());
    		}
    	}
    }

点击运行

[http_download.csdn.net_detail_lrici_9758600]: http://download.csdn.net/detail/lrici/9758600