JSP+WebUploader+Apache POI+MySQL：Excel批量导入导出完整实践方案

JSP项目里要做Excel的上传、解析、导入和导出最实在的一套组合就是WebUploader加Apache POI加MySQL。这个搭配我在好几个后台管理系统里都实践过整体链路通顺踩坑的细节也比较集中今天把完整方案和一堆实测经验写出来给正准备做同样功能的同学做个参照。1. 这套技术组合的定位与为什么这么选型先说清楚这套组合干了什么事。用户在前端通过WebUploader组件选择一个Excel文件并上传后端接收文件后用Apache POI解析内容把数据清洗后批量写入MySQL反过来系统从MySQL查出数据通过POI生成Excel文件再借助WebUploader的下载能力交到用户手里。整个闭环恰好覆盖了业务系统里最典型的“报表导入导出”诉求比如批量导入人员信息、商品资料、设备台账或者导出月度订单明细。选型层面WebUploader不是唯一的文件上传方案市面上还有dropzone.js、fine-uploader、layui自带的上传模块等但WebUploader在JSP这类服务端渲染的传统项目里优势非常明显它对jQuery依赖很轻自带分片上传、并发上传、文件类型过滤、缩略图预览这些能力而且UI样式可控性强后端只需要写一个接收multipart/form-data的Servlet或者Controller即可不需要引入额外的大框架。之前有一个订单系统就是因为需要在大批量Excel导入时看到实时的上传进度条并且要限制只能上传xls和xlsx我从原生input方式迁移到WebUploader后这些需求都很快搞定了。Apache POI在这个链路里的地位也很关键。它能同时处理老版的.xlsHSSFWorkbook和新版的.xlsxXSSFWorkbook既能读也能写支持单元格样式、公式、合并单元格等复杂特征。不过需要注意POI对不同Excel版本封装成了完全不同的两套类——操作xls用的包名是org.apache.poi.hssf.usermodel操作xlsx用的是org.apache.poi.xssf.usermodel写代码时只要按文件后缀分流处理即可。还有一个隐藏要点在版本选择上。早期用3.x版本处理较大xlsx时经常遇到内存溢出后来升级到4.x以上情况明显好转原因是XSSFWorkbook从纯DOM模型引入了流式解析。我实测过一个包含3万行、50列的xlsx旧版本解析耗时在小几十秒级别且发生过OOM升级到4.1.2之后耗时降到几秒内存占用也友好很多。2. 环境搭建与依赖配置别在版本上翻车这一步看着基础实际最容易出问题的是POI依赖之间的版本兼容。2.1 Maven依赖组合我用的是Maven构建标准的依赖写法如下dependency groupIdcommons-io/groupId artifactIdcommons-io/artifactId version2.11.0/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version4.1.2/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version4.1.2/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml-schemas/artifactId version4.1.2/version /dependencypoi主包负责xls格式poi-ooxml负责xlsx格式poi-ooxml-schemas处理Office Open XML底层结构。这三个必须用相同版本否则运行时会抛出“类文件具有错误的版本”或NoSuchMethodError这类非常恶心的异常。2.2 JDK和Servlet版本注意POI 4.x编译目标为Java 8所以项目JDK不要低于8。如果你的服务器还在跑Tomcat 7那要确认Servlet API版本不低于3.0否则WebUploader的分片上传功能会出现请求异常因为分片请求依赖HTTP协议层面的能力老容器支持不完整。2.3 不用Maven的情况有些老项目是Eclipse里直接塞jar到WEB-INF/lib的那你需要手动收集如下jarpoi-4.1.2.jar、poi-ooxml-4.1.2.jar、poi-ooxml-schemas-4.1.2.jar、xmlbeans-3.1.0.jar、commons-collections4-4.4.jar、commons-compress-1.21.jar、curvesapi-1.06.jar。缺xmlbeans会报“Cannot find class org.apache.xmlbeans.XmlObject”缺commons-compress解压xlsx内嵌zip时直接抛IOException这些坑我全都踩过。3. WebUploader前端接入与上传细节调优前端部分是整个流程里体验感最直观的环节。3.1 页面初始化和基础参数先看一个我在真实项目里用过的完整初始化配置var uploader WebUploader.create({ auto: true, swf: basePath /js/webuploader/Uploader.swf, server: basePath /file/importExcel, pick: #filePicker, accept: { title: Excel文件, extensions: xls,xlsx, mimeTypes: application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet }, fileVal: file, duplicate: true, chunked: false, threads: 3, fileNumLimit: 1, fileSizeLimit: 10 * 1024 * 1024, compress: false });几个关键参数说明一下。fileVal指定后端接收文件时使用的请求参数名后端代码里要用request.getPart(file)或框架的RequestParam(file)去接这个命名对不上就是100%的“前端传了后端收不到”。accept里的extensions和mimeTypes要同时写因为不同浏览器对文件类型的识别方式不同只写扩展名在Chrome下可行但在老版本IE里就需要mime类型做补充。chunked我设成了false。项目里日常Excel文件一般不超过10MB没必要开分片但如果你们的场景经常出现50MB以上的大表那chunked必须设为true并且服务端要有对应的合并分片逻辑这部分后面讲。duplicate设为true允许重复选择同一个文件。业务上经常会有用户修改了Excel内容后再次上传同一路径的文件默认WebUploader会认为是重复文件而拒绝上传影响体验。3.2 上传事件处理与错误提示WebUploader事件模型很丰富我通常绑定这样几个事件uploader.on(fileQueued, function (file) { $(#fileTable).append(tr id file.id td file.name /tdtd file.size /tdtd classstatus等待上传/td/tr); }); uploader.on(uploadProgress, function (file, percentage) { $(# file.id).find(.status).text(Math.round(percentage * 100) %); }); uploader.on(uploadSuccess, function (file, response) { if (response.success) { $(# file.id).find(.status).text(导入成功); refreshDataTable(); } else { $(# file.id).find(.status).text(导入失败 response.message); } }); uploader.on(uploadError, function (file, reason) { $(# file.id).find(.status).text(上传失败 reason); }); uploader.on(error, function (type) { if (type Q_TYPE_DENIED) { alert(只能选择Excel文件); } else if (type F_EXCEED_SIZE) { alert(文件大小超出限制); } });这里有一个容易被忽略的点uploadSuccess的response参数来自后端返回的JSON字符串。后端返回JSON时Content-Type要设置为application/json;charsetutf-8如果返回的是text/html或text/plainWebUploader会尝试解析JSON失败导致response.success始终为undefined或null明明导入成功了前端还提示失败。这个调试过程当时花了我不少时间排查链路是先看后端日志确认数据已经入库再打开浏览器F12看响应体确实是JSON最后才发现是Content-Type不对。3.3 文件上传前的本地校验除了后端校验前端还可以做一个快速校验拦截明显不合适的文件减少无谓的上传请求uploader.on(fileQueued, function (file) { var ext file.name.split(.).pop().toLowerCase(); if (ext ! xls ext ! xlsx) { uploader.removeFile(file); alert(只能上传xls或xlsx文件); return; } if (file.size 10 * 1024 * 1024) { uploader.removeFile(file); alert(文件大小不能超过10MB); return; } });别以为设置了accept就不会进来非法的文件。某些浏览器尤其是老Edge的accept过滤是失效的用户完全可以选择任意文件所以fileQueued里的二次校验是防住这层风险的地板砖必须有。4. 后端Servlet接收文件并完成POI解析后端是整个链路里逻辑最重的一环。Servlet接收文件、解析Excel、逐行读数据然后交给业务处理。4.1 文件接收通用处理使用原生的HttpServletRequest接收WebUploader上传的文件核心代码如下protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding(UTF-8); response.setContentType(application/json;charsetUTF-8); PrintWriter out response.getWriter(); ResultBean result new ResultBean(); Part part request.getPart(file); if (part null) { result.setSuccess(false); result.setMessage(未接收到文件); out.write(JSON.toJSONString(result)); return; } String fileName part.getSubmittedFileName(); if (fileName null || (!fileName.endsWith(.xls) !fileName.endsWith(.xlsx))) { result.setSuccess(false); result.setMessage(文件格式不正确); out.write(JSON.toJSONString(result)); return; } try { int importedCount parseExcelAndImport(part.getInputStream(), fileName); result.setSuccess(true); result.setMessage(成功导入 importedCount 条数据); } catch (Exception e) { e.printStackTrace(); result.setSuccess(false); result.setMessage(导入失败 e.getMessage()); } out.write(JSON.toJSONString(result)); }这里有两个细节需要注意。part.getSubmittedFileName()在Servlet 3.0及以上才能使用。如果你的项目还在用老版本Servlet规范需要自己从Content-Disposition头里截取文件名String header part.getHeader(content-disposition); String fileName header.substring(header.indexOf(filename\) 10, header.lastIndexOf(\));另外用完的InputStream一定要关闭最好显式调用part.delete()来清理临时文件。Tomcat默认会把上传文件写到临时目录不清掉的话高并发导入的机器上/temp目录会被塞满数据库没炸服务器先炸了。4.2 POI解析Excel的完整示例解析方法里最关键的一点是“识别文件类型并选择不同Workbook实现”。直接看代码private int parseExcelAndImport(InputStream inputStream, String fileName) throws Exception { Workbook workbook null; if (fileName.endsWith(.xlsx)) { workbook new XSSFWorkbook(inputStream); } else if (fileName.endsWith(.xls)) { workbook new HSSFWorkbook(inputStream); } Sheet sheet workbook.getSheetAt(0); int rowCount sheet.getPhysicalNumberOfRows(); ListUserInfo list new ArrayListUserInfo(); for (int i 0; i rowCount - 1; i) { Row row sheet.getRow(i); if (row null) continue; if (i 0) continue; // 表头跳过 Cell cell0 row.getCell(0); Cell cell1 row.getCell(1); Cell cell2 row.getCell(2); UserInfo user new UserInfo(); user.setName(getCellValue(cell0)); user.setAge(Integer.parseInt(getCellValue(cell1))); user.setPhone(getCellValue(cell2)); list.add(user); } workbook.close(); return batchInsert(list); }变量名解析也简单就是用统一的getCellValue方法处理所有单元格类型避免数值、字符串、日期、公式混在一起时类型转换异常private static String getCellValue(Cell cell) { if (cell null) return ; String value ; switch (cell.getCellType()) { case STRING: value cell.getStringCellValue(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value new SimpleDateFormat(yyyy-MM-dd HH:mm:ss).format(cell.getDateCellValue()); } else { value String.valueOf(cell.getNumericCellValue()); if (value.endsWith(.0)) { value value.substring(0, value.length() - 2); } } break; case BOOLEAN: value String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: try { value String.valueOf(cell.getNumericCellValue()); } catch (Exception e) { value cell.getStringCellValue(); } break; case BLANK: value ; break; default: break; } return value.trim(); }这里有个特别好用的小技巧Excel的纯数值单元格比如身份证号、长手机号在POI里经常被读成double型比如18612345678会被读成1.8612345678E10直接toString就变成了科学计数法。早期没有做“endsWith(.0)就截掉”的处理导致导进MySQL的手机号全是乱码。处理逻辑简单粗暴先toString如果是整数值且末尾带.0就截断如果是手机号这类数字最好在Excel模板里先把单元格格式设置成文本。再补充一点关于公式单元格的经验。如果用户的Excel里某些列是公式计算出来的结果比如“SUM(C2:D2)”POI里需要通过FormulaEvaluator来获取计算结果。上面代码中对公式的处理方式是直接取值如果取不到再去拿字符串缓存值。遇到公式没缓存结果的模板这个方式会失效。稳妥做法是解析前先调用workbook.getCreationHelper().createFormulaEvaluator().evaluateAll()把公式结果算一遍。5. 导入MySQL的批量操作与事务边界处理拿到解析完的List之后写库事务设计必须想清楚否则数据一半成功一半失败对账麻烦到想离职。5.1 使用JDBC批量插入我在这类项目里倾向于直接使用Spring的JdbcTemplate做批量插入简单、可控、性能好Autowired private JdbcTemplate jdbcTemplate; private static final String INSERT_SQL INSERT INTO user_info (name, age, phone, create_time) VALUES (?, ?, ?, NOW()); Transactional public int batchInsert(ListUserInfo list) { int[] result jdbcTemplate.batchUpdate(INSERT_SQL, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { UserInfo user list.get(i); ps.setString(1, user.getName()); ps.setInt(2, user.getAge()); ps.setString(3, user.getPhone()); } public int getBatchSize() { return list.size(); } }); return result.length; }要特别强调批量插入一定要用batchUpdate而不是循环里单条insert。单条插入3万条数据耗时以分钟算批量插入能压缩到几十秒内差别非常明显。5.2 事务边界到底画在哪实操中我会推荐“解析和校验”放在事务之前入库放入事务之内。原因是POI解析耗时较长如果解析过程也放在事务里意味着数据库连接会被长时间占着在高并发场景下容易把连接池拖垮。更好的方式是先完整解析Excel并做内存内的校验把不合规的行剔除出去只对合法数据做批量插入。这样事务持库时间大大缩短。事务在有校验失败的场景下应该整体回滚还是部分导入要看业务场景。比如设备台账导入如果第500行有个设备名重复业务方希望前面499行仍然导入那就在事务里捕获单行异常并继续并把错误行号记录到日志如果是财务对账导入任何一行错误都不能让整体数据进去那一旦发现异常直接抛RuntimeException回滚全部。这块代码之前是加了一个flag配置来控制两种模式if (!excelImportConfig.isAllowPartial()) { if (failCount 0) { throw new RuntimeException(第 errorRows.toString() 行数据校验失败已回滚全部导入); } }导入完成后还有一个容易被忽视的点返回给前端的成功条数应该以最终入库行数为准而不是Excel总行数。因为表头、空行、校验失败的行都不该算数。我见过不少同事直接拿rowCount减1当成功条数用户看到“成功导入999条”实际只入库了990条这种不一致会让业务方对系统的信任度直线下降。6. 生成Excel导出模板和业务数据的经验细节导出功能相对简单但也有很多细节直接影响文件能否正常打开、乱不乱码、快不快。6.1 通过POI创建导出Excel导出的核心代码RequestMapping(/exportExcel) public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { ListUserInfo list userService.queryAllUser(); Workbook workbook new XSSFWorkbook(); Sheet sheet workbook.createSheet(用户信息); String[] headers {姓名, 年龄, 联系电话}; CellStyle headerStyle workbook.createCellStyle(); Font headerFont workbook.createFont(); headerFont.setBold(true); headerStyle.setFont(headerFont); Row headerRow sheet.createRow(0); for (int i 0; i headers.length; i) { Cell cell headerRow.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(headerStyle); sheet.setColumnWidth(i, 20 * 256); } for (int i 0; i list.size(); i) { Row row sheet.createRow(i 1); row.createCell(0).setCellValue(list.get(i).getName()); row.createCell(1).setCellValue(list.get(i).getAge()); row.createCell(2).setCellValue(list.get(i).getPhone()); } // 设置响应头 String fileName 用户信息_ System.currentTimeMillis() .xlsx; fileName URLEncoder.encode(fileName, UTF-8); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment;filename fileName); workbook.write(response.getOutputStream()); workbook.close(); }导出这块有三个坑必须提醒。Content-Disposition里的filename中包含中文时务必先URLEncoder.encode然后在浏览器端自动解码。不处理的话Chrome下载的文件名会是一串百分号Firefox可能直接乱码。setColumnWidth的宽度单位有讲究POI里宽度单位是1/256个字符宽度。20*256表示20个字符宽。老是不乘256的话导出的Excel列宽会窄到令人崩溃。导出大数据量Excel时XSSFWorkbook全部数据驻留内存5万行就能感觉到卡顿20万行直接OOM是常态。这种情况下要升级为SXSSFWorkbook也就是流式写入版本。它会在内存中只保留规定行数的数据其余行刷到磁盘临时文件。用法和XSSFWorkbook几乎一致但实例化签名不同new SXSSFWorkbook(100)表示窗口大小是100行超过就滚动刷盘。6.2 WebUploader下载Excel的兼容做法WebUploader本身没有专门的下载API前端通常用window.location或者iframe发起下载请求function downloadExcel() { window.location.href basePath /file/exportExcel; }这个方案后端要设置好Content-Disposition: attachment。用户点击后浏览器会直接触发文件下载不会跳转页面。实测Chrome、Firefox、Edge都正常但老IE对这类响应处理存在缓存问题用户首次下载可能拿到旧数据。解决方式是在导出接口URL上加一个随机尾参window.location.href basePath /file/exportExcel?t new Date().getTime();强制浏览器不缓存这招在IE下屡试不爽。7. 导入模板的设计规范与前端校验细节很多项目翻车不是代码问题而是“Excel模板本身写得随意”。我这边沉淀了一套模板规范直接在项目里推行。7.1 模板统一设计原则第一行固定为表头表头内容要跟代码里读取的列顺序严格一致不允许标题占行、说明占行的结构。日期统一格式为yyyy-MM-dd或以字符串文本形式存在避免POI解析时出现日期类型和字符串类型的差异化分支。列与列之间不允许有合并单元格合并单元格在实际项目里解析逻辑复杂且极易出错代码里需要额外判断合并区域工作量大且收益低。示例数据不要出现在模板里防止用户忘记清理而把示例当成真实数据导入。7.2 前端下载标准模板为了让用户按模板填写系统要提供“下载导入模板”的按钮。我的实现方式是后端写一个createTemplate接口只生成表头不带数据行存到临时目录然后前端用相同下载方式触发。这样做的好处是模板永远跟后端代码里的表头保持同步。有人图省事把模板静态文件丢在webapps下后期表头一改动旧模板文件就成历史包袱用户下载的模板导入时直接报列数不匹配。8. 典型问题排查链路分享最后复盘几个最常见的坑把完整排查链路写出来方便卡壳的同学知道自己卡在哪一步。8.1 文件传了但后端拿不到Part现象前端显示上传成功后端request.getPart(file)返回null或抛出IllegalStateException。排查链路打开F12看请求Payload确认Form Data里是否包含file字段且对应的是二进制内容不是纯文件名文本。确认fileVal是否等于后端取参参数名。WebUploader里fileVal默认值是file后端如果写request.getParameter(file)去取在multipart请求里取到的是null必须用getPart。确认WebUploader初始化代码里server参数路径是否正确尤其注意项目部署在根路径还是二级路径下。查看服务器日志有没有MultipartException或文件大小超限异常。Spring的multipart配置里有max-file-size和max-request-size两个参数一旦超过会直接抛异常前端表现就是uploadError。8.2 POI解析xlsx时报“NoSuchMethodError”或“Zip bomb”现象解析小型文件正常解析某个特定xlsx就报错。排查链路先看异常栈是不是在org.apache.poi.openxml4j里如果是Zip bomb related通常是文件内部包含异常膨胀的zip数据或者恶意构造的大文件。POI有ZipSecureFile的最小压缩比保护机制默认比率是0.01如果文件内嵌图片过多压缩比低于此值时就会触发误报。处理方式是适当调低临界值ZipSecureFile.setMinInflateRatio(0.001)或者在解析前调大限制ZipSecureFile.setMaxEntrySize(100 * 1024 * 1024L)。NoSuchMethodError则几乎都是依赖版本不对齐引起的比对pom里poi、poi-ooxml、poi-ooxml-schemas、xmlbeans的版本号4.1.2对应的xmlbeans必须是3.1.0。8.3 Excel读取出来全是科学计数法现象解析手机号、身份证号这类超过11位的数字列得到1.23456789123E10。排查链路在getCellValue的NUMERIC分支里打印原始value确认是否double类型。在Excel模板里把该列单元格格式设置为文本这是源头解决法。后端兜底方案判断数值在Excel里原本是整数类型时使用new BigDecimal(String.valueOf(cell.getNumericCellValue())).toBigInteger().toString()来精确还原数字字符串避免double精度丢失。注意不能直接用toString因为double类型的1.8612345678E10转成字符串丢尾数。8.4 导入速度慢到无法接受现象导入1万行Excel页面转圈超过1分钟。排查链路检查是否逐行insert如果是先改成batchUpdate效果立竿见影。确认连接池的maxActive是否足够。批量插入时如果连接池只有10个连接而其他业务大量占用批量插入会进入等待。检查是否有不必要的workbook.write缓存或日志输出。解析循环里打印每一行日志的坑我见过不止一次上线前一定要关掉。9. 从可维护性角度做的几个设计增强基础功能跑通之后我更倾向于在结构上做一些增强避免代码膨胀后改不动。9.1 把解析和入库拆成接口不要把所有Excel导入逻辑写在一个几百行的Servlet里。我会定义两个核心接口ExcelRowParser负责“把一行Row对象转为业务对象或DTO”ExcelImportService负责“传入List业务对象执行校验入库”。这样每个业务模块只需要新增自己的Parser和Service实现公共的WebUploader接收流程、POI解析循环、错误处理中间件就完全复用。上线需求一个接一个来的时候大概率是新增两个类而不是改动核心链路的公共代码。9.2 导入日志落库每次导入都记录操作日志表内容包含操作人、导入时间、文件名、成功条数、失败条数、失败详情。这个业务价值远大于技术价值——一旦业务方反馈“数据对不上”翻日志可以快速定位是哪个文件、哪一行、谁导入的。我自己遇到过客户深夜反馈某月销售数据少了3万条最后就是靠导入日志找到了导入错月份文件的操作记录避免了一场数据事故变成开发事故。9.3 内存优化的特殊实践对于超大Excel的解析除了升级SXSSFWorkbook来应对写入侧读取侧还可以使用XSSFWorkbook的事件模型SAX解析方式但那需要操作底层的OPCPackage和SAXParserHelper代码复杂度提升一个量级学习成本高日常业务里一般不需要碰。站在性价比角度我会先限制上传文件大小在20MB以内同时把最大导入行数控制在5万行超过就明确提示用户拆分文件再导入。这个限制其实也符合业务真实情况——真要几十万条数据的原始导入应该走ETL工具而不是让用户在浏览器里传Excel。10. 一个快速跑通的示例流程整体流程串一遍假设我现在新建一个用户信息导入功能。后端新建两个接口POST /file/importExcel接收WebUploader上传的file参数解析Excel并批量插入用户表。GET /file/exportExcel查询全部用户并生成xlsx文件返回。前端JSP页面引入webuploader.css和webuploader.js页面初始化WebUploader实例绑定fileQueued、uploadProgress、uploadSuccess事件。JSP页面放置div idfilePicker选择Excel文件/div table idfileTable/table button onclickdownloadExcel()导出用户信息/button服务器端依赖、数据库表结构准备好上传一个符合模板的用户Excel页面出现进度条走完显示成功条数数据库里就能查到自己写入的数据行。这个链路我资源复用了多次从最初的4个文件到现在维护了十几套不同业务的导入导出公共核心代码基本没动过只新增业务解析器和对应的Service实现维护成本低了很多。希望这份实践笔记能帮你绕开我踩过的那些暗坑少花些不必要的调试时间。