excel导入

页面

<form id="uploadExcel" method="post" enctype="multipart/form-data">        <table>            <div id="b">                <tr>                    <td><input id="budgetType" name="budgetType" value="1">                    </td>                </tr>                <tr>                    <td><input id="companyID" name="companyID"></td>                </tr>                <tr>                    <td><input type="text" class="jeasy-combobox"                        name="budgetDate" style="display: none;" id="budgetDate">                    </td>                </tr>                <tr>                    <td><input id="excel" name="excel" class="easyui-filebox"                        style="width: 200px"                        data-options="prompt:'请选择文件...' ,buttonText:'选择文件'"></td>                    </td>                </tr>            </div>        </table>    </form>

后台

   @RequestMapping("/upload/uploadExcel")    public void uploadExcel(HttpServletRequest request,HttpServletResponse response,String budgetType,String companyID,String budgetDate) throws IllegalStateException, IOException{        String message="{\"message\":\"ok\"}";        int uid= (Integer) sessionProvider.getAttribute(request, "uid", response);        ///创建一个通用的多部分解析器        CommonsMultipartResolver multipartResolver  = new CommonsMultipartResolver(request.getSession().getServletContext());        // 设置编码        multipartResolver.setDefaultEncoding("utf-8");          // 判断 request 是否有文件上传        if(multipartResolver.isMultipart(request)){             // 强转换成多部分request            MultipartHttpServletRequest  multiRequest = (MultipartHttpServletRequest)request;            //获取所有文件            Iterator<String>  iter = multiRequest.getFileNames();            while(iter.hasNext()){                String next = (String)iter.next();                //next:file                    MultipartFile file = multiRequest.getFile(next);                    //file: org.springframework.web.multipart.commons.CommonsMultipartFile@2e574e38                if(file != null){                    try {                       List<Sheet> sheets = ExcelUtil.readExcel(file); //这里得到的是一个集合，里面的每一个元素是Sheet                       if(sheets.size()>0){                           //打印每一个单元格                           excelModelService.printCell(sheets);                       }else{                           //excel没有数据                       }                    } catch (Exception e) {                        logger.error("excel中数据格式有问题");                        message="{\"message\":\"error\"}";                    }                }            }        }        ResponseUtils.renderJson(response, message);    }

工具类ExcelUtil

public class ExcelUtil {    private static Logger logger  = Logger.getLogger(ExcelUtil.class);    private final static String xls = "xls";    private final static String xlsx = "xlsx";    /**     * 读入excel文件，解析后返回List(ExcelModel)     * @param file     * @throws IOException     */    public static List<Sheet> readExcel(MultipartFile file) throws IOException{            //检查文件            checkFile(file);            //获得Workbook工作薄对象          Workbook workbook =  getWorkBook(file);          List<Sheet> list=new ArrayList<Sheet>();          Sheet sheet=null;          if(workbook != null){              //循环sheet              for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){                  //获得当前sheet工作表                   sheet = workbook.getSheetAt(sheetNum);                  if(StringUtil.isNotEmpty(sheet)){                      list.add(sheet);                  }else {                    continue;                }          }    }        return list;    }    public static void checkFile(MultipartFile file) throws IOException{        //判断文件是否存在        if(null == file){            logger.error("文件不存在！");            throw new FileNotFoundException("文件不存在！");        }        //获得文件名        String fileName = file.getOriginalFilename();        //判断文件是否是excel文件        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){            logger.error(fileName + "不是excel文件");            throw new IOException(fileName + "不是excel文件");        }    }    public static Workbook getWorkBook(MultipartFile file)  {        //获得文件名        String fileName = file.getOriginalFilename();        //创建Workbook工作薄对象，表示整个excel        Workbook workbook = null;        try {            //获取excel文件的io流            InputStream is = file.getInputStream();            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象            if(fileName.endsWith(xls)){                //2003                workbook = new HSSFWorkbook(is);            }else if(fileName.endsWith(xlsx)){                //2007 及2007以上                 workbook = WorkbookFactory.create(is);              }        } catch (Exception e) {            e.printStackTrace();            logger.info(e.getMessage());        }        return workbook;    }    public static String getCellValue(Cell cell){        String cellValue = "";        if(cell == null){            return cellValue;        }        //把数字当成String来读，避免出现1读成1.0的情况        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){            cell.setCellType(Cell.CELL_TYPE_STRING);        }        //判断数据的类型        switch (cell.getCellType()){            case Cell.CELL_TYPE_NUMERIC: //数字                cellValue = String.valueOf(cell.getNumericCellValue());                break;            case Cell.CELL_TYPE_STRING: //字符串                cellValue = String.valueOf(cell.getStringCellValue());                break;            case Cell.CELL_TYPE_BOOLEAN: //Boolean                cellValue = String.valueOf(cell.getBooleanCellValue());                break;            case Cell.CELL_TYPE_FORMULA: //公式//                cellValue = String.valueOf(cell.getCellFormula());                cellValue = String.valueOf(cell.getNumericCellValue());                break;            case Cell.CELL_TYPE_BLANK: //空值                cellValue = "";                break;            case Cell.CELL_TYPE_ERROR: //故障                cellValue = "非法字符";                break;            default:                cellValue = "未知类型";                break;        }        return cellValue;    }}

excelModelService.printCell

   public void printCell(List<Sheet> sheets) {        int num=sheets.size();        if(num>0){        for(int i = 0;i < num;i++){            //获得当前sheet工作表            Sheet sheet = sheets.get(i);            if(StringUtil.isEmpty(sheet)){                continue;            }            //获得当前sheet的开始行的行数            int firstRowNum  = sheet.getFirstRowNum();            //获得当前sheet的结束行的行数            int lastRowNum = sheet.getLastRowNum();            //获取第二行的标题行            Row rowN = sheet.getRow(firstRowNum+1);            //获取开始列的列号            int firstCellNum = rowN.getFirstCellNum();            //获取结束列的列号            int lastCellNum = rowN.getPhysicalNumberOfCells();            //获取sheetName             String fullDepartmentName = sheet.getSheetName();            //第三行开始            for(int rowNum = firstRowNum+2;rowNum <lastRowNum;rowNum++){                //获得当前行                Row row = sheet.getRow(rowNum);                if(row == null){                    continue;                }                //获取当前行的每一单元格                for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){                    //获取当前行的每一单元格                    Cell cell = row.getCell(cellNum);                    String  cellValue= ExcelUtil.getCellValue(cell);                    System.out.println(cellValue);                }            }        }        }    }

pom

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