参看文档https://www.cnblogs.com/whj1986556646/p/6228657.html
本文中要用到的ExcelUtil , StringUtils , IoUtils ,卤猪,由于有个导入excel项目要做,就参看了一下
1 导入excel
1.1 思路
思路就是得到excel数据,拼接一条一条insert语句,执行批量操作就行,总之一句话能成功就行
1.2 Pom
<poi.version>3.14</poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency>
2 后台
2.1 工具类
ExcelUtil为调用类,试用范围,支持.xls和.xlsx文件,只能识别一个工作薄的东西
工具类得到效果
2.2 控制层
这里采用的springmvc的文件上传,提出撸主遇到的坑,得到上传组件和非上传组件
@Controller
@RequestMapping("/api/import")
public classImportExcelController {
@Autowired
private ImportExcelService importExcelService;
private static final Logger LOG = LoggerFactory.getLogger(ImportExcelController.class);
@RequestMapping(value = "/excel",method = RequestMethod.POST)
@ResponseBody
public StringimportExecl(HttpServletRequest request){
MultipartHttpServletRequestmultipartRequest= (MultipartHttpServletRequest) request;
//获得非上传组件
StringtableId= multipartRequest.getParameter("tableId");
StringtableName= multipartRequest.getParameter("tableName");
StringjqArea= multipartRequest.getParameter("jqArea");
//获得上传组件
List<MultipartFile>fileList= multipartRequest.getFiles("file");
MultipartFilemyfile= null;
myfile = fileList.get(0);
StringFileName= myfile.getOriginalFilename();
StringfileFolder= "C:/temp/";
StringpathNameTemp= fileFolder+ FileName;//临时完整目录文件
try {
//先把传过来的文件放在临时文件夹下,然后从文件夹中取出
InputStreaminputStream= myfile.getInputStream();
IoUtils.copyer(inputStream, pathNameTemp);
System.out.println("临时路径:" + pathNameTemp);
List<List<String>>excelDataList= ExcelUtil.importExecl(pathNameTemp);//将excel所有数据都以String形式存入
//上传前简单判断
Stringcode= importExcelService.getColNum(tableId, excelDataList);
if ("400".equals(code)) {
return "400";
}
if (excelDataList != null && excelDataList.size() > 0) {
importExcelService.importExecl(excelDataList, tableId, tableName, jqArea);
}
//删除临时文件
FilefileTemp= newFile(pathNameTemp);
if(fileTemp.exists()&&fileTemp.isFile()) {
fileTemp.delete();
}
}catch(Exception e) {
e.printStackTrace();
}
return "200";
}
}
2.3 业务层
本质就是拼接出一条一条insert的sql
@Service
public classImportExcelService {
@Autowired
private SqlUpdate sqlUpdate;
@Autowired
private SqlQuery sqlQuery;
public String getColNum(StringtableId,List<List<String>> excelDataList) {
Stringcode = "200";
Stringsql1 = "selectfield_cn_name,field_name,field_order,field_data_type,field_order frommeta_table_field where table_id = '"+ tableId
+"' ORDER BY field_order ";
//1.根据tableId 获得在meta_table_field 的字段名称,以及类型
List<Map<String,String>> listMap = sqlQuery.query(sql1, "");
//判断行数是否相等
if(excelDataList.get(0).size() != listMap.size()) {
code= "400";
}
//判断列名(选择前三个)
if(!excelDataList.get(0).get(0).equals(listMap.get(0).get("field_cn_name"))){
code= "400";
}
if(!excelDataList.get(0).get(1).equals(listMap.get(1).get("field_cn_name"))){
code= "400";
}
if(!excelDataList.get(0).get(2).equals(listMap.get(2).get("field_cn_name"))){
code= "400";
}
return code;
}
// 通过field_order排序获取列名
public voidimportExecl(List<List<String>> excelDataList, String tableId,String tableName, String jqArea) throws IOException {
Stringsql1 = "selectfield_cn_name,field_name,field_order,field_data_type,field_order frommeta_table_field where table_id = '"+ tableId
+"' ORDER BY field_order ";
//1.根据tableId 获得在meta_table_field 的字段名称,以及类型
List<Map<String,String>> listMap = sqlQuery.query(sql1, "");
System.out.println("sql1:"+ sql1);
//2.一个表所有字段类型
ListfielDataTypeList = new ArrayList<>();
Stringcolumns = " id, jq_area";
if (listMap != null &&listMap.size() > 0) {
for (int i = 0; i <listMap.size(); i++) {
columns+= ", "+ listMap.get(i).get("field_name");
fielDataTypeList.add(listMap.get(i).get("field_data_type"));
}
System.out.println("columns:"+ columns);
}
//3.批量执行sql,拼接
List<String>sqls = newArrayList<String>();
if (excelDataList != null &&excelDataList.size() > 0) {
for (int j = 1; j <excelDataList.size(); j++) {
UUIDuuid = UUID.randomUUID();
Stringid = uuid.toString().replace("-", "");
Stringvalues = "'"+ id + "', '"+ jqArea +"'";
for (int i = 0; i <excelDataList.get(j).size(); i++) {
StringfieldDataType = (String) fielDataTypeList.get(i);
if (fieldDataType != null && !"".equals(fieldDataType)){
switch (fieldDataType) {
case "varchar":
values += ", '" + excelDataList.get(j).get(i) + "' ";
break;
default:
values += ", " + excelDataList.get(j).get(i) + " ";
break;
}
}
}
System.out.println("values:"+ values);
Stringsql2 = "INSERT INTO " + tableName + " (" + columns + ") VALUES ("+ values + ")";
System.out.println("sql:" + sql2);
sqls.add(sql2);
}
sqlUpdate.batchExecute("", sqls);
}
}
}
3 前台
采用element.上传组件
3.1 HTML
3.2 JS
源码文件:https://download.csdn.net/download/qq_26553781/10328248