springmvc解析excel表

第一步：导入支持的jar包

<!-- ============================上传excel包======================================= -->

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

<!--上传文件需要的jar包-->
<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中配置上传文件

<!--配置上传文件10m-->
  <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对象

ok