Java实现Excel文件的导入导出
操作方式有两种:jxl方式和poi方式
由于版本更新,jxl方式适用于Excel 95-2000的所有版本,生成Excel 2000标准格式(文件后缀为xls格式),但是目前的新版版本(新版本Excel通常为xlsx格式,强制转格式可能会引起文件损坏等错误)可能不适用,此次我们使用poi方式实现。
Jar准备:需要的jar包如下图所示
下载地址:https://download.csdn.net/download/qq_34377273/10688855
这里进行一个简单的案例展示:
创建一个Book类,字段有:书籍名称,书籍价格,书籍作者三个
package com.entity;
public class Book {
private String name;
private double price;
private String author;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Book(String name, double price, String author) {
super();
this.name = name;
this.price = price;
this.author = author;
}
public Book() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Book [name=" + name + ", price=" + price + ", author=" + author
+ "]";
}
}
创建POIDemo类,编写Excel文件导入导出方法
导出方法:
/**
* 导出Excel
*/
public void excelWrite(List<Book> list){
Workbook wb = null;
try {
//创建Excel对象
wb = new XSSFWorkbook();
//创建选项卡对象
Sheet sheet = wb.createSheet("sheet1");
//输入标题行
Row titleRow = sheet.createRow(0);
titleRow.createCell(0).setCellValue("书名");
titleRow.createCell(1).setCellValue("价格");
titleRow.createCell(2).setCellValue("作者");
//循环行数据
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i+1);
Book book = list.get(i);
row.createCell(0).setCellValue(book.getName());
row.createCell(1).setCellValue(book.getPrice());
row.createCell(2).setCellValue(book.getAuthor());
}
//输出Excel文件(Excel文件导出的路径)
wb.write(new FileOutputStream("E:\\book.xlsx"));
} catch (Exception e) {
e.printStackTrace();
}
}
Main方法测试结果:
public static void main(String[] args) {
List<Book> list = new ArrayList<Book>();
list.add(new Book("西游记",100,"吴承恩"));
list.add(new Book("水浒传",80,"施耐庵"));
list.add(new Book("三国演义",90,"罗贯中"));
list.add(new Book("红楼梦",110,"曹雪芹"));
POIDemo pd = new POIDemo();
pd.excelWrite(list);
}
运行完成后出现E盘下出现book.xlsx文件
文件内容:
文件导入方法:
/**
* 导入Excel
*/
public List<Book> excelInput(String filePath){
List<Book> list = new ArrayList<Book>();
Workbook wb = null;
try {
//读取本地文件
InputStream is = new FileInputStream(filePath);
//创建Excel文件对象
wb = new XSSFWorkbook(is);
//获取选项卡对象
Sheet sheet = wb.getSheetAt(0);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if(i<1){
System.out.print(row.getCell(0).getStringCellValue()+"\t"+row.getCell(1).getStringCellValue()+"\t"
+row.getCell(2).getStringCellValue());
continue;
}
Book book = new Book();
book.setName(row.getCell(0).getStringCellValue());
book.setPrice(row.getCell(1).getNumericCellValue());
book.setAuthor(row.getCell(2).getStringCellValue());
list.add(book);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
Main方法调用导入方法:
public static void main(String[] args) {
POIDemo pd = new POIDemo();
//导出操作
// List<Book> list = new ArrayList<Book>();
// list.add(new Book("西游记",100,"吴承恩"));
// list.add(new Book("水浒传",80,"施耐庵"));
// list.add(new Book("三国演义",90,"罗贯中"));
// list.add(new Book("红楼梦",110,"曹雪芹"));
// pd.excelWrite(list);
//导入操作
List<Book> bookList = pd.excelInput("E:\\book.xlsx");
System.out.println();
for (Book book : bookList) {
System.out.println(book.getName()+"\t"+book.getPrice()+"\t"+book.getAuthor());
}
}
输出结果: