Spring MVC Excel的导入和导出

1、EXCEL的导入

  需要使用包：

1.1、Action

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)@ResponseBodypublic void importExcel(@ModelAttribute("javaBeanName") JavaBeanName m, @RequestParam("file") MultipartFile file) {    try {        attachmentService.saveAttachmentImport(file);    } catch (Exception e) {        e.printStackTrace();   //作其他异常处理（友好提示，根据框架来选择）    }    //可返回页面或是json数据提示导入成功}

1.2、serviceImpl

1.2.1

@Overridepublic void saveImport(MultipartFile file) throws IOException {//获取excel中数据，如1.2.2    List<List<Object>> list = this.getExcelData(file);    //1、转换excel的值    for (int i = 0; i < list.size(); i++) {        List<Object> lo = list.get(i);        JavaBeanName javaBeanName = new JavaBeanName();        javaBeanName.setFileName(String.valueOf(lo.get(0)));        javaBeanName.setFileExtension(String.valueOf(lo.get(1)));        //用来测试        System.out.println("导入信息" + i + "-->文件名称:" + javaBeanName.getFileName() + "  类型：" + javaBeanName.getFileExtension());    }    //2、校验，返回错误信息    // 3、保存到数据库}

1.2.2

/** * 获取excel中的数据 * * @param file * @return * @throws IOException */private List<List<Object>> getExcelData(MultipartFile file) throws IOException {    InputStream in = file.getInputStream();    Workbook work = new HSSFWorkbook(in);    List<List<Object>> list = new ArrayList<List<Object>>();    ;    Sheet sheet = null;    Row row = null;    Cell cell = null;    //遍历Excel中所有的sheet    for (int i = 0; i < work.getNumberOfSheets(); i++) {        sheet = work.getSheetAt(i);        if (sheet == null) {            continue;        }        //遍历当前sheet中的所有行，可以控制从第几行开始获取值        for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {            row = sheet.getRow(j);            //||row.getFirstCellNum()==j            if (row == null) {                continue;            }            //遍历所有的列            List<Object> li = new ArrayList<Object>();            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {                cell = row.getCell(y);//转换excel中数据格式,如1.2.3                li.add(this.getCellValue(cell));            }            list.add(li);        }    }    work.close();    in.close();    return list;}

1.2.3

/** * 转换excel中数据格式 * * @param cell * @return */private Object getCellValue(Cell cell) {    Object value = null;    DecimalFormat df = new DecimalFormat("0");  //格式化number String字符    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字    switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING:            value = cell.getRichStringCellValue().getString();            break;        case Cell.CELL_TYPE_NUMERIC:            if ("General".equals(cell.getCellStyle().getDataFormatString())) {                value = df.format(cell.getNumericCellValue());            } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {                value = sdf.format(cell.getDateCellValue());            } else {                value = df2.format(cell.getNumericCellValue());            }            break;        case Cell.CELL_TYPE_BOOLEAN:            value = cell.getBooleanCellValue();            break;        case Cell.CELL_TYPE_BLANK:            value = "";            break;        default:            break;    }    return value;}

2、EXCEL导出

2.1、Action

/** * excel导出 * @param m * @return */@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)@ResponseBodypublic void exportExcel(@ModelAttribute("JavaBeanName") JavaBeanName m, HttpServletResponse response) {    try {        OutputStream os = null;        HSSFWorkbook hssfWorkbook= javaBeanNameService.getExportExcel();        os = response.getOutputStream();                     response.reset();        //设置导出的文件名                   response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode("附件信息.xls", "UTF-8"));        response.setContentType("application/octet-streem");        hssfWorkbook.write(os);    } catch (Exception e) {        e.printStackTrace();       //处理异常信息    }}

2.2、封装EXCEL数据

/**     * 封装excel数据     * @return     */    @Override    public HSSFWorkbook getExportExcel() {        HSSFWorkbook book = new HSSFWorkbook();        //设置工作表名称        HSSFSheet sheet = book.createSheet("Sheet1");        sheet.autoSizeColumn(1, true);//自适应列宽度        //样式设置        HSSFCellStyle style = book.createCellStyle();        //颜色        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 生成一个字体        HSSFFont font = book.createFont();        font.setColor(HSSFColor.VIOLET.index);        font.setFontHeightInPoints((short) 12);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        // 把字体应用到当前的样式        style.setFont(font);        HSSFCellStyle style2 = book.createCellStyle();        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        //设置上下左右边框        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);        //TODO 正式使用时从数据库中获取数据        List<JavaBeanName>  list=new ArrayList<JavaBeanName>();        JavaBeanName javaBeanName1=new JavaBeanName();        javaBeanName1.setFileName("文件名称1");        javaBeanName1.setFileExtension("文件类型1");        javaBeanName1.setUpdateTime(new Date());        list.add(javaBeanName1);        JavaBeanName javaBeanName2=new JavaBeanName();        javaBeanName2.setFileName("文件名称2");        javaBeanName2.setFileExtension("文件类型2");        javaBeanName2.setUpdateTime(new Date());        list.add(javaBeanName2);        //填充表头标题        int colSize = list.size();        System.out.println("size:" + colSize);        //合并单元格供标题使用(表名)        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));        HSSFRow firstRow = sheet.createRow(0);//第几行（从0开始）        HSSFCell firstCell = firstRow.createCell(0);        firstCell.setCellValue("附件信息表");        firstCell.setCellStyle(style);        //填充表头header        HSSFRow row = sheet.createRow(1);        HSSFCell cell = row.createCell(0);        cell.setCellValue("附件名称");        cell.setCellStyle(style2);        HSSFCell cell2 = row.createCell(1);        cell2.setCellValue("附件类型");        cell2.setCellStyle(style2);        HSSFCell cell3= row.createCell(2);        cell3.setCellValue("上传时间");        cell3.setCellStyle(style2);        //填充表格内容        System.out.println("list:" + list.size());        for(int i=0; i<list.size(); i++) {            HSSFRow row2 = sheet.createRow(i+2);//index：第几行             for(int j=0;j<3;j++) {                 HSSFCell cell4 = row2.createCell(j);//第几列：从0开始                 if(j==0) {                     cell4.setCellValue(list.get(i).getFileName());                     cell4.setCellStyle(style2);                 }                 if(j==1) {                     cell4.setCellValue(list.get(i).getFileExtension());                     cell4.setCellStyle(style2);                 }                 if(j==3) {                     cell4.setCellValue(list.get(i).getUpdateTime());                     cell4.setCellStyle(style2);                 }             }        }        return book;}

3、JSP页面

<form:form id="form" cssClass="form" commandName="m"           action="${ctx}/xxx/xxxAction/add" acceptCharset="multipart/form-data"           method="post">    <table align="center">        <tr>            <td>导入文件</td>            <td>                <input name="file" id="file" class="easyui-filebox" data-options="prompt:'文件上传',buttonText:'选择文件'"                      />            </td>        </tr>        <tr>            <td></td>            <td> <input type="button" value="导入" onclick="importFile()"/></td>            <td> <input type="button" value="导出" onclick="exportExcel()"/></td>        </tr>    </table></form:form><script type="text/javascript">        /**     * 导入     * @returns {boolean}     */    function importFile(){        var formData = new FormData($('#form')[0]);        var file = formData.get("file");        if (file.size == 0) {            $.messager.alert("请选择文件！");            return false;        }        $.ajaxform({            url: ctx + "/xxx/xxxAction/importExcel",            data: formData,            processData: false,//不处理发送的数据            contentType: false,//不要设置Content-Type请求头            success: function (responseStr) {                if (responseStr.code == "0") {                    $.messager.alert(responseStr.message);                    $grid.datagrid("reload");                } else {                    $.messager.alert(responseStr.message);                }            }        });    }    /**     * 导出     */    function exportExcel() {        var form = $("<form>");   //定义一个form表单        form.attr('style', 'display:none');   //在form表单中添加查询参数        form.attr('target', '');        form.attr('method', 'post');        form.attr('action', ctx + "/xxx/xxxAction/exportExcel");        $('body').append(form);  //将表单放置在web中        form.submit();    }</script>