实现百万EXCLE数据导入

1. 目标：

30分钟内实现 百万级数据导入到数据库中。

2. 实现原理：

  通过流的方式（InputStream）读入excel表，每个sheet对应一个 inputStream。将sheet内容转换为轻量级的 ReadOnlySharedStringsTable， （同比SharedStringsTable数据量大的时候会报 out of memory） 然后使用 SAX（同样为轻量级的）进行数据读取，在对处理好的每行数据进行数据库导入。

数据库导入是通过批处理进行，我这里设置 每1000次实现一次导入，最后结束时进行提交。

3. 重点介绍：SAX

SAX（simple API for XML）是一种XML解析的替代方法。相比于DOM，SAX是一种速度更快，更有效的方法。它逐行扫描文档，一边扫描一边解析。同时它的缺点时操作复杂。下面为介绍：

一个XML文档举例来说

<doc>

<para>Hello,World!</para>

</doc>其解析的过程为：1.start document

2.start element:doc......

3.start element:para.....

4.characters:Hello,World!

5.end element:para......

6.end element;doc......

7.end document

对于解析过程中的每一步都会有事件发生，都会触发相应接口中的事件处理程序。

编写程序的步骤为：

（1）.创建事件处理程序（也就是编写ContentHandler的实现类，一般继承自DefaultHandler类，采用adapter模式）

（2）.创建SAX解析器

（3）.将事件处理程序分配到解析器

（4）.对文档进行解析，将每个事件发送给事件处理程序

3.1SAX 模型

编辑

.过程：

1.首先SAXParserFactory来创建一个SAXParserFactory实例

SAXParserFactory factory = SAXParserFactory.newInstance();

2.根据SAXParserFactory实例来创建SAXParser

3.SAXParser产生SAXReader

XMLReader reader = factory.newSAXParser().getXMLReader();

4.XMLReader 加载XML，然后解析XML，在解析的过程中触发相对于接口中的事件处理程序。

3.2接口介绍

ContentHandler接口 （主要用到的接口）

ContentHandler是Java类包中一个特殊的SAX接口，位于org.xml.sax包中。该接口封装了一些对事件处理的方法，当XML解析器开始解析XML输入文档时，它会遇到某些特殊的事件，比如文档的开头和结束、元素开头和结束、以及元素中的字符数据等事件。当遇到这些事件时，XML解析器会调用ContentHandler接口中相应的方法来响应该事件。

ContentHandler接口的方法有以下几种：

void startDocument()//文件打开时调用

void endDocument()//当到文档的末尾调用

void startElement(String uri, String localName, String qName, Attributes atts)//当遇到开始标记时调用

void endElement(String uri, String localName, String qName)//当遇到节点结束时调用

void characters(char[ ] ch, int start, int length)//当分析器遇到无法识别为标记或者指令类型字符时调用

4代码

分别为 3个类： BigExcelReader （实现数据导入到库中的类） RowReader（针对每行数据的操作类） BigExcelReaderTest（JUNIT的测试类）

 

RowReader类：

package net.sf.excelutils;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class RowReader {		
		private Integer curRow=-1;
		private int excutCount =0;//执行次数
		/**
		 * 执行处理每一条行数据，进行导入到数据库中
		 * @param sheetIndex
		 * @param curRow
		 * @param row
		 * @param connection
		 * @param statement
		 * @throws Exception
		 */
		public void getRows(int sheetIndex, int curRow, String[] row,Connection connection ,PreparedStatement statement) throws Exception {
			
			if(this.curRow!=curRow) {
			
			    statement.setString(1,row[0]);				
    			statement.setString(2, row[0]);
    			statement.setString(3, row[0]);
    			statement.setString(4, row[0]);
    			statement.setString(5, row[0]);
    			statement.setString(6, row[5]);
    			statement.setString(7, row[6]);
    			statement.setString(8, row[7]);
    			statement.setString(9, row[8]);
    			statement.setString(10, row[9]);
    			statement.setString(11, row[10]);
    			statement.setString(12, row[11]);
    			statement.setString(13, row[12]);
    			statement.setString(14, row[13]);
    			statement.setString(15, row[14]);
    			statement.setString(16, row[15]);
    			statement.addBatch();	
    			excutCount++;//自增
				//批量执行提交
				if(curRow%1000==0){					
						System.out.println("执行批量提交数据");
						statement.executeBatch();
						statement.clearBatch();				
				}
			
				this.curRow=curRow;
			}
			
			
		}
		public int getExcutCount() {
			return excutCount;
		}
		
		
	
}

package net.sf.excelutils;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Pattern;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
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;

/**
 * 类名称：BigExcelReader 类描述：实现大数据导入到数据库中
 */
public class BigExcelReader {
	private XSSFReader xssfReader;
	private final int minColumnCount; // 获取一行时最小数组长度
	private int currentRow = 0;
	private int sheetIndex = -1;
	private String[] record;
	private int thisColumnIndex = -1;
	private boolean dateFlag;// 日期标志
	private boolean numberFlag;// 数字标志
	private boolean isTElement;
	private RowReader rowReader;// 创建的处理行数据的类
	private Connection connection;// 数据库连接 数据库是否在这个创建可以自行选择
	private PreparedStatement statement;// 预编译
	private OPCPackage pkg = null;

	public void setRowReader(RowReader rowReader) {
		this.rowReader = rowReader;
	}

	/**
	 * 构造方法
	 */
	public BigExcelReader(String filename, int minCols) throws Exception {
		if (StringUtils.isEmpty(filename))
			throw new Exception("文件名不能空");
		this.minColumnCount = minCols;
		record = new String[this.minColumnCount];
		this.pkg = OPCPackage.open(filename);

		// 创建数据库连接
		Class.forName("oracle.jdbc.driver.OracleDriver");
		connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@192.168.60.36:1521:orcl", "csctest",
				"csctest");
		String sql = "insert into CSC_PURCHASETRACK values (SEQ_PURCHASETRACK_ID.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		connection.setAutoCommit(false);
		statement = connection.prepareStatement(sql);

	}

	/**
	 * 初始化BigExcelReader
	 * 
	 * @param is
	 * @param minCols
	 * @throws Exception
	 */
	public BigExcelReader(InputStream is, int minCols) throws Exception {
		if (null == is)
			throw new Exception("文件不能空");
		this.minColumnCount = minCols;
		record = new String[this.minColumnCount];// 创建放行数据的数组
		this.pkg = OPCPackage.open(is);
	}

	/**
	 * 遍历每个sheet，进行数据处理
	 * 
	 * @param filename
	 * @throws Exception
	 */

	public void process() throws Exception {
		ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
				this.pkg);
		XSSFReader xssfReader = new XSSFReader(this.pkg);
		StylesTable styles = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
				.getSheetsData();
		int index = 0;
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			processSheet(styles, strings, stream);
			stream.close();
			++index;
		}
	}

	/**
	 * 创建 调用 SAX 流程 使用 详细见： 3.1SAX 模型 -过程
	 * 
	 * @param styles
	 * @param strings
	 * @param sheetInputStream
	 * @throws IOException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 */
	public void processSheet(StylesTable styles,
			ReadOnlySharedStringsTable strings, InputStream sheetInputStream)
			throws IOException, ParserConfigurationException, SAXException {
		InputSource sheetSource = new InputSource(sheetInputStream);
		SAXParserFactory saxFactory = SAXParserFactory.newInstance();
		SAXParser saxParser = saxFactory.newSAXParser();
		XMLReader sheetParser = saxParser.getXMLReader();
		ContentHandler handler = new PagingHandler(strings);
		sheetParser.setContentHandler(handler);
		sheetParser.parse(sheetSource);
	}

	/**
	 * 创建内部类，实现DefaultHandle,详细见
	 */
	private class PagingHandler extends DefaultHandler {
		private ReadOnlySharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		private String index = null;

		private PagingHandler(ReadOnlySharedStringsTable sst) {
			this.sst = sst;
		}

		/**
		 * 开始元素
		 */
		@Override
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException {
			if (name.equals("c")) {
				index = attributes.getValue("r");
				int firstDigit = -1;
				for (int c = 0; c < index.length(); ++c) {
					if (Character.isDigit(index.charAt(c))) {
						firstDigit = c;
						break;
					}
				}
				thisColumnIndex = nameToColumn(index.substring(0, firstDigit));

				// 判断是否是新的一行
				if (Pattern.compile("^A[0-9]+$").matcher(index).find()) {
					currentRow++;
				}
				String cellType = attributes.getValue("t");
				if (cellType != null && cellType.equals("s")) {
					nextIsString = true;
				} else {
					nextIsString = false;
				}
				// 日期格式
				String cellDateType = attributes.getValue("s");
				if ("1".equals(cellDateType)) {
					dateFlag = true;
				} else {
					dateFlag = false;
				}
				String cellNumberType = attributes.getValue("s");
				if ("2".equals(cellNumberType)) {
					numberFlag = true;
				} else {
					numberFlag = false;
				}
			}
			// 当元素为t时
			if ("t".equals(name)) {
				isTElement = true;
			} else {
				isTElement = false;
			}
			lastContents = "";
		}

		/**
		 * 获取value
		 */
		@Override
		public void endElement(String uri, String localName, String name)
				throws SAXException {
			if (nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
						.toString();
				nextIsString = false;
			}
			// t元素也包含字符串
			if (isTElement) {
				String value = lastContents.trim();
				record[thisColumnIndex] = value;
				isTElement = false;
				// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
				// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
			} else if ("v".equals(name)) {
				String value = lastContents.trim();
				value = value.equals("") ? " " : value;
				// 日期格式处理
				if (dateFlag) {
					try {
						Date date = HSSFDateUtil.getJavaDate(Double
								.valueOf(value));
						SimpleDateFormat dateFormat = new SimpleDateFormat(
								"yyyy-MM-dd");
						value = dateFormat.format(date);
					} catch (NumberFormatException e) {
					}
				}
				// 数字类型处理
				if (numberFlag) {
					try {
						BigDecimal bd = new BigDecimal(value);
						value = bd.setScale(3, BigDecimal.ROUND_UP).toString();
					} catch (Exception e) {
					}
				}
				record[thisColumnIndex] = value;

			} else {
				if (name.equals("row")) {
					if (minColumnCount > 0) {
						try {
							rowReader.getRows(sheetIndex, currentRow,
									record.clone(), connection, statement);
						} catch (Exception e) {
							// TODO 自动生成的 catch 块
							e.printStackTrace();
						}

						for (int i = 0; i < record.length; i++) {
							record[i] = null;
						}
					}
				}
			}
		}

		/**
		 * 到文档结束时进行调用实现最后一次执行和提交
		 */
		@Override
		public void endDocument() {
			try {
				statement.executeBatch();
				connection.commit();
				System.out.println("导入的数据数量" + rowReader.getExcutCount());
			} catch (SQLException e) {
				// TODO 自动生成的 catch 块
				e.printStackTrace();
			} finally {

				// 注意关闭连接
				try {
					if (statement != null) {

						statement.close();

					}
					if (connection != null) {
						connection.close();
					}
				} catch (SQLException e) {
					// TODO 自动生成的 catch 块
					e.printStackTrace();
				}
			}

		}

		@Override
		public void characters(char[] ch, int start, int length)
				throws SAXException {
			lastContents += new String(ch, start, length);
		}

	}

	private int nameToColumn(String name) {
		int column = -1;
		for (int i = 0; i < name.length(); ++i) {
			int c = name.charAt(i);
			column = (column + 1) * 26 + c - 'A';
		}
		return column;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	public PreparedStatement getStatement() {
		return statement;
	}

	public void setStatement(PreparedStatement statement) {
		this.statement = statement;
	}

	public static void main(String[] args) throws Exception {
		// 起始日期
		Date datestart = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-DD HH:mm:ss");
		String dateStartSt = format.format(datestart);

		Long starttime = new Date().getTime();// 获取执行时的起始时间
		RowReader rowrReader = new RowReader();
		BigExcelReader reader = new BigExcelReader("D:/181121.xlsx", 97);
		reader.setRowReader(rowrReader);
		try {
			reader.process();
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			// 不能提交返回
			reader.getConnection().rollback();
		}
		Long endtime = new Date().getTime();// 获取结束的时间
		Date dateEnd = new Date();
		String dateEndSt = format.format(dateEnd);
		System.out.println("所需要的的时间" + (endtime - starttime));
		System.out.println("起始时间" + dateStartSt + "结束日期" + dateEndSt);

	}
}

package net.sf.excelutils;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.junit.Test;

public class BigExcelReaderTest {

	@Test
	public void test() throws Exception {

		//起始日期
		Date datestart = new Date();
		SimpleDateFormat format  = new SimpleDateFormat("yyyy-MM-DD HH:mm:ss");
		String dateStartSt = format.format(datestart);
		
		Long starttime =   new Date().getTime();//获取执行时的起始时间
		RowReader rowrReader = new RowReader();
		BigExcelReader reader = new BigExcelReader("D:/181121.xlsx",97);
		reader.setRowReader(rowrReader);
		try {
			reader.process();
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			//不能提交返回
			reader.getConnection().rollback();
		}
		Long endtime = new Date().getTime();//获取结束的时间
		Date dateEnd = new Date();
		String dateEndSt =  format.format(dateEnd);
		System.out.println("所需要的的时间"+(endtime-starttime));
		System.out.println("起始时间"+ dateStartSt +"结束日期"+dateEndSt);
		
	
	}

}

测试结果：我导入了367652 数据，花费了4分钟；