首先卖个萌,poi~
当然这里不是什么夕立啊奇怪的口癖,是指Apache POI。Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能,这里我就改进了对excel的读写方法,因为用到了~
下面是正题:
惯例maven项目,加入以下依赖:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
然后是整体代码,两个方法,读和写。
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by Dian.N.Wang on 2016/11/9.
*/
public class Functions {
/**
* 导入excel
* @param filepath
* @param classref
* @return List
*/
public List importExcelToList(String filepath,Class classref){
List list = new ArrayList();
try {
//Create the input stream from the xlsx/xls file
FileInputStream fis = new FileInputStream(filepath);
//Create Workbook instance for xlsx/xls file input stream
Workbook workbook = null;
if(filepath.toLowerCase().endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(filepath.toLowerCase().endsWith("xls")){
workbook = new HSSFWorkbook(fis);
}
//Get the number of sheets in the xlsx file
int numberOfSheets = workbook.getNumberOfSheets();
//loop through each of the sheets
for(int i=0; i < numberOfSheets; i++){
//Get the nth sheet from the workbook
Sheet sheet = workbook.getSheetAt(i);
//every sheet has rows, iterate over them
Iterator<Row> rowIterator = sheet.iterator();
//ignore header
if (rowIterator.hasNext()) {
rowIterator.next();
}
//every row
while (rowIterator.hasNext())
{
Field[] fs = classref.getDeclaredFields();
Object obj=classref.newInstance();
//Get the row object
Row row = rowIterator.next();
//Every row has columns, get the column iterator and iterate over them
Iterator<Cell> cellIterator = row.cellIterator();
//every cell
while (cellIterator.hasNext()) {
for (Field field : fs) {
String fieldname = field.getName();
String setname="set"+fieldname.substring(0,1).toUpperCase()+ fieldname.substring(1);
Class fieidclazz=field.getType();
Method m=classref.getDeclaredMethod(setname,fieidclazz);
//Get the Cell object
Cell cell = cellIterator.next();
//check the cell type and process accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
m.invoke(obj,cell.getStringCellValue().trim());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
m.invoke(obj,formater.format(d));
} else {
m.invoke(obj,(int) cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BLANK:
m.invoke(obj,null);
break;
case Cell.CELL_TYPE_BOOLEAN:
m.invoke(obj, cell.getBooleanCellValue());
break;
default:
throw new NoSuchTypeException();
}
}
}//end of cell iterator
list.add(obj);
} //end of rows iterator
} //end of sheets for loop
//close file input stream
fis.close();
} catch (IOException e) {
e.printStackTrace();
} catch (NoSuchTypeException e) {
e.dealWithException();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return list;
}
/**
*导出excel
* @param list
* @param fileName
* @throws Exception
*/
public void exportListToExcel(List list,String fileName,Class classref) throws Exception {
Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook();
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook();
}else{
throw new Exception("invalid file name, should be xls or xlsx");
}
//create sheet
Sheet sheet = workbook.createSheet(classref.getName());
Field[] fs = classref.getDeclaredFields();
Iterator iterator = list.iterator();
//create header
int rowIndex = 0;
Row row = sheet.createRow(rowIndex++);
int cellcount=0;
for(Field field:fs) {
Cell cell = row.createCell(cellcount);
cell.setCellValue(field.getName());
cellcount++;
}
//write contents
while(iterator.hasNext()){
Object obj = iterator.next();
row = sheet.createRow(rowIndex++);
cellcount=0;
for(Field field:fs) {
Cell cell = row.createCell(cellcount);
String fieldname = field.getName();
String getname="get"+fieldname.substring(0,1).toUpperCase()+ fieldname.substring(1);
Method m=classref.getDeclaredMethod(getname);
cell.setCellValue(String.valueOf(m.invoke(obj)));
cellcount++;
}
}
//lets write the excel data to file now
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " written successfully");
}
/**
* 抛出未处理的表格单元格的数据类型
*/
private class NoSuchTypeException extends Throwable {
public void dealWithException(){
System.out.println("未解析的excel表格类型");
}
}
}
运用反射机制,这样就可以不需要考虑究竟是何种具体model对象,只要知道是什么类就好(好难表述啊,感觉怎么说都不合适,就是你不需要根据网上找的那些实例为每一个model都建立读写方法了,啊,不知道怎么说了,用到也许会明白吧)。另外,表头的部分如果不使用model中属性的名字的话,可能要自己增加参数或是进行转化(配置文件的方式),代码不可能总是一成不变的,需要什么佐料还是要靠自己添加。
下面是简单的测试方法,也可以看出是这读和写两个方法是如何调用的:
model类:
public class User {
private String name;
private int age;
public User(){};
public User(String name,int age){
this.name=name;
this.age=age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String toString(){
return "name:"+this.name+" age:"+this.age;
}
}
main函数:
public class Test {
public static void main(String[] args) throws Exception {
Functions func=new Functions();
Class<?> clazz = Class.forName("User");
func.importExcelToList("C:\\Users\\Dian.N.Wang\\Desktop\\aaa.xlsx",clazz);
User user1=new User("sb",25);
User user2=new User("sb2",30);
List users=new ArrayList();
users.add(user1);
users.add(user2);
func.exportListToExcel(users,"C:\\Users\\Dian.N.Wang\\Desktop\\bbb.xlsx",clazz);
}
}
其实这只是反射的一个小小的应用点,但是你会发现他可以删减你很多的代码量(自己感觉,你们能理解就好了,泪目/(ㄒoㄒ)/~~)。
最后,跟着我大声喊:“poi~”