百万级Excel数据读取及保存到数据库

//Excel文件导入
@SuppressWarnings({ "unused", "static-access" })
public void uploadExcel(){
// 首先判断Excel文件是否成功上传并获取到文件
if (getExcelFile() == null || !excelFile.exists()) {
outString("{\"flag\":\"导入出错，请刷新页面重试。\"}");
return;
}

//增加异常信息记录信息 add by2017-12-14 PM begin。
StringBuffer sb_error = new StringBuffer();
//增加异常信息记录信息 add by2017-12-14 PM end。

// 读取解析Excel,并判断是否解析成功
final List<String[]> listOrg = new ArrayList<String[]>();

File ff = excelFile;
String name =request.getSession().getId() + ff.getName();
//System.out.println(excelFile.getName());
long l = ff.length();
//long start = System.currentTimeMillis();
//首先判断是EXCEL还是CSV文件
if(excelFile.getName().indexOf(".csv") != -1){

}else{//excel文件
//如果是数据量大的就执行大数据量的解析方法，否则就执行传统解析方式
if(ff.length()>521000){
try {
//执行大数据量的导入转成xml文件格式读取
BigExcelReaderTest reader = new BigExcelReaderTest(ff) {  
       protected void outputRow(String[] datas, int[] rowTypes, int rowIndex) { 
        //获取每一行的数据将数据放入list中保存
        listOrg.add(datas);
        System.out.println(rowIndex);
       }  
 };
reader.parse(); 
} catch (IOException e1) {
e1.printStackTrace();

if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【IO异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【IO异常】:").append(e1.getMessage());
}
} catch (OpenXML4JException e1) {
e1.printStackTrace();

if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【格式转换异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【格式转换异常】:").append(e1.getMessage());
}
} catch (SAXException e1) {
e1.printStackTrace();

if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【SAXE解析异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【SAXE解析异常】:").append(e1.getMessage());
}
}
}else{
List<String[]> listOrgR = AddressExcelUtil.readExcel2(excelFile);
listOrg.addAll(listOrgR);

}
}

if(listOrg.get(0).length==22){
List<String[]> list = listOrg.subList(1, listOrg.size()); 
int count = 10000;//一个线程处理20万条数据
int listSize = list.size();//数据集合大小 
success =0;
dataExctption =0;
//Salemanage.put(name+"Size", listSize);//百分比
int runSize = (listSize/count)+1; //开启的线程数
List<String[]> newlist = null;//存放每个线程的据

JDBCPool cd = new JDBCPool();    
       Connection ct = null;    
       PreparedStatement pst = null; 
       String  insertSql = "insert into ts_sys_Equipment(id,addrid,address,"
        + "Equipment,OBD,Onequipmenttype,onequipment,"
        + "OLT,PON,Accessmode,Coveragemode, "
        + "netsource,Openservice,Openservicenum,addrtime, "
        + "equipmentaddr,oneaddr,twoaddr,threeaddr, "
        + "fouraddr,Fiveaddr,sixaddr,Sevenaddr"
        + ") "
        + "values(sys_Equipment_ID_SEQ.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
       try {
        ct = cd.getConnection();
pst = ct.prepareStatement(insertSql);
ct.setAutoCommit(false);// 开始事务

} catch (SQLException e1) {
e1.printStackTrace();

if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【打开JDBC异常】:").append(e1.getMessage());
}else{
sb_error.append(";").append("【打开JDBC异常】:").append(e1.getMessage());
}
} 
       
       DecimalFormat fm = new DecimalFormat("#");
       SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmmss");
       //增加异常信息输出到日志信息
       StringBuffer sb = new StringBuffer();
       
for(int i=0,len=list.size();i<len;i++){
String[] test =  list.get(i);
try{
//地址ID
if(test[0] != null && !"0".equals(test[0]) && !"".equals(test[0])){
pst.setInt(1, Integer.parseInt(test[0]));
}else{
pst.setInt(1,0);
}
pst.setString(2, test[1]);
pst.setString(3, test[2]);
pst.setString(4, test[3]);
pst.setString(5, test[4]);
pst.setString(6, test[5]);
pst.setString(7, test[6]);
pst.setString(8, test[7]);
pst.setString(9, test[8]);
pst.setString(10, test[9]);
pst.setString(11, test[10]);
pst.setString(12, test[11]);
if(test[12] != null && !"0".equals(test[12]) && !"".equals(test[12])){
//System.out.println(test[12]);
pst.setInt(13, Integer.parseInt(test[12]));
}else{
pst.setInt(13,0);
}
if(test[13]==null || "".equals(test[13])){//时间格式
pst.setString(14,null);
}else{
pst.setTimestamp(14,new java.sql.Timestamp((sf.parse(test[13])).getTime()));
}
if(test[14]==null || "".equals(test[14])){//时间格式
pst.setString(15, null);
}else{
pst.setTimestamp(15,new java.sql.Timestamp((sf.parse(test[14]).getTime())));
}
if(test[15]==null || "".equals(test[15])){//如果一级地址为空
pst.setString(16,"空");
}else{
pst.setString(16, test[15]);
}
if(test[16]==null || "".equals(test[16])){//如果二级地址为空
pst.setString(17, "空");
}else{
pst.setString(17, test[16]);
}
if(test[17]==null || "".equals(test[17])){//如果三级地址为空
pst.setString(18, "空");
}else{
pst.setString(18, test[17]);
}
if(test[18]==null || "".equals(test[18])){//如果四级地址为空
pst.setString(19, "空");
}else{
pst.setString(19, test[18]);
}
if(test[19]==null || "".equals(test[19])){//如果五级地址为空
pst.setString(20, "空");
}else{
pst.setString(20, test[19]);
}
if(test[20]==null || "".equals(test[20])){//如果六级地址为空
pst.setString(21, "空");
}else{
pst.setString(21, test[20]);
}
if(test[21]==null || "".equals(test[21])){//如果七级地址为空
pst.setString(22,"空");
}else{
//System.out.println(test[21]);
pst.setString(22, test[21]);
}
pst.addBatch();
if(((i+1)%count)==0){//每次插入list.size条数据    
       int[] executeBatch = pst.executeBatch(); 
       success +=executeBatch.length;//累计成功数
       System.out.println("提交"+(i+1)+"条");
       pst.clearBatch();
       ct.commit();    
   }
}catch(Exception e){
//数据格式异常
dataExctption++;
e.printStackTrace();

if(StringUtils.isEmpty(sb.toString())){
sb.append(test[0]);
}else{
sb.append(",");
sb.append(test[0]);
}

}
          
}

if(StringUtils.isNotEmpty(sb.toString())){
logger.error("*******************异常导入数据信息:"+sb.toString());

if(StringUtils.isEmpty(sb_error.toString())){
sb_error.append("【导入数据格式异常】:").append(sb.toString());
}else{
sb_error.append(";").append("【导入数据格式异常】:").append(sb.toString());
}
}

   try {
int[] executeBatch = pst.executeBatch();
       success +=executeBatch.length;//累计成功数
       ct.commit(); 
       pst.clearBatch();
       // add in 2017-12-12 pm 所有增加事务还原的自动提交的代码
       ct.setAutoCommit(true);
       /***
        * modify by in 2017-12-13 am 这个过程很耗时，将其迁移到前台关闭窗口在去调用 
       //增加导入数据后,各级地址修证的储存过程调用。
       AddressOverride addressoverride = new AddressOverride();
       logger.error("******************************准备执行【设备地址导入时各级地址修证的储存过程调用】************************");
       service.saveFlushDatas(addressoverride);
       logger.error("******************************执行完毕【设备地址导入时各级地址修证的储存过程调用】************************");
       ****/
   } catch (Exception e) {
e.printStackTrace();
// add in 2017-12-12 pm 异常事务回滚
try {
//异常时回滚
ct.rollback();
} catch (SQLException e1) {

e1.printStackTrace();
}
}finally{
if(pst != null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ct != null){
try {
ct.setAutoCommit(true);
ct.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} 
   
  //记录操作日志
OperationLog operationLog = new OperationLog();
operationLog.setUserAccount(user.getAccount());
operationLog.setUserIp(user.getUserIp());
operationLog.setOpType("导入");
operationLog.setOpResource("AddressOverrideAction#upload");
operationLog.setOpFunction("设备覆盖地地址管理");
operationLog.setOpContent("文件名称为："+"设备覆盖地地址管理"+"文件大小为："+(excelFile.length()/1024+1)+"KB,总共导入："+total+",导入成功："+success+",导入失败："+(total-success));
IOperationLogService operatelog = (IOperationLogService) SpringContainer.getBeanByName("operationLogService");
operatelog.insertData(operationLog);
//返回最终结果

JSONObject  jsonObj = new JSONObject();
if(success>0){  
jsonObj.put("flag", "导入完成! 导入总记录数"+listSize+",导入成功"+success+"条,失败数量:"+(listSize-success)+"条(导入时出错"+(listSize-success-dataExctption)+"条,数据格式异常数量"+dataExctption+");"+"异常信息:"+sb_error.toString());
}else{
jsonObj.put("flag", "导入失败! 导入总记录数"+listSize+"失败数量:"+(listSize-success)+"条(导入时出错"+(listSize-success-dataExctption)+"条,数据格式异常数量"+dataExctption+");"+"异常信息:"+sb_error.toString());
}
outString(jsonObj.toString());
}else{

outString(new JSONObject().put("flag", "导入失败!表格格式不正确.").toString());
}

}

///===================excel文件读取及解析成List  工具类=========================

/*
 * @describe 
 * @fileName com.cttsp.frame.util.StringUtil
 * @company 深圳元道通信技术有限公司
 * @author admin
 * @date 2012 2012-11-30
 */
package com.cttsp.frame.util;




import java.io.File;  
import java.io.IOException;  
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.Arrays;  
import java.util.Date;
import java.util.regex.Pattern;


import org.apache.commons.lang3.time.DateFormatUtils;  
import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;  
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.eventusermodel.XSSFReader;  
import org.apache.poi.xssf.model.SharedStringsTable;  
import org.apache.poi.xssf.model.StylesTable;  
import org.apache.poi.xssf.usermodel.XSSFCellStyle;  
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;  
import org.xml.sax.ContentHandler;  
import org.xml.sax.InputSource;  
import org.xml.sax.SAXException;  
import org.xml.sax.XMLReader;  
import org.xml.sax.helpers.DefaultHandler;  
import org.xml.sax.helpers.XMLReaderFactory;   
public abstract class BigExcelReaderTest{
 
 /*  public static void main(String[] args) throws Exception{  
       Fiel filepath = "";  
       BigExcelReader reader = new BigExcelReader(filepath) {  
           @Override  
           protected void outputRow(String[] datas, int[] rowTypes, int rowIndex) {  
               // 此处输出每一行的数据  
               System.out.println(Arrays.toString(datas));  
           }  
       };  
       // 执行解析  
       reader.parse();  
   }  */

      
    enum xssfDataType {  
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,  
    }  
      
    public static final int ERROR = 1;  
    public static final int BOOLEAN = 1;  
    public static final int NUMBER = 2;  
    public static final int STRING = 3;  
    public static final int DATE = 4;  
    public static final String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";  
      
    public final static String PHONE_PATTERN="^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17([0,1,6,7,]))|(18[0-2,5-9]))\\d{8}$";      
//  private DataFormatter formatter = new DataFormatter();  
    private InputStream sheet;  
    private XMLReader parser;  
    private InputSource sheetSource;  
    private int index = 0;  
      
     
      
    /** 
     * 读大数据量Excel 
     *  
     * @param file Excel文件 
     * @param maxColNum 读取的最大列数 
     * @throws IOException 
     * @throws OpenXML4JException 
     * @throws SAXException 
     */  
    public BigExcelReaderTest(File file) throws IOException, OpenXML4JException, SAXException{ 
    try {
        OPCPackage pkg = OPCPackage.openOrCreate(file);
        init(pkg);  
       
    } catch (Exception e1){
    e1.printStackTrace();
    } 
    }
      
     
      
    /** 
     * 初始化 将Excel转换为XML 
     *  
     * @param pkg 
     * @throws IOException 
     * @throws OpenXML4JException 
     * @throws SAXException 
     */  
    private void init(OPCPackage pkg) throws IOException, OpenXML4JException, SAXException{  
        XSSFReader xssfReader = new XSSFReader(pkg);  
        System.out.println(xssfReader);
        SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();  
        System.out.println(sharedStringsTable);
        StylesTable stylesTable = xssfReader.getStylesTable(); 
        System.out.println(stylesTable);
        sheet = xssfReader.getSheet("rId1");  
        System.out.println(sheet);
        parser = fetchSheetParser(sharedStringsTable, stylesTable);  
        System.out.println(parser);
        sheetSource = new InputSource(sheet);  
        System.out.println(sheetSource);
    }  
      
    /** 
     * 执行解析操作 
     *  
     * @return 读取的Excel行数 
     */  
    public int parse() {  
        try {  
            parser.parse(sheetSource);  
        }   
        catch (IOException e) {  
            e.printStackTrace();  
        }   
        catch (SAXException e) {  
            e.printStackTrace();  
        }  
        finally{  
            if(sheet != null){  
                try {  
                    sheet.close();  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }
            }  
        }  
        return index;  
    }  
      
    private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {  
    XMLReader parser =  
                XMLReaderFactory.createXMLReader(  
                        "org.apache.xerces.parsers.SAXParser"  
                );  
    try {  
        ContentHandler handler = new SheetHandler(sharedStringsTable, stylesTable);  
        parser.setContentHandler(handler);  
    }catch(Exception e){
    e.printStackTrace();  
    }
        return parser;  
    }  
      
    /** 
     * SAX解析的处理类 
     * 每解析一行数据后通过outputRow(String[] datas, int[] rowTypes, int rowIndex)方法进行输出 
     *  
     * @author zpin 
     */  
    private class SheetHandler extends DefaultHandler {  
        private SharedStringsTable sharedStringsTable; // 存放映射字符串  
        private StylesTable stylesTable;// 存放单元格样式  
        private String readValue;// 存放读取值  
        private xssfDataType dataType;// 单元格类型  
        private String[] rowDatas;// 存放一行中的所有数据  
        private int[] rowTypes;// 存放一行中所有数据类型  
        private int colIdx;// 当前所在列  
          
        private short formatIndex;  
//      private String formatString;// 对数值型的数据直接读为数值，不对其格式化，所以隐掉此处  
          
        private SheetHandler(SharedStringsTable sst,StylesTable stylesTable) {  
            this.sharedStringsTable = sst;  
            this.stylesTable = stylesTable;  
        }  
          
        public void startElement(String uri, String localName, String name,  
                Attributes attributes) throws SAXException {  
            if(name.equals("c")) {// c > 单元格  
                colIdx = getColumn(attributes);  
                String cellType = attributes.getValue("t");  
                String cellStyle = attributes.getValue("s");  
                  
                this.dataType = xssfDataType.NUMBER;  
                if ("b".equals(cellType)){  
                    this.dataType = xssfDataType.BOOL;  
                }  
                else if ("e".equals(cellType)){  
                    this.dataType = xssfDataType.ERROR;  
                }  
                else if ("inlineStr".equals(cellType)){  
                    this.dataType = xssfDataType.INLINESTR;  
                }  
                else if ("s".equals(cellType)){  
                    this.dataType = xssfDataType.SSTINDEX;  
                }  
                else if ("str".equals(cellType)){  
                    this.dataType = xssfDataType.FORMULA;  
                }  
                else if(cellStyle != null){  
                    int styleIndex = Integer.parseInt(cellStyle);    
                    XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);    
                    this.formatIndex = style.getDataFormat();    
//                  this.formatString = style.getDataFormatString();    
                }  
            }  
            // 解析到一行的开始处时，初始化数组  
            else if(name.equals("row")){  
                int cols = getColsNum(attributes);// 获取该行的单元格数  
                rowDatas = new String[cols];  
                rowTypes = new int[cols];  
            }  
            readValue = "";  
        }  
          
        public void endElement(String uri, String localName, String name)  
                throws SAXException {  
            if(name.equals("v")) { // 单元格的值  
                switch(this.dataType){  
                    case BOOL: {  
                        char first = readValue.charAt(0);  
                        rowDatas[colIdx] = first == '0' ? "FALSE" : "TRUE";  
                        rowTypes[colIdx] = BOOLEAN;  
                        break;  
                    }  
                    case ERROR: {  
                        rowDatas[colIdx] = "ERROR:" + readValue.toString();  
                        rowTypes[colIdx] = ERROR;  
                        break;  
                    }  
                    case INLINESTR: {  
                        rowDatas[colIdx] = new XSSFRichTextString(readValue).toString();  
                        rowTypes[colIdx] = STRING;  
                        break;  
                    }  
                    case SSTINDEX:{  
                        int idx = Integer.parseInt(readValue);    
                        rowDatas[colIdx] = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)).toString();  
                        rowTypes[colIdx] = STRING;  
                        break;  
                    }  
                    case FORMULA:{  
                        rowDatas[colIdx] = readValue;  
                        rowTypes[colIdx] = STRING;  
                        break;  
                    }  
                    case NUMBER:{  
                        // 判断是否是日期格式    
//                     if(HSSFDateUtil.isValidExcelDate(Double.parseDouble(readValue))){
//                     Double d = Double.parseDouble(readValue);    
//                             Date date = HSSFDateUtil.getJavaDate(d);    
//                             rowDatas[colIdx] = DateFormatUtils.format(date, DATE_FORMAT_STR);  
//                             rowTypes[colIdx] = DATE;
//                     }
//                        if (HSSFDateUtil.isADateFormat(formatIndex, readValue)&&!Pattern.compile(PHONE_PATTERN).matcher(readValue).matches()) {    
//                            Double d = Double.parseDouble(readValue);    
//                            Date date = HSSFDateUtil.getJavaDate(d);    
//                            rowDatas[colIdx] = DateFormatUtils.format(date, DATE_FORMAT_STR);  
//                            rowTypes[colIdx] = DATE;  
//                        }   
//                      else if (formatString != null){  
//                          cellData.value = formatter.formatRawCellContents(Double.parseDouble(cellValue), formatIndex, formatString);  
//                          cellData.dataType = NUMBER;  
//                      }  
//                        else{  
                            rowDatas[colIdx] = readValue;  
                            rowTypes[colIdx] = NUMBER;  
//                        }  
                        break;  
                    }  
                }  
            }  
            // 当解析的一行的末尾时，输出数组中的数据  
            else if(name.equals("row")){  
                outputRow(rowDatas, rowTypes, index++);  
            }  
        }  
  
        public void characters(char[] ch, int start, int length)  
                throws SAXException {  
            readValue += new String(ch, start, length);  
        }  
    }  
      
    /** 
     * 输出每一行的数据 
     *  
     * @param datas 数据 
     * @param rowTypes 数据类型 
     * @param rowIndex 所在行 
     */  
    protected abstract void outputRow(String[] datas, int[] rowTypes, int rowIndex);  
      
    private int getColumn(Attributes attrubuts) {    
        String name = attrubuts.getValue("r");   
        int column = -1;    
        for (int i = 0; i < name.length(); ++i) {  
            if (Character.isDigit(name.charAt(i))) {  
                break;  
            }  
            int c = name.charAt(i);    
            column = (column + 1) * 26 + c - 'A';    
        }    
        return column;    
    }  
      
    private int getColsNum(Attributes attrubuts){  
        String spans = attrubuts.getValue("spans");  
        String cols = spans.substring(spans.indexOf(":") + 1);  
        return Integer.parseInt(cols);  
    }  
   
}