Java实现批量导入excel表格数据到数据库中的方法

Java实现批量导入excel表格数据到数据库中的方法

本文实例讲述了java实现批量导入excel表格数据到数据库中的方法。分享给大家供大家参考，具体如下：

1、创建导入抽象类

package com.gcloud.common.excel;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.PrintStream;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;

import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;

import org.apache.poi.hssf.eventusermodel.HSSFListener;

import org.apache.poi.hssf.eventusermodel.HSSFRequest;

import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;

import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;

import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;

import org.apache.poi.hssf.model.HSSFFormulaParser;

import org.apache.poi.hssf.record.BOFRecord;

import org.apache.poi.hssf.record.BlankRecord;

import org.apache.poi.hssf.record.BoolErrRecord;

import org.apache.poi.hssf.record.BoundSheetRecord;

import org.apache.poi.hssf.record.FormulaRecord;

import org.apache.poi.hssf.record.LabelRecord;

import org.apache.poi.hssf.record.LabelSSTRecord;

import org.apache.poi.hssf.record.NoteRecord;

import org.apache.poi.hssf.record.NumberRecord;

import org.apache.poi.hssf.record.RKRecord;

import org.apache.poi.hssf.record.Record;

import org.apache.poi.hssf.record.SSTRecord;

import org.apache.poi.hssf.record.StringRecord;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**

* 导入抽象类

* Created by charlin on 2017/9/7.

*/

public abstract class HxlsAbstract implements HSSFListener {

private int minColumns;

private POIFSFileSystem fs;

private PrintStream output;

private int lastRowNumber;

private int lastColumnNumber;

/** Should we output the formula, or the value it has? */

private boolean outputFormulaValues = true;

/** For parsing Formulas */

private SheetRecordCollectingListener workbookBuildingListener;

private HSSFWorkbook stubWorkbook;

// Records we pick up as we process

private SSTRecord sstRecord;

private FormatTrackingHSSFListener formatListener;

/** So we known which sheet we're on */

private int sheetIndex = -1;

private BoundSheetRecord[] orderedBSRs;

@SuppressWarnings("unchecked")

private ArrayList boundSheetRecords = new ArrayList();

// For handling formulas with string results

private int nextRow;

private int nextColumn;

private boolean outputNextStringRecord;

private int curRow;

private List rowlist;

@SuppressWarnings( "unused")

private String sheetName;

public HxlsAbstract(POIFSFileSystem fs)

throws SQLException {

this.fs = fs;

this.output = System.out;

this.minColumns = -1;

this.curRow = 0;

this.rowlist = new ArrayList();

}

public HxlsAbstract(String filename) throws IOException,

FileNotFoundException, SQLException {

this(new POIFSFileSystem(new FileInputStream(filename)));

}

//excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型

// public abstract void optRows(int curRow, List rowlist) throws SQLException ;

//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型

public abstract void optRows(int sheetIndex,int curRow, List rowlist) throws Exception;

/**

* 遍历 excel 文件

*/

public void process() throws IOException {

MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(

this);

formatListener = new FormatTrackingHSSFListener(listener);

HSSFEventFactory factory = new HSSFEventFactory();

HSSFRequest request = new HSSFRequest();

if (outputFormulaValues) {

request.addListenerForAllRecords(formatListener);

} else {

workbookBuildingListener = new SheetRecordCollectingListener(

formatListener);

request.addListenerForAllRecords(workbookBuildingListener);

}

factory.processWorkbookEvents(request, fs);

}

/**

* HSSFListener 监听方法，处理 Record

*/

@SuppressWarnings("unchecked")

public void processRecord(Record record) {

int thisRow = -1;

int thisColumn = -1;

String thisStr = null;

String value = null;

switch (record.getSid()) {

case BoundSheetRecord.sid:

boundSheetRecords.add(record);

break;

case BOFRecord.sid:

BOFRecord br = (BOFRecord) record;

//进入sheet

if (br.getType() == BOFRecord.TYPE_WORKSHEET) {

// Create sub workbook if required

if (workbookBuildingListener != null && stubWorkbook == null) {

stubWorkbook = workbookBuildingListener

.getStubHSSFWorkbook();

}

// Works by ordering the BSRs by the location of

// their BOFRecords, and then knowing that we

// process BOFRecords in byte offset order

sheetIndex++;

if (orderedBSRs == null) {

orderedBSRs = BoundSheetRecord

.orderByBofPosition(boundSheetRecords);

}

sheetName = orderedBSRs[sheetIndex].getSheetname();

}

break;

case SSTRecord.sid:

sstRecord = (SSTRecord) record;

break;

case BlankRecord.sid:

BlankRecord brec = (BlankRecord) record;

thisRow = brec.getRow();

thisColumn = brec.getColumn();

thisStr = "";

break;

case BoolErrRecord.sid:

BoolErrRecord berec = (BoolErrRecord) record;

thisRow = berec.getRow();

thisColumn = berec.getColumn();

thisStr = "";

break;

case FormulaRecord.sid:

FormulaRecord frec = (FormulaRecord) record;

thisRow = frec.getRow();

thisColumn = frec.getColumn();

if (outputFormulaValues) {

if (Double.isNaN(frec.getValue())) {

// Formula result is a string

// This is stored in the next record

outputNextStringRecord = true;

nextRow = frec.getRow();

nextColumn = frec.getColumn();

} else {

thisStr = formatListener.formatNumberDateCell(frec);

}

} else {

thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,

frec.getParsedExpression()) + '"';

}

break;

case StringRecord.sid:

if (outputNextStringRecord) {

// String for formula

StringRecord srec = (StringRecord) record;

thisStr = srec.getString();

thisRow = nextRow;

thisColumn = nextColumn;

outputNextStringRecord = false;

}

break;

case LabelRecord.sid:

LabelRecord lrec = (LabelRecord) record;

curRow = thisRow = lrec.getRow();

thisColumn = lrec.getColumn();

value = lrec.getValue().trim();

value = value.equals("")?" ":value;

this.rowlist.add(thisColumn, value);

break;

case LabelSSTRecord.sid:

LabelSSTRecord lsrec = (LabelSSTRecord) record;

curRow = thisRow = lsrec.getRow();

thisColumn = lsrec.getColumn();

if (sstRecord == null) {

rowlist.add(thisColumn, " ");

} else {

value = sstRecord

.getString(lsrec.getSSTIndex()).toString().trim();

value = value.equals("")?" ":value;

rowlist.add(thisColumn,value);

}

break;

case NoteRecord.sid:

NoteRecord nrec = (NoteRecord) record;

thisRow = nrec.getRow();

thisColumn = nrec.getColumn();

// TODO: Find object to match nrec.getShapeId()

thisStr = '"' + "(TODO)" + '"';

break;

case NumberRecord.sid:

NumberRecord numrhttp://ec = (NumberRecord) record;

curRow = thisRow = numrec.getRow();

thisColumn = numrec.getColumn();

value = formatListener.formatNumberDateCell(numrec).trim();

value = value.equals("")?" ":value;

// Format

rowlist.add(thisColumn, value);

break;

case RKRecord.sid:

RKRecord rkrec = (RKRecord) record;

thisRow = rkrec.getRow();

thisColumn = rkrec.getColumn();

thisStr = '"' + "(TODO)" + '"';

break;

default:

break;

}

// 遇到新行的操作

if (thisRow != -1 && thisRow != lastRowNumber) {

lastColumnNumber = -1;

}

// 空值的操作

if (record instanceof MissingCellDummyRecord) {

MissingCellDummyRecord mc = (MissingCellDummyRecord) record;

curRow = thisRow = mc.getRow();

thisColumn = mc.getColumn();

rowlist.add(thisColumn," ");

}

// 如果遇到能打印的东西，在这里打印

if (thisStr != null) {

if (thisColumn > 0) {

output.print(',');

}

output.print(thisStr);

}

// 更新行和列的值

if (thisRow > -1)

lastRowNumber = thisRow;

if (thisColumn > -1)

lastColumnNumber = thisColumn;

// 行结束时的操作

if (record instanceof LastCellOfRowDummyRecord) {

if (minColumns > 0) {

// 列值重新置空

if (lastColumnNumber == -1) {

lastColumnNumber = 0;

}

}

// 行结束时， 调用 optRows() 方法

lastColumnNumber = -1;

try {

optRows(sheetIndex,curRow, rowlist);

} catch (Exception e) {

e.printStackTrace();

}

rowlist.clear();

}

}

}

2、创建导入接口

package com.gcloud.common.excel;

import java.util.List;

public interface HxlsOptRowsInterface {

public static final String SUCCESS="success";

/**

* 处理excel文件每行数据方法

* @param sheetIndex

* @param curRow

* @param rowlist

* @return success：成功，否则为失败原因

* @throws Exception

*/

public String optRows(int sheetIndex, int curRow, List rowlist) throws Exception;

}

3、创建实现类， 在这个方法实现把导入的数据添加到数据库中

package com.gcloud.common.excel;

import java.util.List;

public class HxlsInterfaceImpl implements HxlsOptRowsInterface {

@Override

public String optRows(int sheetIndex, int curRow, List datalist)

throws Exception {

//在这里执行数据的插入

//System.out.println(rowlist);

//saveData(datalist);

return "";

}

}

4、导入工具实现

package com.gcloud.common.excel;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

/**

* excel导入工具

* Created by charlin on 2017/9/7.

*/

public class ExcelImportUtil extends HxlsAbstract{

//数据处理bean

private HxlsOptRowsInterface hxlsOptRowsInterface;

//处理数据总数

private int optRows_sum = 0;

//处理数据成功数量

private int optRows_success = 0;

//处理数据失败数量

private int optRows_failure = 0;

//excel表格每列标题

private List rowtitle ;

//失败数据

private List> failrows;

//失败原因

private List failmsgs ;

//要处理数据所在的sheet索引,从0开始

private int sheetIndex;

public ExcelImportUtil(String filename, int sheetIndex, HxlsOptRowsInterface hxlsOptRowsInterface) throws IOException,

FileNotFoundException, SQLException {

super(filename);

this.sheetIndex = sheetIndex;

this.hxlsOptRowsInterface = hxlsOptRowsInterface;

this.rowtitle = new ArrayList();

this.failrows = new ArrayList>();

this.failmsgs = new ArrayList();

}

@Override

public void optRows(int sheetIndex,int curRow, List rowlist) throws Exception {

/*for (int i = 0 ;i< rowlist.size();i++){

System.out.print("'"+rowlist.get(i)+"',");

}

System.out.println();*/

//将rowlist的长度补齐和标题一致

int k=rowtitle.size()-rowlist.size();

for(int i=0;i

rowlist.add(null);

}

if(sheetIndex == this.sheetIndex){

optRows_sum++;

if(curRow == 0){//记录标题

rowtitle.addAll(rowlist);

}else{

String result = hxlsOptRowsInterface.optRows(sheetIndex, curRow, rowlist);

if(!result.equals(hxlsOptRowsInterface.SUCCESS)){

optRows_failure++;

//失败数据

failrows.add(new ArrayList(rowlist));

failmsgs.add(result);

}else{

optRows_success++;

}

}

}

}

public long getOptRows_sum() {

return optRows_sum;

}

public void setOptRows_sum(int optRows_sum) {

this.optRows_sum = optRows_sum;

}

public long getOptRows_success() {

return optRows_success;

}

public void setOptRows_success(int optRows_success) {

this.optRows_success = optRows_success;

}

public long getOptRows_failure() {

return optRows_failure;

}

public void setOptRows_failure(int optRows_failure) {

this.optRows_failure = optRows_failure;

}

public List getRowtitle() {

return rowtitle;

}

public List> getFailrows() {

return failrows;

}

public List getFailmsgs() {

return failmsgs;

}

public void setFailmsgs(List failmsgs) {

this.failmsgs = failmsgs;

}

}

5、导入实现方法：

public static void main(String[] args){

ExcelImportUtil importUtil;

try {

importUtil = new ExcelImportUtil("d:/data.xls",0, new HxlsInterfaceImpl());

importUtil.process();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

更多关于java相关内容感兴趣的读者可查看本站专题：《Java操作Excel技巧总结》、《Java+mysql数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》及《Java操作DOM节点技巧总结》

希望本文所述对大家java程序设计有所帮助。

rowlist.add(null);

}

if(sheetIndex == this.sheetIndex){

optRows_sum++;

if(curRow == 0){//记录标题

rowtitle.addAll(rowlist);

}else{

String result = hxlsOptRowsInterface.optRows(sheetIndex, curRow, rowlist);

if(!result.equals(hxlsOptRowsInterface.SUCCESS)){

optRows_failure++;

//失败数据

failrows.add(new ArrayList(rowlist));

failmsgs.add(result);

}else{

optRows_success++;

}

}

}

}

public long getOptRows_sum() {

return optRows_sum;

}

public void setOptRows_sum(int optRows_sum) {

this.optRows_sum = optRows_sum;

}

public long getOptRows_success() {

return optRows_success;

}

public void setOptRows_success(int optRows_success) {

this.optRows_success = optRows_success;

}

public long getOptRows_failure() {

return optRows_failure;

}

public void setOptRows_failure(int optRows_failure) {

this.optRows_failure = optRows_failure;

}

public List getRowtitle() {

return rowtitle;

}

public List> getFailrows() {

return failrows;

}

public List getFailmsgs() {

return failmsgs;

}

public void setFailmsgs(List failmsgs) {

this.failmsgs = failmsgs;

}

}

5、导入实现方法：

public static void main(String[] args){

ExcelImportUtil importUtil;

try {

importUtil = new ExcelImportUtil("d:/data.xls",0, new HxlsInterfaceImpl());

importUtil.process();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

更多关于java相关内容感兴趣的读者可查看本站专题：《Java操作Excel技巧总结》、《Java+mysql数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》及《Java操作DOM节点技巧总结》

希望本文所述对大家java程序设计有所帮助。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。