1.入口
@RequestMapping(value = "/importNewPracticeScore",method=RequestMethod.POST)@ResponseBodypublic R doFileUploads(@RequestBody Map<String, Object> map,HttpServletRequest request, final HttpServletResponse response) throws Exception {String id = (String) map.get("id");String examTaskId = (String) map.get("examTaskId");String[] headStr = new String[]{"科目", "考籍号", "成绩"};//头部列String courseName = "音乐,美术,通用技术,体育";//科目列名称List<Score> retList = new ArrayList<Score>();List<String> list =new ArrayList<>(); int totalRows = 0;//excel解析实体对象ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();totalRows = excelXlsxReader.process2(uploadFile.getInputStream());list = excelXlsxReader.getExcelDataList();list = list.parallelStream().filter(string -> !string.replace(",", "").isEmpty()).collect(Collectors.toList());String[] array = list.get(0).split(",", -1);if(array.length != headStr.length) {throw new RRException("导入失败，请核实是否为正确下载的模板");}//查询该考务下所有学生考籍号信息List<Score> allScoreList = baseService.selectScoreRegisterNum(examTaskId);Map<String,Score> maps = new HashMap<>();//查询该考务下学生报名科目//List<ExamineeEntity> examineeEntityList = (List<ExamineeEntity>) netsTaskHBService.selectExamineeListById(examTaskId);//格式化数据for (int i = 0; i < list.size(); i++) {long d1 = System.currentTimeMillis();String errMsg = "";String str = list.get(i);String[] strs = str.split(",", -1);// 校验考籍号if (StringUtils.isEmpty(strs[1])) {throw new RRException("请填写该考生考籍号,");}Optional<Score> examineeBo = allScoreList.parallelStream().filter(v -> v.getRegisterNum().equals(strs[1])).findFirst();if(examineeBo.isPresent()){// 校验科目if (StringUtils.isEmpty(strs[0])) {throw new RRException("请填写该考生"+strs[1]+"科目");}else{if (!courseName.contains(strs[0])){throw new RRException("表格中有非可选科目，请检查后重新上传");}Score score = new Score();score.setCourseName(strs[0]);score.setRegisterNum(strs[1]);score.setScoreTaskId(examTaskId);Score scoreNew = examineeBo.get();if(strs[0].equals("音乐")){score.setMusicPractice(!StringUtils.isEmpty(strs[2])?BigDecimal.valueOf(Double.valueOf(strs[2])):new BigDecimal(0));if(scoreNew != null && scoreNew.getMusicPC() != null&& score.getMusicPractice().compareTo(new BigDecimal("-1"))==1&& scoreNew.getMusicPC().compareTo(new BigDecimal("-1"))==1){score.setMusic(score.getMusicPractice().add(scoreNew.getMusicPC()));}else{score.setMusic(score.getMusicPractice().compareTo(new BigDecimal("-1"))==0?new BigDecimal(0):score.getMusicPractice());}}retList.add(score);}}else{throw new RRException("考籍号对应学生不存在");}long d2 = System.currentTimeMillis();System.out.println("耗时："+(d2-d1));}if(retList!=null && retList.size()>0){int count = retList.size();if(count<5000){baseService.updateBatchScore(retList);}else{int pageSize = 5000;Integer pageCount = 0; //页数if (count % pageSize == 0) {pageCount = count / pageSize;} else {pageCount = count / pageSize + 1;}long begin = System.currentTimeMillis();for (Integer i = 1; i <= pageCount; i++) {int fromIndex = 0; //开始索引int toIndex = 0; //结束索引if (!i.equals(pageCount)) {fromIndex = (i - 1) * pageSize;toIndex = fromIndex + pageSize;} else {fromIndex = (i - 1) * pageSize;toIndex = count;}List<Score> pageList = retList.subList(fromIndex, toIndex);System.out.println(pageList.get(0).getRegisterNum());baseService.updateBatchScore(retList);}}}return R.ok();}2.Excel工具类
public class ExcelXlsxReader extends DefaultHandler {/*** 单元格中的数据可能的数据类型*/enum CellDataType {BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL}/*** 共享字符串表*/private SharedStringsTable sst;/*** 上一次的索引值*/private String lastIndex;/*** 文件的绝对路径*/private String filePath = "";/*** 工作表索引*/private int sheetIndex = 0;/*** sheet名*/private String sheetName = "";/*** 总行数*/private int totalRows=0;/*** 一行内cell集合*/private List<String> cellList = new ArrayList<String>();/*** 全部行集合*/private List<String> excelDataList = new ArrayList<>();/*** 判断整行是否为空行的标记*/private boolean flag = false;/*** 当前行*/private int curRow = 1;/*** 当前列*/private int curCol = 0;/*** T元素标识*/private boolean isTElement;/*** 判断上一单元格是否为文本空单元格*/private boolean startElementFlag = true;private boolean endElementFlag = false;private boolean charactersFlag = false;/*** 异常信息，如果为空则表示没有异常*/private String exceptionMessage;/*** 单元格数据类型，默认为字符串类型*/private CellDataType nextDataType = CellDataType.SSTINDEX;private final DataFormatter formatter = new DataFormatter();/*** 单元格日期格式的索引*/private short formatIndex;/*** 日期格式字符串*/private String formatString;//定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等private String prePreRef = "A", preRef = null, ref = null;//定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格private String maxRef = null;/*** 单元格*/private StylesTable stylesTable;/*** 遍历工作簿中所有的电子表格* 并缓存在mySheetList中** @param filename* @throws Exception*/public int process(String filename) throws Exception {filePath = filename;OPCPackage pkg = OPCPackage.open(filename);XSSFReader xssfReader = new XSSFReader(pkg);stylesTable = xssfReader.getStylesTable();SharedStringsTable sst = xssfReader.getSharedStringsTable();XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");this.sst = sst;parser.setContentHandler(this);XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();while (sheets.hasNext()) { //遍历sheetcurRow = 1; //标记初始行为第一行sheetIndex++;InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错sheetName = sheets.getSheetName();InputSource sheetSource = new InputSource(sheet);parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行sheet.close();}return totalRows;}public int process2(InputStream inputStream) throws Exception {
// filePath = file.getPath();OPCPackage pkg = OPCPackage.open(inputStream);XSSFReader xssfReader = new XSSFReader(pkg);stylesTable = xssfReader.getStylesTable();SharedStringsTable sst = xssfReader.getSharedStringsTable();XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");this.sst = sst;parser.setContentHandler(this);XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();while (sheets.hasNext()) { //遍历sheetcurRow = 1; //标记初始行为第一行sheetIndex++;InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错sheetName = sheets.getSheetName();InputSource sheetSource = new InputSource(sheet);parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行sheet.close();}return totalRows;}/*** 第一个执行** @param uri* @param localName* @param name* @param attributes* @throws SAXException*/@Overridepublic void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {//c => 单元格if ("c".equals(name)) {//前一个单元格的位置if (preRef == null) {preRef = attributes.getValue("r");} else {//中部文本空单元格标识 ‘endElementFlag’ 判断前一次是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串跳过把空字符串的位置赋予preRefif (endElementFlag){preRef = ref;}}//当前单元格的位置ref = attributes.getValue("r");//首部文本空单元格标识 ‘startElementFlag’ 判断前一次，即首部是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串, 且已知当前格，即第二格带“B”标志，则ref赋予preRefif (!startElementFlag && !flag){ //上一个单元格为文本空单元格，执行下面的，使ref=preRef；flag为true表明该单元格之前有数据值，即该单元格不是首部空单元格，则跳过// 这里只有上一个单元格为文本空单元格，且之前的几个单元格都没有值才会执行preRef = ref;}//设定单元格类型this.setNextDataType(attributes);endElementFlag = false;charactersFlag = false;startElementFlag = false;}//当元素为t时if ("t".equals(name)) {isTElement = true;} else {isTElement = false;}//置空lastIndex = "";}/*** 第二个执行* 得到单元格对应的索引值或是内容值* 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值* 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值* @param ch* @param start* @param length* @throws SAXException*/@Overridepublic void characters(char[] ch, int start, int length) throws SAXException {startElementFlag = true;charactersFlag = true;lastIndex += new String(ch, start, length);}/*** 第三个执行** @param uri* @param localName* @param name* @throws SAXException*/@Overridepublic void endElement(String uri, String localName, String name) throws SAXException {//t元素也包含字符串if (isTElement) {//这个程序没经过//将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符String value = lastIndex.trim();cellList.add(curCol, value);endElementFlag = true;curCol++;isTElement = false;//如果里面某个单元格含有值，则标识该行不为空行if (value != null && !"".equals(value)) {flag = true;}} else if ("v".equals(name)) {//v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值//补全单元格之间的空单元格if (!ref.equals(preRef)) {int len = countNullCell(ref, preRef);for (int i = 0; i < len; i++) {cellList.add(curCol, "");curCol++;}} else if (ref.equals(preRef) && !ref.startsWith("A")){ //ref等于preRef，且以B或者C...开头，表明首部为空格int len = countNullCell(ref, "A");for (int i = 0; i <= len; i++) {cellList.add(curCol, "");curCol++;}}cellList.add(curCol, value);curCol++;endElementFlag = true;//如果里面某个单元格含有值，则标识该行不为空行if (value != null && !"".equals(value)) {flag = true;}} else {//如果标签名称为row，这说明已到行尾，调用optRows()方法if ("row".equals(name)) {//默认第一行为表头，以该行单元格数目为最大数目if (curRow == 1) {maxRef = ref;}//补全一行尾部可能缺失的单元格if (maxRef != null) {int len = -1;//前一单元格，true则不是文本空字符串，false则是文本空字符串if (charactersFlag){len = countNullCell(maxRef, ref);}else {len = countNullCell(maxRef, preRef);}for (int i = 0; i <= len; i++) {cellList.add(curCol, "");curCol++;}}if (flag&&curRow!=1){ //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）//ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);ExcelReaderUtil.sendRows(cellList,excelDataList);totalRows++;}cellList.clear();curRow++;curCol = 0;preRef = null;prePreRef = null;ref = null;flag=false;}}}/*** 处理数据类型** @param attributes*/public void setNextDataType(Attributes attributes) {nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字formatIndex = -1;formatString = null;String cellType = attributes.getValue("t"); //单元格类型String cellStyleStr = attributes.getValue("s"); //String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1if ("b".equals(cellType)) { //处理布尔值nextDataType = CellDataType.BOOL;} else if ("e".equals(cellType)) { //处理错误nextDataType = CellDataType.ERROR;} else if ("inlineStr".equals(cellType)) {nextDataType = CellDataType.INLINESTR;} else if ("s".equals(cellType)) { //处理字符串nextDataType = CellDataType.SSTINDEX;} else if ("str".equals(cellType)) {nextDataType = CellDataType.FORMULA;}if (cellStyleStr != null) { //处理日期int styleIndex = Integer.parseInt(cellStyleStr);XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);formatIndex = style.getDataFormat();formatString = style.getDataFormatString();if (formatString.contains("m/d/yyyy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d") ) {nextDataType = CellDataType.DATE;formatString = "yyyy-MM-dd hh:mm:ss";}if (formatString == null) {nextDataType = CellDataType.NULL;formatString = BuiltinFormats.getBuiltinFormat(formatIndex);}}}/*** 对解析出来的数据进行类型处理* @param value 单元格的值，* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值* @param thisStr 一个空字符串* @return*/@SuppressWarnings("deprecation")public String getDataValue(String value, String thisStr) {switch (nextDataType) {// 这几个的顺序不能随便交换，交换了很可能会导致数据错误case BOOL: //布尔值char first = value.charAt(0);thisStr = first == '0' ? "FALSE" : "TRUE";break;case ERROR: //错误thisStr = "\"ERROR:" + value.toString() + '"';break;case FORMULA: //公式thisStr = '"' + value.toString() + '"';break;case INLINESTR:XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());thisStr = rtsi.toString();rtsi = null;break;case SSTINDEX: //字符串String sstIndex = value.toString();try {int idx = Integer.parseInt(sstIndex);XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值thisStr = rtss.toString();//有些字符串是文本格式的，但内容却是日期rtss = null;} catch (NumberFormatException ex) {thisStr = value.toString();}break;case NUMBER: //数字if (formatString != null) {thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();} else {thisStr = value;}thisStr = thisStr.replace("_", "").trim();break;case DATE: //日期thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);// 对日期字符串作特殊处理，去掉TthisStr = thisStr.replace("T", " ");break;default:thisStr = " ";break;}return thisStr;}public int countNullCell(String ref, String preRef) {//excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFDString xfd = ref.replaceAll("\\d+", "");String xfd_1 = preRef==null?"":preRef.replaceAll("\\d+", "");xfd = fillChar(xfd, 3, '@', true);xfd_1 = fillChar(xfd_1, 3, '@', true);char[] letter = xfd.toCharArray();char[] letter_1 = xfd_1.toCharArray();int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);return res - 1;}public String fillChar(String str, int len, char let, boolean isPre) {int len_1 = str.length();if (len_1 < len) {if (isPre) {for (int i = 0; i < (len - len_1); i++) {str = let + str;}} else {for (int i = 0; i < (len - len_1); i++) {str = str + let;}}}return str;}/*** @return the exceptionMessage*/public String getExceptionMessage() {return exceptionMessage;}public List<String> getExcelDataList() {return this.excelDataList;}public void setExcelDataList(final List<String> excelDataList) {this.excelDataList = excelDataList;}
}3.pom引入jar
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17</version></dependency><dependency><groupId>com.syncthemall</groupId><artifactId>boilerpipe</artifactId><version>1.2.1</version></dependency><dependency><groupId>xerces</groupId><artifactId>xercesImpl</artifactId><version>2.11.0</version></dependency><dependency><groupId>xml-apis</groupId><artifactId>xml-apis</artifactId><version>1.4.01</version></dependency><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>2.6.0</version></dependency><dependency><groupId>sax</groupId><artifactId>sax</artifactId><version>2.0.1</version></dependency>