Java实现Excel的导入和导出

最新推荐文章于 2024-09-23 00:00:02 发布

LuciusZhao

最新推荐文章于 2024-09-23 00:00:02 发布

阅读量2w

点赞数 3

分类专栏： Java

本文链接：https://blog.csdn.net/lrici/article/details/56012054

版权

Java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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

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());
		}
	}
}

点击运行