JAVA上传EXCEL解析

后台Controller层用(@RequestParam("fileupload") MultipartFile fileuploa)接收

VO为：SysPurcInfoVo

代码如下：

@RequestMapping(value="/ImportExcel", produces = {"application/json;charset=UTF-8"}, method = {RequestMethod.POST})
@ResponseBody
public List<SysPurcInfoVo> pircInfoImportExcel(@RequestParam("fileupload") MultipartFile fileupload) {

    String fileName = fileupload.getOriginalFilename().substring(fileupload.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
    List<SysPurcInfoVo> purcList = new ArrayList<>();
    if (fileName == null || (!fileName.equals("xls") && !fileName.equals("xlsx"))) {
        throw new CustomizeException("请导入xls或者xlsx的文件!");
    }
    try {
        List<List<Object>> lists = ImportsExcel.readExcel(fileupload);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < lists.size(); i++) {
            //构建vo
            SysPurcInfoVo sysPurcInfoVo=new SysPurcInfoVo();
            sysPurcInfoVo.setTradTypeValue(((String) (lists.get(i).get(0))).trim());
           
            String s = sysPurcInfoService.selectTradType(sysPurcInfoVo.getTradTypeValue());
            sysPurcInfoVo.setTradType(s);
            sysPurcInfoVo.setTradNameValue(((String)(lists.get(i).get(1))).trim());
            
            String s1 = sysPurcInfoService.selectTradName(sysPurcInfoVo.getTradNameValue(),s);
            sysPurcInfoVo.setTradName(s1);
           
            sysPurcInfoVo.setSpecification(((String)(lists.get(i).get(2))).trim());
            sysPurcInfoVo.setMaterial(((String)(lists.get(i).get(3))).trim());
            sysPurcInfoVo.setPlace(((String)(lists.get(i).get(4))).trim());
            String regExp ="^\\d{1,9}(\\.\\d{1,4})?$";
            sysPurcInfoVo.setPurcNum(((String)(lists.get(i).get(5))).trim());
            String purcNum = sysPurcInfoVo.getPurcNum();
           
          
            sysPurcInfoVo.setDeliveryAddress(((String)(lists.get(i).get(6))).trim());
            sysPurcInfoVo.setRemarks(((String) lists.get(i).get(9)).trim());
            try {
                sysPurcInfoVo.setDeliveryDate((sdf.parse((String) lists.get(i).get(7))));
                sysPurcInfoVo.setClosingDate((sdf.parse((String) lists.get(i).get(8))));
            } catch (ParseException e) {
                e.printStackTrace();
            }

            
            purcList.add(sysPurcInfoVo);
        }
    } catch (IOException e) {
        throw new CustomizeException("导入失败");
    }
    return purcList;
}

这里不做持久化操作，返回给前端一个集合供页面展示。

 

附带一个读取Excel的工具类：

public class ImportsExcel {
    /**
     * 对外提供读取excel 的方法
     *
     */
public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
   String extension= file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();

   if("xls".equals(extension)){
    return read2003Excel(file.getInputStream());
   }else if("xlsx".equals(extension)){
    return read2007Excel(file.getInputStream());
   }else{
    throw new IOException("不支持的文件类型");
   }
}
   /**
   * 读取 office 2003 excel
   * @throws IOException
   * @throws FileNotFoundException
   */
private static List<List<Object>> read2003Excel(InputStream stream) throws IOException {
   List<List<Object>> list = new LinkedList<List<Object>>();
   HSSFWorkbook hwb = new HSSFWorkbook(stream);
   HSSFSheet sheet = hwb.getSheetAt(0);
   Object value = null;
   HSSFRow row = null;
   HSSFCell cell = null;
   for(int i = sheet.getFirstRowNum()+1;i<= sheet.getPhysicalNumberOfRows();i++){
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
         linked.add("");
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("###.####");// 格式化数字
     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      if("@".equals(cell.getCellStyle().getDataFormatString())){
         value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
        value = nf.format(cell.getNumericCellValue());
      }else{
        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      value = "";
      break;
     default:
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
         linked.add("");
      continue;
     }
     linked.add(value);
   }
    list.add(linked);
   }
   return list;
}
   /**
   * 读取Office 2007 excel
   */
private static List<List<Object>> read2007Excel(InputStream stream) throws IOException {
   List<List<Object>> list = new LinkedList<List<Object>>();
   // 构造 XSSFWorkbook 对象，strPath 传入文件路径
   XSSFWorkbook xwb = new XSSFWorkbook(stream);
   // 读取第一章表格内容
   XSSFSheet sheet = xwb.getSheetAt(0);
   Object value = null;
   XSSFRow row = null;
   XSSFCell cell = null;
   for (int i = sheet.getFirstRowNum()+1; i <= sheet
     .getPhysicalNumberOfRows(); i++) {
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
         linked.add("");
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("###.####");// 格式化数字
     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      if("@".equals(cell.getCellStyle().getDataFormatString())){
        value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
        value = nf.format(cell.getNumericCellValue());
      }else{
       value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      value = "";
      break;
     default:
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
         linked.add("");
         continue;
     }
     linked.add(value);
    }
    list.add(linked);
   }
   return list;
}

}