前后端分离处理Excel表格上传

*简述：***Excel上传，前后端分离，通过传统ajax上传文件，后台无法得到，因此对前台做了特殊处理，通过from提交方式，直接传流到后台。后台通过POI进行解析。

## 前端代码 ##

  <form id="uploadForm" method="post" enctype="multipart/form-data">
         <input id="prodImageUpload" class="b-position-absolute b-cursor inputstyle" type="file" name="file" style="width:100%;top:5px;right: 0;opacity: 0;">
  </form>

$(function () {
var token = window.sessionStorage.getItem(‘token’);

  var fileObj = $("#prodImageUpload");
  fileObj.change(function(){
      var formData = new FormData($("#uploadForm")[0]);
      $.ajax({
          type: "POST",
          url: BaseURL + '/welfare/upload.ajax?token='+token,
          data: formData,
          async: false,
          cache: false,
          contentType: false,
          processData: false,
          success: function (data) {
              var obj = $.parseJSON(data);
              alert(obj)
          }
      })
  });

*后端使用MVC

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

**Controller层处理，暂未做优化

/**
* Created by LiuD
*/
@Controller
@RequestMapping("/welfare")
public class WelfareController {
/**
     * 倒入理赔模板
     */
    @ResponseBody
    @RequestMapping("/upload.ajax")
    public Map<String, Object> upload(MultipartHttpServletRequest request) {
        Map<String, Object> resultMap = new HashMap<>();
        // todo 导入理赔模板
        List<WelfareVo>>> rdo = null;
        try {
            MultipartFile file = request.getFile("file");
            InputStream inputStream = file.getInputStream();
            exportFromExcel(inputStream, file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultMap;
    }

    private List<WelfareVo> exportFromExcel(InputStream filePath, String file) throws Exception {
         List<WelfareVo> list = new ArrayList<>();
        String[] s;
        Workbook workbook = getWorkBook(file, filePath);
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        System.out.println(lastRowNum);
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            int cells = sheet.getRow(i).getPhysicalNumberOfCells();
            s = getExcelRow(row, cells);
            System.out.println(Arrays.toString(s));
        }
        return map;
    }

    //判断文件类型
    private static Workbook getWorkBook(String filePath, InputStream fis) throws Exception {
        //判断是否为excel的类型
        Workbook workBook = null;
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            throw new Exception("上传格式错误");
        }
        if (filePath.endsWith(".xls")) {
            //2003版本的excel，用.xls结尾
            workBook = new HSSFWorkbook(fis, true);
        }
        if (filePath.endsWith(".xlsx")) {
            //2007版本的excel，用.xlsx结尾
            workBook = new XSSFWorkbook(fis);
        }
        return workBook;
    }

    private static String[] getExcelRow(Row row, int columnNum) {
        String[] s;
        StringBuilder builder = new StringBuilder();
        for (int x = 0; x < columnNum; x++) {
            //获取每一行对应
            Cell cell = row.getCell(x);
            builder.append(getStringCellValue(cell) + ",");
        }
        s = builder.toString().split(",");
        return s;
    }

    //获取单元格内有效数据
    private static String getStringCellValue(Cell cell) {
        StringBuilder sb = new StringBuilder();
        switch (cell.getCellType()) {
            //数字
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    sb.append(cell.getDateCellValue());
                } else {
                    sb.append(cell.getNumericCellValue());
                }
                break;
            //字符串
            case Cell.CELL_TYPE_STRING:
                sb.append(cell.getStringCellValue());
                break;
            //布尔
            case Cell.CELL_TYPE_BOOLEAN:
                sb.append(cell.getBooleanCellValue());
                break;
            //公式
            case Cell.CELL_TYPE_FORMULA:
                sb.append(cell.getCellFormula());
                break;
            //空值
            case Cell.CELL_TYPE_BLANK:
                sb.append("");
                break;
            //故障
            case Cell.CELL_TYPE_ERROR:
                sb.append("");
                break;
            default:
                sb.append("");
                break;
        }
        return sb.toString();
    }

}

因为开发任务繁重，代码写的比较啰嗦，不足之处肯定大家批评指正。感谢