java中Excel导入,下载模板,附带前端展示
后台下载模板代码
@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
@ResponseBody
public void downloadExcel(HttpServletRequest request,HttpServletResponse response,String type) {
try {
String fileName = "";
if("solidWaste".equals(type)){
fileName = "模板名称.xlsx";
}else if("buildTrash".equals(type)){
fileName = "模板名称.xlsx";
}else if("lifeTrash".equals(type)){
fileName = "模板名称.xlsx";
}else if("recycLing".equals(type)){
fileName = "模板名称.xlsx";
}
ExcelUtils.downloadExcel(request,response,fileName);
} catch (Exception ex) {
logger.error("getApplicationTemplate :", ex);
}
}
vue前端调用方法
<el-button type="primary" @click="downloadExcel()">下载Excel模板</el-button>
downloadExcel () {
window.location.href = this.$http.adornUrl('接口名称?type=参数类型')
},
后台导入Excel controller层
@RequestMapping(value = "/importExcel",method = RequestMethod.POST)
public R importExcel(@RequestParam("file") MultipartFile file, HttpSession session) throws IOException {
InputStream inputStream = null;
R result = new R();
try {
int lastIndexOf = file.getOriginalFilename().lastIndexOf(".");
String suffix = file.getOriginalFilename().substring(lastIndexOf);
if(!suffix.equals(".xlsx") && !suffix.equals(".xls")){
result.put("code", Constant.SERVER_ERROR);
result.put("msg","上传文件格式错误,请检查重新上传!");
return result;
}
inputStream = file.getInputStream();
Workbook wb = WorkbookFactory.create(inputStream);
Long userId = getUserId();
Map<String,Object> res = dataSolidWasteService.importExcelData(wb,session,userId);
result.put("code",res.get("code"));
result.put("msg",res.get("msg"));
return result;
}catch (Exception e){
e.printStackTrace();
result.put("code",Constant.SERVER_ERROR);
result.put("msg","导入数据异常,请联系管理员!");
return result;
}
}
后台导入Excel 业务处理层
@Override
public Map<String,Object> importExcelData(Workbook wb, HttpSession session, Long userId){
List<Map<String,Object>> resList = new ArrayList<>();
Sheet sheet = wb.getSheetAt(0);
Map<String,Object> map = new HashMap<>();
List<DataSolidWasteEntity> addList = new ArrayList<>();
int index = ExcelUtils.getExcelRealRow(sheet);
if(index == 0){
map.put("code", Constant.EXCEL_CHECK_NULL);
map.put("msg","导入的记录行数不能为0,请确认后再操作!");
return map;
}
if(sheet.getRow(0).getCell(0) != null){
sheet.getRow(0).getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String name1 = sheet.getRow(0).getCell(0).getStringCellValue().replaceAll(" +","");
String name2 = sheet.getRow(0).getCell(1).getStringCellValue().replaceAll(" +","");
String name3 = sheet.getRow(0).getCell(2).getStringCellValue().replaceAll(" +","");
String name4 = sheet.getRow(0).getCell(3).getStringCellValue().replaceAll(" +","");
String name5 = sheet.getRow(0).getCell(4).getStringCellValue().replaceAll(" +","");
String name6 = sheet.getRow(0).getCell(5).getStringCellValue().replaceAll(" +","");
String name7 = sheet.getRow(0).getCell(6).getStringCellValue().replaceAll(" +","");
if(!"统计月份".equals(name1) || !"区县".equals(name2) || !"企业名称".equals(name3)
|| !"固废种类".equals(name4)
|| !"利用技术".equals(name5) || !"处置利用量".equals(name6)
|| !"主要产品及产量".equals(name7)
){
map.put("code",Constant.EXCEL_CHECK_NULL);
map.put("msg","表头列名错误!");
return map;
}
}
for(int i = 1;i<index+1;i++){
if(sheet.getRow(i) != null){
DataSolidWasteEntity dre = new DataSolidWasteEntity();
if(sheet.getRow(i).getCell(0) != null){
sheet.getRow(i).getCell(0).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(0).getStringCellValue().trim())){
String statisticsMonth = sheet.getRow(i).getCell(0).getStringCellValue().trim();
dre.setStatisticsMonth(statisticsMonth);
}
}
if(sheet.getRow(i).getCell(1) != null){
sheet.getRow(i).getCell(1).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(1).getStringCellValue().trim())){
String countyName = sheet.getRow(i).getCell(1).getStringCellValue().trim();
dre.setCountyName(countyName);
}
}
if(sheet.getRow(i).getCell(2) != null){
sheet.getRow(i).getCell(2).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(2).getStringCellValue().trim())){
String receiveCompany = sheet.getRow(i).getCell(2).getStringCellValue().trim();
dre.setReceiveCompany(receiveCompany);
}
}
if(sheet.getRow(i).getCell(3) != null){
sheet.getRow(i).getCell(3).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(3).getStringCellValue().trim())){
String trashType = sheet.getRow(i).getCell(3).getStringCellValue().trim();
dre.setTrashType(trashType);
}
}
if(sheet.getRow(i).getCell(4) != null){
sheet.getRow(i).getCell(4).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(4).getStringCellValue().trim())){
String usedUtilize = sheet.getRow(i).getCell(4).getStringCellValue().trim();
dre.setUsedUtilize(usedUtilize);
}
}
if(sheet.getRow(i).getCell(5) != null){
sheet.getRow(i).getCell(5).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(5).getStringCellValue().trim())){
String usedUtilizeNum = sheet.getRow(i).getCell(5).getStringCellValue().trim();
dre.setUsedUtilizeNum(usedUtilizeNum);
}
}
if(sheet.getRow(i).getCell(6) != null){
sheet.getRow(i).getCell(6).setCellType(Cell.CELL_TYPE_STRING);
if(!"".equals(sheet.getRow(i).getCell(6).getStringCellValue().trim())){
String mainProductNum = sheet.getRow(i).getCell(6).getStringCellValue().trim();
dre.setMainProductNum(mainProductNum);
}
}
dre.setCreatedBy(userId);
dre.setCreatedTime(new Date());
dre.setUpdatedBy(userId);
dre.setUpdatedTime(new Date());
addList.add(dre);
}else {
continue;
}
}
if(addList.size() > 0){
try {
dataSolidWasteDao.insertBatch(addList);
map.put("code",Constant.TOKEN_CHECK_SUCCESS);
map.put("msg","导入成功!");
resList.add(map);
}catch (Exception e){
e.printStackTrace();
map.put("code",Constant.SERVER_ERROR);
map.put("msg","导入数据异常,请联系管理员!");
resList.add(map);
return map;
}
}
return map;
}
vue前端调用
<el-upload class="upload-demo import"
:action="后台接口"
accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
:show-file-list="false"
:before-upload="beforeAvatarUpload"
:on-progress="uploading"
name="file"
:http-request="importExcel"
multiple
>
<el-button type="success">导入</el-button>
</el-upload>