Java服务端读取excel文件xls格式内容

来源：互联网发布：知礼仪明事理辨是非编辑：程序博客网时间：2024/05/23 11:54

  最近需要读取从客户端发送过来的excel文件内容，excel文件的格式是事先规定好的不变的。所以就研究了一下，因为是自己写的需要测试所以先写了个简单的网页上传文件，看客户端上传文件代码：

<html><body><div class="offset1 span10 container-fluid">            <form id="firmForm" class="form-horizontal"                action="/front/cashFlow/payForAnotherInformationList.htm"                method="post" enctype="multipart/form-data" accept-charset="utf-8">                <div class="control-group">                    <div class="control-group">                        <label class="control-label" for="input01">上传文件:</label>                        <div class="controls" style="padding-top: 5px">                            <input id="entrustFile" name="entrustFile" type="file" value="文件">                        </div>                    </div>                <input name="btnSubmit" type="button" class="tjBtn" id="btnSubmit"                        value="提交" onclick="javascript:submit();"/>            </form>    </div></body></html><script type="text/javascript">    function submit() {        document.getElementById("firmForm").submit();    }</script>

客户端的工作完成了可以上传文件测试了，现在主要介绍服务端的工作，首先我为了解析excel文件的内容导入一个框架：poi-3.9-20121203.jar
然后我们第一步要做的是从request中获取到这个文件，将HttpServletRequest类型的请求强转成MultipartHttpServletRequest类型的请求，然后MultipartHttpServletRequest有个方法，可以通过文件名获取文件，获取到文件后通过getInputStream()函数获取文件内容：

    /**     * Return the contents plus description of an uploaded file in this request,     * or {@code null} if it does not exist.     * @param name a String specifying the parameter name of the multipart file     * @return the uploaded content in the form of a {@link MultipartFile} object     */    MultipartFile getFile(String name);

接下来直接看源码：

    /**     * 读取excel文件     */    public List<CashOutRecord> haveFileFromClient(HttpServletRequest request) throws IOException {        // 转型为MultipartHttpRequest：        MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;        // 获得文件：        MultipartFile contactFile = multiRequest.getFile("entrustFile");        // 获取的代付信息列表        List<CashOutRecord> resultList = new ArrayList<CashOutRecord>();        if (null != contactFile && !contactFile.isEmpty()) {            //解析文档            POIFSFileSystem fs;            //获取整个文档            HSSFWorkbook wb;            // 页            HSSFSheet sheet;            // 行            HSSFRow row;            // 打开文件            try {                fs = new POIFSFileSystem(contactFile.getInputStream());                wb = new HSSFWorkbook(fs);            } catch (IOException e) {                e.printStackTrace();                wb = new HSSFWorkbook();            }            String entrustNo = RandomStringUtils.randomNumeric(9);            //获取第一页            sheet = wb.getSheetAt(0);            // 得到总行数            int rowNum = sheet.getLastRowNum();            // 正文内容应该从第二行开始,第一行为表头的标题            for (int i = 1; i <= rowNum; i++) {                String outNo = entrustNo + "_" + RandomStringUtils.randomNumeric(6);                row = sheet.getRow(i);                String accountType = "";                String accountName = "";                String accountNum = "";                String bankName = "";                String bankProv = "";                String bankCity = "";                BigDecimal amount = null;                String bankBranch = "";                String reason = "";                try {                    int idx = 0;                    idx++;                    accountType = getCellFormatValue(row.getCell(idx));                    idx++;                    accountName = getCellFormatValue(row.getCell(idx));                    idx++;                    accountNum = getCellFormatValue(row.getCell(idx));                    idx++;                    bankName = getCellFormatValue(row.getCell(idx));                    idx++;                    bankProv = getCellFormatValue(row.getCell(idx));                    idx++;                    bankCity = getCellFormatValue(row.getCell(idx));                    idx++;                    amount = new BigDecimal(getCellFormatValue(row.getCell(idx)));                    idx++;                    bankBranch = getCellFormatValue(row.getCell(idx));                    idx++;                    reason = getCellFormatValue(row.getCell(idx));                    CashOutRecord cashEntrustRecord = new CashOutRecord();                    cashEntrustRecord.setOutNo(outNo);                    cashEntrustRecord.setAccountname(accountName);                    cashEntrustRecord.setAccountnum(accountNum);                    cashEntrustRecord.setBankname(bankName);                    cashEntrustRecord.setBankbranch(bankBranch);                    cashEntrustRecord.setBankprov(bankProv);                    cashEntrustRecord.setBankcity(bankCity);                    cashEntrustRecord.setAmount(amount);                    if (accountType.equals("个人账户")) {                        cashEntrustRecord.setAccounttype(11);                    } else if (accountType.equals("企业账户")) {                        cashEntrustRecord.setAccounttype(12);                    }                    cashEntrustRecord.setRemark(reason);                    resultList.add(cashEntrustRecord);                } catch (Exception ex) {                    System.out.print(ex);                }            }            return resultList;        }        return null;    }----------对于每个excel文件的表格中内容的格式我们也要做处理----------    /**     * 匹配excel表格cell的类型     */    private String getCellFormatValue(HSSFCell cell) {        String cellvalue = "";        if (cell != null) {            // 判断当前Cell的Type            switch (cell.getCellType()) {            // 如果当前Cell的Type为NUMERIC            case HSSFCell.CELL_TYPE_NUMERIC:            case HSSFCell.CELL_TYPE_FORMULA: {                // 判断当前的cell是否为Date                if (HSSFDateUtil.isCellDateFormatted(cell)) {                    Date date = cell.getDateCellValue();                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                    cellvalue = sdf.format(date);                }                // 如果是纯数字                else {                    // 取得当前Cell的数值                    cellvalue = String.valueOf(cell.getNumericCellValue());                }                break;            }            // 如果当前Cell的Type为STRIN            case HSSFCell.CELL_TYPE_STRING:                // 取得当前的Cell字符串                cellvalue = cell.getRichStringCellValue().getString();                break;            // 默认的Cell值            default:                cellvalue = " ";            }        } else {            cellvalue = "";        }        return cellvalue;    }

1 0