Java实现excel大数据量导入

Java实现实现excel大数据量导入大数据量导入

主要为大家详细介绍了Java实现excel大数据量导入，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

本文实例为大家分享了Java实现excel大数据量导入的具体代码，供大家参考，具体内容如下

情景分析：情景分析：

通常我们通过poi读取excel文件时，若在用户模式下，由于数据量较大、Sheet较多，很容易出现内存溢出的情况

用户模式读取excel的典型代码如下：

FileInputStream file = new FileInputStream("c:\test.xlsx");

Workbook wb=new XSSFWorkbook(file);

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.io.PrintStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;

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

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

import org.apache.poi.ss.usermodel.BuiltinFormats;

import org.apache.poi.ss.usermodel.DataFormatter;

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

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

public class XLSX2CSV {

enum xssfDataType {

BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,

}

/**

* 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

*/

class MyXSSFSheetHandler extends DefaultHandler {

private final int minColumnCount;

private boolean vIsOpen;

private xssfDataType nextDataType;

private short formatIndex;

private String formatString;

private final DataFormatter formatter;

private List<String[]> rows = new ArrayList<String[]>();

private boolean isCellNull = false;

public MyXSSFSheetHandler(StylesTable styles,ReadOnlySharedStringsTable strings, int cols, PrintStream target) {

this.stylesTable = styles;

this.sharedStringsTable = strings;

this.minColumnCount = cols;

this.output = target;

this.value = new StringBuffer();

this.nextDataType = xssfDataType.NUMBER;

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

vIsOpen = true;

// Clear contents cache

value.setLength(0);

}

// c => cell

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

// Get the cell reference

String r = attributes.getValue("r");

int firstDigit = -1;

thisColumn = nameToColumn(r.substring(0, firstDigit));

// Set up defaults.

this.nextDataType = xssfDataType.NUMBER;

this.formatIndex = -1;

this.formatString = null;

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

String cellStyleStr = attributes.getValue("s");

if ("b".equals(cellType))

nextDataType = xssfDataType.BOOL;

else if ("str".equals(cellType))

nextDataType = xssfDataType.FORMULA;

else if (cellStyleStr != null) {

// It's a number, but almost certainly one

// with a special style or format

int styleIndex = Integer.parseInt(cellStyleStr);

XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);

this.formatIndex = style.getDataFormat();

this.formatString = style.getDataFormatString();

if (this.formatString == null)

public void endElement(String uri, String localName, String name)throws SAXException {

String thisStr = null;

// v => contents of a cell

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

// Process the value contents as required.

// Do now, as characters() may be called more than once

switch (nextDataType) {

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

if (vIsOpen)

value.append(ch, start, length);

}

/**

* Converts an Excel column name like "C" to a zero-based index.

* @param name

* @return Index corresponding to the specified name

*/

return column;

}

private String formateDateToString(Date date) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期

return sdf.format(date);

}

}

* Creates a new XLSX -> CSV converter

*

* @param pkg

* The XLSX package to process

* @param output

* The PrintStream to output the CSV to

* @param minColumns

* The minimum number of columns to output, or -1 for no minimum

*/

public XLSX2CSV(OPCPackage pkg, PrintStream output,int minColumns) {