Java實現(xiàn)批量導入excel表格數(shù)據(jù)到數(shù)據(jù)庫中的方法
更新時間：2017年10月28日 10:00:01 作者：CharlinGod
這篇文章主要介紹了Java實現(xiàn)批量導入excel表格數(shù)據(jù)到數(shù)據(jù)庫中的方法,結合實例形式詳細分析了java導入Excel數(shù)據(jù)到數(shù)據(jù)庫的具體步驟與相關操作技巧,需要的朋友可以參考下
本文實例講述了Java實現(xiàn)批量導入excel表格數(shù)據(jù)到數(shù)據(jù)庫中的方法。分享給大家供大家參考，具體如下：
1、創(chuàng)建導入抽象類
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<String> 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<String>();
}
public HxlsAbstract(String filename) throws IOException,
FileNotFoundException, SQLException {
this(new POIFSFileSystem(new FileInputStream(filename)));
}
//excel記錄行操作方法，以行索引和行元素列表為參數(shù)，對一行元素進行操作，元素為String類型
// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
//excel記錄行操作方法，以sheet索引，行索引和行元素列表為參數(shù)，對sheet的一行元素進行操作，元素為String類型
public abstract void optRows(int sheetIndex,int curRow, List<String> 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 監(jiān)聽方法，處理 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 numrec = (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;
}
}
// 行結束時， 調(diào)用 optRows() 方法
lastColumnNumber = -1;
try {
optRows(sheetIndex,curRow, rowlist);
} catch (Exception e) {
e.printStackTrace();
}
rowlist.clear();
}
}
}
2、創(chuàng)建導入接口
package com.gcloud.common.excel;
import java.util.List;
public interface HxlsOptRowsInterface {
public static final String SUCCESS="success";
/**
* 處理excel文件每行數(shù)據(jù)方法
* @param sheetIndex
* @param curRow
* @param rowlist
* @return success：成功，否則為失敗原因
* @throws Exception
*/
public String optRows(int sheetIndex, int curRow, List<String> rowlist) throws Exception;
}
3、創(chuàng)建實現(xiàn)類， 在這個方法實現(xiàn)把導入的數(shù)據(jù)添加到數(shù)據(jù)庫中
package com.gcloud.common.excel;
import java.util.List;
public class HxlsInterfaceImpl implements HxlsOptRowsInterface {
@Override
public String optRows(int sheetIndex, int curRow, List<String> datalist)
throws Exception {
//在這里執(zhí)行數(shù)據(jù)的插入
//System.out.println(rowlist);
//saveData(datalist);
return "";
}
}
4、導入工具實現(xiàn)
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{
//數(shù)據(jù)處理bean
private HxlsOptRowsInterface hxlsOptRowsInterface;
//處理數(shù)據(jù)總數(shù)
private int optRows_sum = 0;
//處理數(shù)據(jù)成功數(shù)量
private int optRows_success = 0;
//處理數(shù)據(jù)失敗數(shù)量
private int optRows_failure = 0;
//excel表格每列標題
private List<String> rowtitle ;
//失敗數(shù)據(jù)
private List<List<String>> failrows;
//失敗原因
private List<String> failmsgs ;
//要處理數(shù)據(jù)所在的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<String>();
this.failrows = new ArrayList<List<String>>();
this.failmsgs = new ArrayList<String>();
}
@Override
public void optRows(int sheetIndex,int curRow, List<String> 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<k;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++;
//失敗數(shù)據(jù)
failrows.add(new ArrayList<String>(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<String> getRowtitle() {
return rowtitle;
}
public List<List<String>> getFailrows() {
return failrows;
}
public List<String> getFailmsgs() {
return failmsgs;
}
public void setFailmsgs(List<String> failmsgs) {
this.failmsgs = failmsgs;
}
}
5、導入實現(xiàn)方法：
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相關內(nèi)容感興趣的讀者可查看本站專題：《Java操作Excel技巧總結》、《Java+MySQL數(shù)據(jù)庫程序設計總結》、《Java數(shù)據(jù)結構與算法教程》、《Java文件與目錄操作技巧匯總》及《Java操作DOM節(jié)點技巧總結》
希望本文所述對大家java程序設計有所幫助。
相關文章
基于Java語言在窗體上實現(xiàn)飛機大戰(zhàn)小游戲的完整步驟
這篇文章主要給大家介紹了基于Java語言在窗體上實現(xiàn)飛機大戰(zhàn)小游戲的完整步驟,文中通過圖文以及實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2022-02-02
idea普通javaweb項目如何部署到tomcat(讀取web.xml文件)
這篇文章主要介紹了idea普通javaweb項目如何部署到tomcat(讀取web.xml文件),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
這篇文章主要介紹了springboot植入pagerHelper的超詳細教程,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
java開發(fā)分布式服務框架Dubbo服務引用過程詳解
這篇文章主要為大家介紹了java開發(fā)分布式服務框架Dubbo服務引用詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步2021-11-11
這篇文章主要介紹了jdbc連接oracle數(shù)據(jù)庫功能,結合實例形式詳細分析了java基于jdbc連接Oracle數(shù)據(jù)庫的具體操作步驟與相關實現(xiàn)技巧,需要的朋友可以參考下2017-01-01
詳解MyBatis resultType與resultMap中的幾種返回類型
本文主要介紹了MyBatis resultType與resultMap中的幾種返回類型，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-09-09
Java concurrency之CountDownLatch原理和示例_動力節(jié)點Java學院整理
CountDownLatch是一個同步輔助類，在完成一組正在其他線程中執(zhí)行的操作之前，它允許一個或多個線程一直等待。 下面通過本文給大家分享Java concurrency之CountDownLatch原理和示例，需要的的朋友參考下吧2017-06-06
這篇文章主要介紹了Java實現(xiàn)文件變化監(jiān)聽代碼實例,通過定時任務,輪訓查詢文件的最后修改時間,與上一次進行對比,如果發(fā)生變化,則說明文件已經(jīng)修改,進行重新加載或對應的業(yè)務邏輯處理,需要的朋友可以參考下2024-01-01
這篇文章主要介紹了AJAX+JAVA用戶登陸注冊驗證的實現(xiàn)代碼，通過ajax異步刷新頁面驗證用戶輸入的賬號密碼是否在數(shù)據(jù)庫中存在。非常具有實用價值，需要的朋友可以參考下2018-06-06
這幾天看到類在繼承時會用到this和super,這里就做了一點總結,下面這篇文章主要給大家介紹了關于Java中super關鍵字介紹以及super()使用的相關資料,需要的朋友可以參考下2022-01-01