上传Excel并解析

傷城~ 2022-05-28 11:12 243阅读 0赞

该篇博客废除，见解析excel工具类

兼容2007和2003两种类型的文件，举例：这里模板有两个页脚：

![20180404100358222][]                         ![2018040410041868][]

import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import org.apache.poi.POIXMLDocument;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Cell;
    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.beans.factory.annotation.Value;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.annotation.Resource;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import javax.servlet.http.HttpSession;
    import java.io.*;
    import java.net.URLDecoder;
    import java.net.URLEncoder;
    import java.util.*;

/**
         * 选择列表导入方式
         * 文件上传
         * 上传并解析出字段，解析后新增
         * 上传文件字段upfile
         * userGroupName 用户群名称
         * appName 应用名称
         */
        @PostMapping("/upload")
        public Result upload(@RequestParam("file") MultipartFile[]  files,@RequestParam String userGroupName,@RequestParam String applicationName
                , HttpSession session) throws IllegalStateException, IOException{
            UserGroup userGroup = new UserGroup();
            userGroup.setUserGroupName(userGroupName);
            userGroup.setApplicationName(applicationName);
            UapUser uapUser = (UapUser)session.getAttribute("USER");
            if (null != uapUser) {
                userGroup.setCreationBy(uapUser.getLoginName());
            }
            System.out.println("***文件上传***");
            MultipartFile excelFile = null;
            if(files!=null && files.length>0){
                excelFile = files[0];
            }else{
                return ResultGenerator.genCostomFailResult(ResultCode.DELETE_FAIL_ERROR, "没有上传文件");
    
            }
    
            System.out.println("***解析excel上传至从表***");
            //解析出的用户id 或mac地址 集合
            List<String> idlist = new ArrayList<String>();
            List<String> maclist = new ArrayList<String>();
            String fileType = "";
            try {
                String fileName = excelFile.getOriginalFilename();
                fileType = fileName.substring(fileName.lastIndexOf(".") + 1,
                        fileName.length());
                System.out.print("文件类型为"+fileType);
            } catch (Exception e) {
    
                fileType = "";
            }
            if (!fileType.toLowerCase().equals("xls")  && !fileType.toLowerCase().equals("xlsx")) {
                System.out.print("格式不支持");
                return ResultGenerator.genCostomFailResult(ResultCode.DELETE_FAIL_ERROR, "不支持的文件类型");
    
            }
    
            //XSSF
            if("xlsx".equals(fileType)  || "xls".equals(fileType)){
                System.out.println("开始解析"+fileType);
                Workbook wb = null;
    
                InputStream inp = excelFile.getInputStream();
                try {
    
                    if(! inp.markSupported()) {
                        inp = new PushbackInputStream(inp, 8);
                    }
    
                    if(POIFSFileSystem.hasPOIFSHeader(inp)) {
                       wb = new HSSFWorkbook(inp);
                    }else if(POIXMLDocument.hasOOXMLHeader(inp)) {
                       wb =  new XSSFWorkbook(OPCPackage.open(inp));
                    }
    
                    if (null == wb){
                        return ResultGenerator.genCostomFailResult(ResultCode.ADD_FAIL_ERROR,
                                "导入失败!");
    
                    }
                    Sheet macSheet = wb.getSheetAt(0);//mac地址
                    Sheet idSheet = wb.getSheetAt(1);//用户id
                    String mac = wb.getSheetName(0);// mac地址  Sheet1
    
    
    
                    /*  String id = wb.getSheetName(1);//用户id  Sheet2*/
                  //解析mac地址
                    System.out.println("解析mac地址"+mac);
                    for (int i = macSheet.getFirstRowNum()+1; i <= macSheet.getLastRowNum(); i++) {
                        Row row = macSheet.getRow(i);
                        Iterator cells = row.cellIterator();
                        while (cells.hasNext()) {
                            Cell cell = (Cell) cells.next();
                            String str = cell.getStringCellValue();
                            System.out.print(" " + str);
                            maclist.add(str);
                        }
                    }
                    //解析用户id
                    System.out.println("解析id地址");
                    for (int i = idSheet.getFirstRowNum()+1; i <= idSheet.getLastRowNum(); i++) {
                        Row row = idSheet.getRow(i);
                        Iterator cells = row.cellIterator();
                        while (cells.hasNext()) {
                          Cell cell = (Cell) cells.next();  //用户Id可能为字符串也可能为纯数字，不确定
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String str = cell.getStringCellValue();
                            System.out.println("内容"+cell.toString());
                            /*Cell cell = (Cell) cells.next();
                            String str = cell.getStringCellValue();
                            System.out.print(" " + str);*/
                            idlist.add(str);
                        }
                    }
    
    
                } catch (Exception e) {
                    System.out.print(e);
                  //  return ResultGenerator.genCostomFailResult(ResultCode.ADD_FAIL_ERROR, "新增失败");
                    // BusinessServiceException("未知原因！保存Excel文件时，请不要将鼠标最终定位在Excel中的可以下拉选值的列上。");
                }
            }
    
    
    
    
            System.out.println("用户id列表 "+idlist);
            System.out.print("mac列表 "+maclist);
            userGroup.setUserIdList(idlist);
            userGroup.setMacList(maclist);
          UserGroup dealUserGroup = userGroupService.dealExcelImportUserGroup(userGroup);
          if(dealUserGroup==null){
              return ResultGenerator.genCostomFailResult(ResultCode.ADD_FAIL_ERROR, "新增失败，应用名称不存在");
    
          }
            int a = userGroupService.addEcxelImportUserGroup(dealUserGroup);
          if(a==-2){
              return ResultGenerator.genCostomFailResult(ResultCode.ADD_FAIL_ERROR, "新增失败，该用户群名称已存在");
    
          }else  if (a <= 0) {
                return ResultGenerator.genCostomFailResult(ResultCode.ADD_FAIL_ERROR, "新增失败");
            }
            return ResultGenerator.genSuccessResult("添加成功");
        }

[20180404100358222]: https://img-blog.csdn.net/20180404100358222
[2018040410041868]: /images/20220528/a15921d9dacf4934bb391c6e37983cd6.png