1 使用Jxl实现Excel的导入与导出
1 ) 新建实体类Book
- package com.bean;
- public class Book {
- 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 ) 实现导入与导出方法
- package com.main;
- import java.io.File;
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
- import com.bean.Book;
- import jxl.Cell;
- import jxl.Sheet;
- import jxl.Workbook;
- import jxl.write.Label;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import jxl.write.WriteException;
- public class ExcleBook {
- /**
- * 针对Book类进行导出的操作
- * @param list
- */
- public void excleOut(List<book> list) {
- WritableWorkbook book = null;
- try {
- // 创建一个excle对象
- book = Workbook.createWorkbook(new File("h:/excleTest/book.xls"));
- // 通过excle对象创建一个选项卡对象
- WritableSheet sheet = book.createSheet("sheet1", 0);
- // 创建一个单元格对象 列 行 值
- // Label label = new Label(0, 2, "test");
- for (int i = 0; i < list.size(); i++) {
- Book book2 = list.get(i);
- Label label1 = new Label(0, i, String.valueOf(book2.getId()));
- Label label2 = new Label(1, i, book2.getName());
- Label label3 = new Label(2, i, book2.getAuthor());
- // 将创建好的单元格对象放入选项卡中
- sheet.addCell(label1);
- sheet.addCell(label2);
- sheet.addCell(label3);
- }
- // 写入目标路径
- book.write();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- book.close();
- } catch (WriteException | IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
- /**
- * 针对Book类进行导入的操作
- * @return
- */
- public List<book> excleIn() {
- List<book> list = new ArrayList<>();
- Workbook workbook = null;
- try {
- // 获取Ecle对象
- workbook = Workbook.getWorkbook(new File("h:/excleTest/book.xls"));
- // 获取选项卡对象 第0个选项卡
- Sheet sheet = workbook.getSheet(0);
- // 循环选项卡中的值
- for (int i = 0; i < sheet.getRows(); i++) {
- Book book = new Book();
- // 获取单元格对象
- Cell cell0 = sheet.getCell(0, i);
- // 取得单元格的值,并设置到对象中
- book.setId(Integer.valueOf(cell0.getContents()));
- // 获取单元格对象,然后取得单元格的值,并设置到对象中
- book.setName(sheet.getCell(1, i).getContents());
- book.setAuthor(sheet.getCell(2, i).getContents());
- list.add(book);
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- workbook.close();
- }
- return list;
- }
- public static void main(String[] args) {
- ExcleBook book = new ExcleBook();
- List<book> list = new ArrayList<>();
- Book book2 = new Book();
- book2.setId(1);
- book2.setName("书本名1");
- book2.setAuthor("张三");
- Book book3 = new Book();
- book3.setId(2);
- book3.setName("书本名2");
- book3.setAuthor("李四");
- list.add(book2);
- list.add(book3);
- book.excleOut(list);
- List<book> books = book.excleIn();
- for (Book bo : books) {
- System.out.println(bo.getId() + " " + bo.getName() + " " + bo.getAuthor());
- }
- }
- }
- </book></book></book></book></book>
在这当中需要加入jxl.jar这个包,自己在网上下载,如果你使用Mavne,进行对应的配置就可以了。
2 使用Poi实现Excel的导入与导出
我们在网上找 POI下载的时候,会发现它有点大,而且还有几个不同的包,那么我们到底需要那个。如果你只需要对xls文件进行解释,只需要加入poi-Version.jar;如果要对xlsx文件进行解释,那么你需要加入poi-ooxml-Version.jar;如果你要对word、ppt、viso、outlook等时需要用到poi-scratchpad-version-yyyymmdd.jar。因为我们这里只是对xls文件解释,所以我们加入的poi-3.17.jar这个文件。
- package com.main;
- 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.bean.Book;
- public class ExclePoiBook {
- /**
- * 针对Book类进行导出的操作
- *
- * @param list
- */
- public void excleOut(List<book> list) {
- // 创建Excel文档
- HSSFWorkbook hwb = new HSSFWorkbook();
- // 通过excle对象创建一个选项卡对象
- HSSFSheet sheet = hwb.createSheet("sheet1");
- Book book = null;
- // 循环list创建行
- 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行第2列的数据
- row.createCell(2).setCellValue(book.getAuthor());
- }
- OutputStream out = null;
- try {
- out = new FileOutputStream("h:/excleTest/bookPoi.xls");
- hwb.write(out);
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- } finally {
- try {
- out.close();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
- /**
- * 针对Book类进行导入的操作
- *
- * @return
- */
- public List<book> excleIn() {
- List<book> list = new ArrayList<>();
- Book book = null;
- try {
- InputStream is = new FileInputStream("h:/excleTest/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读取的内容是有类型的,处理起来也jxl有所不同
- 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 Auto-generated catch block
- e.printStackTrace();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- return list;
- }
- public static void main(String[] args) {
- ExclePoiBook exclePoiBook = new ExclePoiBook();
- List<book> list = new ArrayList<>();
- Book book2 = new Book();
- book2.setId(1);
- book2.setName("书本名1");
- book2.setAuthor("张三");
- Book book3 = new Book();
- book3.setId(2);
- book3.setName("书本名2");
- book3.setAuthor("李四");
- list.add(book2);
- list.add(book3);
- exclePoiBook.excleOut(list);
- List<book> books = exclePoiBook.excleIn();
- for (int i = 0; i < books.size(); i++) {
- System.out.println(books.get(i).getId() + " " + books.get(i).getName() + " " + books.get(i).getAuthor());
- }
- }
- }