接手过不少后台管理系统几乎每个系统都会碰到一个特别常见的需求把Excel里的数据批量导入MySQL或者反过来把数据库的表导成Excel给运营下载。这类需求听起来不复杂但真动手做的时候各有各的坑。今天想聊聊我最近梳理的一套组合方案前端用WebUploader做上传后端用JSPServlet接收文件解析Excel用Apache POI数据最终落到MySQL完整覆盖导入导出的整个闭环。这篇内容围绕实际项目里的真实操作展开既有前端上传组件的完整配置也有后端Servlet接收文件、POI解析单元格、批量入库、以及导出Excel的完整代码片段。适合刚接触JSPServlet开发、或者想在老项目里快速加一个“Excel导入导出”功能的同学参考。我会把每一步为什么这么做、参数怎么定、有哪些坑都说明白尽量做到看完就能动手。1. 技术选型与整体设计思路1.1 为什么选用WebUploader做文件上传做文件上传原生input typefile配合表单提交其实也能用但体验和可控性都比较差。没有进度条、不能限制类型、大文件上传时页面要等很久、用户误操作也不好取消。ajax上传能改善一些但队列管理、并发控制还是得自己写。WebUploader在这类场景里很合适它是一个基于HTML5的上传组件同时保留了Flash兼容降级方案支持文件队列、分片上传、并发控制、进度回调和断点续传。这个组合方案里前端只承担“把Excel文件安全可靠地传到后端”这件事。WebUploader的价值在于自带UI交互选择文件、显示文件名、上传进度、成功失败状态都有现成钩子。支持chunked分片上传但Excel文件通常只有几MB一般不需要分片做上传限流和格式校验更重要。提供beforeFileQueued、uploadSuccess、uploadError等事件方便在关键节点插入自定义逻辑。如果项目本身有现成的vue或react环境也可以用别的上传库但JSP老项目里WebUploader几乎是零成本接入的方案。直接引入静态资源、初始化一个uploader实例就能跑通。1.2 Apache POI在Excel解析中的地位Excel文件解析后端可选方案不少常见的有Apache POIAlibaba EasyExcel直接读CSVEasyExcel其实是基于POI做的封装它在读大文件时有更好的内存控制能力但POI的功能更全面操作Workbook、Sheet、单元格、样式、公式、合并单元格、数据透视等都能覆盖而且文档丰富、社区成熟。如果业务里要频繁处理自定义模板、要写复杂表头、要对导出文件做样式控制POI是更稳的选择。POI里针对Excel的组件需要拆开说清楚组件适用格式说明HSSFWorkbook.xls上限65536行老格式兼容性好XSSFWorkbook.xlsx行数上限大幅提升但大文件吃内存SXSSFWorkbook.xlsx基于XSSF做了滑动窗口适合导出大数据量导入解析时用HSSFWorkbook或XSSFWorkbook按文件类型创建导出时如果数据量几千行用XSSFWorkbook没问题几万行以上必须考虑SXSSFWorkbook。这个选型背后是内存的取舍后面会展开讲。1.3 导入导出的整体数据流设计整套功能的数据流并不复杂但每个节点都需要明确职责导入链路WebUploader选择Excel文件 - 上传到UploadServlet - Servlet保存临时文件 - POI解析Workbook - 逐行校验字段 - JDBC批量写入MySQL - 返回导入结果JSON导出链路前端发起下载请求 - ExportServlet接收查询条件 - 查询MySQL数据 - POI生成Workbook - 写入响应输出流 - 浏览器下载文件设计层需要提前定几个关键决策临时文件保存到哪个目录、解析后是否删除、校验失败如何反馈、大数据量怎么分批、导出文件名怎么编码。这些问题看起来小但都是实际线上项目最容易出故障的点下面每个环节都会给出对应的落地方案。2. 环境准备与项目基础搭建2.1 Maven依赖与版本搭配我用的是Maven项目JSPServlet 3.0以上环境。核心依赖不多但版本之间要注意兼容性。POI 4.x和5.x的API差异不大5.x已经是很成熟的版本。下面是一份可用的pom.xml依赖片段properties maven.compiler.source1.8/maven.compiler.source maven.compiler.target1.8/maven.compiler.target /properties dependencies !-- Servlet API -- dependency groupIdjavax.servlet/groupId artifactIdjavax.servlet-api/artifactId version3.1.0/version scopeprovided/scope /dependency !-- Apache POI -- dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version5.2.3/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- 文件上传 -- dependency groupIdcommons-fileupload/groupId artifactIdcommons-fileupload/artifactId version1.4/version /dependency !-- MySQL驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency !-- JSON -- dependency groupIdcom.alibaba/groupId artifactIdfastjson/artifactId version1.2.83/version /dependency /dependencies依赖里值得注意的坑commons-fileupload需要依赖commons-io如果项目里没有需要一并加上。POI 5.x支持JDK 1.8但如果用的JDK 8比较旧的小版本建议用较新的8u版本。mysql-connector-java8.x要求JDK 8以上老服务器要注意。如果项目里还有别的POI旧版本排除冲突否则会出现NoSuchMethodError一类问题。2.2 JSP页面引入WebUploader资源WebUploader不是npm包它是一套静态资源。下载后的目录结构里包含webuploader.css、webuploader.js、Uploader.swf等文件把它们放到项目的webapp/static/webuploader目录下即可。JSP页面里引入方式如下link relstylesheet href${pageContext.request.contextPath}/static/webuploader/webuploader.css script src${pageContext.request.contextPath}/static/webuploader/webuploader.js/script注意webuploader.js内部会请求Uploader.swf用于兼容不支持HTML5的老浏览器。如果项目是HTTPS环境页面也要保持HTTPS否则Flash会被浏览器拦截。现在大多数场景都走HTML5模式Flash降级基本用不上但保留这份静态资源没有坏处。JSP页面主体结构我会放一个上传按钮、一个文件列表区和一个进度条容器后面初始化uploader实例时会用到这些DOM节点。div iduploader-demo div idfileList classuploader-list/div div idprogressBar classprogress styledisplay:none; div classprogress-bar stylewidth:0%;/div /div div idbtnPickFile选择Excel文件/div button typebutton idbtnUpload开始上传/button /div2.3 数据库表结构设计导入导出的“落点”是数据库表表结构的好坏直接影响导入代码的复杂度。我用一个用户信息表做示例业务字段尽量精简但能覆盖常见情况CREATE TABLE user_info ( id bigint(20) NOT NULL AUTO_INCREMENT, user_name varchar(50) NOT NULL COMMENT 姓名, phone varchar(20) NOT NULL COMMENT 手机号, email varchar(100) DEFAULT NULL COMMENT 邮箱, province varchar(50) DEFAULT NULL COMMENT 省份, status tinyint(4) DEFAULT 1 COMMENT 状态1有效 0无效, create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息导入表;这里给phone加唯一索引是有意的。导入场景里最常见的问题就是重复数据如果数据源本身可能有重复代码里就得做查重而不是完全依赖数据库报错。唯一索引是最后一道防线。字符集用utf8mb4避免Excel里存了特殊字符导致写入报错。3. 前端实现WebUploader上传Excel的核心配置3.1 初始化uploader实例与关键参数WebUploader的初始化代码不长真正要理解的是每个参数的含义。下面是一份我在项目里用过的完整配置配合上一节里的DOM节点var uploader WebUploader.create({ // 选择文件按钮的触发元素 pick: { id: #btnPickFile, multiple: false }, // 上传按钮 // 这里不写后面手动触发上传 // 上传接口地址 server: uploadExcelServlet, // 后端接收文件时对应的表单字段名 fileVal: file, // 允许上传的文件类型 accept: { title: Excel, extensions: xls,xlsx, mimeTypes: application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet }, // 文件大小限制单位是字节。这里限制5MB fileSizeLimit: 5 * 1024 * 1024, // 文件数量限制 fileNumLimit: 1, // 不开启分片Excel场景不需要 chunked: false, // 禁止压缩上传原文件 compress: false, // 自动上传开关这里关掉等用户确认后手动触发 auto: false, // 上传并发数 threads: 1 });参数解释pick.id是点击选文件的DOM节点multiple设为false一次只选一个Excel符合导入场景。server指向后端Servlet的URL。注意JSP里如果项目部署路径带contextPath这里要拼接${pageContext.request.contextPath}。fileVal非常关键它决定后端拿到的表单字段名。后端用fileItem.getFieldName()能取到这个值如果不一致会直接拿不到文件。accept只是前端引导它给用户弹出文件选择框时过滤类型但用户依然可以选“所有文件”强制选择其它后缀所以后端必须二次校验。chunked: false分片上传在处理超大文件时有意义Excel文件通常几MB不分片逻辑更简单。3.2 文件选择校验扩展名与文件大小WebUploader创建后要注册事件。beforeFileQueued会在文件进入队列前触发这里可以做同步校验。uploader.on(beforeFileQueued, function(file) { var ext file.name.split(.).pop().toLowerCase(); if ([xls, xlsx].indexOf(ext) -1) { alert(只能上传xls或xlsx格式的Excel文件); return false; } if (file.size 5 * 1024 * 1024) { alert(文件大小不能超过5MB); return false; } return true; });实际上WebUploader的accept配置已经做了第一层过滤但我在实际项目里见过不少用户强行选择“所有文件”把csv或者其他格式传上来的情况。所以前端这里用一个简单的扩展名校验能把大部分误操作拦截在请求发出之前减少后端压力。校验通过后把文件信息渲染到文件列表里uploader.on(fileQueued, function(file) { var html div id file.id classitem span classname file.name /span span classsize (file.size / 1024 / 1024).toFixed(2) MB/span /div; $(#fileList).append(html); });3.3 上传进度、成功与失败处理进度回调用progress事件uploader.on(progress, function(file, percentage) { var percent Math.round(percentage * 100); $(#progressBar).show(); $(#progressBar .progress-bar).css(width, percent %); });后端处理完成后无论成功失败都会走uploadSuccess或uploadError事件。这里有个经验后端返回的结果要约定成统一的JSON格式前端拿到后直接展示。uploader.on(uploadSuccess, function(file, response) { var result JSON.parse(response._raw); if (result.success) { alert(导入完成成功 result.successCount 条失败 result.failCount 条); // 如果有错误报告下载URL这里可以放一个链接 if (result.errorReportUrl) { $(#errorReport).attr(href, result.errorReportUrl).show(); } } else { alert(导入失败 result.msg); } }); uploader.on(uploadError, function(file, reason) { alert(上传失败 reason); });手动触发上传是绑定到按钮点击事件上$(#btnUpload).on(click, function() { uploader.upload(); });WebUploader的坑不多但有一个容易踩response._raw是一个JSON字符串不要直接当成对象用。不同版本里response对象的字段名可能略有差异稳妥做法是先打印出来看一下再解析。4. 后端接收与Apache POI解析Excel4.1 Servlet接收上传文件并保存临时文件后端用commons-fileupload接收multipart请求。Servlet示例代码WebServlet(/uploadExcelServlet) public class UploadExcelServlet extends HttpServlet { private static final long serialVersionUID 1L; Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException { request.setCharacterEncoding(UTF-8); response.setContentType(application/json;charsetUTF-8); JSONObject result new JSONObject(); // 临时文件目录 String tempDir System.getProperty(java.io.tmpdir) File.separator excel_upload; File dir new File(tempDir); if (!dir.exists()) { dir.mkdirs(); } try { DiskFileItemFactory factory new DiskFileItemFactory(); ServletFileUpload upload new ServletFileUpload(factory); upload.setHeaderEncoding(UTF-8); upload.setFileSizeMax(5 * 1024 * 1024); upload.setSizeMax(10 * 1024 * 1024); ListFileItem items upload.parseRequest(request); FileItem fileItem null; for (FileItem item : items) { if (!item.isFormField() file.equals(item.getFieldName())) { fileItem item; break; } } if (fileItem null) { result.put(success, false); result.put(msg, 未获取到上传文件); response.getWriter().write(result.toJSONString()); return; } String fileName fileItem.getName(); // 获取原始文件名 String originalName fileName.substring(fileName.lastIndexOf(\\) 1); // 扩展名校验 String ext originalName.substring(originalName.lastIndexOf(.) 1).toLowerCase(); if (!xls.equals(ext) !xlsx.equals(ext)) { result.put(success, false); result.put(msg, 仅支持xls或xlsx格式); response.getWriter().write(result.toJSONString()); return; } // 用UUID作为存储文件名避免中文和路径问题 String storeName UUID.randomUUID().toString().replace(-, ) . ext; File storeFile new File(dir, storeName); fileItem.write(storeFile); // 解析Excel并导入 ImportResult importResult ExcelImportService.importExcel(storeFile.getAbsolutePath(), originalName); // 导入完成后删除临时文件 storeFile.delete(); result.put(success, importResult.isSuccess()); result.put(successCount, importResult.getSuccessCount()); result.put(failCount, importResult.getFailCount()); result.put(msg, importResult.getMessage()); if (importResult.hasErrorReport()) { result.put(errorReportUrl, downloadErrorReport?fileName importResult.getReportFileName()); } } catch (Exception e) { e.printStackTrace(); result.put(success, false); result.put(msg, 导入异常 e.getMessage()); } response.getWriter().write(result.toJSONString()); } }这里的关键点有两个存储文件名用UUID重写不信任用户提交的原始文件名。原始文件名只用来提取扩展名这样能避免路径穿越和中文编码问题。临时文件在解析完成后立刻删除。如果服务器频繁上传大文件临时目录会堆满这个细节很容易被忽视。4.2 POI创建Workbook并遍历SheetExcel解析的核心是创建Workbook对象。根据扩展名不同需要分别用HSSFWorkbook和XSSFWorkbook读取public static ImportResult importExcel(String filePath, String originalName) { ImportResult result new ImportResult(); String ext originalName.substring(originalName.lastIndexOf(.) 1).toLowerCase(); try (InputStream is new FileInputStream(filePath)) { Workbook workbook; if (xls.equals(ext)) { workbook new HSSFWorkbook(is); } else { workbook new XSSFWorkbook(is); } Sheet sheet workbook.getSheetAt(0); int lastRowNum sheet.getLastRowNum(); // 导入的数据有效行数 int successCount 0; int failCount 0; ListString errorMessages new ArrayList(); // 从第1行开始跳过表头 for (int i 1; i lastRowNum; i) { Row row sheet.getRow(i); if (row null || isEmptyRow(row)) { continue; } // 解析用户对象 String rowError parseRowToUser(row, i); if (rowError ! null) { failCount; errorMessages.add(第 (i 1) 行 rowError); } else { successCount; } } workbook.close(); result.setSuccessCount(successCount); result.setFailCount(failCount); if (failCount 0) { // 有错误行时生成错误报告文件 result.setErrorReport(true); // 错误报告写入临时目录并保存文件名方便前端下载 result.setReportFileName(writeErrorReport(errorMessages)); result.setMessage(导入完成部分行校验失败); } else { result.setMessage(全部导入成功); } return result; } catch (Exception e) { e.printStackTrace(); result.setSuccess(false); result.setMessage(文件解析失败 e.getMessage()); return result; } }关于遍历行数的边界处理有个容易错的地方sheet.getLastRowNum()返回的是最后一行的索引它从0开始算。假如Excel里只有表头一行这个值是0循环条件i 0不会执行数据行不会处理。假如有5行数据表头算第0行数据是第1到第5行lastRowNum是5循环条件i 5刚好覆盖。空行判断不能直接依赖row null。有些Excel经过用户编辑后行对象存在但所有单元格都是空字符串。所以要单独写一个isEmptyRow方法private static boolean isEmptyRow(Row row) { if (row null) { return true; } short minColIx row.getFirstCellNum(); short maxColIx row.getLastCellNum(); if (minColIx -1 || maxColIx -1) { return true; } for (short colIx minColIx; colIx maxColIx; colIx) { Cell cell row.getCell(colIx); if (cell ! null !.equals(cell.toString().trim())) { return false; } } return true; }4.3 单元格数据读取与类型转换Excel单元格的类型很杂有字符串、数字、布尔值、公式、日期、空白等。如果不做类型判断直接cell.getStringCellValue()遇到数字单元格会直接抛异常。所以读取每个单元格都要先判断类型再取值private static Object getCellValue(Cell cell) { if (cell null) { return null; } switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } // 避免科学计数法用BigDecimal转换 return BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString(); case BOOLEAN: return cell.getBooleanCellValue(); case FORMULA: return cell.getCellFormula(); case BLANK: return null; default: return null; } }这里几个点必须说明数字单元格默认读出来是double如果手机号、身份证这类超长数字直接转字符串会出现科学计数法。用BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString()可以拿到不带指数的完整数字字符串。日期单元格在Excel底层是数字必须用DateUtil.isCellDateFormatted(cell)判断否则拿到的是序列号一样的东西。字符串单元格要.trim()用户从Excel里复制数据经常带着前后空格不处理的话后面入库会带上脏数据。公式单元格一般导入场景不涉及但如果有可以根据cell.getCachedFormulaResultType()取计算后的值。实际解析User对象的代码大致是private static String parseRowToUser(Row row, int rowIndex) { String name getStringCell(row.getCell(0)); String phone getStringCell(row.getCell(1)); String email getStringCell(row.getCell(2)); String province getStringCell(row.getCell(3)); if (StringUtils.isBlank(name)) { return 姓名不能为空; } if (StringUtils.isBlank(phone)) { return 手机号不能为空; } if (!phone.matches(^1[3-9]\\d{9}$)) { return 手机号格式不正确; } if (StringUtils.isNotBlank(email) !email.matches(^[\\w.%-][\\w.-]\\.[A-Za-z]{2,}$)) { return 邮箱格式不正确; } User user new User(); user.setUserName(name); user.setPhone(phone); user.setEmail(email); user.setProvince(province); user.setCreateTime(new Date()); users.add(user); return null; }4.4 大批量Excel的解析性能优化POI解析Excel时HSSFWorkbook和XSSFWorkbook都会把整个文件结构加载进内存。XSSFWorkbook对xlsx大文件尤其消耗内存一个10MB的xlsx占用的堆内存可能超过几百MB。所以解析环节的优化思路是控制上传文件大小这是最直接的手段。5MB限制在导入场景里足够覆盖大部分业务。不要在一个请求里硬扛超大Excel。超过一定行数比如1万行拆成异步任务处理响应前端“导入中”让后台慢慢处理避免HTTP请求超时。如果确实要处理超大xlsx考虑POI的eventmodel方式基于SAX逐行读取但代码量会明显增加小项目性价比不高。从实际操作角度看多数后台系统的Excel导入量级都在几百到几千行做好大小限制后同步解析完全够用。真遇到超大文件优先考虑限制加异步而不是一上来就上SAX方案。5. 数据校验与MySQL批量入库5.1 逐行校验与错误信息收集导入功能不能简单“能读就导”必须做业务校验。校验不通过的行不能入库同时要告诉用户哪些行有问题。上一节的parseRowToUser里已经体现了单行校验逻辑。需要说明的是校验的粒度通常放在解析阶段做比较好因为这时候能拿到行号和原始值方便生成错误提示。错误提示的格式建议是第3行手机号格式不正确 第5行邮箱格式不正确 第7行姓名不能为空不要只返回“导入失败”四个字用户完全不知道哪里错了。错误信息可以前端直接弹窗展示也可以生成一个错误报告文件下载。我的做法是失败行数小于20条时直接弹窗展示超过20条生成txt或Excel错误报告。生成错误报告文件的方法private static String writeErrorReport(ListString errorMessages) { String fileName error_ System.currentTimeMillis() .txt; File dir new File(System.getProperty(java.io.tmpdir) File.separator excel_upload_error); if (!dir.exists()) { dir.mkdirs(); } File file new File(dir, fileName); try (BufferedWriter writer new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), UTF-8))) { for (String msg : errorMessages) { writer.write(msg); writer.newLine(); } } catch (IOException e) { e.printStackTrace(); } return fileName; }5.2 基于JDBC的批量插入与事务控制解析完后得到User对象集合接下来要入库。最忌讳的是逐条INSERT1000条数据要执行1000次SQL性能很差。正确做法是用JDBC的批量更新public static void batchInsertUsers(ListUser users) throws SQLException { String sql INSERT INTO user_info(user_name, phone, email, province, status, create_time) VALUES(?, ?, ?, ?, ?, ?); Connection conn null; PreparedStatement ps null; try { conn DBUtil.getConnection(); // 关闭自动提交手动控制事务 conn.setAutoCommit(false); ps conn.prepareStatement(sql); int batchSize 500; int count 0; for (User user : users) { ps.setString(1, user.getUserName()); ps.setString(2, user.getPhone()); ps.setString(3, user.getEmail()); ps.setString(4, user.getProvince()); ps.setInt(5, 1); ps.setTimestamp(6, new Timestamp(user.getCreateTime().getTime())); ps.addBatch(); count; if (count % batchSize 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit(); } catch (SQLException e) { if (conn ! null) { conn.rollback(); } throw e; } finally { if (ps ! null) ps.close(); if (conn ! null) DBUtil.close(conn); } }这里有一个MySQL连接参数值得专门说明。在JDBC连接串上加上rewriteBatchedStatementstrue批量插入性能会有质的提升jdbc:mysql://127.0.0.1:3306/yourdb?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrue不加这个参数时MySQL驱动会把executeBatch()的每条SQL分别发送到数据库执行加了这个参数后驱动会把一批SQL重写成INSERT INTO ... VALUES (...),(...),(...)的合并语句网络开销大幅下降。我在本地实测过几千条数据插入时间从秒级降到毫秒级。事务控制上每个批次提交一次这样单批出错时只会回滚当前批次。但要注意如果批次里前一批已经提交后一批失败会出现部分导入成功的情况。错误报告里能反映出哪些行失败业务上一般可接受。如果业务严格要求全部成功才算成功则所有数据放在一个大事务里但那样数据量大时锁表时间长不建议默认使用。5.3 重复数据预处理表上有手机号唯一索引但代码层也要处理重复。处理方式// 导入前查询已有用户集合 SetString existingPhones UserDao.selectAllPhones(); ListUser toInsert new ArrayList(); for (User user : userList) { if (existingPhones.contains(user.getPhone())) { failCount; errorMessages.add(手机号 user.getPhone() 已存在); } else { toInsert.add(user); } }这种做法的前提是数据量不大一次请求内查全表手机号是可以接受的。如果用户表有几百万行全量查出来就会很吃力。这时可以把校验做成分批每处理500条数据查询这批手机号在库里是否存在String sql SELECT phone FROM user_info WHERE phone IN (?, ?, ?, ...);然后动态拼接占位符查出来不存在的才能插入。但要注意IN查询的占位符不能超过MySQL限制超过1000个要拆成多批。数据量再大的场景直接用临时表加INSERT ... ON DUPLICATE KEY UPDATE也是一种方案但错误定位会困难一些。小项目用全量查询即可逻辑简单。6. 数据导出从MySQL到Excel下载6.1 导出流程与响应头设置导出Excel的流程核心是两步查数据、写文件流。但有几个细节直接影响用户拿到文件后是否打开正常。第一个是响应头设置。必须设置Content-Type和Content-Disposition否则浏览器会把文件内容直接显示到页面上或者文件名乱码response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(UTF-8); String fileName 用户数据_ DateUtil.format(new Date(), yyyyMMddHHmmss) .xlsx; // 文件名做URL编码解决浏览器下载时中文乱码 String encodedFileName URLEncoder.encode(fileName, UTF-8).replaceAll(\\, %20); response.setHeader(Content-Disposition, attachment;filename encodedFileName);URL编码这里有个细节URLEncoder.encode会把空格编码成号但在HTTP头里会被解析成空格导致文件名里的空格丢失。所以要用replaceAll(\\, %20)把加号替换成%20。这个坑不踩一次很难发现。前端触发下载有两种常见方式a hrefexportUserServlet?province北京市导出Excel/a或者用表单提交form actionexportUserServlet methodget input typehidden nameprovince value${province} button typesubmit导出Excel/button /formGET方式比较适合导出因为导出不改变服务端数据而且URL里可以带查询条件。6.2 大数据量导出SXSSFWorkbook与分页查询数据量小的导出直接查全量再写入Workbook完全没有问题。但数据量超过几万行时XSSFWorkbook会把所有行对象都留在内存GG的可能性很大。这时候要换成SXSSFWorkbookpublic static void exportUsers(HttpServletResponse response, ListMapString, Object userList) throws IOException { // 开启滑动窗口只在内存保留100行其余写入磁盘临时文件 SXSSFWorkbook workbook new SXSSFWorkbook(100); Sheet sheet workbook.createSheet(用户数据); // 写表头 String[] headers {姓名, 手机号, 邮箱, 省份, 状态, 创建时间}; Row headerRow sheet.createRow(0); for (int i 0; i headers.length; i) { headerRow.createCell(i).setCellValue(headers[i]); } int rowNum 1; for (MapString, Object user : userList) { Row row sheet.createRow(rowNum); row.createCell(0).setCellValue((String) user.get(userName)); row.createCell(1).setCellValue((String) user.get(phone)); row.createCell(2).setCellValue((String) user.get(email)); row.createCell(3).setCellValue((String) user.get(province)); row.createCell(4).setCellValue((String) user.get(status)); Cell createTimeCell row.createCell(5); createTimeCell.setCellValue((Date) user.get(createTime)); DataFormat format workbook.createDataFormat(); CellStyle dateStyle workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat(yyyy-mm-dd hh:mm:ss)); createTimeCell.setCellStyle(dateStyle); } // 流式输出 OutputStream out response.getOutputStream(); workbook.write(out); out.flush(); out.close(); // 清理SXSSF临时文件 workbook.dispose(); workbook.close(); }SXSSFWorkbook的使用要点构造传入100代表滑动窗口大小超过100行的内容会写进磁盘临时文件显著降低内存占用。使用完必须调用dispose()删除临时文件否则临时目录里会残留文件。SXSSFWorkbook不支持读操作只能用于写。数据查询不能一次性全部load到内存。配合分页查询查5000条、写5000条内存占用基本稳定。导出时分页查询的代码示例int pageSize 5000; int pageNum 1; ListMapString, Object pageData; do { pageData UserDao.selectUserPage(pageNum, pageSize, condition); writeRowsToSheet(sheet, pageData, rowNum); // 每页写完后做一次flush控制内存占用 pageNum; } while (pageData.size() pageSize);6.3 表格样式与下载体验优化纯粹导出数据可以不做任何样式。但用户拿到的Excel如果表头不加粗、列宽不适应、时间格式不友好多多少少会觉得“不够正式”。适当加一点样式能明显提升体验。// 表头样式加粗、背景色 CellStyle headerStyle workbook.createCellStyle(); Font headerFont workbook.createFont(); headerFont.setBold(true); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 列宽自适应但不生效于所有模拟数据建议手动设置 sheet.setColumnWidth(0, 12 * 256); sheet.setColumnWidth(1, 18 * 256); sheet.setColumnWidth(2, 25 * 256);列宽设置里Excel的单位是字符宽度的1/256。12*256大约等于12个字符宽。名字列设12字符、手机号列设18字符、邮箱列设25字符是最常见的手机号、中文名宽度组合。如果要做模板下载功能和导出功能复用可以把表头定义和创建Workbook封装成公共方法模板下载时只导出表头数据导出时再写入数据行。7. 常见问题排查与性能优化思路7.1 高频问题速查表把实际项目里遇到最多的几个问题整理成一张速查表方便直接对照排查问题现象可能原因解决办法上传时提示“文件格式不支持”前端accept限制或者后端扩展名校验不通过确认文件确实是xls/xlsx检查扩展名提取逻辑是否被中文文件名干扰后端收不到fileItemWebUploader的fileVal和后端表单字段名不一致检查前端fileVal配置后端用item.getFieldName()匹配中文文件名上传后乱码上传组件或Servlet的编码设置缺失request和response统一用UTF-8upload.setHeaderEncoding(UTF-8)导入时Date类型变成一串数字日期单元格没有用DateUtil判断读取单元格时先判断DateUtil.isCellDateFormatted(cell)手机号变成科学计数法丢了精度数字单元格直接用getStringCellValue()用BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString()下载的Excel文件名乱码Header里没做URL编码或者空格被编码成用URLEncoder.encode(fileName, UTF-8).replaceAll(\\, %20)大Excel导入时内存溢出XSSFWorkbook加载大xlsx消耗太多堆内存限制上传大小超大文件走异步任务必要时用SAX eventmodel批量插入特别慢JDBC连接串缺少rewriteBatchedStatementstrue连接串加上该参数控制批次大小导出几万行数据卡死XSSFWorkbook全量驻留内存换SXSSFWorkbook并配合分页查询错误报告下载后中文乱码生成时编码不是UTF-8写txt用OutputStreamWriter(file, UTF-8)7.2 导入导出的性能瓶颈分析导入和导出看着简单但性能瓶颈并不相同导入瓶颈主要在POI解析和数据库写入。POI解析耗时跟文件大小强相关Excel格式越新解析越慢。数据库写入慢通常不是因为SQL复杂而是网络往返次数太多。减少往返次数的核心思路就是批量提交。所以在导入链路里优先级最高的是控制上传文件大小、采用批量插入、合理设置批次大小。导出瓶颈主要在数据查询和文件写出。数据查询慢通常是查询条件缺乏索引或者全量数据一次性load到内存。文件写出慢通常是POI的内存问题而不是CPU慢。换SXSSFWorkbook后内存占用下来了但会引入磁盘IO因为滑动窗口会把行数据写到临时文件。所以导出大数据量时除了关注内存也要留意临时目录的磁盘空间。这两个场景可以共用一套优化思路分而治之。导入分批、导出分页本质上都是避免一次性处理太多数据。系统资源是有限的一次请求能扛的量也有上限把大任务切成小任务才稳定。7.3 安全与部署注意点文件上传是有安全风险的功能。Excel上传功能虽然不像脚本上传那么高危但该做的防护不能少文件类型双重校验。前端校验只是用户体验后端必须再次校验扩展名最好再读一下文件头字节判断真实类型防止伪装成xlsx的恶意文件。临时文件及时清理。解析完成后删除临时文件避免恶意请求不断上传导致磁盘占满。上传大小和频率都做限制。5MB文件大小、单次请求文件数量限制这两项在Servlet端已经体现。如果有条件还可以在网关层加请求频率限制。不要保存用户上传的原始文件名到头像、附件等公开下载路径。本文用UUID重命名存储原始文件名只用于展示就是一个正确的处理方式。导出接口如果是GET要注意URL参数长度限制。查询条件过多时改成POST表单提交更稳妥。部署层面临时目录的权限要设置好不要放在webapp可访问目录下。用System.getProperty(java.io.tmpdir)拿到的是系统临时目录相对安全。错误报告文件虽然也在临时目录但如果需要前端下载必须单独做一个下载Servlet映射不能把整个临时目录暴露到webapp里。8. 最后分享一点实际项目里的体会这套JSPWebUploaderPOIMySQL的组合我在实际项目里反复搭过好几轮最深的感触是导入导出看着是外围功能一旦没设计好就是线上事故的温床。刚开始时我直接拿XSSFWorkbook读一个10MB的xlsx项目直接内存溢出后来才换成分批、限制大小和SXSSF的方案。还有一个小技巧分享给后来人如果业务允许导入模板一定要固定列。让用户按照固定模板填写数据后端解析时按列索引读取简单又不容易出错。一旦放开让用户随便拿一张表来导入就要写很多“猜测列含义”的逻辑维护成本会指数级上升。这个方案后续要扩展的话还可以加上异步导入通知、导入进度查询或者把导出任务丢到线程池里异步生成文件再提供下载思路都差不多把链路拆开各自优化就行。
