自定义注解实现Excel导入导出
程序员文章站
2022-04-25 20:01:35
...
1 自定义@ExcelAnnotation注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 生成Excel模板时,需要有哪些字段名、字段标题、字段之间的排序、字段中内容的位置、对齐方式等信息
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
String headName();
int order();
String datePattern() default "yyyyMMdd HH:mm:ss";
enum DataType {
String, Number, Date,
}
/**
* 数据类型,可以是String,Number(数字型),Date等类型
*
* @return
*/
DataType type() default DataType.String;
}
2 ExcelUtils工具类
package com.grm.util;
import com.grm.annotation.ExcelAnnotation;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.util.*;
/**
* @author gaorimao
*/
public class ExcelUtils {
private static ExcelUtils instance;
private ExcelUtils() {
}
/**
* 单例模式
*
* @return
*/
public static ExcelUtils getInstance() {
if (instance == null) {
instance = new ExcelUtils();
}
return instance;
}
/**
* excel的导出
*
* @param response
* @param infos
*/
public static void exportExcel(HttpServletResponse response, List<?> infos) {
try {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
XSSFSheet sheet = xssfWorkbook.createSheet();
sheet.createRow(0);
Map<Field, Integer> map = new LinkedHashMap<>();
for (Object o : infos) {
Field[] fields = o.getClass().getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
map.put(field, annotation.order());
}
}
}
List<Map.Entry<Field, Integer>> list = new ArrayList<>(map.entrySet());
Collections.sort(list, Comparator.comparing(Map.Entry::getValue));
List<Field> excelFields = new ArrayList<>();
for (Map.Entry<Field, Integer> map1 : list) {
excelFields.add(map1.getKey());
}
List<ExcelAnnotation> annotations = new ArrayList<>();
for (Field excelField : excelFields) {
annotations.add(excelField.getAnnotation(ExcelAnnotation.class));
}
addDataToExcel(xssfWorkbook, infos, excelFields, annotations, sheet);
xssfWorkbook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
private static <T> void addDataToExcel(XSSFWorkbook wb, List<T> dataset, List<Field> excelFields, List<ExcelAnnotation> attributes,
Sheet sheet)
throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, ParseException {
XSSFCellStyle style = wb.createCellStyle();
// 居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// excel放入第一行列的名称
Row row = sheet.createRow(0);
for (int j = 0; j < excelFields.size(); j++) {
Cell cell = row.createCell(j);
ExcelAnnotation oneAttribute = attributes.get(j);
cell.setCellValue(oneAttribute.headName());
cell.setCellStyle(style);
}
// 添加数据到excel
for (int i = 0; i < dataset.size(); i++) {
// 数据行号从1开始,因为第0行放的是列的名称
row = sheet.createRow(i + 1);
for (int j = 0; j < attributes.size(); j++) {
Cell cell = row.createCell(j);
ExcelAnnotation annotation = attributes.get(j);
style = wb.createCellStyle();
// 居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 四个边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
cell.setCellStyle(style);
// 根据属性名获取属性值
String cellValue = BeanUtils.getProperty(dataset.get(i), excelFields.get(j).getName());
if (ExcelAnnotation.DataType.Date.equals(annotation.type())) {
String date = DateUtils
.dateStringToOtherDateString(cellValue, DateUtils.EXCEL_DATE_FORMAT, annotation.datePattern());
cell.setCellValue(date);
} else {
cell.setCellValue(cellValue);
}
}
}
}
/**
* excel的导入
*
* @param file file
* @param clazz
* @return
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
public static List<?> importExcel(MultipartFile file, Class<?> clazz)
throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
Workbook workbook = WorkbookFactory.create(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
Row titleCell = sheet.getRow(0);
List<Object> dataList = new ArrayList<>(sheet.getLastRowNum());
Object datum;
Map<String, Field> fieldMap = getFieldMap(clazz);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
datum = clazz.newInstance();
int minCell = row.getFirstCellNum();
int maxCell = row.getLastCellNum();
for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {
Cell title = titleCell.getCell(cellNum);
if (title == null) {
continue;
}
String tag = title.getStringCellValue();
Field field = fieldMap.get(tag);
if (field == null) {
continue;
}
Class<?> type = field.getType();
Object value = null;
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
if (type.equals(Date.class)) {
value = cell.getDateCellValue();
ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
String datePattern = annotation.datePattern();
Date date = DateUtils.dateStringToDate((String) value, DateUtils.DEFAULT_DATE_FORMAT);
PropertyUtils.setProperty(datum, field.getName(), date);
} else if (type.equals(Byte.class)) {
Double numericCellValue = cell.getNumericCellValue();
value = numericCellValue.byteValue();
PropertyUtils.setProperty(datum, field.getName(), value);
} else if (type.equals(Short.class)) {
Double numericCellValue = cell.getNumericCellValue();
value = numericCellValue.shortValue();
PropertyUtils.setProperty(datum, field.getName(), value);
} else if (type.equals(Integer.class)) {
Double numericCellValue = cell.getNumericCellValue();
value = numericCellValue.intValue();
PropertyUtils.setProperty(datum, field.getName(), value);
} else if (type.equals(Long.class)) {
Double numericCellValue = cell.getNumericCellValue();
value = numericCellValue.longValue();
PropertyUtils.setProperty(datum, field.getName(), value);
} else if (type.equals(Float.class)) {
Double numericCellValue = cell.getNumericCellValue();
value = numericCellValue.floatValue();
PropertyUtils.setProperty(datum, field.getName(), value);
} else if (type.equals(Double.class)) {
Double numericCellValue = cell.getNumericCellValue();
PropertyUtils.setProperty(datum, field.getName(), numericCellValue);
} else {
value = cell.getStringCellValue();
PropertyUtils.setProperty(datum, field.getName(), value);
}
}
dataList.add(datum);
}
return dataList;
}
/**
* key :headName val:该名称对应的字段
*
* @param clazz
* @param <T>
* @return
*/
private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {
Field[] fields = clazz.getDeclaredFields();
Map<String, Field> fieldMap = new HashMap<>();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
fieldMap.put(annotation.headName(), field);
}
}
return fieldMap;
}
}
上一篇: Fetch抓取
下一篇: Java面试基础知识复习
推荐阅读