1、背景
项目中有使用easypoi,处理常规excel问题,但是现在有个需求,需要动态生成导出的报表字段。同时,根据导入的excel,增加数据信息。(有可能会出现,导入的报表是几天前下载的,不会最新的数据库字段。),所以,只能使用poi导入导出,项目添加poi和easypoi。
2、技术:
2.1、前:
- 页面参考
<html lang="zh_CN" xmlns:th="http://www.thymeleaf.org"
xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<meta charset="utf-8">
<head th:include="include::header"></head>
<title>导入</title>
<style type="text/css">
.message .files {
position: absolute;
left: -1000px;
top: 52px;
heigth: 26px;
cursor: pointer;
filter: Alpha(opacity = 0);
-moz-opacity: 0;
opacity: 0;
}
</style>
</head>
<body>
<form id="signupForm" method="post" enctype="multipart/form-data">
<input name="publishTaskId" id="publishTaskId" th:value="${publishTaskId}" hidden>
<div id="dpLTE" class="container-fluid tc-box">
<table class="form" id="form" style="table-layout: fixed;">
<tr>
<td colspan="2">
<a id="zhCna"
href='/modelExcle/fieldTemplate.xlsx'>
<button type="button" class="btn btn-default"
style="margin-bottom: 10px">
<i class="fa fa-download"></i> 下载导入模板
</button>
</a>
<div class="alert alert-warning">提示:请先下载批量导入模板-excel文件,按格式填写后上传提交,方可导入;
</div>
</td>
</tr>
<tr>
<td><input type="text" id="txt" name="txt"
class="input form-control" value="文件域" disabled="disabled" /></td>
<td class="message">
<input type="button"
onMouseMove="f.style.pixelLeft=event.x-60;f.style.pixelTop=this.offsetTop;"
value="选择文件" size="30" onClick="f.click()" class="btn btn-orange"
style="margin-left: 10px">
<input type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" name="SensitiveExcle" id="f"
onChange="txt.value=this.value" style="height: 26px;" class="files" size="1" hidefocus>
</td>
</tr>
</table>
</div>
<div class="form-group">
<div class="col-sm-6 col-sm-offset-5">
<button type="submit" class="btn btn-primary">提交</button>
</div>
</div>
</form>
<div th:include="include::footer"></div>
<script src="/js/appjs/sys/auditSecPage/import.js"></script>
</body>
</html>
js中调用后台方法接收EXCEL文件流
function save() {
var formData = new FormData($('#signupForm')[0]);
$.ajax({
url : "/sys/audit/importdata",
type: 'POST',
data: formData,
async: true,
cache: false,
contentType: false,
processData: false,
error : function(request) {
parent.layer.alert("网络超时");
},
success : function(data) {
if (data.code == 0) {
parent.layer.msg("操作成功");
parent.reLoad();
var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
} else {
parent.layer.alert(data.msg)
}
}
});
}
2.2、后
- pom依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<packaging>war</packaging>
<groupId>com.yuneasy</groupId>
<artifactId>mpccApi</artifactId>
<version>3.0.0-SNAPSHOT</version>
<name>mpccApi</name>
<description>Yuneasy's MpccApi Project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- <dependency> -->
<!-- <groupId>org.springframework.boot</groupId> -->
<!-- <artifactId>spring-boot-starter-amqp</artifactId> -->
<!-- </dependency> -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-quartz</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- <dependency> -->
<!-- <groupId>org.springframework.amqp</groupId> -->
<!-- <artifactId>spring-rabbit-test</artifactId> -->
<!-- <scope>test</scope> -->
<!-- </dependency> -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-test</artifactId>
<scope>test</scope>
</dependency>
<!--mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!--fast_json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--<dependency> -->
<!--<groupId>com.github.pagehelper</groupId> -->
<!--<artifactId>pagehelper</artifactId> -->
<!--<version>5.1.11</version> -->
<!--</dependency> -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-jwt</artifactId>
<version>1.0.9.RELEASE</version>
</dependency>
<dependency>
<groupId>io.jsonwebtoken</groupId>
<artifactId>jjwt</artifactId>
<version>0.9.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!--导入导出 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
<!--解析Excel -->
<!-- 文件上传组件 -->
<!-- https://mvnrepository.com/artifact/commons-net/commons-net -->
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
</dependencies>
<build>
<finalName>baba</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- 返回结果
import java.util.HashMap;
import java.util.Map;
public class R extends HashMap<String, Object> {
private static final long serialVersionUID = 1L;
public R() {
put("code", 0);
put("msg", "操作成功");
}
public static R error() {
return error(1, "操作失败");
}
public static R error(String msg) {
return error(500, msg);
}
public static R error(int code, String msg) {
R r = new R();
r.put("code", code);
r.put("msg", msg);
return r;
}
public static R ok(String msg) {
R r = new R();
r.put("msg", msg);
return r;
}
public static R ok(Map<String, Object> map) {
R r = new R();
r.putAll(map);
return r;
}
public static R ok() {
return new R();
}
@Override
public R put(String key, Object value) {
super.put(key, value);
return this;
}
}
- Excel导入工具类
package com.yuneasy.mpcc.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.date.DateUtil;
/**
* 对数据导入导出 导入导出模板
*/
@Component
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
/* 导出客户,因为包含自定义字段,所以对象不固定 */
public static Map<String, String> exportCustomerData(List<Map<String, Object>> fieldMap,
List<Map<String, Object>> customerList, HttpServletRequest request, HttpServletResponse response) {
long start = System.currentTimeMillis();
Map<String, String> resultMap = new HashMap<>();
List<ExcelExportEntity> entityList = new ArrayList<>();
try {
// 构造导出excel表头
// 将手机号码放在第一列
entityList.add(new ExcelExportEntity("手机号码", "telephoneOne"));
for (Map<String, Object> map : fieldMap) {
if ("telephoneOne".equals(map.get("custom_col_name"))) {
continue;
}
ExcelExportEntity entity = new ExcelExportEntity();
entity.setName(String.valueOf(map.get("custom_name")));
entity.setKey(map.get("custom_col_name"));
entityList.add(entity);
}
ExportParams params = new ExportParams();
// 设置导出类型为07excel
params.setType(ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(params, entityList, customerList);
String fileName = new String(("导出数据" + DateUtil.now() + ".xlsx").getBytes("GBK"), "ISO_8859_1");
response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
response.setCharacterEncoding("UTF-8");
// response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("content-Type", "application/json");
workbook.write(response.getOutputStream());
resultMap.put("code", "000000");
resultMap.put("msg", "导出成功");
long end = System.currentTimeMillis();
LOGGER.info("-----------------------------------------------------");
LOGGER.info("导出用时::{}", (end - start));
} catch (Throwable t) {
resultMap.put("code", "999999");
resultMap.put("msg", "导出失败");
LOGGER.error("导出客户失败,原因:{}", t.getMessage());
}
return resultMap;
}
/**
* 创建WorkBook对象
*
* @param filePath
* @return
* @throws IOException
*/
public static final Workbook createWorkbook(String filePath) throws IOException {
if (StringUtils.isBlank(filePath)) {
throw new IllegalArgumentException("文件地址为空。你么跌");
}
File file = new File(filePath);
if (!file.exists()) {
throw new FileNotFoundException("文件地址不存在。你么跌");
}
if (filePath.trim().toLowerCase().endsWith("xls")) {
return new XSSFWorkbook(new FileInputStream(filePath));
} else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook(new FileInputStream(filePath));
} else {
throw new IllegalArgumentException("文件后缀名错误,2胡");
}
}
/**
* 创建WorkBook对象
*
* @return
* @throws IOException
*/
public static final Workbook createWorkbook(InputStream inputStream, String fileName) throws IOException {
if (StringUtils.isBlank(fileName)) {
throw new IllegalArgumentException("文件地址为空。你么跌");
}
Workbook workbook = null;
try {
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
throw new IllegalArgumentException("文件后缀名错误,2胡");
}
} catch (Exception e) {
throw new IllegalArgumentException("文件后缀名错误,2胡");
}
return workbook;
}
/**
* 获取Sheet页面(按名称)
*
* @param wb
* @param sheetName
* @return
*/
public static final Sheet getSheet(Workbook wb, String sheetName) {
return wb.getSheet(sheetName);
}
/**
* 获取Sheet页面(按页标)
*
* @param wb
* @param index
* @return
*/
public static final Sheet getSheet(Workbook wb, int index) {
return wb.getSheetAt(index);
}
/**
* 获取Sheet页内容
*
* @param sheet
* @return
*/
public static final List<Object[]> listFromSheet(Sheet sheet) {
List<Object[]> list = new ArrayList<Object[]>();
for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null || row.getPhysicalNumberOfCells() == 0)
continue;
Object[] cells = new Object[row.getLastCellNum()];
for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell == null)
continue;
// 判断是否为日期类型
// if (HSSFDateUtil.isCellDateFormatted(cell)) {
// // 用于转化为日期格式
// Date d = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// cells[c] = formater.format(d);
// } else {
// cells[c] = getValueFromCell(cell);
// }
// cells[c] = getValueFromCell(cell);
cell.setCellType(CellType.STRING);
cells[c] = cell.getStringCellValue();
}
list.add(cells);
}
return list;
}
/**
* 获取单元格内信息
*
* @param cell
* @return
*/
public static final Object getValueFromCell(Cell cell) {
if (cell == null) {
System.out.println("Cell is null !!!");
return null;
}
Object result = null;
if (cell != null) {
// 单元格类型:Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
CellType cellType = cell.getCellType();
switch (cellType) {
case STRING:
result = ((HSSFCell) cell).getStringCellValue();
break;
case FORMULA:
DecimalFormat df = new DecimalFormat("###.####");
result = df.format(((HSSFCell) cell).getNumericCellValue());
break;
case NUMERIC:
result = ((HSSFCell) cell).getNumericCellValue();
break;
case BOOLEAN:
result = ((HSSFCell) cell).getBooleanCellValue();
break;
case BLANK:
result = null;
break;
case ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
return result;
}
/**
* 根据Sheet页导入Excel信息
*
* @param filePath 文件路径
* @param sheetIndex Sheet页下标
* @param startRow 开始列 :默认第一列
* @param startLine 开始行 :默认第一行
* @throws Exception
*/
public static final List<Object[]> importExcelBySheetIndex(String filePath, int sheetIndex, int startRow,
int startLine) throws Exception {
List<Object[]> resultList = null;
// 创建WorkBook对象
Workbook wb = createWorkbook(filePath);
// 获取Sheet
Sheet sheet = ExcelUtil.getSheet(wb, sheetIndex);
// 判断Sheet是否为空
if (sheet != null) {
// 遍历Sheet
List<Object[]> list = ExcelUtil.listFromSheet(sheet);
if (list != null && list.size() > 0) {
resultList = new ArrayList<Object[]>();
if (startLine <= list.size()) {
for (int i = startLine; i < list.size(); i++) {
int nullCount = 0;
Object[] rows = list.get(i);
if (rows != null && rows.length > 0) {
List<Object> resultObjects = new ArrayList<Object>();
for (int n = startRow; n < rows.length; n++) {
if (IsNullUtils.isEmpty(rows[n])) {
nullCount++;
}
resultObjects.add(rows[n]);
}
// 判断空的单元格个数
if (nullCount >= rows.length) {
break;
} else {
resultList.add(resultObjects.toArray());
}
}
}
}
}
}
return resultList;
}
}
- 解决POI中DateUtil.isCellDateFormatted(Cell cell)不能判断中文日期的问题:
根据客户提供的Excel,在导入日期数据时,发现获得的值是一串数字。客户设置的日期格式为yyyyy年MM月,当把日期格式设置为yyyy-MM时,就能够正确读取。我们用得是POI3.6,方法:org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(Cell cell)。调用时总是返回false,经跟踪源码发现只要含有中文时间单位就不行(例如含有“年”或“月”等时间单位),故修改,增加了对中文时间单位的过滤,经测试判断正确。
方法:DateUtil.isADateFormat(int formatIndex, String formatString)
增加代码内容:
fs = fs.replaceAll("[\"|\']","").replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");
附件为修改之后的可执行代码!
自己参考:
- 版本 不同,getValueFromCell()上面的方法可能会不存在或者过期(poi-4.1.0.jar是我上面的)
(简单方法:
)
3.9版本(会和我的easypoi版本冲突):
<!-- 文件上传组件 -->
<!-- https://mvnrepository.com/artifact/commons-net/commons-net -->
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>/**
* 获取单元格内信息
*
* @param cell
* @return
*/
public static final Object getValueFromCell(Cell cell) {
if (cell == null) {
System.out.println("Cell is null !!!");
return null;
}
Object result = null;
if (cell instanceof HSSFCell) {
if (cell != null) {
// 单元格类型:Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = ((HSSFCell) cell).getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = ((HSSFCell) cell).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("###.####");
result = df.format(((HSSFCell) cell).getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = ((HSSFCell) cell).getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = ((HSSFCell) cell).getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
} else if (cell instanceof XSSFCell) {
if (cell != null) {
// 单元格类型:Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = ((XSSFCell) cell).getCellType();
switch (cellType) {
case XSSFCell.CELL_TYPE_STRING:
result = ((XSSFCell) cell).getRichStringCellValue().getString();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("###.####");
result = df.format(((XSSFCell) cell).getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:
result = ((XSSFCell) cell).getNumericCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
result = ((XSSFCell) cell).getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case XSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
}
return result;
}
解决jar冲突的:
- 判空方法工具类
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* 判空方法工具类
*
*/
public class IsNullUtils {
/**
* 对象是否为空
* @param o String,List,Map,Object[],int[],long[]
* @return
*/
@SuppressWarnings("rawtypes")
public static boolean isEmpty(Object o) {
if (o == null) {
return true;
}
if (o instanceof String) {
if (o.toString().trim().equals("")) {
return true;
}
if (o.equals("null") || o.equals("NULL")) {
return true;
}
} else if (o instanceof List) {
if (((List) o).size() == 0) {
return true;
}
} else if (o instanceof Map) {
if (((Map) o).size() == 0) {
return true;
}
} else if (o instanceof Set) {
if (((Set) o).size() == 0) {
return true;
}
} else if (o instanceof Object[]) {
if (((Object[]) o).length == 0) {
return true;
}
} else if (o instanceof int[]) {
if (((int[]) o).length == 0) {
return true;
}
} else if (o instanceof long[]) {
if (((long[]) o).length == 0) {
return true;
}
}
return false;
}
}
- 文件上传方法
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
/**
* 文件上传
*/
public class UploadFile {
/**
* 文件上传方法
*/
public static boolean fileUpLoad(MultipartFile[] files, HttpServletRequest request, String path)
throws IOException {
if (files != null && files.length > 0) {
for (int i = 0; i < files.length; i++) {
MultipartFile file = files[i];
// 保存文件
return saveFile(request, file, path);
}
}
return false;
}
/**
* 保存上传文件
*
* @param request
* @param file
* @return
*/
public static boolean saveFile(HttpServletRequest request, MultipartFile file, String path) {
if (!file.isEmpty()) {
try {
File saveDir = new File(path);
if (!saveDir.getParentFile().exists())
saveDir.getParentFile().mkdirs();
// 转存文件
file.transferTo(saveDir);
return true;
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
}
}
- 控制层接收文件流
/*
* 批量导入数据
* */
@ResponseBody
@PostMapping("/importdata")
R importdata(@RequestParam("publishTaskId") String publishTaskId,@RequestParam("SensitiveExcle") MultipartFile[] files,HttpServletRequest request)throws Exception {
return checkFieldInfoService.importData(publishTaskId,files,request);
}
- 接口
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
public interface CheckFieldInfoService {
R importData(String publishTaskId,MultipartFile[] files, HttpServletRequest request);
}
- 逻辑和调用:
package com.system.service.impl;
import com.system.dao.CheckFieldInfoMapper;
import com.system.domain.audit.CheckFieldInfo;
import com.system.service.CheckFieldInfoService;
import com.common.utils.R;
import com.system.utils.ImportExcelUtils;
import com.system.utils.IsNullUtils;
import com.system.utils.UploadFile;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.util.*;
@Service
public class CheckFieldInfoServiceImpl implements CheckFieldInfoService {
@Autowired
private CheckFieldInfoMapper checkFieldInfoMapper;
@Override
public R importData(String publishTaskId, MultipartFile[] files, HttpServletRequest request) {
int count = 0;
/*上传路径*/
String path = "/file/fileExcle/" + files[0].getOriginalFilename();
try {
boolean status = UploadFile.fileUpLoad(files, request, path);
if (!status) {
return R.error("文件上传失败!");
}
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Workbook workbook = null; //工作簿
Sheet sheet = null; //工作表
String[] headers = null; //表头信息
try {
workbook = ImportExcelUtils.createWorkbook(path);
} catch (Exception e) {
e.printStackTrace();
}
sheet = ImportExcelUtils.getSheet(workbook, 0);
List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
if (!IsNullUtils.isEmpty(oList)) {
headers = Arrays.asList(oList.get(0)).toArray(new String[0]);
if (!headers[0].replaceAll(" ", "").equals("ID")) {
return R.error("请选择正确模板导入!");
}
}
List<CheckFieldInfo> senList = new ArrayList<CheckFieldInfo>();
if (!IsNullUtils.isEmpty(oList.get(1))) {
for (int s = 1; s < oList.size(); s++) {
String[] rows = null;
rows = Arrays.asList(oList.get(s)).toArray(new String[0]);
R r = null;
CheckFieldInfo checkFieldInfo = new CheckFieldInfo();
checkFieldInfo.setFieldId(UUID.randomUUID().toString());
checkFieldInfo.setPublishTaskId(publishTaskId);
checkFieldInfo.setId(Integer.parseInt(rows[0]));
checkFieldInfo.setFieldname(rows[1]);
checkFieldInfo.setFieldtype(rows[2]);
checkFieldInfo.setLenPrecision(rows[3]);
checkFieldInfo.setLenScala(rows[4]);
checkFieldInfo.setFieldformat(rows[5]);
checkFieldInfo.setChecknull(rows[6]);
checkFieldInfo.setCheckrepeat(rows[7]);
checkFieldInfo.setCheckenum(rows[8]);
checkFieldInfo.setEnumvalue(rows[9]);
senList.add(checkFieldInfo);
}
if (senList.size() > 0) {
for (CheckFieldInfo c : senList) {
count = checkFieldInfoMapper.insertData(c);
if (count<=0){
R.error("批量导入异常");
}
}
}
}
return R.ok();
}
}