1.导入

    @RequestMapping(value = "importExcelPartInfo.do", method = RequestMethod.POST, headers = { "Accept=application/html" })
    public void importExcelPartInfo(HttpServletRequest request,@RequestPart(value = "file") MultipartFile[] uploadfiles,HttpServletResponse response) throws Exception {
            
            Map<String, Object> where = DataConvertHelper.getRequestParam(request);
            UploadResult uploadResult = new UploadResult();
            ListResult<Map<String, Object>> result = new ListResult<>();
        try {
            String newPath = DataConvertHelper.getImportPath("mp/iwms/");
            String realPath = request.getSession().getServletContext().getRealPath("")+ newPath;
            File dir = new File(realPath);
            if (!dir.isDirectory()){
                dir.mkdirs();
            }
            String fileformat = "xlsx|xls|";
            boolean FileFlag = false;
            for (MultipartFile multipartFile : uploadfiles) {
                if (fileformat.indexOf(FilenameUtils.getExtension(
                        multipartFile.getOriginalFilename()).toLowerCase()
                        + "|") < 0) {
                    FileFlag = true;
                    break;
                }
            }
            if (FileFlag) {
                uploadResult.setResult("3");
                uploadResult.setMsg("格式错误,请重新输入"); // 上传文件格式不对
                returnHtml(uploadResult, response);
                return ;
            }

            for (MultipartFile multipartFile : uploadfiles) {
                String filename = multipartFile.getOriginalFilename();
                File saveFile = new File(realPath, filename);
                multipartFile.transferTo(saveFile);

                String[] columnNames = {"PART_NO","FORM_PART_NO","PART_NAME_CN","PART_NAME_EN","PART_UNIT" ,"MAN_MODEL", "PART_USE","IS_RETORACTIVITY","IS_ENABLE"};
                String fileName = realPath + File.separator + filename;
                List<Map<String, Object>> dataList = new ExcelHelper<>().readDataFromExcel(fileName, columnNames, 1);
                
                if (dataList != null && dataList.size() > 0) {
                    //此处进行循环获取sql进行批处理,或传入一个List
                    result = partInformationBIZ.importExcelPartInfo(dataList,where);
                    uploadResult.setMsg(result.getMsg()); 
                    uploadResult.setResult(result.getResult());
                }
                if(uploadResult.getResult().equals("0")){

                    JSONObject obj= JSONObject.fromObject(uploadResult.getMsg());
                    Object  ob=  obj.get("rows");
                    List<Map<String,Object>> upload = (List<Map<String,Object>>)ob;
                    
//                    String orgchange="";
                    String[] columnNames2 = { "PART_NO","FORM_PART_NO","PART_NAME_CN","PART_NAME_EN","PART_UNIT" ,"MAN_MODEL", "PART_USE","IS_RETORACTIVITY","IS_ENABLE","IS_TRUE"};
                    String[] titleNames =   {  "零件编号", "零件显示编号" , "中文名" , "英文名" , "计量单位" ,"厂家型号", "用途说明" ,"是否追塑件" ,"是否可用" , "错误提示"   };

                    // 判断路径是否存在,不存在则创建
//                    File dir = new File(realPath);
//                    if (!dir.isDirectory())
//                        dir.mkdirs();

                    String sheetName = "零件信息导入数据更正";
                    String fileName2 = realPath + File.separator + sheetName + ".xlsx";
                    new ExcelHelper<>().writeDataToExcel(fileName2, sheetName,
                            titleNames, columnNames2, upload);
                    
                    String ip = request.getLocalAddr();
                    int port = request.getLocalPort();
                    System.out.println("port:"+port);
                    String downloadUrl = "/" + newPath.replace(File.separator, "/")
                            + "/" + sheetName + ".xlsx";
                //    downloadUrl = request.getScheme()+"://"+ip+":"+port + downloadUrl;
                //    downloadUrl =  downloadUrl;
                    uploadResult.setDownloadUrl(downloadUrl);
                    returnHtml(uploadResult, response);
                }
                
            }
        } catch (Exception e) {
            logger.error("异常信息:" + e.getMessage(), e);
            uploadResult.setResult(Globals.FAIL_CODE);
            uploadResult.setMsg("导入失败"); //导入失败

        }
        returnHtml(uploadResult, response);
        return ;
    }

(2)returnHtml()方法

    private void returnHtml(UploadResult uploadResult,
            HttpServletResponse response) {
        try {
            JSONObject jsonObject = JSONObject.fromObject(uploadResult);
            String jsonStr = jsonObject.toString();

            response.setContentType("text/html;charset=UTF-8");
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);

            response.getWriter().write(jsonStr);
            response.getWriter().flush();
            response.getWriter().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

 (3)解析excel文件

    /**
     * 解析Excel文件
     * 
     * @param fileName
     *            Excel文件名
     * @param columnNames
     *            字段名称,作为Map的key值
     * @param startIndex
     *            sheet中数据开始行,下标从0开始
     * @return List<Map<String, Object>>数据
     * @throws Exception
     */
    public List<Map<String, Object>> readDataFromExcel(String fileName, String[] columnNames, int startIndex)
            throws Exception {
        List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();

        FileInputStream fis = new FileInputStream(fileName);

        Workbook workbook = WorkbookFactory.create(fis);

        Sheet sheet = null;
        int sheetNum = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
            sheet = workbook.getSheetAt(i);
            data.addAll(readSheet(sheet, columnNames, startIndex));
        }

        return data;
    }

(4)

     * 解析sheet,返回List<Map<String, Object>>类型数据
     * 
     * @param sheet
     *            Sheet对象
     * @param columnNames
     *            字段名称,作为Map的key值
     * @param startIndex
     *            开始读取数据的行
     * @return List<Map<String, Object>>数据
     */
    private List<Map<String, Object>> readSheet(Sheet sheet, String[] columnNames, int startIndex) {
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();

        Row row = null;
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = startIndex; i < rowNum; i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            row = sheet.getRow(i);
            for (int j = 0; j < columnNames.length; j++) {
                map.put(columnNames[j], readCellValue(row.getCell(j)));
            }
            mapList.add(map);
        }

        return mapList;
    }

(5)

    /**
     * 读取Excel格子中的值
     * 
     * @param c
     *            Excel中的格子对象
     * @return Object 数据
     */
    @SuppressWarnings("deprecation")
    private Object readCellValue(Cell c) {
         DecimalFormat df = new DecimalFormat("0");   
        if (c == null) {
            return "";
        } else {
            switch (c.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                return "";
            case Cell.CELL_TYPE_BOOLEAN:
                return c.getBooleanCellValue();
            case Cell.CELL_TYPE_ERROR:
                return c.getErrorCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return c.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                //String dataFormat = c.getCellStyle().getDataFormatString();
                if (DateUtil.isCellDateFormatted(c)) {
                    SimpleDateFormat sdf; //331844 功能服务关系管理,导入文件时,某个单元格只包含整数或日期,导入数据格式显示不一致 ,否则显示为Sun Jan 17 00:00:00 CST 2016
                    Date d=c.getDateCellValue(); //直接返回显示为Sun Jan 17 00:00:00 CST 2016
                    if(d.getHours()==0 && d.getMinutes()==0 && d.getSeconds()==0){
                        sdf=new SimpleDateFormat("yyyy-MM-dd");
                    }else{
                        sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                                        
                    return sdf.format(d);                 
               } else{
                   return df.format(c.getNumericCellValue()); //数字型
               }
                
            case Cell.CELL_TYPE_STRING:
                return c.getStringCellValue();
            }
            return "Unknown Cell Type:" + c.getCellType();
        }
    }
     * 获取导入文件的地址
     * path = mp/dff/模块
     * @return
     * @author 李祖一
     */
    public static String getImportPath(String path) {
        return path + "importDffService" + File.separator
                + DateTimeHelper.getCurrentTime("yyyyMMdd")
                + File.separator + StringHelper.GetGUID();
    }