import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public final class ExcelUtils {
public static <T> SXSSFWorkbook createUserXlsFa(List<T> users) throws IllegalAccessException {
XSSFWorkbook xssfWorkbook=new XSSFWorkbook();
SXSSFWorkbook excel = new SXSSFWorkbook(xssfWorkbook , 1000);
Sheet sheetAt=excel.createSheet("用户信息表");
Row row;
Cell cell;
int rowNum=0;
row=sheetAt.createRow(rowNum);
rowNum++;
T object=users.get(0);
Class tempClass=object.getClass();
List<Field> filesList= Arrays.asList(tempClass.getDeclaredFields());
int m=0;
for (Field field: filesList){
if(field.isAnnotationPresent(Excel.class)){
Excel annotation = field.getAnnotation(Excel.class);
if(annotation.isExcel()){
continue;
}else {
cell=row.createCell(m);
cell.setCellValue(annotation.name().equals("fieldName")?field.getName():annotation.name());
}
}else {
cell=row.createCell(m);
cell.setCellValue(field.getName());
}
m++;
}
for (T obj : users){
m=0;
row=sheetAt.createRow(rowNum);
for (Field field:filesList) {
String type = field.getGenericType().toString();
if(field.isAnnotationPresent(Excel.class)){
Excel annotation = field.getAnnotation(Excel.class);
if(annotation.isExcel()){
continue;
}else {
cell=row.createCell(m);
field.setAccessible(true);
setValue(type,field.get(obj),cell);
}
}else {
cell=row.createCell(m);
field.setAccessible(true);
setValue(type,field.get(obj),cell);
}
m++;
}
rowNum++;
}
return excel;
}
public static<T> void exportExcel(List<T> users) throws IOException {
SXSSFWorkbook wb;
try {
wb=createUserXlsFa(users);
ByteArrayOutputStream byteArrayOutputStream=new ByteArrayOutputStream();
wb.write(byteArrayOutputStream);
byte[] xlsxBytes = byteArrayOutputStream.toByteArray();
OutputStream out = new FileOutputStream("D://excel.xlsx");
out.write(xlsxBytes);
out.close();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException {
List<Dog> objects = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Dog dog = new Dog();
dog.setSex(i%2==0?0:1);
dog.setName("狗狗"+i+"号");
dog.setAge(5);
dog.setPrice(1.0);
dog.setPrice1(2.0);
dog.setPrice2(BigDecimal.valueOf(3.0));
dog.setFlag(true);
dog.setFlag1(false);
dog.setBigNum(2089146174099386620L);
objects.add(dog);
}
exportExcel(objects);
List<String[]> list = readExcel("");
List<Dog> dogs = changeToObject(list,Dog.class);
}
private static void setValue(String type ,Object obj,Cell cell){
switch (type) {
case "class java.lang.String":
cell.setCellValue(ObjectUtils.isEmpty(obj)?null:String.valueOf(obj));
break;
case "int":
case "class java.lang.Integer":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Integer.parseInt(String.valueOf(obj)));
break;
case "double":
case "class java.lang.Double":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0.0:Double.parseDouble(String.valueOf(obj)));
break;
case "long":
case "class java.lang.Long":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0L:Long.parseLong(String.valueOf(obj)));
break;
case "short":
case "class java.lang.Short":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Short.parseShort(String.valueOf(obj)));
break;
case "char":
case "class java.lang.Character":
cell.setCellValue(ObjectUtils.isEmpty(obj)?null:(Character)obj);
break;
case "float":
case "class java.lang.Float":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0F:Float.parseFloat(String.valueOf(obj)));
break;
case "class java.math.BigDecimal":
cell.setCellValue(ObjectUtils.isEmpty(obj)?0:Double.parseDouble(String.valueOf(obj)));
break;
case "boolean":
case "class java.lang.Boolean":
cell.setCellValue(ObjectUtils.isEmpty(obj)?null:(Boolean) obj);
break;
default:
break;
}
}
public static List<String[]> readExcel(String path) {
path = "D://excel.xlsx";
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
List<String[]> list = null;
try {
File excelFile = new File(path);
FileInputStream is = new FileInputStream(excelFile);
Workbook workbook = WorkbookFactory.create(is);
int sheetCount = workbook.getNumberOfSheets();
list = new ArrayList<String[]>();
for (int s = 0; s < sheetCount; s++) {
Sheet sheet = workbook.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells();
String[] model = new String[cellCount];
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
CellType cellType = cell.getCellType();
String cellValue = null;
switch (cellType.getCode()) {
case 1:
cellValue = cell.getStringCellValue();
break;
case 0:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue());
} else {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 3:
cellValue = cell.getStringCellValue();
break;
case 5:
cellValue = "错误";
break;
case 2:
cellValue = "错误";
break;
default:
cellValue = "错误";
}
model[c] = cellValue;
}
list.add(model);
}
}
is.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
private static <T> List<T> changeToObject(List<String[]> list, Class<T> d){
String[] strings = list.get(0);
List<T> objects = new ArrayList<>();
for(int i = 1 ;i <list.size();i++){
objects.add(setValue1(strings,list.get(i),d));
}
return objects;
}
private static <T> T setValue1(String[] strings, String[] strs, Class<T> d) {
Field[] fields = d.getDeclaredFields();
T t = null;
try {
t = d.newInstance();
for (Field f : fields) {
f.setAccessible(true);
for (int i = 0; i < strings.length; i++) {
if (f.getName().equals(strings[i])) {
String tName = f.getName();
char[] tChars = tName.toCharArray();
tChars[0] -= 32;
String tMethodName = "set" + String.valueOf(tChars);
Method tMethod = d.getMethod(tMethodName, f.getType());
String type = f.getGenericType().toString();
changeValue(tMethod,t, strs[i],type);
break;
}
}
}
} catch (Exception e) {
System.out.println(e);
}
return t;
}
private static <T> void changeValue(Method tMethod,T t, String str, String type ) throws InvocationTargetException, IllegalAccessException {
switch (type) {
case "class java.lang.String":
case "char":
case "class java.lang.Character":
tMethod.invoke(t, str);
break;
case "int":
case "class java.lang.Integer":
tMethod.invoke(t, Integer.parseInt(str));
break;
case "double":
case "class java.lang.Double":
tMethod.invoke(t, Double.parseDouble(str));
break;
case "long":
case "class java.lang.Long":
tMethod.invoke(t, Long.parseLong(str));
break;
case "short":
case "class java.lang.Short":
tMethod.invoke(t,Short.parseShort(str));
break;
case "float":
case "class java.lang.Float":
tMethod.invoke(t, Float.parseFloat(str));
break;
case "class java.math.BigDecimal":
tMethod.invoke(t,new BigDecimal(str));
break;
case "boolean":
case "class java.lang.Boolean":
if(StringUtils.isNotEmpty(str)){
if("true".equals(str)){
tMethod.invoke(t,true);
}else {
tMethod.invoke(t,false);
}
}else {
tMethod.invoke(t,false);
}
break;
default:
break;
}
}
}
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Excel {
String name() default "fieldName";
boolean isExcel() default false;
}
import lombok.Data;
import java.math.BigDecimal;
@Data
public class Dog {
@Excel(name = "name")
private String name;
@Excel(name = "sex")
private int sex;
@Excel(name = "age")
private Integer age;
@Excel(name = "price")
private double price;
@Excel(name = "price1")
private Double price1;
@Excel(name = "price2")
private BigDecimal price2;
@Excel(name = "flag")
private boolean flag;
@Excel(name = "flag1")
private Boolean flag1;
@Excel(name = "bigNum")
private long bigNum;
}