使用Poi实现Excel的导入与导出

使用Poi实现Excel的导入与导出

(这里只是进行了excel的导入导出，所以只引入了poi:3.14-betal依赖包)

1.编写一个book实体类

/**
 * 
 */
package com.zhiyou100.entity;

import java.io.Serializable;

/**
 * @author Administrator
 *
 */
public class Book implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String name;
	private String author;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

}

 2.ExcelUtil.java

/**
 * 
 */
package com.zhiyou100.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.zhiyou100.entity.Book;

/**
 * @author Administrator
 *
 */
public class ExcleUtil {
/**
 * 针对Book类进行导出操作
 */
	public static void excelOut(List<Book> list) {
//		创建excel文档
		HSSFWorkbook hwd = 
				new HSSFWorkbook();
//		通过excel对象创建一个选项卡对象,命名为sheet1
		HSSFSheet sheet = 
				hwd.createSheet("sheet1");
		Book book = null;
//		循环遍历
		for (int i = 0; i < list.size(); i++) {
//			新建一行
			HSSFRow row = sheet.createRow(i);
			book = list.get(i);
//			设置i+1行第0列的数据
			row.createCell(0).setCellValue(book.getId());
//			设置i+1行第1列的数据
			row.createCell(1).setCellValue(book.getName());
//			设置i+1行第二列的数据
			row.createCell(2).setCellValue(book.getAuthor());
		
		}
		OutputStream out = null;
		try {
			out = 
					new FileOutputStream("E:\\杂乱包\\bookPoi.xls");
			try {
				hwd.write(out);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			try {
				out.close();
			} catch (IOException e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}
	}
	/**
	 * 针对Book类进行导入的操作
	 */
	public static List<Book> excleIn() {
		List<Book> list = new ArrayList<Book>();
		Book book = null;
		try {
			InputStream is = 
					new FileInputStream("E:\\杂乱包\\bookPoi.xls");
			HSSFWorkbook hssfWorkbook = 
					new HSSFWorkbook(is);
			/**
			 * 获取选项卡对象 第0个选项卡，因为我们这里只有一个选项卡，
			 * 如果你每个选项卡的内容是一样的，可以通过循环取出
			 */
			HSSFSheet hssfSheet = 
					hssfWorkbook.getSheetAt(0);
			/**
			 * 循环取出每行的值
			 */
			for (int rowNum = 0; rowNum <=hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow =
						hssfSheet.getRow(rowNum);
				book = new Book();
			/**
			 * 注意POI读取的内容是由类型的
			 */
				book.setId((int)hssfRow.getCell(0).getNumericCellValue());
				book.setName(hssfRow.getCell(1).getStringCellValue());
				book.setAuthor(hssfRow.getCell(2).getStringCellValue());
				list.add(book);
			}
		} catch (FileNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();
		}catch (IOException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return list;
	}
	public static void main(String[] args) {
		List<Book> list = new ArrayList<Book>();
		Book book = new Book();
		book.setId(1);
		book.setName("书本1");
		book.setAuthor("张三");
		
		Book book2 = new Book();
		book2.setId(2);
		book2.setName("书本2");
		book2.setAuthor("李四");
		
		list.add(book);
		list.add(book2);
		excelOut(list);
		System.out.println("导出完毕");
		List<Book> books = excleIn();
		for (int i = 0; i < books.size(); i++) {
			System.out.println(books.get(i).getId()+" "
					+books.get(i).getName()+" "
					+books.get(i).getAuthor());
		}
	}
}

3.查看效果