2023-01-13
892
原创
springboot-poi 封装注解式导入导出
springboot-poi 封装注解式导入导出
以下代码是基于poi的功能封装为注解控制导入导出，项目框架为springboot项目！
下面代码适用于简单的excel导入导出，以及对应列合计
文件目录结构：
├── entity -- excel的实体类包
| ├── ABean -- excel文件对应的数据类型
├── annotation -- 注解包
| ├── ColumnName -- 标记excel列字段的注解
| ├── ExcelConfig -- 标记excel全局样式的注解
├── util -- 工具包
| ├── ExcelUtil -- Excel导入导出工具类（核心）
├── service --
| ├── ExcelSerivce -- 调用demo
创建标记excel全局样式的注解：
package com.fnl.annotation;
import java.lang.annotation.*;
/**
* @Author 陌攻
* @Date 2022/3/14 15:57
* @Description 标记excel全局样式的注解
*/
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelConfig {
//从哪一行开始读取
int row() default 1;
}
创建标记excel列字段的注解
package com.fnl.annotation;
import java.lang.annotation.*;
/**
* @Author 陌攻
* @Date 2022/1/28 10:15
* @Description 标记excel列字段的注解
*/
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ColumnName {
//excel列名
String name() default "";
//列对应的列坐标
int index() default 0;
//是否将该列进行合计并显示合计结果在最下面
boolean sum() default false;
}
创建Excel对应的数据体，并做标记：
/**
* @Author 陌攻
* @Date 2022/1/28 11:16
* @Description excel文件对应的数据类型
*/
@ExcelConfig(row = 2)
public class ABean{
private String id;
private Integer num;
private Date time;
private Double dou;
@ColumnName(index = 0,name = "id列")
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@ColumnName(index = 1,name = "数字")
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
@ColumnName(index = 2,name = "时间")
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
@ColumnName(index = 3,name = "数值")
public Double getDou() {
return dou;
}
public void setDou(Double dou) {
this.dou = dou;
}
}
编写导入导出工具类：
/**
* @Author 陌攻
* @Date 2022/1/22 15:46
* @Description Excel导入导出工具类
*/
public class ExcelUtil {
/**
* author: 陌攻
* describe: 将Excel数据填充到list对象中
* @param clazz 要填充的bean对象
* @param is Excel文件流
* @param <A>
* @return
* @throws IntrospectionException
* @throws IllegalAccessException
* @throws InstantiationException
* @throws InvocationTargetException
*/
public static <A> List<A> getExcelData(Class<A> clazz,InputStream is) throws IntrospectionException, IllegalAccessException, InstantiationException, InvocationTargetException {
Workbook xssfWorkbook = WorkbookFactory.create(is);//支持2003 2005 2007 2010
Sheet sheet = xssfWorkbook.getSheetAt(0); //获取第一张sheet
int firstRowIndex = sheet.getFirstRowNum();//获取第一行标志0
int lastRowIndex = sheet.getLastRowNum();//获取最后一行（允许中间有空行）标志n-1;
int rowIndex=1;
ExcelConfig annotation = clazz.getAnnotation(ExcelConfig.class);
if(annotation!=null){
rowIndex=annotation.row();
}
List<A> list = new ArrayList<>();
for(int rownum = firstRowIndex+rowIndex;rownum<=lastRowIndex;rownum++) {
Row row = sheet.getRow(rownum);//当前数据行
if(row==null)
continue;
BeanInfo bi = Introspector.getBeanInfo(clazz);
PropertyDescriptor[] pds = bi.getPropertyDescriptors();//获取bean字段信息
A obj = clazz.newInstance();
for(PropertyDescriptor pd:pds){
ColumnName columnName = pd.getReadMethod().getAnnotation(ColumnName.class);
if(columnName!=null) {
Cell cell = row.getCell(columnName.index());
if(cell!=null) {
if (pd.getPropertyType().isAssignableFrom(String.class)) {
//避免字符串列中出现数字而导致的异常
if(cell.getCellType()== HSSFCell.CELL_TYPE_STRING) {
pd.getWriteMethod().invoke(obj, cell.getStringCellValue());
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
pd.getWriteMethod().invoke(obj, String.valueOf(cell.getNumericCellValue()));
}
}else if(pd.getPropertyType().isAssignableFrom(Double.class)){
pd.getWriteMethod().invoke(obj, cell.getNumericCellValue());
}else if(pd.getPropertyType().isAssignableFrom(Integer.class)){
pd.getWriteMethod().invoke(obj, new Double(cell.getNumericCellValue()).intValue());
}else if(pd.getPropertyType().isAssignableFrom(Float.class)){
pd.getWriteMethod().invoke(obj, new Float(cell.getNumericCellValue()));
}else if(pd.getPropertyType().isAssignableFrom(Date.class)){
pd.getWriteMethod().invoke(obj, cell.getDateCellValue());
}else if(pd.getPropertyType().isAssignableFrom(Boolean.class)){
pd.getWriteMethod().invoke(obj, cell.getBooleanCellValue());
}
}
}
}
list.add(obj);
}
//列合并(纵向) 读取
for (CellRangeAddress range:sheet.getMergedRegions()) {
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow() - rowIndex;
int lastRow = range.getLastRow() - rowIndex;
if(firstRow>=0) {
A firstObj = list.get(firstRow);
BeanInfo bi = Introspector.getBeanInfo(clazz);
PropertyDescriptor[] pds = bi.getPropertyDescriptors();
PropertyDescriptor pd = getPd(pds, firstColumn);
if (pd != null) {
for (int i = firstRow + 1; i <= lastRow; i++) {
A obj = list.get(i);
Object file = pd.getReadMethod().invoke(firstObj);
if (file != null)
pd.getWriteMethod().invoke(obj, file);
}
}
}
}
return list;
}
//获取字段
public static PropertyDescriptor getPd(PropertyDescriptor[] pds,int firstColumn){
for (PropertyDescriptor pd : pds) {
ColumnName columnName = pd.getReadMethod().getAnnotation(ColumnName.class);
if(columnName!=null) {
if (columnName.index() == firstColumn) {
return pd;
}
}
}
return null;
}
/**
* author: 陌攻
* describe: 将list填充到Excel中
* @param cls Excel映射的bean
* @param list 数据
* @param tableName Excel表名
* @param <A>
* @return
* @throws IntrospectionException
* @throws IllegalAccessException
* @throws InstantiationException
* @throws InvocationTargetException
*/
public <A>HSSFWorkbook setExcel(Class<A> cls,List<A> list,String tableName) throws IntrospectionException, IllegalAccessException, InstantiationException, InvocationTargetException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("信息");
sheet.setDefaultColumnWidth((short) 15);//设置默认列宽
//标题样式
HSSFCellStyle h_style = wb.createCellStyle();
h_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
h_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
h_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
h_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
h_style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
h_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
HSSFFont titleFont = (HSSFFont) wb.createFont(); // 创建字体对象
titleFont.setFontHeightInPoints((short) 20); // 设置字体大小
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
h_style.setFont(titleFont);
//标题合并单元格
CellRangeAddress region= new CellRangeAddress(0,0,0,10);
sheet.addMergedRegion(region);
//列名样式
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
//字体
HSSFFont font = (HSSFFont) wb.createFont();
font.setFontHeightInPoints((short) 12); // 设置字体大小
font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
//文本样式
HSSFCellStyle i_style = wb.createCellStyle();
i_style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
i_style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
i_style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
i_style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
i_style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
i_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
//标题设置
HSSFRow headrow = sheet.createRow(0);//第一行
HSSFCell headcell = headrow.createCell(0);
headcell.setCellValue(tableName);
headcell.setCellStyle(h_style);
BeanInfo biName = Introspector.getBeanInfo(cls);
PropertyDescriptor[] pdsName = biName.getPropertyDescriptors();//获取bean字段信息
Map<Integer,String> head = new HashMap<Integer,String>();
//读取所有列名
for (PropertyDescriptor p:pdsName) {
ColumnName annotation = p.getReadMethod().getAnnotation(ColumnName.class);
if(annotation!=null)
head.put(annotation.index(),annotation.name());
}
//列名设置
HSSFRow t_row = sheet.createRow(1); //第二行
for(Integer key:head.keySet()) {
HSSFCell cell = t_row.createCell(key); //表头单元格
HSSFRichTextString text = new HSSFRichTextString(head.get(key));
cell.setCellValue(text);
cell.setCellStyle(style);
}
HSSFRow row =null;
HSSFCell item = null;
Map<Integer,Double> map=new HashMap<Integer,Double>();
//往Excel里填充数据
for(int i=0;i<list.size();i++) {
A clazz = list.get(i);
row = sheet.createRow(i+2);
int index = 0;
for (PropertyDescriptor pd : pdsName) {
ColumnName colName = pd.getReadMethod().getAnnotation(ColumnName.class);
if(colName!=null) {
Object col = pd.getReadMethod().invoke(clazz);
item = row.createCell(colName.index());
if (col != null && StringHelper.isNotEmpty(col.toString())) {
item.setCellValue(col.toString());
if(colName.sum()){
map.put(colName.index(),map.getOrDefault(colName.index(),0D)+Double.valueOf(col.toString()));
}
} else {
item.setCellValue("-");
}
item.setCellStyle(i_style);
index++;
}
}
}
//判断是否需要合计行
if(map.size()>0) {
row = sheet.createRow(list.size()+2);//表名和列明占两行
//中文提示
item = row.createCell(0);
item.setCellValue("合计");
item.setCellStyle(i_style);
//实际数量累计和
for (Integer index : map.keySet()) {
item = row.createCell(index);
item.setCellValue(map.getOrDefault(index, 0D));
item.setCellStyle(i_style);
}
}
return wb;
}
}
调用方式：
/**
* @Author 陌攻
* @Date 2022/1/22 15:46
* @Description 调用demo
*/
public class ExcelUtil {
//读取Excel文件demo
@Override
public Result readExcel(String name, CommonsMultipartFile mfile,UserInfo userInfo) {
File file = ReadExcelUtils.getFile(name, mfile);
try {
InputStream is = new FileInputStream(file);
//读取Excel文件里的数据
List<ABean> list = ExcelSaveObj.getExcelData(ABean.class, is);
return Result.success(list);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return Result.failed("文件错误！");
}
//存入Excel文件demo
@Override
public HSSFWorkbook sampleHistory(SampleOperationRecordVO query) {
try {
//从数据库里查询ABean数据
List<ABean> list = dateDao.getList();
return setExcel(ABean.class,list,"表名");
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
throw new RuntimeException("导出异常");
}
}
java
SpringBoot
评论