java 百万数据下载,1、POI读取百万级数据工具

1、SheetHandler

SheetHandler  类中处理从excle获取的数据，官方文档中 SheetHandler以内部类形式，为保证更新代码减少内部类class文件忘记打包，改为一般java类

package com.lee.poi.util;

import java.util.LinkedHashMap;

import org.apache.poi.xssf.model.SharedStringsTable;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.xml.sax.Attributes;

import org.xml.sax.SAXException;

import org.xml.sax.helpers.DefaultHandler;

/**

SheetHandler 类中处理从excle获取的数据，官方文档中 SheetHandler以内部类形式，为保证更新代码减少内部类class文件忘记打包，改为一般java类

*/

public class SheetHandler extends DefaultHandler{

private SharedStringsTable sst;

private String lastContents;

private boolean nextIsString;

private String cellPosition;

private LinkedHashMap rowContents = new LinkedHashMap();

public LinkedHashMap getRowContents() {

return rowContents;

}

public void setRowContents(LinkedHashMap rowContents) {

this.rowContents = rowContents;

}

public SheetHandler(SharedStringsTable sst) {

this.sst = sst;

}

public void startElement(String uri, String localName, String name,

Attributes attributes) throws SAXException {

if(name.equals("c")) {

cellPosition=attributes.getValue("r");

String cellType = attributes.getValue("t");

if(cellType != null && cellType.equals("s")) {

nextIsString = true;

} else {

nextIsString = false;

}

}

// 清楚缓存内容

lastContents = "";

}

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;

}

if(name.equals("v")) {

//数据读取结束后，将单元格坐标,内容存入map中

if(!(cellPosition.length()==2)||(cellPosition.length()==2&&!"1".equals(cellPosition.substring(1)))){//不保存第一行数据

rowContents.put(cellPosition, lastContents);

}

}

}

public void characters(char[] ch, int start, int length)

throws SAXException {

lastContents += new String(ch, start, length);

}

}

2、 LargeExcelFileReadUtil

数据量比较大(8万条以上)的excel文件解析，将excel文件解析为 行列坐标-值的形式存入map中，此方式速度快，内存耗损小 但只能读取excle文件

提供处理单个sheet方法 processOneSheet(String  filename) 以及处理多个sheet方法 processAllSheets(String  filename)

只需传入文件路径+文件名即可  调用处理方法结束后，只需 接收LargeExcelFileReadUtil.getRowContents()返回值即可获得解析后的数据

package com.lee.poi.util;

import java.io.InputStream;

import java.util.Iterator;

import java.util.LinkedHashMap;

import java.util.Map;

import java.util.Map.Entry;

import org.apache.poi.openxml4j.opc.OPCPackage;

import org.apache.poi.xssf.eventusermodel.XSSFReader;

import org.apache.poi.xssf.model.SharedStringsTable;

import org.junit.Test;

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.XMLReaderFactory;

/**

* 数据量比较大(8万条以上)的excel文件解析，将excel文件解析为 行列坐标-值的形式存入map中，此方式速度快，内存耗损小 但只能读取excle文件

* 提供处理单个sheet方法 processOneSheet(String filename) 以及处理多个sheet方法 processAllSheets(String filename)

* 只需传入文件路径+文件名即可 调用处理方法结束后，只需 接收LargeExcelFileReadUtil.getRowContents()返回值即可获得解析后的数据

*/

public class LargeExcelFileReadUtil {

private LinkedHashMap rowContents=new LinkedHashMap();

private SheetHandler sheetHandler;

public LinkedHashMap getRowContents() {

return rowContents;

}

public void setRowContents(LinkedHashMap rowContents) {

this.rowContents = rowContents;

}

public SheetHandler getSheetHandlexr() {

return sheetHandler;

}

public void setSheetHandler(SheetHandler sheetHandler) {

this.sheetHandler = sheetHandler;

}

//处理一个sheet

public void processOneSheet(String filename) throws Exception {

InputStream sheet2=null;

OPCPackage pkg =null;

try {

pkg = OPCPackage.open(filename);

XSSFReader r = new XSSFReader(pkg);

SharedStringsTable sst = r.getSharedStringsTable();

XMLReader parser = fetchSheetParser(sst);

sheet2 = r.getSheet("rId1");

InputSource sheetSource = new InputSource(sheet2);

parser.parse(sheetSource);

setRowContents(sheetHandler.getRowContents());

}catch (Exception e) {

e.printStackTrace();

throw e;

}finally{

if(pkg!=null){

pkg.close();

}

if(sheet2!=null){

sheet2.close();

}

}

}

//处理多个sheet

public void processAllSheets(String filename) throws Exception {

OPCPackage pkg =null;

InputStream sheet=null;

try{

pkg=OPCPackage.open(filename);

XSSFReader r = new XSSFReader( pkg );

SharedStringsTable sst = r.getSharedStringsTable();

XMLReader parser = fetchSheetParser(sst);

Iterator sheets = r.getSheetsData();

while(sheets.hasNext()) {

System.out.println("Processing new sheet:\n");

sheet = sheets.next();

InputSource sheetSource = new InputSource(sheet);

parser.parse(sheetSource);

}

}catch (Exception e) {

e.printStackTrace();

throw e;

}finally{

if(pkg!=null){

pkg.close();

}

if(sheet!=null){

sheet.close();

}

}

}

public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {

XMLReader parser =

XMLReaderFactory.createXMLReader(

"com.sun.org.apache.xerces.internal.parsers.SAXParser"

);

setSheetHandler(new SheetHandler(sst));

ContentHandler handler = (ContentHandler) sheetHandler;

parser.setContentHandler(handler);

return parser;

}

//测试

@Test

public void test ()throws Exception {

Long time=System.currentTimeMillis();

LargeExcelFileReadUtil example = new LargeExcelFileReadUtil();

example.processOneSheet("C:\\Users\\Never Give Up\\Desktop\\导入文件\\2、24万企业数据\\24万企业数据.xlsx");

Long endtime=System.currentTimeMillis();

LinkedHashMap map=example.getRowContents();

Iterator> it= map.entrySet().iterator();

int count=0;

int paramCount = 0;

String prePos="";

Entity entity = null;

while (it.hasNext()){

paramCount++;

Map.Entry entry=(Map.Entry)it.next();

String pos=entry.getKey();

if(!pos.substring(1).equals(prePos)){

prePos=pos.substring(1);

count++;

entity = new Entity();

}

if("A".equals(pos.substring(0,1))) {

entity.setCompanyName(entry.getValue());

}

if("B".equals(pos.substring(0,1))) {

entity.setMemberName(entry.getValue());

}

if("C".equals(pos.substring(0,1))) {

entity.setPhone(entry.getValue());

}

if("D".equals(pos.substring(0,1))) {

if(entry.getValue().contains("【依法须经批准的项目，经相关部门批准后方可开展经营活动】")) {

entity.setMainProducts(entry.getValue().substring(0, entry.getValue().indexOf("【依法须经批准的项目，经相关部门批准后方可开展经营活动】")));

}else {

entity.setMainProducts(entry.getValue());

}

paramCount=0;

ImportExcel.insertMember_2(entity);

}

}

System.out.println("解析数据"+count+"条;耗时"+(endtime-time)/1000+"秒");

}

}

转载至链接:https://my.oschina.net/u/3551274/blog/1843669