Java实现对Excel文件导入导出_excel java导入导出jar

1.导入jar包

2.创建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() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Book(String name, double price, String author) {
		super();
		this.name = name;
		this.price = price;
		this.author = author;
	}
	@Override
	public String toString() {
		return "Book [name=" + name + ", price=" + price + ", author=" + author
				+ "]";
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

3.导出Excel文件

public void ExcelExp(){
	//添加元素
	List<Book> list = new ArrayList<Book>();
	Book book1 = new Book("西游记", 99.0, "吴承恩");
	Book book2 = new Book("三国演义", 79.0, "罗贯中");
	Book book3 = new Book("红楼梦", 89.0, "曹雪芹");
	Book book4 = new Book("水浒传", 109.0, "施耐庵");
	list.add(book1);
	list.add(book2);
	list.add(book3);
	list.add(book4);
	//设置输出文件路径
	String url = "G:/book.xlsx";
	Workbook wb = null;
	try {
		OutputStream os = new FileOutputStream(url);
		wb = new XSSFWorkbook();
		//获取选项卡
		Sheet sheet = wb.createSheet("Sheet1");
		//获取第一行，对第一行设置标签
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue("书名");
		row.createCell(1).setCellValue("价格");
		row.createCell(2).setCellValue("作者");
		//循环遍历列表，将所有数据存储到行内
		for(int i=0;i<list.size();i++){
			Book books = list.get(i);
			Row rows = sheet.createRow(i+1);
			rows.createCell(0).setCellValue(books.getName());
			rows.createCell(1).setCellValue(books.getPrice());
			rows.createCell(2).setCellValue(books.getAuthor());
		}
		//导出文件
		wb.write(os);
	} catch (Exception e) {
		e.printStackTrace();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

4.导入 Excel文件

@Test
public void ExcelImp(){
	//设置读取Excel文件路径
	String url = "G:/book.xlsx";
	Workbook wb = null;
	try {
		InputStream is = new FileInputStream(url);
		wb = new XSSFWorkbook(is);
		//获取指定选项卡
		Sheet sheet = wb.getSheet("sheet1");
		//获取数据的总行数
		int line = sheet.getLastRowNum();
		//遍历，不获取第一行便签
		for(int i=1;i<=line;i++){
			String name = sheet.getRow(i).getCell(0).getStringCellValue();
			double price = sheet.getRow(i).getCell(1).getNumericCellValue();
			String autor = sheet.getRow(i).getCell(2).getStringCellValue();
			//输出Excel文件内容
			System.out.println(new Book(name,price,autor));
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5.对于包的导入
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

6.大致流程