POI读取excel百万级-SAX方式解析

Myth丶恋晨 2022-04-03 09:13 287阅读 0赞

一. 简介

  1.        在excel解析的时候，采用SAX方方式会将excel转换为xml进行解析避免了内存溢出。
  3.        速度在3秒1W的数据写入，100W条记录，大概50M的数据，耗时大概4分半（如果不需要校验，可能会更快）；
  5.        暂时先直接将项目中的拷贝出来，使用的时候直接调工具类即可。目前正在搞自己的一个项目，后期会将导入，导出都弄上去，再优化下，放到git上。
  7.        另外，膜拜下原生jdbc, 昨天问了下，听说可以达到1秒10W数量级的写入；
  9.        是在网上找的一个，然后自己封装了下， 加了一个委托，目前存在一个BUG： 就是如果excel中没有数据，会自动跳过该空格。目前解决方案是：excel中为空的使用“-”来标识，后期解决。
  11.        关于去重的，请看最下面的案例说明。
  13.        注： 这个中间变量处理的并不完善  可以参考博主的另一篇博客                       
  15.                 https://blog.csdn.net/qq\_35206261/article/details/88579151
  17.                 里面的变量边界处理的比较完善，  这个暂时没有处理，因为有点缺陷，准备换为阿里的easyExcel的导入方式。待处理

二. 代码DEMO

2.1 POM依赖

  1. <!-- poi -->
  2.       <dependency>
  3.           <groupId>org.apache.poi</groupId>
  4.           <artifactId>poi</artifactId>
  5.           <version>3.17</version>
  6.       </dependency>
  7.       <dependency>
  8.           <groupId>org.apache.poi</groupId>
  9.           <artifactId>poi-ooxml</artifactId>
  10.           <version>3.17</version>
  11.       </dependency>
  12.       <dependency>
  13.           <groupId>org.apache.poi</groupId>
  14.           <artifactId>poi-ooxml-schemas</artifactId>
  15.           <version>3.17</version>
  16.       </dependency>
  19.       <!-- sax -->
  20.       <dependency>
  21.           <groupId>sax</groupId>
  22.           <artifactId>sax</artifactId>
  23.           <version>2.0.1</version>
  24.       </dependency>
  25.       <dependency>
  26.           <groupId>xml-apis</groupId>
  27.           <artifactId>xml-apis</artifactId>
  28.           <version>1.4.01</version>
  29.       </dependency>
  30.       <dependency>
  31.           <groupId>org.apache.xmlbeans</groupId>
  32.           <artifactId>xmlbeans</artifactId>
  33.           <version>2.6.0</version>
  34.       </dependency>
  35.       <dependency>
  36.           <groupId>xerces</groupId>
  37.           <artifactId>xercesImpl</artifactId>
  38.           <version>2.11.0</version>
  39.       </dependency>

2.2 EXCEL常量类

  1. package com.yzx.osp.common.constant;
  3. /**
  4.  * @author qjwyss
  5.  * @date 2018/9/19
  6.  * @description EXCEL常量类
  7.  */
  8. public class ExcelConstant {
  10.     /**
  11.      * excel2007扩展名
  12.      */
  13.     public static final String EXCEL07_EXTENSION = ".xlsx";
  16.     /**
  17.      * 每个sheet存储的记录数 100W
  18.      */
  19.     public static final Integer PER_SHEET_ROW_COUNT = 1000000;
  21.     /**
  22.      * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
  23.      */
  24.     public static final Integer PER_WRITE_ROW_COUNT = 200000;
  27.     /**
  28.      * 每个sheet的写入次数 5
  29.      */
  30.     public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
  33.     /**
  34.      * 读取excel的时候每次批量插入数据库记录数
  35.      */
  36.     public static final Integer PER_READ_INSERT_BATCH_COUNT = 10000;
  40. }

2.3 读取EXCEL辅助类

  1. package com.yzx.osp.common.util;
  3. import org.apache.poi.openxml4j.opc.OPCPackage;
  4. import org.apache.poi.ss.usermodel.BuiltinFormats;
  5. import org.apache.poi.ss.usermodel.DataFormatter;
  6. import org.apache.poi.xssf.eventusermodel.XSSFReader;
  7. import org.apache.poi.xssf.model.SharedStringsTable;
  8. import org.apache.poi.xssf.model.StylesTable;
  9. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  10. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
  11. import org.xml.sax.Attributes;
  12. import org.xml.sax.InputSource;
  13. import org.xml.sax.SAXException;
  14. import org.xml.sax.XMLReader;
  15. import org.xml.sax.helpers.DefaultHandler;
  16. import org.xml.sax.helpers.XMLReaderFactory;
  18. import java.io.InputStream;
  19. import java.util.ArrayList;
  20. import java.util.List;
  22. /**
  23.  * @author qjwyss
  24.  * @date 2018/12/19
  25.  * @description 读取EXCEL辅助类
  26.  */
  27. public class ExcelXlsxReaderWithDefaultHandler extends DefaultHandler {
  29.     private ExcelReadDataDelegated excelReadDataDelegated;
  31.     public ExcelReadDataDelegated getExcelReadDataDelegated() {
  32.         return excelReadDataDelegated;
  33.     }
  35.     public void setExcelReadDataDelegated(ExcelReadDataDelegated excelReadDataDelegated) {
  36.         this.excelReadDataDelegated = excelReadDataDelegated;
  37.     }
  39.     public ExcelXlsxReaderWithDefaultHandler(ExcelReadDataDelegated excelReadDataDelegated) {
  40.         this.excelReadDataDelegated = excelReadDataDelegated;
  41.     }
  43.     /**
  44.      * 单元格中的数据可能的数据类型
  45.      */
  46.     enum CellDataType {
  47.         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
  48.     }
  50.     /**
  51.      * 共享字符串表
  52.      */
  53.     private SharedStringsTable sst;
  55.     /**
  56.      * 上一次的索引值
  57.      */
  58.     private String lastIndex;
  60.     /**
  61.      * 文件的绝对路径
  62.      */
  63.     private String filePath = "";
  65.     /**
  66.      * 工作表索引
  67.      */
  68.     private int sheetIndex = 0;
  70.     /**
  71.      * sheet名
  72.      */
  73.     private String sheetName = "";
  75.     /**
  76.      * 总行数
  77.      */
  78.     private int totalRows = 0;
  80.     /**
  81.      * 一行内cell集合
  82.      */
  83.     private List<String> cellList = new ArrayList<String>();
  85.     /**
  86.      * 判断整行是否为空行的标记
  87.      */
  88.     private boolean flag = false;
  90.     /**
  91.      * 当前行
  92.      */
  93.     private int curRow = 1;
  95.     /**
  96.      * 当前列
  97.      */
  98.     private int curCol = 0;
  100.     /**
  101.      * T元素标识
  102.      */
  103.     private boolean isTElement;
  105.     /**
  106.      * 异常信息，如果为空则表示没有异常
  107.      */
  108.     private String exceptionMessage;
  110.     /**
  111.      * 单元格数据类型，默认为字符串类型
  112.      */
  113.     private CellDataType nextDataType = CellDataType.SSTINDEX;
  115.     private final DataFormatter formatter = new DataFormatter();
  117.     /**
  118.      * 单元格日期格式的索引
  119.      */
  120.     private short formatIndex;
  122.     /**
  123.      * 日期格式字符串
  124.      */
  125.     private String formatString;
  127.     //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
  128.     private String preRef = null, ref = null;
  130.     //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
  131.     private String maxRef = null;
  133.     /**
  134.      * 单元格
  135.      */
  136.     private StylesTable stylesTable;
  139.     /**
  140.      * 总行号
  141.      */
  142.     private Integer totalRowCount;
  144.     /**
  145.      * 遍历工作簿中所有的电子表格
  146.      * 并缓存在mySheetList中
  147.      *
  148.      * @param filename
  149.      * @throws Exception
  150.      */
  151.     public int process(String filename) throws Exception {
  152.         filePath = filename;
  153.         OPCPackage pkg = OPCPackage.open(filename);
  154.         XSSFReader xssfReader = new XSSFReader(pkg);
  155.         stylesTable = xssfReader.getStylesTable();
  156.         SharedStringsTable sst = xssfReader.getSharedStringsTable();
  157.         XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
  158.         this.sst = sst;
  159.         parser.setContentHandler(this);
  160.         XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
  161.         while (sheets.hasNext()) { //遍历sheet
  162.             curRow = 1; //标记初始行为第一行
  163.             sheetIndex++;
  164.             InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
  165.             sheetName = sheets.getSheetName();
  166.             InputSource sheetSource = new InputSource(sheet);
  167.             parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
  168.             sheet.close();
  169.         }
  170.         return totalRows; //返回该excel文件的总行数，不包括首列和空行
  171.     }
  173.     /**
  174.      * 第一个执行
  175.      *
  176.      * @param uri
  177.      * @param localName
  178.      * @param name
  179.      * @param attributes
  180.      * @throws SAXException
  181.      */
  182.     @Override
  183.     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
  185.         // 获取总行号  格式： A1:B5    取最后一个值即可
  186.         if("dimension".equals(name)) {
  187.             String dimensionStr = attributes.getValue("ref");
  188.             totalRowCount = Integer.parseInt(dimensionStr.substring(dimensionStr.indexOf(":") + 2)) - 1;
  189.         }
  191.         //c => 单元格
  192.         if ("c".equals(name)) {
  193.             //前一个单元格的位置
  194.             if (preRef == null) {
  195.                 preRef = attributes.getValue("r");
  196.             } else {
  197.                 preRef = ref;
  198.             }
  200.             //当前单元格的位置
  201.             ref = attributes.getValue("r");
  202.             //设定单元格类型
  203.             this.setNextDataType(attributes);
  204.         }
  206.         //当元素为t时
  207.         if ("t".equals(name)) {
  208.             isTElement = true;
  209.         } else {
  210.             isTElement = false;
  211.         }
  213.         //置空
  214.         lastIndex = "";
  215.     }
  218.     /**
  219.      * 第二个执行
  220.      * 得到单元格对应的索引值或是内容值
  221.      * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
  222.      * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
  223.      *
  224.      * @param ch
  225.      * @param start
  226.      * @param length
  227.      * @throws SAXException
  228.      */
  229.     @Override
  230.     public void characters(char[] ch, int start, int length) throws SAXException {
  231.         lastIndex += new String(ch, start, length);
  232.     }
  235.     /**
  236.      * 第三个执行
  237.      *
  238.      * @param uri
  239.      * @param localName
  240.      * @param name
  241.      * @throws SAXException
  242.      */
  243.     @Override
  244.     public void endElement(String uri, String localName, String name) throws SAXException {
  246.         //t元素也包含字符串
  247.         if (isTElement) {//这个程序没经过
  248.             //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
  249.             String value = lastIndex.trim();
  250.             cellList.add(curCol, value);
  251.             curCol++;
  252.             isTElement = false;
  253.             //如果里面某个单元格含有值，则标识该行不为空行
  254.             if (value != null && !"".equals(value)) {
  255.                 flag = true;
  256.             }
  257.         } else if ("v".equals(name)) {
  258.             //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
  259.             String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
  260.             //补全单元格之间的空单元格
  261.             if (!ref.equals(preRef)) {
  262.                 int len = countNullCell(ref, preRef);
  263.                 for (int i = 0; i < len; i++) {
  264.                     cellList.add(curCol, "");
  265.                     curCol++;
  266.                 }
  267.             }
  268.             cellList.add(curCol, value);
  269.             curCol++;
  270.             //如果里面某个单元格含有值，则标识该行不为空行
  271.             if (value != null && !"".equals(value)) {
  272.                 flag = true;
  273.             }
  274.         } else {
  275.             //如果标签名称为row，这说明已到行尾，调用optRows()方法
  276.             if ("row".equals(name)) {
  277.                 //默认第一行为表头，以该行单元格数目为最大数目
  278.                 if (curRow == 1) {
  279.                     maxRef = ref;
  280.                 }
  281.                 //补全一行尾部可能缺失的单元格
  282.                 if (maxRef != null) {
  283.                     int len = countNullCell(maxRef, ref);
  284.                     for (int i = 0; i <= len; i++) {
  285.                         cellList.add(curCol, "");
  286.                         curCol++;
  287.                     }
  288.                 }
  290.                 if (flag && curRow != 1) { //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
  291.                     // 调用excel读数据委托类进行读取插入操作
  292.                     excelReadDataDelegated.readExcelDate(sheetIndex, totalRowCount, curRow, cellList);
  293.                     totalRows++;
  294.                 }
  296.                 cellList.clear();
  297.                 curRow++;
  298.                 curCol = 0;
  299.                 preRef = null;
  300.                 ref = null;
  301.                 flag = false;
  302.             }
  303.         }
  304.     }
  306.     /**
  307.      * 处理数据类型
  308.      *
  309.      * @param attributes
  310.      */
  311.     public void setNextDataType(Attributes attributes) {
  312.         nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
  313.         formatIndex = -1;
  314.         formatString = null;
  315.         String cellType = attributes.getValue("t"); //单元格类型
  316.         String cellStyleStr = attributes.getValue("s"); //
  317.         String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
  319.         if ("b".equals(cellType)) { //处理布尔值
  320.             nextDataType = CellDataType.BOOL;
  321.         } else if ("e".equals(cellType)) {  //处理错误
  322.             nextDataType = CellDataType.ERROR;
  323.         } else if ("inlineStr".equals(cellType)) {
  324.             nextDataType = CellDataType.INLINESTR;
  325.         } else if ("s".equals(cellType)) { //处理字符串
  326.             nextDataType = CellDataType.SSTINDEX;
  327.         } else if ("str".equals(cellType)) {
  328.             nextDataType = CellDataType.FORMULA;
  329.         }
  331.         if (cellStyleStr != null) { //处理日期
  332.             int styleIndex = Integer.parseInt(cellStyleStr);
  333.             XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
  334.             formatIndex = style.getDataFormat();
  335.             formatString = style.getDataFormatString();
  336.             if (formatString.contains("m/d/yy") || formatString.contains("yyyy/mm/dd") || formatString.contains("yyyy/m/d")) {
  337.                 nextDataType = CellDataType.DATE;
  338.                 formatString = "yyyy-MM-dd hh:mm:ss";
  339.             }
  341.             if (formatString == null) {
  342.                 nextDataType = CellDataType.NULL;
  343.                 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
  344.             }
  345.         }
  346.     }
  348.     /**
  349.      * 对解析出来的数据进行类型处理
  350.      *
  351.      * @param value   单元格的值，
  352.      *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
  353.      *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
  354.      * @param thisStr 一个空字符串
  355.      * @return
  356.      */
  357.     @SuppressWarnings("deprecation")
  358.     public String getDataValue(String value, String thisStr) {
  359.         switch (nextDataType) {
  360.             // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
  361.             case BOOL: //布尔值
  362.                 char first = value.charAt(0);
  363.                 thisStr = first == '0' ? "FALSE" : "TRUE";
  364.                 break;
  365.             case ERROR: //错误
  366.                 thisStr = "\"ERROR:" + value.toString() + '"';
  367.                 break;
  368.             case FORMULA: //公式
  369.                 thisStr = '"' + value.toString() + '"';
  370.                 break;
  371.             case INLINESTR:
  372.                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
  373.                 thisStr = rtsi.toString();
  374.                 rtsi = null;
  375.                 break;
  376.             case SSTINDEX: //字符串
  377.                 String sstIndex = value.toString();
  378.                 try {
  379.                     int idx = Integer.parseInt(sstIndex);
  380.                     XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
  381.                     thisStr = rtss.toString();
  382.                     rtss = null;
  383.                 } catch (NumberFormatException ex) {
  384.                     thisStr = value.toString();
  385.                 }
  386.                 break;
  387.             case NUMBER: //数字
  388.                 if (formatString != null) {
  389.                     thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
  390.                 } else {
  391.                     thisStr = value;
  392.                 }
  393.                 thisStr = thisStr.replace("_", "").trim();
  394.                 break;
  395.             case DATE: //日期
  396.                 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
  397.                 // 对日期字符串作特殊处理，去掉T
  398.                 thisStr = thisStr.replace("T", " ");
  399.                 break;
  400.             default:
  401.                 thisStr = " ";
  402.                 break;
  403.         }
  404.         return thisStr;
  405.     }
  407.     public int countNullCell(String ref, String preRef) {
  408.         //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
  409.         String xfd = ref.replaceAll("\\d+", "");
  410.         String xfd_1 = preRef.replaceAll("\\d+", "");
  412.         xfd = fillChar(xfd, 3, '@', true);
  413.         xfd_1 = fillChar(xfd_1, 3, '@', true);
  415.         char[] letter = xfd.toCharArray();
  416.         char[] letter_1 = xfd_1.toCharArray();
  417.         int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
  418.         return res - 1;
  419.     }
  421.     public String fillChar(String str, int len, char let, boolean isPre) {
  422.         int len_1 = str.length();
  423.         if (len_1 < len) {
  424.             if (isPre) {
  425.                 for (int i = 0; i < (len - len_1); i++) {
  426.                     str = let + str;
  427.                 }
  428.             } else {
  429.                 for (int i = 0; i < (len - len_1); i++) {
  430.                     str = str + let;
  431.                 }
  432.             }
  433.         }
  434.         return str;
  435.     }
  438. }

注: 此处使用了setter、getter、构造函数的方式将写数据委托接口注入进去，想了半天。

2.4 写数据委托接口

  1. package com.yzx.osp.common.util;
  3. import java.util.List;
  5. /**
  6.  * @author qjwyss
  7.  * @date 2018/12/19
  8.  * @description 读取excel数据委托接口
  9.  */
  10. public interface ExcelReadDataDelegated {
  12.     /**
  13.      * 每获取一条记录，即写数据
  14.      * 在flume里每获取一条记录即写，而不必缓存起来，可以大大减少内存的消耗，这里主要是针对flume读取大数据量excel来说的
  15.      *
  16.      * @param sheetIndex    sheet位置
  17.      * @param totalRowCount 该sheet总行数
  18.      * @param curRow        行号
  19.      * @param cellList      行数据
  20.      */
  21.     public abstract void readExcelDate(int sheetIndex, int totalRowCount, int curRow, List<String> cellList);
  23. }

2.5 读取工具类

  1. package com.yzx.osp.common.util;
  3. import com.yzx.osp.common.constant.ExcelConstant;
  5. import java.util.List;
  7. /**
  8.  * @author qjwyss
  9.  * @date 2018/12/19
  10.  * @description 读取EXCEL工具类
  11.  */
  12. public class ExcelReaderUtil {
  15.     public static void readExcel(String filePath, ExcelReadDataDelegated excelReadDataDelegated) throws Exception {
  16.         int totalRows = 0;
  17.         if (filePath.endsWith(ExcelConstant.EXCEL07_EXTENSION)) {
  18.             ExcelXlsxReaderWithDefaultHandler excelXlsxReader = new ExcelXlsxReaderWithDefaultHandler(excelReadDataDelegated);
  19.             totalRows = excelXlsxReader.process(filePath);
  20.         } else {
  21.             throw new Exception("文件格式错误，fileName的扩展名只能是xlsx!");
  22.         }
  23.         System.out.println("读取的数据总行数：" + totalRows);
  24.     }
  27.     public static void main(String[] args) throws Exception {
  28.         String path = "E:\\temp\\5.xlsx";
  29.         ExcelReaderUtil.readExcel(path, new ExcelReadDataDelegated() {
  30.             @Override
  31.             public void readExcelDate(int sheetIndex, int totalRowCount, int curRow, List<String> cellList) {
  32.                 System.out.println("总行数为：" + totalRowCount + " 行号为：" + curRow + " 数据：" + cellList);
  33.             }
  34.         });
  35.     }
  36. }

注： 此处可以直接使用，将sheet索引、总行数、当前行号、当前行记录打印出来，结果如下：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MjA2MjYx_size_16_color_FFFFFF_t_70

三. 使用案例

3.1 特殊说明

  1.       刚开始进行导入的 时候，因为每条记录要进行三个字段的校验（是否存在）和转换（名称要转换为对应的数据库ID），刚开始的做法是 读取一条记录，然后发3次查询sql进行校验，然后再发一次插入sql进行保存。然后悲剧了，测试导入1W条数据要好几分钟。  第二天早上想来突然想到优化方案， 瓶颈在于频繁发送sql语句。然后就在开始导入之前，将需要校验的记录先查询出来放到map中，然后每次遍历的时候直接从map中取出来进行校验，不必发sql了； 还有就是批量保存；

3.2 使用案例

  1.        忙，没时间修改，直接将代码拷贝上去了，自己用的时候直接调用工具类即可。 
  3. @Override
  4.     public ResultVO<Void> importMobileManagerList(String filePath) throws Exception {
  6.         logger.info("开始导入号码列表：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  8.         List<Integer> errorRowNumber = new ArrayList<>();
  9.         List<MobileManager> mobileManagerList = new ArrayList<>();
  11.         MobileManagerVO mobileManagerVO = new MobileManagerVO();
  12.         List<String> mobileList = this.mobileManagerMapper.selectMobileList(mobileManagerVO);
  14.         SysAreaVO sysAreaVO = new SysAreaVO();
  15.         List<SysAreaVO> sysAreaVOList = this.sysAreaMapper.selectSysAreaVOList(sysAreaVO);
  17.         CustomerVO customerVO = new CustomerVO();
  18.         List<String> customerIdList = this.customerDao.selectLineOrBusinessCustomerIdList(customerVO);
  19.         List<CustomerVO> customerVOList = this.customerDao.selectCustomerIdAndAccountIdList(customerVO);
  21.         // 用来存储EXCEL中的号码集合 用来去重
  22.         List<String> excelMobileList = new ArrayList<>();
  24.         ExcelReaderUtil.readExcel(filePath, new ExcelReadDataDelegated() {
  25.             @Override
  26.             public void readExcelDate(int sheetIndex, int totalRowCount, int curRow, List<String> cellList) {
  28.                 // 校验数据合法性
  29.                 Boolean legalFlag = true;
  30.                 Integer provinceId = null;
  31.                 Integer cityId = null;
  33.                 List<String> accountIdList = null;
  35.                 // 号码、成本号码费、成本低消费、客户号码费、客户低消费不能为空
  36.                 if (CommonUtil.checkStringIsNullOrLine(cellList.get(0)) || CommonUtil.checkStringIsNullOrLine(cellList.get(12))
  37.                         || CommonUtil.checkStringIsNullOrLine(cellList.get(13)) || CommonUtil.checkStringIsNullOrLine(cellList.get(14))
  38.                         || CommonUtil.checkStringIsNullOrLine(cellList.get(15))) {
  39.                     legalFlag = false;
  40.                 }
  43.                 // 校验EXCEL中号码不能重复号码不能重复
  44.                 if(excelMobileList.contains(cellList.get(0).trim())) {
  45.                     legalFlag = false;
  46.                 } else {
  47.                     excelMobileList.add(cellList.get(0).trim());
  48.                 }
  50.                 // 客户类型为VBOSS并且分配了客户时，账户不能为空
  51.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(7)) && !CommonUtil.checkStringIsNullOrLine(cellList.get(8))) {
  52.                     if (cellList.get(7).trim().equals("VBOSS") && CommonUtil.checkStringIsNullOrLine(cellList.get(8))) {
  53.                         legalFlag = false;
  54.                     }
  55.                 }
  57.                 // 客户类型为空的时候客户账户不能有值
  58.                 if (CommonUtil.checkStringIsNullOrLine(cellList.get(7))) {
  59.                     if (!CommonUtil.checkStringIsNullOrLine(cellList.get(8)) || !CommonUtil.checkStringIsNullOrLine(cellList.get(9))) {
  60.                         legalFlag = false;
  61.                     }
  62.                 }
  64.                 // 客户类型为bss的时候账户不能有值
  65.                 if (CommonUtil.checkStringIsNullOrLine(cellList.get(7))) {
  66.                     if (cellList.get(7).trim().equals("BSS")) {
  67.                         if (!CommonUtil.checkStringIsNullOrLine(cellList.get(9))) {
  68.                             legalFlag = false;
  69.                         }
  70.                     }
  71.                 }
  74.                 // 号码、区号必须为数字
  75.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(0))) {
  76.                     if (!CommonUtil.checkIsInteger(cellList.get(0).trim())) {
  77.                         legalFlag = false;
  78.                     }
  79.                 }
  80.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(1))) {
  81.                     if (!CommonUtil.checkIsInteger(cellList.get(1).trim())) {
  82.                         legalFlag = false;
  83.                     }
  84.                 }
  86.                 // 运营商只能为 移动、联通、电信、铁通、其它之一
  87.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(5))) {
  88.                     if (!cellList.get(5).trim().equals("移动") && !cellList.get(5).trim().equals("联通")
  89.                             && !cellList.get(5).trim().equals("电信") && !cellList.get(5).trim().equals("铁通")
  90.                             && !cellList.get(5).trim().equals("其它")) {
  91.                         legalFlag = false;
  92.                     }
  93.                 }
  95.                 // 客户类型只能是 VBOSS或BSS之一
  96.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(7))) {
  97.                     if (!cellList.get(7).trim().equalsIgnoreCase("VBOSS") && !cellList.get(7).trim().equalsIgnoreCase("BSS")) {
  98.                         legalFlag = false;
  99.                     }
  100.                 }
  102.                 // 成本号码费、成本低消费、客户号码费、客户低消费只能为小数
  103.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(12))) {
  104.                     if (!CommonUtil.checkIsSmallNumber(cellList.get(12).trim())) {
  105.                         legalFlag = false;
  106.                     }
  107.                 }
  108.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(13))) {
  109.                     if (!CommonUtil.checkIsSmallNumber(cellList.get(13).trim())) {
  110.                         legalFlag = false;
  111.                     }
  112.                 }
  113.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(14))) {
  114.                     if (!CommonUtil.checkIsSmallNumber(cellList.get(14).trim())) {
  115.                         legalFlag = false;
  116.                     }
  117.                 }
  118.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(15))) {
  119.                     if (!CommonUtil.checkIsSmallNumber(cellList.get(15).trim())) {
  120.                         legalFlag = false;
  121.                     }
  122.                 }
  125.                 // 数据库校验
  127.                 // 校验号码是否存在
  128.                 if (!CollectionUtils.isEmpty(mobileList)) {
  129.                     if (mobileList.contains(cellList.get(0).trim())) {
  130.                         legalFlag = false;
  131.                     }
  132.                 }
  134.                 // 校验省是否存在
  135.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(2))) {
  136.                     if (CollectionUtils.isEmpty(sysAreaVOList)) {
  137.                         legalFlag = false;
  138.                     } else {
  139.                         Boolean hasFlag = false;
  140.                         for (SysAreaVO eachSysAreaVO : sysAreaVOList) {
  141.                             if (eachSysAreaVO.getAreaName().equals(cellList.get(2).trim())) {
  142.                                 hasFlag = true;
  143.                                 provinceId = eachSysAreaVO.getSaid();
  144.                                 break;
  145.                             }
  146.                         }
  147.                         if (!hasFlag) {
  148.                             legalFlag = false;
  149.                         }
  150.                     }
  151.                 }
  154.                 // 校验市是否存在
  155.                 if (!CommonUtil.checkStringIsNullOrLine(cellList.get(3))) {
  156.                     if (CollectionUtils.isEmpty(sysAreaVOList)) {
  157.                         legalFlag = false;
  158.                     } else {
  159.                         Boolean hasFlag = false;
  160.                         for (SysAreaVO eachSysAreaVO : sysAreaVOList) {
  161.                             if (eachSysAreaVO.getAreaName().equals(cellList.get(3).trim())) {
  162.                                 hasFlag = true;
  163.                                 cityId = eachSysAreaVO.getSaid();
  164.                                 break;
  165.                             }
  166.                         }
  167.                         if (!hasFlag) {
  168.                             legalFlag = false;
  169.                         }
  170.                     }
  171.                 }
  174.                 // 如果选择了客户类型并且分配了客户，则需要校验客户ID是否存在
  175.                 if(!CommonUtil.checkStringIsNullOrLine(cellList.get(7)) && !CommonUtil.checkStringIsNullOrLine(cellList.get(8))) {
  177.                     // 校验客户ID是否存在
  178.                     Boolean hasCustomerIdFlag = true;
  179.                     if(CollectionUtils.isEmpty(customerIdList)) {
  180.                         hasCustomerIdFlag = false;
  181.                         legalFlag = false;
  182.                     } else {
  183.                         if(!customerIdList.contains(cellList.get(8).trim())) {
  184.                             hasCustomerIdFlag = false;
  185.                             legalFlag = false;
  186.                         }
  187.                     }
  189.                     // 如果该客户ID存在，并且选中的客户类型是VBOSS，则需要校验账户和客户是否匹配
  190.                     if(hasCustomerIdFlag) {
  191.                         if(cellList.get(7).equals("VBOSS") && !CommonUtil.checkStringIsNullOrLine(cellList.get(9))) {
  193.                             if(CollectionUtils.isEmpty(customerVOList)) {
  194.                                 legalFlag = false;
  195.                             } else {
  196.                                 for (CustomerVO eachCustomerVO: customerVOList) {
  197.                                     if(eachCustomerVO.getCustomerId().equals(cellList.get(8).trim())) {
  198.                                         accountIdList = eachCustomerVO.getAccountIdList();
  199.                                         break;
  200.                                     }
  201.                                 }
  203.                                 if(CollectionUtils.isEmpty(accountIdList)) {
  204.                                     legalFlag = false;
  205.                                 } else {
  206.                                     if(!accountIdList.contains(cellList.get(9).trim())) {
  207.                                         legalFlag = false;
  208.                                     }
  209.                                 }
  210.                             }
  211.                         }
  212.                     }
  213.                 }
  216.                 // 如果数据合法，则封装号码对象
  217.                 if (!legalFlag) {
  218.                     if (!errorRowNumber.contains(curRow)) {
  219.                         errorRowNumber.add(curRow);
  220.                     }
  221.                 } else {
  223.                     try {
  225.                         MobileManager mobileManager = new MobileManager();
  226.                         mobileManager.setMobile(cellList.get(0).trim());
  227.                         mobileManager.setAreaCode(CommonUtil.checkStringIsNullOrLine(cellList.get(1)) ? null : cellList.get(1));
  228.                         mobileManager.setProvinceId(provinceId == null ? null : provinceId);
  229.                         mobileManager.setCityId(cityId == null ? null : cityId);
  230.                         mobileManager.setType(CommonUtil.checkStringIsNullOrLine(cellList.get(4)) ? null : cellList.get(4));
  232.                         if (!CommonUtil.checkStringIsNullOrLine(cellList.get(5))) {
  233.                             Integer operator = null;
  234.                             if (cellList.get(5).trim().equals("移动")) {
  235.                                 operator = MobileManagerConstant.OPERATOR_YIDONG;
  236.                             } else if (cellList.get(5).trim().equals("联通")) {
  237.                                 operator = MobileManagerConstant.OPERATOR_LIANTONG;
  238.                             } else if (cellList.get(5).trim().equals("电信")) {
  239.                                 operator = MobileManagerConstant.OPERATOR_DIANXIN;
  240.                             } else if (cellList.get(5).trim().equals("铁通")) {
  241.                                 operator = MobileManagerConstant.OPERATOR_TIETONG;
  242.                             } else if (cellList.get(5).trim().equals("其它")) {
  243.                                 operator = MobileManagerConstant.OPERATOR_ELSE;
  244.                             }
  245.                             mobileManager.setOperator(operator);
  246.                         }
  248.                         mobileManager.setSupplierName(CommonUtil.checkStringIsNullOrLine(cellList.get(6)) ? null : cellList.get(6));
  251.                         if (!CommonUtil.checkStringIsNullOrLine(cellList.get(7))) {
  252.                             Integer customerType = null;
  253.                             if (cellList.get(7).trim().equalsIgnoreCase("VBOSS")) {
  254.                                 customerType = MobileManagerConstant.CUSTOMER_TYPE_VBOSS;
  255.                                 mobileManager.setAccountId(CommonUtil.checkStringIsNullOrLine(cellList.get(9)) ? null : cellList.get(9));
  256.                             } else if (cellList.get(7).trim().equalsIgnoreCase("BSS")) {
  257.                                 customerType = MobileManagerConstant.CUSTOMER_TYPE_BSS;
  258.                             }
  259.                             mobileManager.setCustomerType(customerType);
  260.                             mobileManager.setCustomerId(CommonUtil.checkStringIsNullOrLine(cellList.get(8)) ? null : cellList.get(8));
  261.                             mobileManager.setState(CommonUtil.checkStringIsNullOrLine(cellList.get(8)) ?
  262.                                     MobileManagerConstant.STATE_USEING : MobileManagerConstant.STATE_USEING);
  263.                             mobileManager.setDistributeTime(CommonUtil.checkStringIsNullOrLine(cellList.get(8)) ? null : new Date());
  264.                         } else {
  265.                             mobileManager.setState(MobileManagerConstant.STATE_UNUSE);
  266.                         }
  269.                         mobileManager.setTerminalUser(CommonUtil.checkStringIsNullOrLine(cellList.get(10)) ? null : cellList.get(10));
  271.                         if (!CommonUtil.checkStringIsNullOrLine(cellList.get(11))) {
  272.                             mobileManager.setPlanRecoveryDate(DateUtil.formatDateStrWithLine2Date(cellList.get(11).trim()));
  273.                         }
  275.                         mobileManager.setCostMobileFee(CommonUtil.checkStringIsNullOrLine(cellList.get(12)) ? null : Double.parseDouble(cellList.get(12).trim()));
  276.                         mobileManager.setCostLowFee(CommonUtil.checkStringIsNullOrLine(cellList.get(13)) ? null : Double.parseDouble(cellList.get(13).trim()));
  277.                         mobileManager.setCustomerMobileFee(CommonUtil.checkStringIsNullOrLine(cellList.get(14)) ? null : Double.parseDouble(cellList.get(14).trim()));
  278.                         mobileManager.setCustomerLowFee(CommonUtil.checkStringIsNullOrLine(cellList.get(15)) ? null : Double.parseDouble(cellList.get(15).trim()));
  279.                         mobileManager.setRemark(CommonUtil.checkStringIsNullOrLine(cellList.get(16)) ? null : cellList.get(16).trim());
  280.                         mobileManager.setCreateTime(new Date());
  281.                         mobileManagerList.add(mobileManager);
  283.                     } catch (Exception e) {
  284.                         e.printStackTrace();
  285.                         if (!errorRowNumber.contains(curRow)) {
  286.                             errorRowNumber.add(curRow);
  287.                         }
  288.                     }
  289.                 }
  292.                 // 批量保存
  293.                 try {
  294.                     if (mobileManagerList.size() == ExcelConstant.PER_READ_INSERT_BATCH_COUNT) {
  295.                         mobileManagerMapper.saveMobileManagerBatch(mobileManagerList);
  296.                         mobileManagerList.clear();
  297.                     } else if (mobileManagerList.size() < ExcelConstant.PER_READ_INSERT_BATCH_COUNT) {
  298.                         int lastInsertBatchCount = totalRowCount % ExcelConstant.PER_READ_INSERT_BATCH_COUNT == 0 ?
  299.                                 totalRowCount / ExcelConstant.PER_READ_INSERT_BATCH_COUNT :
  300.                                 totalRowCount / ExcelConstant.PER_READ_INSERT_BATCH_COUNT + 1;
  301.                         if ((curRow - 1) >= ((lastInsertBatchCount - 1) * ExcelConstant.PER_READ_INSERT_BATCH_COUNT + 1)
  302.                                 && (curRow - 1) < lastInsertBatchCount * ExcelConstant.PER_READ_INSERT_BATCH_COUNT) {
  303.                             if (curRow - 1 == totalRowCount) {
  304.                                 if(!CollectionUtils.isEmpty(mobileManagerList)) {
  305.                                     mobileManagerMapper.saveMobileManagerBatch(mobileManagerList);
  306.                                 }
  307.                             }
  308.                         }
  309.                     }
  310.                 } catch (Exception e) {
  311.                     e.printStackTrace();
  312.                     if (!errorRowNumber.contains(curRow)) {
  313.                         errorRowNumber.add(curRow);
  314.                     }
  315.                 }
  317.             }
  318.         });
  320.         logger.info("导入号码列表完成：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
  322.         return ResultVO.getSuccess("批量导入VOIP用户完成, 共有" + errorRowNumber.size() + "条记录存在问题，失败行号为：" + errorRowNumber);
  323.     }
  325.     有一点要说明的是关于去重的：  之前准备直接在数据库采用唯一索引来保证不重复，但是有BUG：因为是批量分批保存，如果保存的这一批中有重复的数据，则会导致该批数据都保存不了。
  327.     解决方案：1. excel数据去重:  定义一个全局变量集合用来存储可能重复的字段，如上面的mobileList, 每读取到excel一条记录，就判断该条记录是否在改全局变量集合中已经存在，如果存在，则将标志设置为false, 就不再放到批量存储对象的集合中，如果不存在，则放入；  2. 数据库校验是否存在：  这个就是在前面一次性查出来，每读取一条excel记录查看是否存在，如果存在，则将标志设置为false, 就不再放到批量存储对象的集合中，如果不存在，则放入;

发表评论

表情：
评论列表 （有 0 条评论，287人围观）

还没有评论，来说两句吧...

相关阅读