上传Excel文件使用JXL解析

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

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

<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" style="padding-top:10px">                    <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="/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盘，然后就一直报错，害的我折腾了好久才看出来，这个是用来存储上传文件的临时文件夹。

使用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);        }    }

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的方式解析。

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对象，也就是工作薄，可以是一个文件，也可以是一个输入流，

InputStreamis=newFileInputStream(sourcefile);

Workbookrwb=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文件即可。

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

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

/**     * @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.WritableWorkbookwwb=Workbook.createWorkbook(newFile(targetfile));

　　一种是输出流：

1. OutputStreamos=newFileOutputStream(targetfile);
2. jxl.write.WritableWorkbookwwb=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的导入导出的全部内容了，有说的不对的地方欢迎大家指正。