Java导入excel的操作

最新推荐文章于 2024-11-22 16:40:03 发布

原创最新推荐文章于 2024-11-22 16:40:03 发布 · 299 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个基于JSP的网页应用，该应用允许用户通过上传Excel文件来导入运费数据，并提供了相应的数据验证和导入流程。文章详细展示了从用户界面到后台处理的完整过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、Jsp页面

<html:form action="/importExcel.do" enctype="multipart/form-data">
<tr><td class="search_header" nowrap="nowrap"><font size="3">运费导入导出</font></td> </tr>
<table width="100%" style="font-size: 12px;">
  <tr><td class="underline"></td></tr>
 <tr>
  <td>
  <table width="100%" border="0" class="cistb" cellpadding="2" cellspacing="2" style="font-size: 12px;">
   <tr>
    <td width='100%' colspan="2" class="search_header">第一步: 准备导入数据</td>
   </tr>
   <tr>
         <td class="search_header" width="18%"><font color="red" >*</font>发货方式</td>
         <td>
          <html:radio property="priceType" value="L"/>汽运<html:radio property="priceType" value="A"/>空运
         </td>
        </tr>
   <tr>
    <td width='20%' class="search_header">&nbsp;&nbsp;&nbsp;&nbsp;模板下载</td>
    <td width='80%'><a href="downloanCostExcelTemplate.do">运费导入模板</a></td>
   </tr>
   <tr>
    <td width='100%' colspan="2" class="search_header">第二步: 数据导入</td>
   </tr>
   <tr>
    <td width='20%' class="search_header">&nbsp;&nbsp;&nbsp;&nbsp;选择文件</td>
    <td width='80%'><html:file property="file" ></html:file>&nbsp;<input
     type="button" value="导入" onclick="priceTypeExcel();" class="button"/></td>
   </tr>
  </table>
  </td>
 </tr>
 <tr><td class="underline"></td></tr>
</table>
</html:form>

二对EXCEL读取操作：
public Map importExcel(Map oMap) throws CommandException {
  Map resultMap = new HashMap();
  LoanDAO dao = new LoanDAO();
  InputStream is = null;
  FeedBackTO fto = new FeedBackTO();
  FormFile oFile = (FormFile) oMap.get("file");
  String priceType = (String) oMap.get("priceType"); //类型判断
  String companyName = "";
  String companyNameTmp = "";
  String province = "";
  String provinceTmp = "";
  String city = "";
  try {
   String dataVersion = TimeUtils.timestamp2String("yyyyMMddHHmmss",TimeUtils.getCurrentTimestamp());//查询条件
   is = oFile.getInputStream();
   // ////数据格式验证
   Workbook rwb = Workbook.getWorkbook(is);
   if (rwb.equals("")) {
    fto.setDescription("文件无法解析,可能引起的原因如模板格式不正确");
    fto.setBakPath("test/importExcel.do");
    resultMap.put("feedBack", fto);
    return resultMap;
   }
   Sheet sheet = rwb.getSheet(0);
   if (sheet != null) {
    StringBuffer errMsg = new StringBuffer();
    int rsRows = sheet.getRows();// 总行数读取EXCEL信息
    companyName = sheet.getCell(0, 1).getContents().trim();
    province = sheet.getCell(1, 1).getContents().trim();
    city = sheet.getCell(2, 1).getContents().trim();
    //条件判断
    if (StringUtils.isEmpty(companyName)|| StringUtils.isEmpty(province)|| StringUtils.isEmpty(city)) {
     fto.setDescription("文件无法解析,可能引起的原因如模板格式不正确");
     fto.setBakPath("test/importExcel.do");
     resultMap.put("feedBack", fto);
     return resultMap;
    } else {
     for (int j = 1; j < rsRows; j++) {
      companyName = sheet.getCell(0, 1).getContents().trim();
      province = sheet.getCell(1, 1).getContents().trim();
      city = sheet.getCell(2, 1).getContents().trim();
      String price1 = sheet.getCell(3, j).getContents().trim();
      String price2 = sheet.getCell(4, j).getContents().trim();
      String price3 = sheet.getCell(5, j).getContents().trim();
      if (StringUtils.isNotEmpty(companyName)&& StringUtils.isEmpty(province)) {
       errMsg.append("第" + (j) + "行格式不正确;");
       fto.setDescription(errMsg.toString());
       fto.setBakPath("test/importExcel.do");
       resultMap.put("feedBack", fto);
       return resultMap;
      }
      if (StringUtils.isNotEmpty(province)&& StringUtils.isEmpty(city)) {
       errMsg.append("第" + (j) + "行格式不正确;");
       fto.setDescription(errMsg.toString());
       fto.setBakPath("test/importExcel.do");
       resultMap.put("feedBack", fto);
       return resultMap;
      }
      if (StringUtils.isEmpty(price1)&& Integer.parseInt(price1) < 0) {
       errMsg.append("第" + (j) + "行格式不正确;");
       fto.setDescription(errMsg.toString());
       fto.setBakPath("test/importExcel.do");
       resultMap.put("feedBack", fto);
       return resultMap;
      }
      if (StringUtils.isEmpty(price2)&& Integer.parseInt(price1) < 0) {
       errMsg.append("第" + (j) + "行格式不正确;");
       fto.setDescription(errMsg.toString());
       fto.setBakPath("test/loanCostImExportIndex.do");
       resultMap.put("feedBack", fto);
       return resultMap;
      }
      if (priceType.equals("A")) {
       if (StringUtils.isEmpty(price3)
         && Integer.parseInt(price1) < 0) {
        errMsg.append("第" + (j) + "行格式不正确;");
        fto.setDescription(errMsg.toString());
        fto.setBakPath("test/importExcel.do");
        resultMap.put("feedBack", fto);
        return resultMap;
       }
      }
     }

     ////开始导入数据
     for (int i = 1; i < rsRows; i++) {
      companyName = sheet.getCell(0, i).getContents().trim();
      if (StringUtils.isNotEmpty(companyName)) {
       companyNameTmp = companyName;
      }
      province = sheet.getCell(1, i).getContents().trim();
      if (StringUtils.isNotEmpty(province)) {
       provinceTmp = province;
      }
      city = sheet.getCell(2, i).getContents().trim();
      NumberCell number1 = (NumberCell)sheet.getCell(3, i); 
      NumberCell number2 = (NumberCell)sheet.getCell(4, i);
      double price1 = number1.getValue(); 
      double price2 = number2.getValue(); 
      if (priceType.equals("L")) {
       LoanLandPriceTO landTO = new LoanLandPriceTO();
       landTO.setCarrier(companyNameTmp);
       landTO.setProvince(provinceTmp);
       landTO.setCity(city);
       landTO.setPrice1(new BigDecimal(price1));
       landTO.setPrice2(new BigDecimal(price2));
       landTO.setDataVersion(dataVersion);
       dao.create(landTO, LoanLandPriceTO.class);
      } else {
       NumberCell number3 = (NumberCell)sheet.getCell(5, i);
       double price3 = number3.getValue(); 
       LoanAirliftPriceTO airliftTO = new LoanAirliftPriceTO();
       airliftTO.setCarrier(companyNameTmp);
       airliftTO.setProvince(provinceTmp);
       airliftTO.setCity(city);
       airliftTO.setPrice1(new BigDecimal(price1));
       airliftTO.setPrice2(new BigDecimal(price2));
       airliftTO.setPrice3(new BigDecimal(price3));
       airliftTO.setDataVersion(dataVersion);
       dao.create(airliftTO, LoanAirliftPriceTO.class);
      }
     }
    }
    String url = "test/importExcel.do";
    fto.setBakPath(url);
    fto.setDescription("导入成功");
    resultMap.put("feedBack", fto);
   }
  } catch (Exception e) {
   e.fillInStackTrace();
   String url = "test/importExcel.do";
   fto.setBakPath(url);
   fto.setDescription("文件无法解析,可能引起的原因如模板格式不正确");
   resultMap.put("feedBack", fto);
  } finally {
   try {
    is.close();
   } catch (IOException e) {
    e.fillInStackTrace();
   }
  }
  return resultMap;
 }