java Excel表格导入

html:

<div class="form-group"> <div style="display: none" id="uploadFile"><input type="file" id="file" ></div> <a class="btn btn-primary" \@click="upload"> <i class="fa fa-search"></i> 导入</a> </div>

js:

upload: function() { $("#uploadFile").empty(); $("#uploadFile").append("<input type=\"file\" id=\"file\">"); $("#file").click(); $("#file").on("change", function () { var file = document.getElementById("file").files[0]; if (file == null || file == undefined) { dialogMsg('请选择上传文件！'); return false; } var fm = new FormData(); fm.append('file', file); $.ajax( { url: '../../student/grade/importExcel', data: fm, type: 'POST', dataType: 'json', contentType: false, processData: false, success: function (data) { if (data.code == '0') { dialogMsg(data.msg, "success"); vm.load(); } else { dialogMsg(data.msg, "error"); } } } ) }) },

java:

/** * 表格导入 * @param request * @return */ @RequestMapping("/importExcel") public R importExcel(HttpServletRequest request) { CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver( request.getSession().getServletContext()); if (multipartResolver.isMultipart(request)) { MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; MultipartFile file = multiRequest.getFile("file"); long fileSize = file.getSize(); if (file.isEmpty()) { return R.error( "文件为空"); } else if (fileSize > (10 * 1024 * 1024)) { return R.error("文件过大"); } else { // 获取文件名 String fileName = file.getOriginalFilename(); //验证文件名是否合格 if (!ExcelImportUtils.validateExcel(fileName)) { return R.error( "文件必须是excel格式！"); } //进一步判断文件内容是否为空（即判断其大小是否为0或其名称是否为null） long size = file.getSize(); if (StringUtils.isEmpty(fileName) || size == 0) { return R.error("文件不能为空！"); } //批量导入 return this.batchImport(fileName, file); } } return R.error(); } private R batchImport(String fileName, MultipartFile mfile) { try { Workbook wb = null; if (ExcelImportUtils.isExcel2007(fileName)) { wb = new XSSFWorkbook(mfile.getInputStream()); } else { wb = new HSSFWorkbook(mfile.getInputStream()); } //根据excel里面的内容读取知识库信息 int count = readExcelValue(wb); if (count > 0) { return R.ok("导入成功！"); } else { return R.error("导入出错！请检查数据格式！"); } } catch (Exception e) { e.printStackTrace(); } return R.error("导入出错！请检查数据格式！"); } private int readExcelValue(Workbook wb) { Long areaId=sysUserService.userArea().getAreaId(); //得到第一个shell Sheet sheet = wb.getSheetAt(0); //得到Excel的行数 int totalRows = sheet.getPhysicalNumberOfRows(); //循环Excel行数,从第1行开始。标题不入库 for (int r = 1; r < totalRows; r++) { Row row = sheet.getRow(r); if (row != null) { XStudentGradeEntity entity = new XStudentGradeEntity(); //循环Excel的列 if (row.getCell(0) != null) { row.getCell(0).setCellType(CellType.STRING); if (!"".equals(row.getCell(0).getStringCellValue())) { entity.setName(row.getCell(0).getStringCellValue()); } } if (row.getCell(1) != null) { row.getCell(1).setCellType(CellType.STRING); if (!"".equals(row.getCell(1).getStringCellValue())) { entity.setIdCardNo(row.getCell(1).getStringCellValue()); } } if (row.getCell(2) != null) { row.getCell(2).setCellType(CellType.STRING); if (!"".equals(row.getCell(2).getStringCellValue())) { entity.setGrade(Integer.valueOf(row.getCell(2).getStringCellValue())); } } if (row.getCell(3) != null) { row.getCell(3).setCellType(CellType.STRING); if (!"".equals(row.getCell(3).getStringCellValue())) { entity.setSchoolName(row.getCell(3).getStringCellValue()); } } entity.setId(UUID.randomUUID().toString().replace("-", "")); entity.setOrgId(areaId); xStudentGradeService.saveXStudentGrade(entity); } } return 1; }

utils:

package com.x.common.utils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class ExcelImportUtils { // @描述：是否是2003的excel，返回true是2003 public static boolean isExcel2003(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } //@描述：是否是2007的excel，返回true是2007 public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 验证EXCEL文件 * @param filePath * @return */ public static boolean validateExcel(String filePath){ if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){ return false; } return true; } public List<List<String>> importExcel(InputStream inputStream, String fileName) throws Exception{ List<List<String>> listALL = new ArrayList<>(); boolean isE2007 = false; //判断是否是excel2007格式 if(fileName.endsWith("xlsx")){ isE2007 = true; } try { InputStream input = inputStream; //建立输入流 Workbook wb; //根据文件格式(2003或者2007)来初始化 if(isE2007){ wb = new XSSFWorkbook(input); }else{ wb = new HSSFWorkbook(input); } Sheet sheet = wb.getSheetAt(0); //获得第一个表单 int rowCount = sheet.getLastRowNum()+1; for(int i = 2; i < rowCount;i++){ Row row ; List<String> listRow = new ArrayList<>(); for(int j = 0;j<26;j++){ if(isMergedRegion(sheet,i,j)){ String rowString = getMergedRegionValue(sheet, i, j); if(rowString!=null&&!"".equals(rowString)){ listRow.add(rowString); }else{ listRow.add(""); } }else{ row = sheet.getRow(i); if(row.getCell(j)!=null){ listRow.add(row.getCell(j).toString()); }else{ listRow.add(""); } } } listALL.add(listRow); } } catch (Exception ex) { ex.printStackTrace(); } return listALL; } /** * 获取单元格的值 * @param cell * @return */ public String getCellValue(Cell cell){ if(cell == null) {return "";} cell.setCellType(CellType.STRING); return cell.getStringCellValue(); } /** * 合并单元格处理,获取合并行 * @param sheet * @return List<CellRangeAddress> */ public List<CellRangeAddress> getCombineCell(Sheet sheet) { List<CellRangeAddress> list = new ArrayList<>(); //获得一个 sheet 中合并单元格的数量 int sheetmergerCount = sheet.getNumMergedRegions(); //遍历所有的合并单元格 for(int i = 0; i<sheetmergerCount;i++) { //获得合并单元格保存进list中 CellRangeAddress ca = sheet.getMergedRegion(i); list.add(ca); } return list; } private int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){ int xr = 0; int firstC = 0; int lastC = 0; int firstR = 0; int lastR = 0; for(CellRangeAddress ca:listCombineCell) { //获得合并单元格的起始行, 结束行, 起始列, 结束列 firstC = ca.getFirstColumn(); lastC = ca.getLastColumn(); firstR = ca.getFirstRow(); lastR = ca.getLastRow(); if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) { if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) { xr = lastR; } } } return xr; } /** * 判断单元格是否为合并单元格，是的话则将单元格的值返回 * @param listCombineCell 存放合并单元格的list * @param cell 需要判断的单元格 * @param sheet sheet * @return */ public String isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet) throws Exception{ int firstC = 0; int lastC = 0; int firstR = 0; int lastR = 0; String cellValue = null; for(CellRangeAddress ca:listCombineCell) { //获得合并单元格的起始行, 结束行, 起始列, 结束列 firstC = ca.getFirstColumn(); lastC = ca.getLastColumn(); firstR = ca.getFirstRow(); lastR = ca.getLastRow(); if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) { if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) { Row fRow = sheet.getRow(firstR); Cell fCell = fRow.getCell(firstC); cellValue = getCellValue(fCell); break; } } else { cellValue = ""; } } return cellValue; } /** * 获取合并单元格的值 * @param sheet * @param row * @param column * @return */ public String getMergedRegionValue(Sheet sheet, int row, int column){ int sheetMergeCount = sheet.getNumMergedRegions(); for(int i = 0 ; i < sheetMergeCount ; i++){ CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ Row fRow = sheet.getRow(firstRow); Cell fCell = fRow.getCell(firstColumn); return getCellValue(fCell) ; } } } return null ; } /** * 判断指定的单元格是否是合并单元格 * @param sheet * @param row 行下标 * @param column 列下标 * @return */ private boolean isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ return true; } } } return false; } }