最近做的一個項(xiàng)目用到了Excel導(dǎo)入曾沈，我選擇了使用easypoi進(jìn)行Excel解析这嚣。
1.前期準(zhǔn)備
如果使用maven等項(xiàng)目管理工具，在配置文件pom.xml中塞俱，添加以下三個依賴:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
如果沒使用項(xiàng)目管理工具姐帚，在build path中導(dǎo)入三個jar包
image.png
2.業(yè)務(wù)流程
客戶下載Excel模板----->客戶填寫Excel上傳----->解析Excel----->將數(shù)據(jù)持久化存儲（錄入數(shù)據(jù)庫）----->查詢操作，將數(shù)據(jù)顯示在數(shù)據(jù)表格
Excel模板:
image.png
填寫后的Excel:
image.png
3.效果預(yù)覽
layer.gif
可以看見障涯，我們在Excel中錄入的名稱為hello和world的數(shù)據(jù)已經(jīng)錄入數(shù)據(jù)庫了罐旗。
4.代碼實(shí)現(xiàn)
前端使用layui(別的前端UI框架也可以 使用文件上傳功能即可):
<div class="layui-btn-container">
<a class="layui-btn btn-add btn-default" id="btn-excel">選擇Excel文件</a>
        
<a class="layui-btn btn-add btn-default" id="btn-excel-sure">上傳導(dǎo)入</a>
</div>
layui.use(['upload'], function() {
var $ = layui.jquery;
var upload = layui.upload;
}
layui.use(['element', 'form', 'table', 'layer', 'vip_table', 'laydate','upload'], function() {
var form = layui.form,
table = layui.table,
layer = layui.layer,
vipTable = layui.vip_table,
element = layui.element,
$ = layui.jquery;
var laydate = layui.laydate;
var upload = layui.upload;
//Excel導(dǎo)入
upload.render({
elem: '#btn-excel'
,url: 'layer/excelparser?fileName=1'
,auto: false
//,multiple: true
,bindAction: '#btn-excel-sure'
,size: 2048 //最大允許上傳的文件大小 2M
,accept: 'file' //允許上傳的文件類型
,exts:'xlsx'//只上傳pdf文檔
,done: function(res){
console.log(res)
if(res.code == 1){//成功的回調(diào)
//do something （比如將res返回的圖片鏈接保存到表單的隱藏域）
// $('#set-add-put input[name="fileName"]').val(res.data.fileName);
layer.msg(res.msg, {
icon: 6
});
location.reload();
}else if(res.code==2){
layer.msg(res.msg, {
icon: 5
});
}
}
});
});
這里使用layui實(shí)現(xiàn)上傳下載的組件，并對返回結(jié)果進(jìn)行回調(diào)唯蝶，狀態(tài)碼1成功尤莺，2失敗
后臺:
Controller層:
/**
* 處理Excel解析的方法
* @param file 前臺上傳的文件對象
* @return
*/
@RequestMapping(value = "Index/layer/excelparser")
@ResponseBody
public Map<String,Object> Excel(HttpServletRequest request,@RequestParam("file")MultipartFile file)throws Exception
{
Map<String, Object> dataMap = new HashMap<String, Object>();
String fileName1 = request.getParameter("fileName");// 設(shè)置文件名，根據(jù)業(yè)務(wù)需要替換成要下載的文件名
String fileName;
try {
//上傳目錄地址
String uploadDir = request.getSession().getServletContext().getRealPath("/") +"upload/";
uploadDir=uploadDir.substring(0,uploadDir.length()-1);
uploadDir=uploadDir+"\\";//下載目錄
String realPath=uploadDir+fileName1;//
File dir = new File(realPath);
if(!dir.exists())
{
dir.mkdir();
}
//調(diào)用上傳方法
fileName=upload.executeUpload1(uploadDir, file,fileName1);
uploadDir=uploadDir.substring(0,uploadDir.length()-1);
dataMap.put("fileName",fileName);
dataMap.put("dir",uploadDir);
}catch (Exception e)
{
//打印錯誤堆棧信息
e.printStackTrace();
return api.returnJson(2,"解析失敗",dataMap);
}
ExcelParser(fileName);
return api.returnJson(1,"解析成功",dataMap);
}
public void ExcelParser(String fileName)throws Exception{
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = new Date().getTime();
List<Layer> list=new ArrayList<>();
list = upload.importExcel("C:/Users/sl/Desktop/layer/layer/src/main/webapp/upload/"+fileName, 1, 1, Layer.class);
System.out.println(new Date().getTime() - start);
System.out.println(list.size());
System.out.println(list);
int testId=1;
int isInsert=0;
for (int i = 0; i <list.size() ; i++) {
Layer layer=new Layer();
UUID uuid=UUID.randomUUID();
String layerId=uuid.toString();
layer.setLayerId(layerId);
layer.setLayerName(list.get(i).getLayerName());
layer.setDescription(list.get(i).getDescription());
layer.setRecordTime(list.get(i).getRecordTime());
layer.setReleaseTime(list.get(i).getReleaseTime());
int is_add=layerService.InsertLayer(layer);
System.out.println(is_add);
}
}
用到的工具類:Upload.java
package com.example.sl.layer.util;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.util.List;
import java.util.UUID;
//上傳
public class Upload {
public String executeUpload1(String uploadDir,MultipartFile file,String fileName) throws Exception
{
//文件后綴名
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
//上傳文件名
String filename = fileName + suffix;
//服務(wù)器端保存的文件對象
File serverFile = new File(uploadDir + filename);
//將上傳的文件寫入到服務(wù)器端文件內(nèi)
file.transferTo(serverFile);
return filename;
}
public <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (Exception e) {
e.printStackTrace();
}
return list;
}
public <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
if (file == null){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
}
model層實(shí)體類:(這里使用easypoi的注解解析生棍，對時間等特殊格式也加上解析即可)
package com.example.sl.layer.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.util.Date;
@ExcelTarget("Layer")
public class Layer {
private String layerId;
@Excel(name = "法規(guī)名稱", isImportField = "true_st")
private String layerName;
@Excel(name = "法規(guī)描述", isImportField = "true_st")
private String description;
@Excel(name = "法規(guī)發(fā)布日期",importFormat = "yyyy-MM-dd")
private Date releaseTime;
@Excel(name = "法規(guī)上傳日期",importFormat = "yyyy-MM-dd")
private Date recordTime;
private String fileName;
public String getLayerId() {
return layerId;
}
public void setLayerId(String layerId) {
this.layerId = layerId == null ? null : layerId.trim();
}
public String getLayerName() {
return layerName;
}
public void setLayerName(String layerName) {
this.layerName = layerName == null ? null : layerName.trim();
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description == null ? null : description.trim();
}
public Date getReleaseTime() {
return releaseTime;
}
public void setReleaseTime(Date releaseTime) {
this.releaseTime = releaseTime;
}
public Date getRecordTime() {
return recordTime;
}
public void setRecordTime(Date recordTime) {
this.recordTime = recordTime;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName == null ? null : fileName.trim();
}
}
dao層:
package com.example.sl.layer.dao;
import com.example.sl.layer.model.Layer;
import org.apache.ibatis.annotations.Param;
import java.util.Date;
import java.util.List;
public interface LayerMapper {
int insert(Layer record);
}
Service層:
LayerService:
package com.example.sl.layer.service;
import com.example.sl.layer.model.Layer;
import java.util.Date;
import java.util.List;
public interface LayerService {
public int InsertLayer(Layer layer);
}
LayerServiceImpl:
package com.example.sl.layer.service;
import com.example.sl.layer.dao.LayerMapper;
import com.example.sl.layer.model.Layer;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
@Service("layerService")
@Transactional
public class LayerServiceImpl implements LayerService{
@Resource
private LayerMapper layerMapper;
@Override
public int InsertLayer(Layer layer) {
return layerMapper.insert(layer);
}
}
5.最終實(shí)現(xiàn)
layer.gif
image.png
image.png
解析成功颤霎，并且入庫。
喜歡就給顆小????吧。
