springmvc解析excel表

素颜马尾好姑娘i 2022-05-29 06:43 176阅读 0赞

第一步：导入支持的jar包

<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>

<dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.3</version>
    </dependency>

第二步：在springmvc中配置上传文件

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="maxUploadSize" value="10485760" />
        <property name="defaultEncoding" value="UTF-8"/>
      </bean>

第三步：接收文件

@RequestMapping(value = "analysisExcel", method = RequestMethod.POST)
    @ResponseBody
    public JSONObject analysisExcel(@RequestParam(value = "file") MultipartFile file, @RequestParam(value = "token") String token) throws Exception {
        return this.ompSmsService.analysisExcel(file, token);
    }

第四步：解析

package im.qingtui.utils;
    
    import im.qingtui.comstants.CodeMessage;
    import im.qingtui.dto.omp.SmsUserDto;
    import im.qingtui.dto.omp.SmsUserUnqualified;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    import net.sf.json.JSONObject;
    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.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    
    /** * 解析Excel的内容，并返回给前端 * * @author jiajia */ public class ExcelAnalysisContents {
    
        /* * 操作excel存入数据 */ public static JSONObject analysisExcel(MultipartFile file) throws Exception{
            JSONObject message = new JSONObject();
            message.put("code", CodeMessage.PARAMETER_IS_NULL);
            message.put("message", "文档格式不正确");
            //存放所有用户信息的List List<SmsUserDto> upEmpList = new ArrayList<SmsUserDto>();
            //存放所有不合格信息集合 List<SmsUserUnqualified> smsUserUnqualified = new ArrayList<SmsUserUnqualified>();
            //错误标志 boolean isErr = false;
            Workbook wb = null; // 工作区域 //获取文件名 String filename=file.getOriginalFilename();
            // 获取文件后缀 String prefix=filename.substring(filename.lastIndexOf(".")+1);
    
            if (prefix.equals("xlsx")) {
                try {
                    wb = new XSSFWorkbook(file.getInputStream());
                } catch (IOException e) {
                    // TODO Auto-generated catch block e.printStackTrace();
                }
    
            }
            else if (prefix.equals("xls")) {
                try {
                    wb = new HSSFWorkbook(file.getInputStream());
                } catch (IOException e) {
                    // TODO Auto-generated catch block e.printStackTrace();
                }
            } else {
                return message;
            }
            int j=1;
            // 这个必须用接口 Sheet sheet1 = wb.getSheetAt(0);
            for (org.apache.poi.ss.usermodel.Row row : sheet1) {
                if(j--==1)    //跳过第一列 continue;
                int i = 1;
                SmsUserDto emp = new SmsUserDto();//构建一个用户对象bean for (Cell cell : row) {
                    String data=getStringCellValue(cell);
                    // cell.getCellType是获得cell里面保存的值的type switch (i++) {
                        case 1:
                            // 姓名 if (StringUtils.isBlank(data)){
                                isErr = true;
                                smsUserUnqualified.add(ExcelAnalysisContents.nameOrMobileIsNull(i, "用户名为空"));
                                break;
                            }
                            emp.setName(data);break;
                        case 2:
                            // 电话号码 if (StringUtils.isBlank(data)){
                                isErr = true;
                                smsUserUnqualified.add(ExcelAnalysisContents.nameOrMobileIsNull(i, "电话号码为空"));break;
                            }
                            emp.setMobile(data);break;
                            default:break; }
                }
                upEmpList.add(emp);
            }
            if(isErr){
                message.put("data", smsUserUnqualified);
                message.put("message", CodeMessage.PARAMETER_IS_ERR_MESSAGE);
                message.put("code", CodeMessage.PARAMETER_IS_ERR);
            }else {
                message.put("data", upEmpList);
                message.put("message", CodeMessage.SUCCESS_MESSAGE);
                message.put("code", CodeMessage.SUCCESS);
            }
            return message;
        }
        //读取单元格内容 并转为字符串 private static String getStringCellValue(Cell cell) {
            String strCell;
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    strCell = String.valueOf((int)cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    strCell = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    strCell = "";
                    break;
                default:
                    strCell = "";
                    break;
            }
            if (strCell.equals("")) {
                return "";
            }
            return strCell;
        }
    
        /** * 产生记录错误的对象 * @param i 第几行 * @return SmsUserUnqualified记录对象 */ private static SmsUserUnqualified nameOrMobileIsNull(int i, String err){
            SmsUserUnqualified unqualified = new SmsUserUnqualified();
            unqualified.setLine(i);
            unqualified.setErrStr(err);
            return unqualified;
        }
    }

由于使用的是全json数据，后端提供接口，所以返回的是json对象