WEB项目导入Excel,话不多说,直接干货
1:添加maven相关依赖
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.2</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.6</version>
</dependency>
2:ExcelHead类:存放Excel表的表头信息,导入时要完全匹配表头信息才可导入
public class ExcelHead {
//读取表头
private String head;
//节点 这里要导入的Excel的表头必须和这儿完全匹配
private String point = "节点编号*,节点名称*,节点描述,节点类别,仓库编号*,仓库名称*,是否自动接收*,备注";
public String getHead() {
return head;
}
public void setHead(String head) {
if (StringUtil.isEmpty(this.head)) {
this.head = head.trim();
} else {
this.head = String.format("%s,%s", this.head, head.trim());
}
}
public String getPoint() {
return point;
}
public void setPoint(String point) {
this.point = point;
}
}
point-body.xml文件.这里存放的是表体信息
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet idx="0">
<!-- 表头开始至结束行 -->
<section startRow="0" endRow="0"/>
<!-- 开始循环读取文件数据,配置开始行,items映射的list var映射的bean varType 类路径
startRow:开始循环的行数
endRow-startRow:循环体的大小,0代表一行,依次论推
每循环一次,判断是否结束,不结束继续循环,直至结束
-->
<loop startRow="1" endRow="1" items="pointList" var="sku"
varType="com.eglsc.ams.domain.vo.PointVo">
<!-- 循环开始行 -->
<section startRow="1" endRow="1">
<!-- 节点编号 -->
<mapping row="1" col="0" nullAllowed="true">
sku.pointNo
</mapping>
<!-- 节点名称 -->
<mapping row="1" col="1" nullAllowed="true">
sku.pointName
</mapping>
<!-- 节点描述 -->
<mapping row="1" col="2" nullAllowed="true">
sku.pointDescription
</mapping>
<!-- 节点类别 -->
<mapping row="1" col="3" nullAllowed="true">
sku.pointCategory
</mapping>
<!-- 仓库编号 -->
<mapping row="1" col="4" nullAllowed="true">
sku.siteNo
</mapping>
<!-- 仓库名称 -->
<mapping row="1" col="5" nullAllowed="true">
sku.siteName
</mapping>
<!-- 是否自动接收 -->
<mapping row="1" col="6" nullAllowed="true">
sku.automaticFlag
</mapping>
<!-- 备注 -->
<mapping row="1" col="7" nullAllowed="true">
sku.remark
</mapping>
</section>
<!-- 结束条件配置 -->
<loopbreakcondition>
<rowcheck offset="0">
<!-- 空白结束不填 -->
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
point-head.xml文件.这里配置的是表头信息
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet idx="0">
<!-- 表头开始至结束行 -->
<section startRow="0" endRow="0">
<!-- 节点编号 -->
<mapping row="0" col="0" nullAllowed="true">
excelHead.head
</mapping>
<!-- 节点名称 -->
<mapping row="0" col="1" nullAllowed="true">
excelHead.head
</mapping>
<!-- 节点描述 -->
<mapping row="0" col="2" nullAllowed="true">
excelHead.head
</mapping>
<!-- 节点类别 -->
<mapping row="0" col="3" nullAllowed="true">
excelHead.head
</mapping>
<!-- 仓库编号 -->
<mapping row="0" col="4" nullAllowed="true">
excelHead.head
</mapping>
<!-- 仓库名称 -->
<mapping row="0" col="5" nullAllowed="true">
excelHead.head
</mapping>
<!-- 是否自动接收 -->
<mapping row="0" col="6" nullAllowed="true">
excelHead.head
</mapping>
<!--备注-->
<mapping row="0" col="7" nullAllowed="true">
excelHead.head
</mapping>
</section>
</worksheet>
</workbook>
导入代码:
2 @RequestMapping(value = "/import", method = RequestMethod.POST) @Response
3 @Transactional
4 public Response importExcel(@RequestParam(required = true) MultipartFile file, HttpServletRequest request) {
5 String fileName = file.getOriginalFilename().toLowerCase();
6 if(!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")){
7 throw new ApplicationException("请上传Excel文件");
8 }
9 //获取文件
10 CommonsMultipartFile commonsMultipartFile = (CommonsMultipartFile)file;
11 //获取xml文件头配置(我的xml文件是放在/template/import/point目录下的)
12 String xmlConfigPath = String.format("%s%s", request.getServletContext().getRealPath("/"), "/template/import/point/");
13 String xmlConfigName = String.format("%s%s", xmlConfigPath, "point-head.xml");
14 Map<String,Object> beans = new HashMap<>();
15 ExcelHead excelHead = new ExcelHead();
16 beans.put("excelHead",excelHead);
17 //解析文件头
18 this.parseExcelService.parseExcel(xmlConfigName,commonsMultipartFile,beans);
19 if(!excelHead.getPoint().equals(excelHead.getHead())){
20 throw new RuntimeException("模板错误");
21 }
22 //获取文件体信息
23 xmlConfigName = String.format("%s%s", xmlConfigPath, "point-body.xml");
24 List<PointVo> pointVoList = new ArrayList<>();
25 beans.clear();
26 beans.put("pointList",pointVoList);
27 //解析文件体
28 parseExcelService.parseExcel(xmlConfigName,commonsMultipartFile,beans);
29 this.pointService.importExcel(pointVoList);
30 return Response.success("导入成功");
31 }
Response类携带着操作结束后的信息,供前端人员调用
执行到this.pointService.importExcel(pointVoList)这段代码意味着要导入的Excel中的数据已经放到pointList中了,执行pointService中的方法把数据批量导入进数据库中就完成了.
解析Excel工具类:ParseExcelService
public class ParseExcelService {
/**
* 解析Excel
*
* @param xmlConfigName xml配置(字段和Excel单元格的映射)
* @param commonsMultipartFile 上传的文件
* @param beans 解析后的数据
*/
public void parseExcel(String xmlConfigName, CommonsMultipartFile commonsMultipartFile, Map<String, Object> beans) {
InputStream inputStream = null;
FileInputStream xmlConfig = null;
InputStream inputXML = null;
InputStream inputXLS = null;
try {
//上传的文件流
inputStream = commonsMultipartFile.getInputStream();
//xml配置的文件流
xmlConfig = new FileInputStream(xmlConfigName);
//执行解析
inputXML = new BufferedInputStream(xmlConfig);
XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
inputXLS = new BufferedInputStream(inputStream);
mainReader.read(inputXLS, beans);
} catch (XLSDataReadException e) {
logger.error("数据转换异常:", e);
throw new ApplicationException("数据转换异常");
} catch (InvalidFormatException e) {
logger.error("无效格式异常:", e);
throw new ApplicationException("无效格式异常");
} catch (FileNotFoundException e) {
logger.error("文件未找到:", e);
throw new ApplicationException("文件未找到");
} catch (SAXException e) {
logger.error("SAX解析异常:", e);
throw new ApplicationException("SAX解析异常");
} catch (IOException e) {
logger.error("IO流异常:", e);
throw new ApplicationException("IO流异常");
} finally {
try {
if (inputXLS != null)
inputXLS.close();
if (inputXML != null)
inputXML.close();
if (xmlConfig != null)
xmlConfig.close();
if (inputStream != null)
inputStream.close();
} catch (IOException e) {
logger.error("parse excel error", e);
}
}
}
}
原文链接: icanner