一个自己写的简单导入导出工具类,客户端请根据情况调整stream输出
1、注解类(导入)
import java.lang.annotation.*;
/**
* 特殊性Excel文件头读取/请使用ccys/../Util同包下的工具
* @author mars_q
* @date 2020/7/6 11:29
* create by 2012692013@qq.com
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
public @interface ExcelImporter {
/**
* 表名
* @return
*/
String value() default "";
/**
* 工作表名 没有的时候默认取sheet(0)
* @return
*/
String sheetName() default "";
/**
* 标题行
* @return
*/
int titleRow() default 0;
/**
* 起始行
* @return
*/
int startRow() default 1;
/**
* 描述
* @return
*/
String desc() default "";
}
import java.lang.annotation.*;
/**
* 特殊性Excel文件列读取/请使用ccys/../Util同包下的工具
* @author mars_q
* @date 2020/7/6 11:29
* create by 2012692013@qq.com
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelCellImporter {
/**
* 列名称
* @return
*/
String value() default "";
/**
* 描述
* @return
*/
String desc() default "";
/**
* 是否必须
* @return
*/
boolean required() default true;
/**
* 需要转换解释的值
* @return
*/
ExcelTrans[] trans() default {};
}
2、注解类(导出)
import java.lang.annotation.*;
/**
* excel文件导出/请使用ccys/../Util同包下的工具
* @author mars_q
* @date 2020/7/10 11:29
* create by 2012692013@qq.com
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
public @interface ExcelExporter {
/**
* 表名
* @return
*/
String value() default "";
/**
* 工作表名 没有的时候默认工作表名
* @return
*/
String sheet() default "";
/**
* 描述
* @return
*/
String desc() default "";
}
import java.lang.annotation.*;
/**
* excel文件导出/请使用ccys/../Util同包下的工具
* @author mars_q
* @date 2020/7/10 11:29
* create by 2012692013@qq.com
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelCellExporter {
/**
* 列名称
* @return
*/
String value() default "";
/**
* 排序
* @return
*/
int order() default 0;
/**
* 描述
* @return
*/
String desc() default "";
/**
* 需要转换解释的值
* @return
*/
ExcelTrans[] trans() default {};
}
3、解释器注解类
import java.lang.annotation.*;
/**
* Excel文件->bean/bean->文件 值相互转换/请使用ccys/../Util同包下的工具
* @author mars_q
* @date 2020/7/6 11:29
* create by 2012692013@qq.com
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.FIELD})
public @interface ExcelTrans {
/**
* 当前类的值
* @return
*/
String bean() default "";
/**
* 描述
* @return
*/
String excel() default "";
}
4、导入|导出工具类
//这里可自行定义一个excelException进行错误抛出
import com.demo.util.base.exception.ExcelException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
public class ExcelImport {
public ExcelImport() {
}
/**
* 读EXCEL文件,获取信息集合
*
* @param mFile
* @return
*/
public static <T> List<T> getExcelInfo(MultipartFile mFile,Class<T> clazz) {
String fileName = mFile.getOriginalFilename();// 获取文件名
try {
if (!validateExcel(fileName)) {// 验证文件名是否合格
return null;
}
boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
if (isExcel2007(fileName) || isExcelCsv(fileName)) {
isExcel2003 = false;
}
return createExcel(mFile.getInputStream(), isExcel2003,clazz);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 根据excel里面的内容读取客户信息
*
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
private static <T> List<T> createExcel(InputStream is, boolean isExcel2003,Class<T> clazz) {
try {
Workbook wb = null;
if (isExcel2003) {// 当excel是2003时,创建excel2003
wb = new HSSFWorkbook(is);
} else {// 当excel是2007时,创建excel2007
wb = new XSSFWorkbook(is);
}
return readExcelValue(wb,clazz);// 读取Excel里面客户的信息
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 读取Excel
*
* @param wb
* @return
*/
private static <T> List<T> readExcelValue(Workbook wb,Class<T> clazz) {
try {
ExcelImporter excelReader = clazz.getAnnotation(ExcelImporter.class);
// // 预留于多表文件上传
// String value = excelReader.value();
String sheetName = excelReader.sheetName();
int titleNum = excelReader.titleRow();
int startNum = excelReader.startRow();
Sheet sheet = null;
if (StringUtils.isBlank(sheetName))
sheet = wb.getSheetAt(0);
else
sheet = wb.getSheet(sheetName);
// 得到Excel的行数
int totalRows = sheet.getPhysicalNumberOfRows();
int totalCells = 0;
// 得到Excel的列数(前提是有行数)
if (totalRows > 1 && sheet.getRow(titleNum) != null) {
totalCells = sheet.getRow(titleNum).getLastCellNum();
}
Row titleRow = sheet.getRow(titleNum);
// 解析表头
Map<String, Integer> titleMap = new HashMap<>();
for (int i = 0; i < titleRow.getLastCellNum(); i++) {
Cell cell = titleRow.getCell(i);
if (cell == null || StringUtils.isBlank(cell.getStringCellValue()))
continue;
String cellValue = cell.getStringCellValue();
cellValue = cellValue.trim();
titleMap.put(cellValue, i);
}
// 解析实体类
Map<String, String> beanMap = new HashMap<>();
// 需要解析的列
List<Integer> parseCell = new ArrayList<>();
// 解析的节点
Map<Integer, String> parseBean = new HashMap<>();
// 翻译节点
Map<String, Map<String,String>> transMap = new HashMap<>();
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field field = declaredFields[i];
boolean present = field.isAnnotationPresent(ExcelCellImporter.class);
if (present) {
ExcelCellImporter reader = field.getAnnotation(ExcelCellImporter.class);
String title = reader.value();
if (titleMap.get(title) == null && reader.required()) {
System.err.println("Excel内找不到约定对应字段:" + title + "\r\n\tfrom:" + clazz.getName());
//这里可自行定义一个excelException进行错误抛出
throw new ExcelException("Excel内找不到约定对应字段:" + title);
}
beanMap.put(title, field.getName());
parseCell.add(titleMap.get(title));
parseBean.put(titleMap.get(title), field.getName());
// 加入解析器
ExcelTrans[] trans = reader.trans();
if (trans != null && trans.length > 0) {
Map<String, String> propertyTransMap = new HashMap<>();
for (int j = 0; j < trans.length; j++) {
ExcelTrans tran = trans[j];
String bean = tran.bean();
String excel = tran.excel();
propertyTransMap.put(excel, bean);
}
transMap.put(field.getName(),propertyTransMap);
}
} else continue;
}
List<T> exportList = new ArrayList<>();
if (parseCell.size() < 1)
return null;
// 循环Excel行数
for (int r = startNum; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
T instance = clazz.newInstance();
//有效数据行
int validData = 0;
for (int c = 0; c < parseCell.size(); c++) {
Integer cellNum = parseCell.get(c);
if (cellNum == null)
continue;
Cell cell = row.getCell(cellNum);
if (cell == null || cell.toString().trim().equals(""))
continue;
validData++;
cell.setCellType(CellType.STRING);
Field field = clazz.getDeclaredField(parseBean.get(cellNum));
Class<?> type = field.getType();
Object value = cell.getStringCellValue().trim();
Map<String, String> valueTransMap = transMap.get(field.getName());
if (valueTransMap != null && valueTransMap.get(value) != null)
value = valueTransMap.get(value);
if (type.equals(String.class))
value = String.valueOf(value);
if (type.equals(Integer.class))
value = Integer.valueOf(new Double(value.toString()).intValue());
if (type.equals(BigDecimal.class))
value = new BigDecimal(value.toString());
if (type.equals(Date.class))
value = new BigDecimal(String.valueOf(value));
if (type.equals(Double.class))
value = new Double(String.valueOf(value));
if (type.equals(Float.class))
value = new Float(String.valueOf(value));
field.setAccessible(true);
field.set(instance,value);
field.setAccessible(false);
}
if (validData > 0) {
if (instance instanceof ExcelImportReader) {
Method reconstruct = clazz.getDeclaredMethod("reconstruct", Object.class);
Object invoke = reconstruct.invoke(instance,instance);
instance = (T) invoke;
}
if (instance != null)
exportList.add(instance);
}
}
return exportList;
}catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
return new ArrayList<T>();
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
private static boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath) || isExcelCsv(filePath))) {
//文件名不是excel格式
return false;
}
return true;
}
// @描述:是否是2003的excel,返回true是2003
private static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
// @描述:是否是2007的excel,返回true是2007
private static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
// @描述:是否是csv文件
private static boolean isExcelCsv(String filePath) {
return filePath.matches("^.+\\.(?i)(csv)$");
}
}
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author mars_q
* @date 2019/5/6 14:25
* create by 2012692013@qq.com
*/
public class ExcelExport {
private static String FILE_TYPE = ".xlsx";
public static <T> void export(HttpServletResponse response, List<T> data) {
if (data == null || data.isEmpty())
return;
//获取模板
Class<?> clazz = data.get(0).getClass();
//只支持模板型创建
if (!clazz.isAnnotationPresent(ExcelExporter.class))
return;
ExcelExporter exporter = clazz.getAnnotation(ExcelExporter.class);
String fileSimpleName = StringUtils.isNotBlank(exporter.value())?exporter.value():clazz.getSimpleName();
String fileName = fileSimpleName + FILE_TYPE;
String sheetName = fileSimpleName;
Field[] fields = clazz.getDeclaredFields();
// 排序列
List<ExportCellOrder> exportCellOrders = new ArrayList<>();
// 翻译节点
Map<String, Map<String,String>> transMap = new HashMap<>();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (!field.isAnnotationPresent(ExcelCellExporter.class))
continue;
ExcelCellExporter cellExporter = field.getAnnotation(ExcelCellExporter.class);
int order = cellExporter.order();
String cellName = cellExporter.value();
String property = field.getName();
exportCellOrders.add(new ExportCellOrder() {{
setOrder(order);
setCellName(cellName);
setProperty(property);
}});
// 加入解析器
ExcelTrans[] trans = cellExporter.trans();
if (trans != null && trans.length > 0) {
Map<String, String> propertyTransMap = new HashMap<>();
for (int j = 0; j < trans.length; j++) {
ExcelTrans tran = trans[j];
String bean = tran.bean();
String excel = tran.excel();
propertyTransMap.put(bean, excel);
}
transMap.put(field.getName(),propertyTransMap);
}
}
Collections.sort(exportCellOrders);
HSSFWorkbook workbook = create(response, sheetName, exportCellOrders, data, clazz,transMap);
try {
buildExcelFile(fileName, workbook);
buildExcelDocument(fileName,workbook,response);
} catch (Exception e) {
e.printStackTrace();
}
}
private static<T> HSSFWorkbook create(HttpServletResponse response,
String sheetName,
List<ExportCellOrder> cells,
List<T> data,
Class<?> clazz,
Map<String, Map<String,String>> transMap) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
//设置为居中加粗
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd HH:mm:ss"));
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
HSSFCell cell;
for (int i = 0; i < cells.size(); i++) {
ExportCellOrder cellOrder = cells.get(i);
// 余两个字符使居中列头不挤压
sheet.setColumnWidth(i, (cellOrder.getCellName().length() + 2) * 512);
cell = row.createCell(i);
cell.setCellValue(cellOrder.getCellName());
cell.setCellStyle(style);
}
for (int i = 0; i < data.size(); i++) {
HSSFRow dataRow = sheet.createRow(i + 1);
T t = data.get(i);
for (int j = 0; j < cells.size(); j++) {
ExportCellOrder c = cells.get(j);
Field field = null;
try {
String property = c.getProperty();
field = clazz.getDeclaredField(property);
Class<?> type = field.getType();
field.setAccessible(true);
Object o = field.get(t);
field.setAccessible(false);
HSSFCell rowCell = dataRow.createCell(j);
if (!type.equals(Date.class)) {
Map<String, String> valueTransMap = transMap.get(field.getName());
if (valueTransMap != null && valueTransMap.get(o) != null)
o = valueTransMap.get(o);
rowCell.setCellValue(o == null ? null : o.toString());
} else {
rowCell.setCellValue(o == null ? null : new SimpleDateFormat().format(((Date) o)));
}
} catch (Exception e) {
if (field != null)
field.setAccessible(false);
e.printStackTrace();
}
}
}
return workbook;
}
//生成excel文件
private static void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
FileOutputStream fos = new FileOutputStream(filename);
workbook.write(fos);
fos.flush();
fos.close();
}
//浏览器下载excel
private static void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
private static class ExportCellOrder implements Comparable<ExportCellOrder>{
private int order;
private String property;
private String cellName;
public ExportCellOrder() {
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public String getProperty() {
return property;
}
public void setProperty(String property) {
this.property = property;
}
public String getCellName() {
return cellName;
}
public void setCellName(String cellName) {
this.cellName = cellName;
}
@Override
public int compareTo(ExportCellOrder o) {
int cop = order - o.getOrder();
if (cop != 0)
return cop;
else
return property.compareTo(o.property);
}
@Override
public String toString() {
return "ExportCellOrder{" +
"order=" + order +
", property='" + property + '\'' +
", cellName='" + cellName + '\'' +
'}';
}
}
}
5、简单使用示例(导入和注解内其他参数可自行查看)
@ExcelExporter("金币导出表")
public class Point{
@ExcelCellExporter("金币数量")
private Integer num;
@ExcelCellExporter(value="金币种类",required=false,trans = {
@ExcelTrans(bean = "0",excel = "普通金币"),
@ExcelTrans(bean = "1",excel = "高级金币"),
@ExcelTrans(bean = "2",excel = "钻石金币"),
})
private Integer type;
@GetMapping("/export")
@ApiOperation("导出")
public void export(HttpServletResponse response,@ApiIgnore PointDto pointDto) {
List<Point> listByLimit = pointService.findListByLimit(Point.class, new ArrayList<String>() {{
if (pointDto.getState() != null)
add("state = '" + pointDto.getState() + "'");
}}, CodeEnum.DESC.msgOf());
ExcelExport.export(response,listByLimit);
}
}
@ExcelImporter("金币")
//@ExcelImporter(sheetName="金币")
//@ExcelImporter(sheetName="金币",titleRow=2,startRow=10)
public class Point{
@ExcelCellImporter("金币数量")
private Integer num;
@ExcelCellImporter(value="金币种类",required=false,trans = {
@ExcelTrans(bean = "0",excel = "普通金币"),
@ExcelTrans(bean = "1",excel = "高级金币"),
@ExcelTrans(bean = "2",excel = "钻石金币"),
})
private Integer type;
@PostMapping("/import")
@ApiOperation("导入")
public void export(HttpServletRequest request,MultipartFile multipartFile) {
List<Point> excelInfo = ExcelImport.getExcelInfo(multipartFile, Point.class);
}
}
以上,如需要自行CV一下, 路过点个赞谢谢,写的不是细,各位大佬见谅
2020-08-13
新增接口:ExcelImportReader
解释:当实现该接口内reconstruct方法,在调用excel读取工具进行读取时,会自动调用此方法,以减少一次数据取出时的再处理操作。
/**
* EXCEL读取数据处理接口
* @author mars_q
* @Description
* @e-mail: 2012692013@qq.com
* @date 2020/8/13 001314:23
*/
public interface ExcelImportReader {
/**
* 实现此方法以在读取过程中对instance进行数据处理
* 如果实体类内进行了autowired,那么请从spring上下文中获取
* @param o
* @return
*/
Object reconstruct(Object o);
}
另外,如果你在上面方法内调用了spring注入的bean,那么建议用以下工具获取bean,因为instance是被new声明的,无法注入对应bean
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
/**
* @author mars_q
* @Description
* @e-mail: 2012692013@qq.com
* @date 2020/8/13 001315:16
*/
@Component
public class SpringUtils implements ApplicationContextAware {
private static ApplicationContext applicationContext = null;
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if (SpringUtils.applicationContext == null) {
SpringUtils.applicationContext = applicationContext;
}
}
/**
* 获取对应Bean
*/
public static Object getBean(String name) {
return getApplicationContext().getBean(name);
}
}