poi--4.导入excel

参看文档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