Excel文档导出和导入

2 Excel文档导出response.setContentType("application/octet-stream; charset=utf-8");            response.setHeader("Content-Disposition",                    "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));2.1  3 Excel文档导入3.1  界面:var htmlStr = "";    htmlStr += '<div style="width:600px;" >';       htmlStr += '<div style="display:block; padding-bottom:20px;" align="center" >';    htmlStr += '<form  id = "userExcelFileFormId" action="/SSMPro/uploadExcel" method="post" >';    htmlStr += '<input type="file" id="userExcelFile" name="userExcelFile"  onchange="ValidateFileType()" >';    htmlStr += ' <input type="submit"  value="导入"  />  ';    htmlStr += '</form>';    htmlStr += '</table>';    htmlStr += '</div>';    htmlStr += '</div>';    $.layer({        type : 1,        title : '导入用户',        area : [ 'auto', 'auto' ],        page : {            html : htmlStr        }    });function ValidateFileType() {    var array = new Array();    // 得到上传的Excel表格的名称    var excelName = $("#userExcelFile").val();    // 拆分    array = excelName.split(".");    var suffix = array[array.length - 1];    if (suffix != "xlsx" && suffix != "xls") {        alert("您选择的不是excel文档，请重新选择");        var file = document.getElementById("userExcelFile");        file.value = "";    }}/** * 使用ajax提交表单，要引入jquryForm的js文件 */function submitFrom() {    var options = {        beforeSubmit : showRequest,        success : showResponse,        resetForm : true,        dataType : 'json'    };    $("#userExcelFileFormId").submit(function() {        $(this).ajaxSubmit(options);        return false;    });}function showRequest(){}function showResponse(responseText) {    if (responseText.isSuccess == true) {        alert('导入成功');        window.location = "/SSMProject/user/userMana";    }}@RequestMapping("/uploadExcel")    public void uploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {        Map<String, Object> resultMap = new HashMap<>();        try {            // 把request对象转换成Spring的request对象            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;            // 获取上传的文件列表            Map<String, MultipartFile> fileMap = multiRequest.getFileMap();            // 遍历            for (Map.Entry<String, MultipartFile> entry : fileMap.entrySet()) {                // 获取上传的excel文件                MultipartFile file = entry.getValue();                // 得到文件输入流                InputStream inputStream = file.getInputStream();                // 创建excel文档对象                Workbook workbook = WorkbookFactory.create(inputStream);                // 读取文档的内容                List<Map<String, Object>> list = this.readExcel(workbook);                // 把读取到的内容插入到数据库中                userService.insertData(list);                // 返回标识                resultMap.put("isSuccess", true);            }        } catch (Exception e) {            e.printStackTrace();            resultMap.put("isSuccess", false);        }        Gson gson = new Gson();        String responseContent = gson.toJson(resultMap);        this.flushResponse(response, responseContent);    }    /**     * 读取sheet中的内容     * @param workbook     * @return     */    private List<Map<String, Object>> readExcel(Workbook workbook) {        // 得到SimpleReadParameter对象，封装了插入的字段名和读取文档的下标        SimpleReadParameter srp = this.getSimpleReadParameter();        String[] fieldNames = srp.getFieldsId();        int startIndex = srp.getStartIndex();        ExcelUtil excelUtil = new ExcelUtil();        List<Map<String, Object>> list = new ArrayList<>();        // 判断是否有工作单元        if (workbook != null && workbook.getNumberOfSheets() > 0) {            // 得到sheet对象            Sheet sheet = workbook.getSheetAt(0);            // 通过工具类来读取sheet中的内容            List<Map<String, Object>> sheetData = excelUtil.readSimple(sheet, startIndex, fieldNames);            if (sheetData != null && sheetData.size() > 0) {                list.addAll(sheetData);            }        }        return list;    }    /**     * 得到要插入到数据库的字段名和起始下标     *      * @return     */    private SimpleReadParameter getSimpleReadParameter() {        SimpleReadParameter simpleReadParameter = new SimpleReadParameter();        StringBuffer sbBuffer = new StringBuffer();        sbBuffer.append("userChName,").append("mobilePhone,").append("email,").append("userSex,").append("userName,")                .append("orgId,");        String[] filedNames = sbBuffer.toString().split(",");        int startIndex = 2;        simpleReadParameter.setFieldsId(filedNames);        simpleReadParameter.setStartIndex(startIndex);        return simpleReadParameter;    }}导出@RequestMapping("/exportUserExcel")    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {        String fileName = "";        // 获取传过来的参数        Map<String, Object> param = this.getParam(request);        Object fileNameObj = param.get("fileName");        // 创建一个默认的日期来拼接文件名        Date date = new Date();        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        String defaultDate = sdf.format(date);        if (fileNameObj != null && !("").equals(fileNameObj.toString())) {            String fileChName = fileNameObj.toString();            // 截取文件名            if (fileChName.endsWith(".xls") || fileChName.endsWith(".xlsx")) {                fileChName = fileChName.substring(0, fileChName.lastIndexOf("."));            }            fileName = fileChName + defaultDate;        } else {            fileName = defaultDate;        }        // 统一确定后缀        fileName = fileName + ".xls";        OutputStream outputStream = null;        try {            // 得到一个输出流            outputStream = response.getOutputStream();            // 创建一个excel文档对象            Workbook wb = new HSSFWorkbook();            // 设置响应头            // Content-Disposition 的作用，当Content-Type 的类型为要下载的类型时 ,            // 这个信息头会告诉浏览器这个文件的名字或类型。            response.setContentType("application/octet-stream; charset=utf-8");            response.setHeader("Content-Disposition",                    "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));            // Excel导出操作            this.exprotUserExcel(wb);            // 把文档对象输出            wb.write(outputStream);            outputStream.flush();        } catch (Exception e) {            e.printStackTrace();            if (outputStream != null) {                outputStream.close();            }        }    }    // Excel导出操作    private void exprotUserExcel(Workbook wb) {        // 得到输入excel文档数据的对象        SimpleExportParameter parameters = this.getSimpleExportParamter();        // 创建sheet对象        Sheet sheet = wb.createSheet();        ExcelUtil util = new ExcelUtil();        // 填充excel文档内容        util.simpleExport(wb, sheet, parameters);    }    //填充SimpleExportParameter对象    private SimpleExportParameter getSimpleExportParamter() {        // 从数据库查出的属性要与下面的属性对应（用“,”分隔，先后顺序与数据库查出结果的顺序一致）        String filedIds = "userChName,userSex,mobilePhone,provinceName,cityName,contryName,userBirthday";        // 中文名称        String filedName = "姓名,性别,电话,省份,地市,区县,生日";        // 列宽        String widthsStr = "20,20,20,20,20,20,20";        // 从数据库中查询数据        List<Map<String, Object>> dataList = userService.queryExprotData();        // 设置标题        String title = "千锋员工信息";        // 设置sheet名称        String sheetName = "员工sheet";        String[] ids = filedIds.split(",");        String[] names = filedName.split(",");        String[] widths = widthsStr.split(",");        // 给对象赋值        SimpleExportParameter sep = new SimpleExportParameter();        sep.setTitle(title);        sep.setTitleEn(sheetName);        sep.setFieldsId(ids);        sep.setFieldsName(names);        sep.setWidths(widths);        sep.setDataList(dataList);        return sep;    }}