Dowemo
0 0 0 0

2 Excel文档导出
response.setContentType("application/octet-stream; charset=utf-8");
 response.setHeader("Content-Disposition",
 "attachment; filename=" + newString(fileName.getBytes("utf-8"), "ISO-8859-1"));2.13 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
 }
 });functionValidateFileType() {var array = newArray();
 //得到上传的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文件
 */functionsubmitFrom() {var options = {
 beforeSubmit : showRequest,
 success : showResponse,
 resetForm : true,
 dataType : 'json' };
 $("#userExcelFileFormId").submit(function() { $(this).ajaxSubmit(options);
 returnfalse;
 });
}functionshowRequest(){}functionshowResponse(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 = newDate();
 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=" + newString(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;
 }
}



Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs