使用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左右,为什么会很慢呢?
求大牛指点下,谢谢
求大牛指点下,谢谢
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左右,为什么会很慢呢?
求大牛指点下,谢谢