POI实现Excel批量导入数据（SpringMVC）

来源：互联网发布：log4j json格式化编辑：程序博客网时间：2024/06/16 10:18

最近再做个东西，需要Excel批量导入数据，以前没有做过这类东西，写篇博客做个记录

首先是前期准备，在SpringMVC配置文件中开启文件上传

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>

导入相关的Jar包

xbean.jar

jsr173_1.0_api.jar

dom4j-1.6.1.jar
poi-3.8-20120326.jar
poi-ooxml-3.5-FINAL.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
xmlbeans-2.3.0.jar

前台代码

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><head><script type="text/javascript">function closeDialog() {$.pdialog.closeCurrent();navTab.reload("basinfo/sourceRiskList", {navTabId : "sourceRiskList"});}</script></head><div class="pageContent"><form method="post" action="basinfo/importExcel" class="pageForm required-validate" onsubmit="return iframeCallback(this, dialogAjaxDone);" enctype="multipart/form-data" ><table width="590" style="border-collapse:separate; border-spacing:0px 10px;"><tr height="90"><td><lable>导入文件</lable></td><td> <input type="file" name="sourceRiskFile"/> </td><td> <button type="submit">上传</button> </td><td> <button onclick="closeDialog()">查看上传结果</button> </td></tr><tr height="50"></tr></table></form></div>

Controller代码

@RequestMapping(value="importExcel", method=RequestMethod.POST)@ResponseBodypublic PageResult importExcel(@RequestParam(value = "sourceRiskFile", required = false) MultipartFile sourceRiskFile,HttpServletRequest request, Model model){String fileName = sourceRiskFile.getOriginalFilename();System.out.println(fileName);PageResult result = new PageResult();if(sourceRiskFile!=null){//判断该文件是否为Excel文件if(fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){try {int res = sourceRiskService.importExcel(sourceRiskFile, sourceRiskFile.getOriginalFilename());result.setMessage("成功添加" + res + "条记录");result.setStatusCode(200);} catch (Exception e) {e.printStackTrace();result.setStatusCode(500);result.setMessage("添加失败，请检查文件是否符合要求");}}else{result.setStatusCode(500);result.setMessage("添加失败，请检查文件是否为Excel文件");}}return result;}

Service代码

@Transactional(readOnly=false)public int importExcel(MultipartFile file, String fileName){//记录成功添加的记录数int res = 0;System.out.println(1111);try {InputStream fileInputStream = file.getInputStream();        //判断是否是03版本的Excel(还是07的)          boolean is03Excel = fileName.matches("^.+\\.(?i)(xls)$");          //1、读取工作簿          Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream)                  : new XSSFWorkbook(fileInputStream);          //2、读取工作表          Sheet sheet=workbook.getSheetAt(0);          //3、读取行          System.out.println(sheet.getPhysicalNumberOfRows());        if(sheet.getPhysicalNumberOfRows()>1){              SourceRisk sourceRisk = null;              for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {                  //4、读取单元格                  Row row=sheet.getRow(i);                  sourceRisk = new SourceRisk();                  if(row==null || row.getCell(0) == null){                continue;                }                //Id                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);                  sourceRisk.setId(row.getCell(0).getStringCellValue());                  //危险源名称                  row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);                sourceRisk.setName(row.getCell(1).getStringCellValue());                      //风险源                  try {                //先查询是否存在，然后再添加                SourceRisk temp = sourceRiskDao.findEntityById(sourceRisk.getId());                if(temp == null){                sourceRiskDao.add(sourceRisk);                res++;                                }} catch (Exception e) {}            }         }        fileInputStream.close(); } catch (Exception e) {e.printStackTrace();}return res;}

阅读全文

0 0