你的位置:首页 > Java教程

[Java教程]上传Excel文件使用JXL解析

  继续昨天的说,昨天说到用fullcalendar日期控件来显示日程,现在有一个需求是读取Excel中的日程信息,然后显示在日历上,继续记录备忘。

一、上传文件

  上传文件也困惑了我很久,今天一起记录一下。项目框架是SSH的,所以上传文件就使用了struts2的fileupload,所需要的jar包都在引入struts2的时候引入了,然后就是直接上代码操作了。

1.1 页面

<form id="excelform" action="。。。。。。。。。" method="post" enctype="multipart/form-data">      <div class='excel-btn'>        <!-- File upload field -->        <div class="input-group input-group-sm">          <input id="source" type="file" name="excel" class="form-control" accept=".xlsx,.xls"/>          <span class="input-group-btn">            <button id="import" type="submit" class="btn btn-primary">              上传&nbsp;<i class="glyphicon glyphicon-upload"></i>            </button>          </span>        </div>        <!-- export agenda event field -->        <div class="text-right" >          <div class="btn-group btn-group-sm">            <button id="export" type="button" class="btn btn-warning">              导出日程&nbsp;<i class="glyphicon glyphicon-share"></i>            </button>            <a class="btn btn-success" href="http://www.cnblogs.com//wldproject/model/events.xls">              模版下载&nbsp;<i class="glyphicon glyphicon-download"></i>            </a>          </div>        </div>      </div>    </form>

上传文件的form必不可少的就是enctype="multipart/form-data",文件域中的accept=".xlsx,.xls"表示接收上传的文件类型,当然也可以在struts2的拦截器里面设置上传文件的大小、上传文件的类型等信息,我这里使用的是另一种方式:

<!-- 指定允许上传的文件最大字节数。默认值是2097152(2M) -->  <constant name="struts.multipart.maxSize" value="1048576"/>  <!-- 设置上传文件的临时文件夹,默认使用javax.servlet.context.tempdir -->  <constant name="struts.multipart.saveDir " value="d:/tmp" />

文件接收类型在文件域中设置,允许上传文件的大小在struts2的配置文件中直接使用constant设置了,上面我标红的代码要注意一下,上次我在本地设置的D盘,但是放到服务器上的时候,服务器只有C盘没有D盘,然后就一直报错,害的我折腾了好久才看出来,这个是用来存储上传文件的临时文件夹。

1.2 JS提交表单

使用ajaxForm的方式提交表单,因此要引入jquery和jquery  form的js文件

//提交表单  $("#excelform").ajaxForm({    beforeSubmit: showRequest, //表单验证    success: showResponse //成功返回  });function showRequest(){    var filename = $("#source").val();    if(filename == null || filename == ''){      alert("请选择文件...");      $("#source").focus();      return false;    }    $("#excelform").attr("action", "。。。。。。");  }  function showResponse(responseText, statusText){    if(statusText=="success") {        if(responseText == "1") {        alert("Excel文件导入成功");        //重新获取所有事件数据        $('#calendar').fullCalendar('refetchEvents');      } else {        alert(responseText);      }    } else {      alert(statusText);    }  }

1.3 后台实现

private File excel;  private String excelContentType;  private String excelFileName;

fileupload上传文件时,先接收上面的三个参数,File 的名称要跟文件域的name属性一致,文件名称和文件类型前面要加上文件域的name属性。

public String importEvent() throws IOException {    // 获取文件存储路径     // get the path to save the file    String path = ServletActionContext.getRequest().getRealPath("/WEB-INF/upload");        path += FileUtil.getPath();// child path    // 获取文件存储名称    // get the name save to    String name = FileUtil.getName(excelFileName);    // upload the file and return the target file object    File file = FileUtil.upload(excel, path, name);

  在获取文件存储路径这里,我更喜欢使用String path = request.getSession().getServletContext().getRealPath("/WEB-INF/upload");因为ServletActionContext.getRequest().getRealPath("/WEB-INF/upload");现在已经不推荐使用了。

  为了读取时方便,因此当天上传的文件放在upload文件夹下面的以当天日期命名的文件夹中,为了避免重复,以当前日期时间对当前文件进行重命名。

public static String getPath(){    Date date = new Date();    sdf.applyPattern("/yyyy-MM-dd");    return sdf.format(date);  }public static String getName(String fileName){    Date date = new Date();    sdf.applyPattern("HH-mm-ss");    return sdf.format(date) + getSuffix(fileName);  }  /**   * @param fileName   * @return   */  public static String getSuffix(String fileName){    int dotIndex = fileName.lastIndexOf('.');    return fileName.substring(dotIndex);  }

  因为主要目的是解析上传的Excel文件,因此,上传文件之后返回该文件进行解析,具体上传步骤:

/**   * @param source   * @param dest   * @return   */  public static File upload(File src, String path, String name) {    File directory = new File(path);    if(!directory.exists()){      directory.mkdirs();    }    File dest = new File(path, name);    if(upload(src, dest)){      return dest;    }    return null;  }/**   * @param src   * @param dest   */  public static boolean upload(File src, File dest) {    BufferedInputStream bis = null;    BufferedOutputStream bos = null;    byte[] buf = new byte[1024];    int len = 0;    try {      bis = new BufferedInputStream(new FileInputStream(src));      bos = new BufferedOutputStream(new FileOutputStream(dest));      while (((len = bis.read(buf)) != -1)) {        bos.write(buf, 0, len);      }    } catch (Exception e) {      e.printStackTrace();      return false;    } finally {      try {        if (bos != null) {          bos.close();        }        if (bis != null) {          bis.close();        }      } catch (Exception e) {        bos = null;        bis = null;      }    }    return true;  }

上面就是文件上传的过程,上传成功后对该文件进行解析,解析Excel使用的JXL的方式,也可以使用POI的方式解析。

二、JXL解析EXCEL

public boolean importEvent(File file) {        ExcelUtil excel = new ExcelUtil();    ExcelContent eContent = excel.getFromExcel(file);    if(eContent == null){      return false;    }    List<Agenda> alist = agendaDAO.getFromExcelContent(eContent);    return agendaDAO.batchSave(alist);  }

EXCEL表格可以理解为一个二维数组,因此使用List套List的方式来存储读取出来的内容;

public class ExcelContent {  private List<String> title;//标题  private List<List<String>> content;//内容

从excel文件中读取信息存储到ExcelContent 中:

/**   * get contents from a excel file   * @param file----the excel file path   * @param hasTitle   * @return   */  public ExcelContent getFromExcel(File file) {    Workbook rwb = null;    ExcelContent eContent = new ExcelContent();    List<List<String>> datas = new ArrayList<List<String>>();    try {      rwb = Workbook.getWorkbook(file);      Sheet sheet = rwb.getSheet(0);// deploy the first sheet      int rows = sheet.getRows();      // start to loop and get the datas      for (int index = 0; index < rows; index++) {        Cell[] cells = sheet.getRow(index);        List<String> row = new ArrayList<String>();        for (Cell cell : cells) {          row.add(getContent(cell));        }        if(index == 0){// title banner          eContent.setTitle(row);        } else {          datas.add(row);        }      }      eContent.setContent(datas);    } catch (Exception e) {      return null;    }    return eContent;  }

首先需要构建一个workbook对象,也就是工作薄,可以是一个文件,也可以是一个输入流,

InputStream is = new FileInputStream(sourcefile);  

Workbook rwb = Workbook.getWorkbook(is); 

  获取到工作薄之后就是获取到工作表了,也就是sheet,这里只有一个工作表,所以使用了Sheet sheet = rwb.getSheet(0);如果一个工作薄里面有多个工作表,那么可以使用

Sheet[] sheets = rwb.getSheets();然后循环对每个sheet进行操作即可,int sheets = rwb.getNumberOfSheets();可以获取到sheet的数量。

  获取到sheet之后就可以对一个工作表进行操作了,int rows = sheet.getRows();表示获取到该sheet中的行数,int rsColumns = rs.getColumns();表示获取到总列数;

知道总行数之后循环取出每一行的数据 Cell[] cells = sheet.getRow(index);表示取出第index行的数据,取数据的时候,由于EXCEL表格中存在日期格式的,因此要对数据进行简单的处理:

/**   * excel format   * @param cell   * @return   */  private String getContent(Cell cell){    CellType type = cell.getType();    if(type == CellType.DATE){      DateCell c = (DateCell) cell;      return sdf.format(c.getDate());    }    return cell.getContents();  }

取出的第一行数据为标题,后面的为正式的数据,如果没有标题,那就不需要处理标题了。取出excel中的数据后,将其放在实现准备好的eContent 对象中返回,之后再从eContent 取出数据,存入数据库。

/**   * attention: no id include!!!   * convert the Excel content to agenda objects without id included   * @param eContent----the Excel content   * @return a list of agenda objects   */  public List<Agenda> getFromExcelContent(ExcelContent eContent){        List<String> title = eContent.getTitle();// excel title    List<List<String>> contents = eContent.getContent();// excel rows        List<Agenda> aList = new ArrayList<Agenda>();        int len = title.size();    // loop the all excel content    for(List<String> row : contents){      Agenda agenda = new Agenda();      for(int i = 0; i < len; i++){        String cell = row.get(i);        String field = title.get(i);        if(field.equalsIgnoreCase("title")){// title field          agenda.setTitle(cell.trim());        } else if(field.equalsIgnoreCase("allday")){// all day field          if(cell.matches("[yY1]")){            agenda.setAllDay(true);          } else if(cell.matches("[nN0]")){            agenda.setAllDay(false);          }        } else if(field.equalsIgnoreCase("starttime")){// start time field          if(!StringUtil.isSpace(cell)){            agenda.setStart(DateUtil.parse2Date(cell, format));          }        } else if(field.equalsIgnoreCase("endtime")){// end time field          if(!StringUtil.isSpace(cell)){            agenda.setEnd(DateUtil.parse2Date(cell, format));          }        } else if(field.equalsIgnoreCase("color")){// color field          agenda.setColor(cell.trim());        } else if(field.equalsIgnoreCase("user")){// user field          agenda.setUser(cell.trim());        } else if(field.equalsIgnoreCase("supporter")){// supporter field          agenda.setSupporter(cell.trim());        }      }      aList.add(agenda);    }    return aList;  }

这里面唯一要说的就是starttime和endtime,在excel文件中,这两个数值为时间戳,因此到这里之后需要对时间戳进行处理,转换成时间之后才能存入数据库;

 public static Date timeStamp2Date(String seconds,String format) {       if(seconds == null || seconds.isEmpty() || seconds.equals("null")){         return null;       }       if(format == null || format.isEmpty()) format = "yyyy-MM-dd HH:mm:ss";       SimpleDateFormat sdf = new SimpleDateFormat(format);             String str = sdf.format(new Date(Long.valueOf(seconds+"000")));      return parse2Date(str,format);     } 

返回的alist在持久化层进行批量存储即可,这样读取EXCEL就完成了。

三、导出EXCEL

  页面在上传文件的时候已经给出了,导出启程就是查询数据库的日程,然后导出为一个excel文件即可。

3.1 JS实现

//export agenda  $("#export").click(function(){    $("#excelform").attr("action", "。。。。。。");    document.forms[0].submit();  });

将上面上传的form的action改成导出的action,提交表单即可。

3.2 后台实现

/**   * @return   * @throws IOException   */  public String exportEvent() throws IOException {    // start to output    response.addHeader("Content-Disposition", "attachment;filename=events.xls");    response.setContentType("application/octet-stream");    ServletOutputStream ss = response.getOutputStream();    OutputStream stream = new BufferedOutputStream(ss);    boolean success = excelServ.exportEvent(stream);    if(!success){      response.reset();      response.setContentType("text/plain");      PrintWriter out = response.getWriter();      out.print("failed");      out.flush();      out.close();    }    return null;  }

  response.addHeader("Content-Disposition", "attachment;filename=events.xls"); response.setContentType("application/octet-stream");这两句是用户点击下载按钮时,可以弹出提示框用户可以选择直接打开还是下载,牵扯到http协议的一些东西,我也不是太懂,只知道是这么写,但是太具体的我就不知道了,大家感兴趣的可以自己了解一下。

  下载设置好之后就是读取数据库数据,转换成excel格式了。

/**   * export events to Excel file   * @return   */  public boolean exportEvent(OutputStream os) {    List<Agenda> alist = agendaDAO.findAll();    if(alist == null || alist.size() == 0){      return false;    }    List<List<String>> content = new ArrayList<List<String>>();    for(Agenda agenda : alist){      // add the agenda property to a String row      List<String> row = new ArrayList<String>();      row = agenda.toListString();      content.add(row);    }    ExcelUtil excel = new ExcelUtil();    excel.exportToExcel(os, Agenda.getPrintHead(), content);    return true;  }

  前面说过excel数据就是一个二维数组,因此,可以先将查询出的日程列表进行处理,转换成List<List<String>>形式,为了实现这种功能,我在agenda中添加了toListString()方法:

 

/**   * used to convert the Agenda object to String list   * @return list of string array stands for every filed   */  public List<String> toListString(){    // add the agenda property to a String row    List<String> row = new ArrayList<String>();    row.add(String.valueOf(id));    row.add(title);        String format = "yyyy-MM-dd";    if(!allDay){      format = "yyyy-MM-dd HH:mm";    }    row.add(DateUtil.parse2String(start, format));    row.add(DateUtil.parse2String(end, format));    row.add(StringUtil.bool2String(allDay));    row.add(color);    row.add(this.user + " ");    row.add(this.supporter + " ");    return row;  }

 

返回一个String类型的list集合,添加到content中,之后再获取到要导出的数据的标题,也在agenda中实现:

/**   * @return the String array used to export the agenda object to excel   */  public static String[] getPrintHead(){    return new String[]{"ID", "title", "starttime", "endtime", "allday", "color", "user", "supporter"};  }

这两个处理完成之后,再加上输出流即可开始导出excel文件:

/**   * export to excel   * @param os----the output stream of excel file to save   * @param title----the array of the title banner   * @param content----a array list of the data to save   * @return   */  public void exportToExcel(OutputStream os, String[] title, List<List<String>> content) {    WritableWorkbook workbook = null;//create the excel    WritableSheet sheet = null;//create excel sheet    // start    try {      workbook = Workbook.createWorkbook(os);      sheet = workbook.createSheet("sheet1", 0);      int rowNum = 0;      // whether the title include in the source file      if (title != null && title.length != 0) {        /********** format the excel cell *************/        WritableCellFormat title_style = cellFormat.getCellFormat(ExcelCellFormat.TITLE_CENTER);        for (int i = 0; i < title.length; i++) {          sheet.addCell(new Label(i, 0, title[i], title_style));        }        rowNum++;      }      WritableCellFormat text_style = cellFormat.getCellFormat(ExcelCellFormat.TEXT_LEFT);      for (List<String> rows : content) {        int colNum = 0;        for (String obj : rows) {          if (obj == null) {            obj = "";          }          Label la = new Label(colNum, rowNum, obj,text_style);          sheet.addCell(la);          colNum++;        }        rowNum++;      }      workbook.write();// write the content to the file stream    } catch (Exception e) {      e.printStackTrace();    } finally {// close      try {        if (workbook != null) {          workbook.close();        }      } catch (Exception e) {        e.printStackTrace();      }    }  }

  与读取excel文件类似,首先要使用workbook类的工厂方法创建一个可写入的工作薄,这里要注意的是,只能通过 API提供的工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,因为类WritableWorkbook的构造函 数为protected类型。

  创建可写入的工作薄有两种方法,一种是file:

   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile)); 

  一种是输出流:

  1.  OutputStream os = new FileOutputStream(targetfile); 
  2.     jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

 

 

本文使用输出流的方式进行导出,工作薄创建完成之后就需要创建工作表,使用sheet = workbook.createSheet("sheet1", 0);创建工作表,两个参数分别表示工作表的名称和工作表在工作薄中的位置。

工作薄和工作表设置好之后就是对内容进行设置了,jxl提供对单元格及其单元格中的内容进行设置的方式,比如设置字体、设置字体颜色等等。

public class ExcelCellFormat {    public static int TITLE_CENTER = 0;  public static int TEXT_LEFT = 1;  public static int CELLFORMATE_TEXT_RIGHT = 2;    public WritableCellFormat getCellFormat(int type) throws WriteException {    WritableCellFormat cellFormat = null;    if (TITLE_CENTER == type) {// 用于标题居中      WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);      cellFormat = new WritableCellFormat(BoldFont);      cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);      cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 用于文字垂直      cellFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐      cellFormat.setWrap(false); // 文字是否换行    } else if (TEXT_LEFT == type) {// 用于正文居左      WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);      cellFormat = new WritableCellFormat(NormalFont);      cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条      cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐      cellFormat.setAlignment(Alignment.LEFT); // 文字水平对齐      cellFormat.setWrap(false); // 文字是否换行    } else if (CELLFORMATE_TEXT_RIGHT == type) {// 用于正文居左      WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);      cellFormat = new WritableCellFormat(NormalFont);      cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条      cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐      cellFormat.setAlignment(Alignment.RIGHT); // 文字水平对齐      cellFormat.setWrap(false); // 文字是否换行    }    return cellFormat;  }}

上面的内容表示对单元格中内容的一些设置,其他的设置大家可以查查API就可以了。

 

  上面就是使用JXL进行excel的导入导出的全部内容了,有说的不对的地方欢迎大家指正。