Spring MVC 实现Excel的导入导出功能(1：Excel的导入)
简介
这篇文章主要记录自己学习上传和导出Excel时的一些心得，企业办公系统的开发中，经常会收到这样的需求：批量录入数据、数据报表使用 Excel 打开，或者职能部门同事要打印 Excel 文件，而他们又没有直接操作数据库的能力，这时就需要在某些模块中实现导入、导出 Excel 的功能。
接下来，我们就来看看借助哪些库、工具可以实行上述这些需求。
POI 简介
Apache POI 是 Apache 软件基金会的开放源码函式库，POI 提供了 API，可以帮助 Java 程序实现对 Microsoft Office 格式档案的读写功能。
首先需要了解下 Excel 的文件格式，目前主要有两种格式，即 xls 和 xlsx 格式。 xlsx 是从 Office 2007 版开始使用的，使用新的基于 XML 的压缩文件格式取代了当时专有的默认文件格式，在传统文件扩展名后面添加了字母 x 使其占用空间更小，可以向下兼容 xls ，2007 版本后的 Excel 软件都可以操作 xls 和 xlsx 格式文件，而之前的版本只能打开 xls 格式文件。
针对不同 Excel 文档格式，POI 提供了不同的类来处理。
针对 xls 格式，相应的类有：
- HSSFWorkbook excel 文档对象
- HSSFSheet excel 表格对象
- HSSFRow excel 表格行对象
- HSSFCell excel 单元格对象
- HSSFCellStyle excel 单元格格式
- ……
针对 xlsx 格式，相应的类有：
- XSSFWorkbook excel 文档对象
- XSSFSheet excel 表格对象
- XSSFRow excel 表格行对象
- XSSFCell excel 单元格对象
- XSSFCellStyle excel 单元格格式
- ……
操作 Excel，POI 也提供了相应的方法。
读取 Excel，相应的方法有：
//获取文件流 InputStream is = new FileInputStream(file); //得到Excel工作簿对象 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); //得到Excel工作表对象 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); //得到Excel工作表的指定行对象 XSSFRow xssfRow = xssfSheet.getRow(i); //得到Excel工作表指定行的单元格 XSSFCell xssfCell = xssfRow.getCell(i); //得到单元格样式 XSSFCellStyle xssfCellStyle = xssfCell.getCellStyle();
创建 Excel，相应的方法有：
//创建工作薄
XSSFWorkbook wb = new XSSFWorkbook(); //创建工作表对象 XSSFSheet sheet = wb.createSheet("sheet1"); //创建Excel工作表的行对象 XSSFRow row = sheet.createRow(i); //创建单元格样式 XSSFCellStyle style = wb.createCellStyle(); //创建Excel工作表指定行的单元格 XSSFCell cell = row.createCell(i); //设置Excel单元格的值 cell.setCellStyle(style);
前端实现
导入功能涉及到文件上传，因此需要增加文件上传插件，引入 ajaxupload.js，代码如下：
<!-- ajax upload --> <script src="plugins/ajaxupload/ajaxupload.js"></script>
增加 “ 导入 ” 功能按钮：
<button class="btn btn-file" id="importV1Button"> <i class="fa fa-upload"></i> 导入V1 </button>
导入功能的处理流程是，首先判断上传文件的格式，之后向后端发送请求，后端处理完成后返回结果，前端根据返回结果进行判断，如果错误则给出错误提示，如果正确则提示导入了多少条数据。
new AjaxUpload('#importV1Button', { action: 'users/importV1', name: 'file', autoSubmit: true, responseType: 'json', onSubmit: function (file, extension) { //文件格式限制 if (!(extension && /^(xlsx)$/.test(extension.toLowerCase()))) { alert('只支持xlsx格式的文件！', { icon: "error", }); return false; } }, onComplete: function (file, r) { if (r.resultCode == 200) { //提示用户 alert("成功导入" + r.data + "条记录！"); //列表数据重新加载 reload(); return false; } else { alert(r.message); } } });
前端处理流程可总结为：选择导入文件 -> 文件上传 -> 处理回调信息 -> 重新加载列表数据。
后端逻辑
控制层
在 Controller 层处理文件流，并调用业务层方法进行导入：
/** * <p> * 批量导入用户(直接导入) */ @RequestMapping(value = "/importV1", method = RequestMethod.POST) public Result saveByExcelFileV1(@RequestParam("file") MultipartFile multipartfile) { File file = FileUtil.convertMultipartFileToFile(multipartfile); if (file==null){ return ResultGenerator.genFailResult("导入失败！"); } int i = adminUserService.importUsersByExcelFile(file); if (i > 0) { Result result = ResultGenerator.genSuccessResult(); result.setData(i); return result; } else { return ResultGenerator.genFailResult("导入失败"); } }
FileUtil的工具类
import org.apache.commons.io.FileUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletContext; import javax.servlet.http.HttpServletRequest; import java.io.*; import java.net.URL; import java.net.URLConnection; import java.util.UUID; public class FileUtil { /** * 转换MultipartFile对象为java.io.File类型 * * @param multipartFile * @return */ public static File convertMultipartFileToFile(MultipartFile multipartFile) { File result = null; try { /** * UUID.randomUUID().toString()是javaJDK提供的一个自动生成主键的方法。 * UUID(Universally Unique Identifier)全局唯一标识符,是指在一台机器上生成的数字， * 它保证对在同一时空中的所有机器都是唯一的，是由一个十六位的数字组成,表现出来的形式。 * 由以下几部分的组合：当前日期和时间(UUID的第一个部分与时间有关，如果你在生成一个UUID之后， * 过几秒又生成一个UUID，则第一个部分不同，其余相同)，时钟序列， * 全局唯一的IEEE机器识别号（如果有网卡，从网卡获得，没有网卡以其他方式获得）， * UUID的唯一缺陷在于生成的结果串会比较长。 * * * File.createTempFile和File.createNewFile()的区别： * 后者只是创建文件，而前者可以给文件名加前缀和后缀 */ //这里对生成的文件名加了UUID随机生成的前缀,后缀是null result = File.createTempFile(UUID.randomUUID().toString(), null); multipartFile.transferTo(result); result.deleteOnExit(); } catch (Exception e) { e.printStackTrace(); } return result; } /** * 根据url获取文件对象 * * @param fileUrl * @return */ public static File downloadFile(String fileUrl) { File result = null; try { result = File.createTempFile(UUID.randomUUID().toString(), null); URL url = new URL(fileUrl); URLConnection connection = url.openConnection(); connection.setConnectTimeout(3000); BufferedInputStream bis = new BufferedInputStream(connection.getInputStream()); BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(result)); byte[] car = new byte[1024]; int l = 0; while ((l = bis.read(car)) != -1) { bos.write(car, 0, l); } bis.close(); bos.close(); } catch (Exception e) { e.printStackTrace(); } return result; } /** * @param request * @return */ public static String getRealPath(HttpServletRequest request) { ServletContext sc = request.getSession().getServletContext(); String uploadDir = sc.getRealPath("/upload"); return uploadDir; } public static boolean saveFile(String savePath, String fileFullName, MultipartFile file) throws IOException { File uploadFile = new File(savePath + fileFullName); FileUtils.writeByteArrayToFile(new File(savePath, fileFullName), file.getBytes()); return uploadFile.exists(); } public static String mergeFile(int chunksNumber, String ext, String uploadFolderPath, HttpServletRequest request) { //合并分片流 String mergePath = uploadFolderPath; String destPath = getRealPath(request);// 文件路径 String newName = System.currentTimeMillis() + ext;// 文件新名称 SequenceInputStream s; InputStream s1; try { s1 = new FileInputStream(mergePath + 0 + ext); String tempFilePath; InputStream s2 = new FileInputStream(mergePath + 1 + ext); s = new SequenceInputStream(s1, s2); for (int i = 2; i < chunksNumber; i++) { tempFilePath = mergePath + i + ext; InputStream s3 = new FileInputStream(tempFilePath); s = new SequenceInputStream(s, s3); } //分片文件存储到/upload/chunked目录下 StringBuilder filePath = new StringBuilder(); filePath.append(destPath).append(File.separator).append("chunked").append(File.separator); saveStreamToFile(s, filePath.toString(), newName); // 删除保存分块文件的文件夹 deleteFolder(mergePath); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return newName; } private static boolean deleteFolder(String mergePath) { File dir = new File(mergePath); File[] files = dir.listFiles(); if (files != null) { for (File file : files) { try { file.delete(); } catch (Exception e) { e.printStackTrace(); } } } return dir.delete(); } private static void saveStreamToFile(SequenceInputStream inputStream, String filePath, String newName) throws Exception { File fileDirectory = new File(filePath); synchronized (fileDirectory) { if (!fileDirectory.exists()) { if (!fileDirectory.mkdir()) { throw new Exception("文件夹创建失败,路径为：" + fileDirectory); } } if (!fileDirectory.exists()) { if (!fileDirectory.mkdir()) { throw new Exception("文件夹创建失败,路径为：" + fileDirectory); } } } OutputStream outputStream = new FileOutputStream(filePath + newName); byte[] buffer = new byte[1024]; int len = 0; try { while ((len = inputStream.read(buffer)) != -1) { outputStream.write(buffer, 0, len); outputStream.flush(); } } catch (Exception e) { e.printStackTrace(); throw e; } finally { outputStream.close(); inputStream.close(); } } }
通过调用FileUtil.convertMultipartFileToFile()方法将MultipartFile类型转换成File类型再进行操作.
然后判空操作. 调用业务层里的方法，将转换完的File类型对象传入
业务层
@Override public int importUsersByExcelFile(File file) { XSSFSheet xssfSheet = null; //读取File对象并转换成XSSFSheet类型对象进行处理 try { //表格对象 xssfSheet = PoiUtil.getXSSFSheet(file); } catch (Exception e) { e.printStackTrace(); return 0; } ArrayList<AdminUser> adminUsers = new ArrayList<>(); //第一行是表名称，第二行才是数据，所以从第二行开始读取 for (int i = 1; i <= xssfSheet.getLastRowNum(); i++) { //获取Excel表格指定行的对象 XSSFRow row = xssfSheet.getRow(i); if (row != null) { AdminUser adminUser = new AdminUser(); //获取用户名 XSSFCell userName = row.getCell(0); //获取密码 XSSFCell password = row.getCell(1); //设置用户名 if (!StringUtils.isEmpty(userName)) { adminUser.setUserName(PoiUtil.getValue(userName)); } if (!StringUtils.isEmpty(password)) { adminUser.setPassword(MD5Util.MD5Encode(PoiUtil.getValue(password), "utf-8")); } //用户验证 已存在或者为空则不进行insert操作 if (!StringUtils.isEmpty(adminUser.getUserName()) && !StringUtils.isEmpty(adminUser.getPassword()) && selectusername(adminUser.getUserName()) == null) { adminUsers.add(adminUser); } } } //判空 if (!CollectionUtils.isEmpty(adminUsers)) { //adminUsers用户列表不为空则执行批量添加sql return adminUserDao.addExcel(adminUsers); } return 0; }
Excel类型的工具类
package com.ssm.demo.utils; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.xssf.usermodel.*; import org.springframework.util.StringUtils; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * author:13 * date:2018-07 */ public class PoiUtil { //excel默认宽度； private static int width = 512 * 14; //默认字体 private static String excelfont = "微软雅黑"; /** * @param excelName 导出的EXCEL名字 * @param headers 导出的表格的表头 * @param fileds 导出的数据 map.get(key) 对应的 key * @param formators 导出数据的样式 * @param widths 表格的列宽度 默认为 512 * 14 * @param data 数据集 List<Map> * @param response * @throws IOException */ public static void exportFile(String excelName, String[] headers, String[] fileds, int[] formators, int[] widths, List<Map<String, Object>> data, HttpServletRequest request, HttpServletResponse response) throws IOException { if (widths == null) { widths = new int[fileds.length]; for (int i = 0; i < fileds.length; i++) { widths[i] = width; } } if (formators == null) { formators = new int[fileds.length]; for (int i = 0; i < fileds.length; i++) { formators[i] = 1; } } //设置文件名 String fileName = "导出数据"; if (!StringUtils.isEmpty(excelName)) { fileName = excelName; } //创建工作薄 XSSFWorkbook wb = new XSSFWorkbook(); //创建sheet XSSFSheet sheet = wb.createSheet("sheet1"); //创建表头，没有则跳过此步骤 int headerrow = 0; if (headers != null) { XSSFRow row = sheet.createRow(headerrow); //表头样式 XSSFCellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName(excelfont); font.setFontHeightInPoints((short) 11); style.setFont(font); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); for (int i = 0; i < headers.length; i++) { sheet.setColumnWidth((short) i, (short) widths[i]); XSSFCell cell = row.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(style); } headerrow++; } //表格主体 if (data != null) { List styleList = new ArrayList(); //列数 for (int i = 0; i < fileds.length; i++) { XSSFCellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontName(excelfont); font.setFontHeightInPoints((short) 10); style.setFont(font); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); if (formators[i] == 1) { style.setAlignment(XSSFCellStyle.ALIGN_LEFT); } else if (formators[i] == 2) { style.setAlignment(XSSFCellStyle.ALIGN_CENTER); } else if (formators[i] == 3) { style.setAlignment(XSSFCellStyle.ALIGN_RIGHT); } else if (formators[i] == 4) { //int类型 style.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0")); } else if (formators[i] == 5) { //float类型 style.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); } else if (formators[i] == 6) { //百分比类型 style.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); } styleList.add(style); } for (int i = 0; i < data.size(); i++) { //行数 XSSFRow row = sheet.createRow(headerrow); Map map = data.get(i); for (int j = 0; j < fileds.length; j++) { //列数 XSSFCell cell = row.createCell(j); Object o = map.get(fileds[j]); if (o == null || "".equals(o)) { cell.setCellValue(""); } else if (formators[j] == 4) { //int cell.setCellValue((Long.valueOf((map.get(fileds[j])) + "")).longValue()); } else if (formators[j] == 5 || formators[j] == 6) { //float cell.setCellValue((Double.valueOf((map.get(fileds[j])) + "")).doubleValue()); } else { cell.setCellValue(map.get(fileds[j]) + ""); } cell.setCellStyle((XSSFCellStyle) styleList.get(j)); } headerrow++; } } //文件名+excel格式"xlsx" fileName = fileName + ".xlsx"; String filename = ""; try { filename = encodeChineseDownloadFileName(request, fileName); } catch (Exception e) { e.printStackTrace(); } response.setHeader("Content-disposition", filename); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment;filename=" + filename); response.setHeader("Pragma", "No-cache"); OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); } /** * 对文件流输出下载的中文文件名进行编码以屏蔽各种浏览器版本的差异性 * * @throws UnsupportedEncodingException */ public static String encodeChineseDownloadFileName( HttpServletRequest request, String pFileName) throws Exception { String filename = null; String agent = request.getHeader("USER-AGENT"); if (null != agent) { if (-1 != agent.indexOf("Firefox")) {//Firefox filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?="; } else if (-1 != agent.indexOf("Chrome")) {//Chrome filename = new String(pFileName.getBytes(), "ISO8859-1"); } else {//IE7+ filename = java.net.URLEncoder.encode(pFileName, "UTF-8"); filename = filename.replace("+", "%20"); } } else { filename = pFileName; } return filename; } /** * 获取sheet对象 * * @param file * @return */ public static XSSFSheet getXSSFSheet(File file) { InputStream is = null; XSSFWorkbook xssfWorkbook = null; try { is = new FileInputStream(file); xssfWorkbook = new XSSFWorkbook(is); } catch (IOException e) { return null; } //获取工作表对象 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); return xssfSheet; } /** * 将单元格数据转换为String * * @param cell * @return */ public static String getValue(XSSFCell cell) { String cellValue = ""; if (null != cell) { //判断数据类型，防止报错 switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: // 数字 DecimalFormat df = new DecimalFormat("0"); cellValue = df.format(cell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case XSSFCell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case XSSFCell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case XSSFCell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } } return cellValue; } }
这里首先通过PoiUtil工具类中的getXSSFSheet()方法获取XSSFSheet类型的表格对象。
然后创建一个集合，通过for循环遍历，这里注意循环的时候i的初始值是1。
因为第一行是表名称，第二行才是数据。
通过getRow方法获取指定行的XSSFRow类型的对象。
再通过row.getCell()方法获取第一列和第二列的用户名和密码，如果不为空，就通过PoiUtil.getValue()方法来获取String类型的用户名和密码装入AdminUser类型的对象再装入创建的集合中。
还需要判断一下添加的用户名和数据库是否重复，如果重复则不添加。
如果集合中的数据不为空，则通过持久层调用Mapper操作数据库，将Excel表中的数据批量增加。
持久层
int addExcel(@RequestParam("adminUsers") List<AdminUser> adminUsers);
Mapper
<!--这样批量插入可以返回成功插入的数量--> <insert id="addExcel" parameterType="AdminUser"> insert into tb_admin_user(user_name,password_md5) values <foreach collection="list" index="index" item="adminUser" open="" separator="," close=""> (#{adminUser.userName},#{adminUser.password}) </foreach> </insert>
如果返回的int类型大于0的话，就代表批量插入成功，我们把返回的插入数量返回给前端，在插入完成后给用户一个插入多少条数据的提醒
效果
