Java使用jacob组件读取大量Excel数据,性能差的问题

使用Jacob读取数据的时候,数据也就8000左右,为什么会很慢呢?
求大牛指点下,谢谢

public static void main(String[] args) throws FileNotFoundException, IOException {
String filename="C:/text/1234.xlsx";
ComThread.InitSTA();
/**
* 声明变量
* */
String xuhao = "";
String xuhao1 ="";
String number1 = "";
String number = "";
String name = "";
String mps = "";
String count = "";
// String unit="";
String cl = "";
String lbjzl = "";
String ps = "";
String bz = "";
String unit = "";
String isEndItem = "n";
String pn = "";
int flag = 0;
List list = new LinkedList();
ActiveXComponent xl=new ActiveXComponent("Excel.Application");

xl.setProperty("Visible", new Variant(false));//Excel显示或者隐藏
Dispatch workbooks=xl.getProperty("Workbooks").toDispatch();//获得workBooks

Variant workbookV=workbooks.invoke(workbooks, "Open", //打开Excel
Dispatch.Method,
new Object[] { filename, new Variant(false),//Excel的位置
new Variant(false) },
new int[1]);

Dispatch workbook=workbookV.toDispatch();//获得workbook
Dispatch sheets= Dispatch.get(workbook,"Worksheets").toDispatch();//获得所有的Sheet
int SheetCount=Dispatch.get(sheets,"Count").getInt();//获得有多少个sheet
System.out.println("11111111111111111111111111111111111111111111");
for(int i=1;i<=SheetCount;i++){//遍历sheets
Dispatch sheet = Dispatch.invoke(sheets,
"Item",
Dispatch.Get,
new Object[] { new Integer(i) },
new int[1]).toDispatch();
String sheetName=Dispatch.get(sheet,"Name").toString();//获得sheet的名字

if(sheetName.indexOf("BOM")>=0){//判断sheet名字是否有包含BOM
//System.out.println(sheetName+"------------------------------------------");
Dispatch userRange=Dispatch.call(sheet, "UsedRange").toDispatch();//获取Excel使用的sheet
Dispatch row=Dispatch.call(userRange, "Rows").toDispatch();
int rowCount=Dispatch.get(row,"Count").getInt();//excel的使用的行数
//Dispatch cell=Dispatch.call(userRange, "Columns").toDispatch();
//int cellCount=Dispatch.get(cell,"Count").getInt();//Excel使用列数
int beginPosition = getRecordRow(userRange,1,rowCount);
System.out.println(sheetName+"------------------------------------------"+rowCount);
for(int j=beginPosition;j<=rowCount;j++){//循环Excel中sheet的行数
HashMap<String, String> partInfo = new HashMap<String, String>();

number=GetValue(row,"B"+(j-1));
xuhao=GetValue(row,"A"+(j-1));
if (number.equals("物料编号")) {

}else{
//if (xuhao.equals("编制:") && j==){
// break;
//}
if ((!number.equals(""))) {
flag = 0;
partInfo = getPartInfo(xuhao1, number1,
name, mps, count, unit, cl, lbjzl,
ps, pn, bz, isEndItem);
list.add(partInfo);
number1 = number;
xuhao1 =xuhao;
name=GetValue(row, "C"+j);
mps=GetValue(row, "D"+j);
count=GetValue(row, "L"+j);
unit=GetValue(row, "M"+j);
cl=GetValue(row, "N"+j);
lbjzl=GetValue(row, "O"+j);
ps=GetValue(row, "Q"+j);
pn=GetValue(row, "R"+j);
bz=GetValue(row, "T"+j);
if (xuhao.equals("0.1")) {
isEndItem = "y";
} else {
isEndItem = "n";
}
}else{
flag++;
if (flag > 4)
continue;
if (flag == 4) {
// System.out.println("编号:"+number1+" "+"名称:"+name+" "+"主要参数:"+mps+" "+"材料:"+cl+" "+"零部件重量:"+lbjzl+" "+"生产状态:"+ps+" "+"备注:"+bz+" ");
mps=mps+GetValue(row, "D"+j);
partInfo = getPartInfo(xuhao1, number1,
name, mps, count, unit, cl,
lbjzl, ps, pn, bz, isEndItem);
list.add(partInfo);
number = number1 = "";
xuhao =xuhao1= "";
name = "";
mps = "";
count = "";
cl = "";
unit = "";
// unitKey="";
lbjzl = "";
ps = "";
pn = "";
bz = "";
isEndItem = "n";
xuhao = "";
// flag=0;
// i=getRecordRow(sheet,
// currentRow-1)-1;
}
}
}

}
}else{
continue;
}

}
System.out.println(list.size()+"---------List.Size()");
Variant f = new Variant(false);
Dispatch.call(workbook, "Close",f);
ComThread.Release();
}


//读取Excel的值
public static String GetValue(Dispatch sheet, String position) {
Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
new Object[] { position }, new int[1]).toDispatch();
String value = Dispatch.get(cell, "Value").toString();
return value;
}

//把数字转换成Excel中的列数
public static String ConvertToColName(int colIndex) {

String strRtn = "";
if (colIndex <= 0){
return "";
}

int numMod = 0;
int numTemp = colIndex;
char ch = 'A';

do{
numMod = numTemp % 26;
numTemp = numTemp / 26;
if (numMod == 0) {
ch = 'Z';
numTemp--;
}
else{
ch = (char)('A' + numMod - 1);
}

strRtn = ch + strRtn;
}

while (numTemp > 0);
return strRtn;
}

public static int getRecordRow(Dispatch sheet, int rowNumber,int rowCount) {
int ReturnrowCount = 0;
for (int i = rowNumber; i < rowCount; i++) {
String addres="B"+i;
String temp = GetValue(sheet,addres);
if (temp.equals("物料编号")) {
// System.out.println("物料编号所在行数:"+(i+1));
ReturnrowCount = i;
break;
}
}
return ReturnrowCount + 1;
}

/**
* 把读取到的值封装成HashMap
*
* **/
public static HashMap<String, String> getPartInfo(String xuhao,
String number, String name, String mps, String count, String unit,
String cl, String lbjzl, String ps, String pn, String bz,
String isEndItem) {
HashMap<String, String> part = new HashMap<String, String>();
part.put("xuhao", xuhao);
part.put("number", number);
part.put("name", name);
part.put("mps", mps);
part.put("count", count);
part.put("unit", unit);
part.put("cl", cl);
part.put("lbjzl", lbjzl);
part.put("ps", ps);
part.put("pn", pn);
part.put("bz", bz);
part.put("isEndItem", isEndItem);
return part;
}

使用Jacob读取数据的时候,数据也就8000左右,为什么会很慢呢?
求大牛指点下,谢谢


怎么就没人说说是怎么回事呢?
我知道是和COM接口使用过多造成的,但是又不知道怎么改
name=GetValue(row, "C"+j);
mps=GetValue(row, "D"+j);
count=GetValue(row, "L"+j);
unit=GetValue(row, "M"+j);
cl=GetValue(row, "N"+j);
lbjzl=GetValue(row, "O"+j);
ps=GetValue(row, "Q"+j);
pn=GetValue(row, "R"+j);
bz=GetValue(row, "T"+j);

就是这段太慢了,去掉这段只要30秒,加上就要4分钟左右,大家说说有什么好的方式读呢?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值