通过使用poi完成java对excel文件的读写,话不多说直接开始。
注意:这里依赖的jar包需要使用两个,一个是poi,一个是jxl。
附上依赖:
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
测试excel表格的样式如下:
姓名 | 手机号 | 地址 |
---|---|---|
张三 | 138******** | 蘑菇屯 |
文章最后是对应该excel格式的bean对象类
下面代码包括读,写excel,中间读取完数据需要对数据进行处理就不做具体介绍了。
package com.test;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
public static void main(String[] args) {
File file = new File("/excelPath/excel.xls");
List<PeopleBean> list;
try {
//1.读excel
list = readExcel(file);
//2.读完数据可以对数据进行处理再写出,这里就不做举例了
//3.写excel
createExcel(list);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*将excel内的数据读取到list集合里
* @param file file
* @return
* @throws Exception
*/
public static List<PeopleBean> readExcel(File file) throws Exception {
Workbook workbook = Workbook.getWorkbook(new FileInputStream(
file.getAbsoluteFile()));
//选择工作簿,简单测试只选择第一个工作簿,为0
Sheet sheet = workbook.getSheet(0);
//获取该工作簿的行数
Integer rows = sheet.getRows();
List<PeopleBean> datalist = new ArrayList<PeopleBean>();
PeopleBean people ;
//这里i从1开始,0是标题行,去掉不作为数据。
for(int i = 0; i < rows; i++){
Cell[] cells = sheet.getRow(i);
//表格只有三列,这里简单测试就直接写死了
people = new PeopleBean(cells[0].getContents().trim(),
cells[1].getContents().trim(),cells[2].getContents().trim());
//将创建的bean添加到结果集
datalist.add(people);
}
return datalist;
}
/**
* 将集合内的数据输出到新的excel
* @param resultList
* @throws IOException
*/
public static void createExcel(List<PeopleBean> resultList) throws IOException {
// 创建workbook对应的excel
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//在workbook里面创建对应excel的工作簿sheet
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
//创建工作簿第0行的标题
HSSFRow hssfRow = hssfSheet.createRow(0);
//创建单元格,设置表头
HSSFCell hssfCell = hssfRow.createCell(0);
hssfCell.setCellValue("");
hssfCell = hssfRow.createCell(1);
hssfCell.setCellValue("");
hssfCell = hssfRow.createCell(2);
hssfCell.setCellValue("");
hssfCell = hssfRow.createCell(3);
//数据循环遍历添加到行信息
for(int i = 0; i<resultList.size(); i++){
HSSFRow hssfRow1 = hssfSheet.createRow(i);
hssfRow1.createCell(0).setCellValue(resultList.get(i).getName());
hssfRow1.createCell(1).setCellValue(resultList.get(i).getPhone());
hssfRow1.createCell(2).setCellValue(resultList.get(i).getAdress());
}
//写出生成新的excel文件
hssfWorkbook.write(new FileOutputStream("/filePath/newExcel.xls"));
System.out.println("write success!");
}
}
以及bean对象类:
package com.test;
public class PeopleBean {
private String name;
private String phone;
private String adress;
public PeopleBean() {
}
public PeopleBean(String name, String phone, String adress) {
this.name = name;
this.phone = phone;
this.adress = adress;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAdress() {
return adress;
}
public void setAdress(String adress) {
this.adress = adress;
}
}