解析EXCEL文件

最新推荐文章于 2022-07-13 20:49:39 发布

Sweblish

最新推荐文章于 2022-07-13 20:49:39 发布

阅读量5.6k

点赞数

分类专栏： java 文章标签： excel string upload null class download

本文链接：https://blog.csdn.net/Sweblish/article/details/6976494

版权

java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

1、在jsp页面中增加一个上传文件的按钮
<table width="500px">
    <tbody>
      <tr>
        <th width="200px"><abbr title="必填项" class="require">*</abbr><label for="file">文件(xlsx):</label></th>
        <td><s:file name="attachment" size="40"/> </td>
      </tr>
      <tr>
        <th></th>
        <td style="color: red">注意：导入的物流回单类型是xlsx文件，文件格式：id,status,type,subtype,source,city,area,subarea,categoryid,price,
        discount,discountmoney,sale,posx,posy,download,print,storeid,1,starttime,endtime,updatetime,id,usetype,
        discounttype,createtime,title,subtitle,chkcode,storename,sourceinfo,address,phones,message,useinfo,disclaimer,
        storeids,content,picture,link 导入之前请确认文件格式是否正确？是否有乱码？各数据项是否准确？以免出现异常。</td>
      </tr>
      <tr>
        <td colspan="2">
        <s:hidden name="year" id="year" ></s:hidden>
        <s:submit cssClass="btn" value="确定" />
        <s:reset value="返回" οnclick="reloadPage()" cssClass="btn"></s:reset>

        </td>
      </tr>
    </tbody>
  </table>
2、进行文件配置

<package name="default" namespace="/" extends="struts-default">
  <interceptors>
   <interceptor name="upload" class="org.apache.struts2.interceptor.FileUploadInterceptor">
      
     <param name="allowedTypes">application/vnd.ms-csv,application/vnd.ms-excel,text/plain,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet</param>
      
      <param name="maximumSize">209715500</param>
   </interceptor>
  <interceptors>
</package>

<package name="fileUpload" namespace="/upfile" extends="default">
    
    <action name="upFile" class="com.koubei.coupon.action.UpFileAction" method="upFile">
      <result name="input" type="redirect">/coupon/upfile.jsp</result>
      <result name="success" type="dispatcher">/coupon/upfile.jsp</result>
    <interceptor-ref name="upload" />
    <interceptor-ref name="defaultStack" />
    </action>
</package>

配置文件下的结构：<package>
                      <interceptors></interceptors>
                      <global-results></global-results>
                      <action></action>
                  </package>

3、action中进行的操作

File attchment = "";//get、set方法省略
public String upFile(){
  boolean res = false;
  String result="上传文件失败";
  if(attachment!=null)
  {
   if(attachment.length()>524288){
    addActionMessage("数据文件超过512kb，无法上传！");
    return "success";
   }
   res = uploadBiz.readFile2DB(attachment);
  }
  if(res){
   result="上传文件成功";
   addActionError(result);//由于没有前端支持，这里addAcitonError代表成功了。
   return "success";
  }
  addActionMessage(result);//返回
  return "success";
}
4、uploadBiz.readFile2DB(attachment)实现对文件的解析
InputStream ins = null;
WorkBook book = null;
ins = new FileInputStream(attachment);
book = WorkBookFactory.create(ins);//将文件中的内容都加载到WorkBook中
ins.close();
Sheet sheet = book.getSheetAt(0);//这里的0代表excel中的sheet1
Iterator<Row> rit = sheet.iterator();//迭代出每行的数据
Map<String, String> lineMap;//用于存放读出的数据
while((rit.hasNext()) {
     Row row = rit.next();//获取每行数据
     Cell cell = null;
     for (int i = 0; i <= row.getLastCellNum(); i++) {
   cell = row.getCell(i);//获取每个单元格
   String k = ""; // 用于接收每个单元格的数据。
   if (cell == null) {
    lineMap.put(String.valueOf((i)), k); // 空值的时候。
    continue;
   }
   switch (cell.getCellType()) {//根据单元格中数据的类型进行分析
   case Cell.CELL_TYPE_BLANK:
    k = "";
    break;
   case Cell.CELL_TYPE_ERROR:
    k = Byte.toString(cell.getErrorCellValue());
    break;
   case Cell.CELL_TYPE_STRING:
    k = cell.getRichStringCellValue().getString();
    break;
   case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     k = sdf.format(cell.getDateCellValue());
    }else {
     k = String.valueOf(cell.getNumericCellValue());
     if(k.indexOf("E") != -1){
      k = Long.toString((long)cell.getNumericCellValue());
     }
    }
    break;
   case Cell.CELL_TYPE_BOOLEAN:
    k = Boolean.toString(cell.getBooleanCellValue());
    break;
   case Cell.CELL_TYPE_FORMULA:
    k = cell.getCellFormula();
    break;
   default:
    k = "";
   }
   if ((k != null) && (!"".equals(k))) {