一、使用Apache POI方式
1.1 基本实现方式
HSSFWorkbook sheets = new HSSFWorkbook();
HSSFSheet sheet = sheets.createSheet(aClass.getName());
HSSFRow row = sheet.createRow(0);
HSSFCell cell=null;
for (int i = 0; i <declaredFields.length ; i++) {
cell= row.createCell(i);
cell.setCellValue(declaredFields[i].getName());
}
sheets.write(new FileOutputStream("C:\Users\yaohuiqin\Desktop\excel\excelname.xls"));
1.2 列合并
int mergeBeginRow = 3;
int mergeEndRow = 4;
int columnIndex = 0;
sheet.addMergedRegion(new CellRangeAddress(
mergeBeginRow , //first row (0-based)
mergeEndRow, //last row (0-based)
columnIndex, //first column (0-based)
columnIndex //last column (0-based)
));
1.3 直接读EXCEL
package com.amazing.poi.excel.test;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
/**
* @author yaohuiqin
* @description
* @date 2020-04-20
*/
public class ReadTest {
public static void main(String[] args) throws IOException {
ReadTest readTest = new ReadTest();
readTest.readexcel();
}
public void readexcel() throws IOException {
System.out.println("开始读。。。。。。");
String path = "C:\Users\yaohuiqin\Desktop\excel\m-cell-3.xls";
FileInputStream fis = new FileInputStream(path);
Workbook wb = WorkbookFactory.create(fis);
Sheet sheetAt = wb.getSheetAt(0);
Iterator<Row> rowIterator = sheetAt.rowIterator();
while (rowIterator.hasNext()) {
Row next = rowIterator.next();
rowhandler(next);
System.out.println();
}
}
public void rowhandler(Row row0) {
Iterator<Cell> cellIterator = row0.cellIterator();
while (cellIterator.hasNext()) {
Cell next = cellIterator.next();
Object getvalue = getvalue(next);
System.out.print(getvalue + " ");
}
}
public Object getvalue(Cell tmp) {
Object value = null;
switch (tmp.getCellType()) {
case _NONE:
break;
case BLANK:
break;
case STRING:
value = tmp.getStringCellValue();
break;
case BOOLEAN:
value = tmp.getBooleanCellValue();
break;
case FORMULA:
value = tmp.getCellFormula();
break;
case NUMERIC:
value = tmp.getNumericCellValue();
break;
case ERROR:
value = tmp.getErrorCellValue();
break;
default:
break;
}
return value;
}
}
1.4 读取excel，将excel转为对象列表
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface excelvalue {
String name() default "";
}
public class Person {
@excelvalue(name = "姓名")
String name;
@excelvalue(name = "年龄")
int age;
@excelvalue(name = "性别")
String gender;
//get and set
}
package com.amazing.poi.excel.test;
import com.amazing.poi.excel.commons.ObjectCastUtils;
import org.apache.poi.ss.usermodel.*;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
/**
* @author yaohuiqin
* @description
* @date 2020-04-20
*/
public class ReadToObjectTest {
public static void main(String[] args) throws Exception {
ReadToObjectTest readToObjectTest = new ReadToObjectTest();
String path = "C:\Users\yaohuiqin\Desktop\excel\m-cell-3.xls";
FileInputStream fis = new FileInputStream(path);
Person person = new Person();
List<Person> peoples = readToObjectTest.readExcel(person, fis);
for(Person people : peoples){
System.out.println(people);
}
}
public <T> List<T> readExcel(T t, FileInputStream fis) throws Exception {
Workbook wb = WorkbookFactory.create(fis);
List<T> list = new LinkedList<T>();
Sheet sheetAt = wb.getSheetAt(0);
Iterator<Row> rowIterator = sheetAt.rowIterator();
Map<Integer, Method> headerhandler = null;
//处理表头
if(rowIterator.hasNext()){
Row headerRow = rowIterator.next();
headerhandler = headerhandler(t, headerRow);
}
while (rowIterator.hasNext()) {
Row next = rowIterator.next();
T t1 = rowhandler(t,next,headerhandler);
list.add(t1);
}
return list;
}
public <T> Map<Integer,Method> headerhandler(T t, Row headerRow) throws Exception {
Iterator<Cell> cellIterator = headerRow.cellIterator();
int i = 1;
Map<Integer,Method> map = new HashMap<Integer,Method>();
while (cellIterator.hasNext()) {
Cell next = cellIterator.next();
Object getvalue = getvalue(next);
Method writeMethod = getcellHandler(t, getvalue);
if(writeMethod == null){
throw new Exception("excel头部和对象不匹配");
}
map.put(i,writeMethod);
i++;
}
return map;
}
public <T> T rowhandler(T t, Row row0, Map<Integer, Method> headerhandler) throws IllegalAccessException, InstantiationException, InvocationTargetException {
Class<?> aClass = t.getClass();
T object = (T) aClass.newInstance();
Iterator<Cell> cellIterator = row0.cellIterator();
int i = 1;
while (cellIterator.hasNext()) {
Cell next = cellIterator.next();
Object getvalue = getvalue(next);
Method writeMethod = headerhandler.get(i);
Class<?>[] parameterTypes = writeMethod.getParameterTypes();
Class<?> parameterType = parameterTypes[0];
Object castValue = ObjectCastUtils.objectCast(parameterType,getvalue);
i++;
writeMethod.invoke(object,castValue);
}
return object;
}
public <T> Method getcellHandler(T t,Object getvalue) throws IntrospectionException {
System.out.println(getvalue.getClass());
Field[] declaredFields = t.getClass().getDeclaredFields();
List<String > list = new ArrayList<>();
for(Field field :declaredFields){
excelvalue annotation = field.getAnnotation(excelvalue.class);
String name = annotation.name();
if(name.equals(getvalue)){
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), t.getClass());
Method writeMethod = pd.getWriteMethod();
return writeMethod;
}
}
return null;
}
public Object getvalue(Cell tmp) {
Object value = null;
switch (tmp.getCellType()) {
case _NONE:
break;
case BLANK:
break;
case STRING:
value = tmp.getStringCellValue();
break;
case BOOLEAN:
value = tmp.getBooleanCellValue();
break;
case FORMULA:
value = tmp.getCellFormula();
break;
case NUMERIC:
value = tmp.getNumericCellValue();
break;
case ERROR:
value = tmp.getErrorCellValue();
break;
default:
break;
}
return value;
}
}