使用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.查看效果