java springboot poi 从controller 接收不同类型excel 文件处理
程序员文章站
2022-03-20 16:54:28
根据poi接收controller层的excel文件导入 可使用后缀名xls或xlsx格式的excel。 1.pom引入 org.apache.poi poi
根据poi接收controller层的excel文件导入
可使用后缀名xls或xlsx格式的excel。
1.pom引入
<!-- poi 操作excel --> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>3.17</version> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>3.17</version> </dependency>
2.excelimportutil 工具类创建
import com.guard.biz.common.util.excel.excelin; import org.apache.commons.beanutils.beanutilsbean; import org.apache.commons.lang3.stringutils; import org.apache.commons.lang3.reflect.fieldutils; import org.apache.poi.hssf.usermodel.hssfdateutil; import org.apache.poi.ss.usermodel.cell; 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.slf4j.logger; import org.slf4j.loggerfactory; import java.io.filenotfoundexception; import java.io.ioexception; import java.io.inputstream; import java.lang.reflect.field; import java.lang.reflect.invocationtargetexception; import java.math.bigdecimal; import java.util.arraylist; import java.util.hashmap; import java.util.list; import java.util.map; /** * @author wei * @time 2019/10/29 * @description excel 导入工具类 */ public class excelimportutil<t> { private static final logger log = loggerfactory.getlogger(excelimportutil.class); private static beanutilsbean beanutilsbean = new beanutilsbean(); static { beanutilsbean.getconvertutils().register(new org.apache.commons.beanutils.converters.dateconverter(null), java.util.date.class); } /** * 表头名字和对应所在第几列的下标,用于根据title取到对应的值 */ private final map<string, integer> title_to_index = new hashmap<>(); /** * 所有带有excelin注解的字段 */ private final list<field> fields = new arraylist<>(); /** * 统计表格的行和列数量用来遍历表格 */ private int firstcellnum = 0; private int lastcellnum = 0; private int firstrownum = 0; private int lastrownum = 0; private string sheetname; private sheet sheet; public list<t> read(inputstream in, class clazz) throws exception { gatherannotationfields(clazz); configsheet(in); configheader(); list rlist = null; try { rlist = readcontent(clazz); } catch (illegalaccessexception e) { throw new exception(e); } catch (instantiationexception e) { throw new exception(e); } catch (invocationtargetexception e) { throw new exception(e); } return rlist; } private list readcontent(class clazz) throws illegalaccessexception, instantiationexception, invocationtargetexception { object o = null; row row = null; list<object> rslist = new arraylist<>(); object value = null; for (int i = (firstrownum + 1); i <= lastrownum; i++) { o = clazz.newinstance(); row = sheet.getrow(i); cell cell = null; for (field field : fields) { //根据注解中的title,取到表格中该列所对应的的值 integer column = title_to_index.get(field.getannotation(excelin.class).title()); if (column == null) { continue; } cell = row.getcell(column); value = getcellvalue(cell); if (null != value && stringutils.isnotblank(value.tostring())) { beanutilsbean.setproperty(o, field.getname(), value); } } rslist.add(o); } return rslist; } private void configsheet(inputstream in) throws exception { // 根据文件类型来分别创建合适的workbook对象 try (workbook wb = workbookfactory.create(in)) { getsheetbyname(wb); } catch (filenotfoundexception e) { throw new exception(e); } catch (ioexception e) { throw new exception(e); } } /** * 根据sheet获取对应的行列值,和表头对应的列值映射 */ private void configheader() { this.firstrownum = sheet.getfirstrownum(); this.lastrownum = sheet.getlastrownum(); //第一行为表头,拿到表头对应的列值 row row = sheet.getrow(firstrownum); this.firstcellnum = row.getfirstcellnum(); this.lastcellnum = row.getlastcellnum(); for (int i = firstcellnum; i < lastcellnum; i++) { title_to_index.put(row.getcell(i).getstringcellvalue(), i); } } /** * 根据sheet名称获取sheet * * @param workbook * @return * @throws exception */ private void getsheetbyname(workbook workbook) throws exception { int sheetnumber = workbook.getnumberofsheets(); for (int i = 0; i < sheetnumber; i++) { string name = workbook.getsheetname(i); if (stringutils.equals(this.sheetname, name)) { this.sheet = workbook.getsheetat(i); return; } } throw new exception("excel中未找到名称为" + this.sheetname + "的sheet"); } /** * 根据自定义注解,获取所要导入表格的sheet名称和需要导入的字段名称 * * @param clazz * @throws exception */ private void gatherannotationfields(class clazz) throws exception { if (!clazz.isannotationpresent(excelin.class)) { throw new exception(clazz.getname() + "类上没有excelin注解"); } excelin excelin = (excelin) clazz.getannotation(excelin.class); this.sheetname = excelin.sheetname(); // 得到所有定义字段 field[] allfields = fieldutils.getallfields(clazz); // 得到所有field并存放到一个list中 for (field field : allfields) { if (field.isannotationpresent(excelin.class)) { fields.add(field); } } if (fields.isempty()) { throw new exception(clazz.getname() + "中没有excelin注解字段"); } } private object getcellvalue(cell cell) { if (cell == null) { return ""; } object obj = null; switch (cell.getcelltypeenum()) { case boolean: obj = cell.getbooleancellvalue(); break; case error: obj = cell.geterrorcellvalue(); break; case formula: try { obj = string.valueof(cell.getstringcellvalue()); } catch (illegalstateexception e) { obj = numerictobigdecimal(cell); } break; case numeric: obj = getnumericvalue(cell); break; case string: string value = string.valueof(cell.getstringcellvalue()); value = value.replace(" ", ""); value = value.replace("\n", ""); value = value.replace("\t", ""); obj = value; break; default: break; } return obj; } private object getnumericvalue(cell cell) { // 处理日期格式、时间格式 if (hssfdateutil.iscelldateformatted(cell)) { return cell.getdatecellvalue(); } else if (cell.getcellstyle().getdataformat() == 58) { // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58) double value = cell.getnumericcellvalue(); return org.apache.poi.ss.usermodel.dateutil.getjavadate(value); } else { return numerictobigdecimal(cell); } } private object numerictobigdecimal(cell cell) { string valueof = string.valueof(cell.getnumericcellvalue()); bigdecimal bd = new bigdecimal(valueof); return bd; } }
3.excelin注解
import java.lang.annotation.elementtype; import java.lang.annotation.retention; import java.lang.annotation.retentionpolicy; import java.lang.annotation.target; /** * @author lei * @time 2019/10/29 * @description */ @retention(value = retentionpolicy.runtime) @target(value = {elementtype.type, elementtype.field}) public @interface excelin { /** * 导入sheet名称 * * @return */ string sheetname() default ""; /** * 字段对应的表头名称 * * @return */ string title() default ""; }
4.创建excel中的对象
import lombok.data; import lombok.tostring; import java.util.date; /** * @author lei * @time 2019/10/29 * @description */ @tostring @data @excelin(sheetname = "用户") public class user { private string id; @excelin(title = "姓名") private string name; @excelin(title = "年龄") private integer age; @excelin(title = "出生日期") private date birthdate; }
5.controller层接收
@postmapping("/batch/excel") @apioperation(value = "根据excel文件批量导入") public responsevo batchadddevicebyexcelimport(multipartfile multipartfile) { return new responsevo(deviceservice.adddevicebyexcelimport(multipartfile)); }
6.service处理(此处仅打印)
public boolean adddevicebyexcelimport(multipartfile multipartfile) { file file = null; try { file = file.createtempfile("temp", null); } catch (ioexception e) { e.printstacktrace(); } try { multipartfile.transferto(file); } catch (ioexception e) { e.printstacktrace(); } file.deleteonexit(); inputstream inputstream = null; try { inputstream = new fileinputstream(file); } catch (filenotfoundexception e) { e.printstacktrace(); } excelimportutil<user> reader = new excelimportutil<>(); list<user> userlist = null; try { userlist = reader.read(inputstream, user.class); } catch (exception e) { log.error(e.getmessage()); throw new codeexception("51302", e.getmessage()); } userlist.stream().foreach(e -> log.info(e.tostring())); return true; }
7.测试
(1)两种文件类型的excel
(2)excel中格式如下,注意红色箭头所指的地方 对应user对象中的字段以及sheet名
(3)swagger测试
(4)成功打印
最后,欢迎留言交流吐槽。。。
上一篇: Eureka源码解析系列文章汇总