关闭

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

标签: java读取excel上传文件
3019人阅读 评论(0) 收藏 举报
分类:
  最近需要读取从客户端发送过来的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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:42707次
    • 积分:811
    • 等级:
    • 排名:千里之外
    • 原创:38篇
    • 转载:0篇
    • 译文:0篇
    • 评论:5条
    最新评论