使用的是ActiveX控件，只支持IE浏览器
var excelApp;
var excelWorkBook;
var excelSheet;
try{
excelApp = new ActiveXObject("Excel.Application");
excelWorkBook = excelApp.Workbooks.open("C:\1.xls");
excelSheet = excelWorkBook.ActiveSheet; //WorkSheets("sheet1")
//alert(excelSheet.Cells(1,1).value);
//excelSheet.Cells(6,2).value;//cell的值
//excelSheet.usedrange.rows.count;//使用的行数
//excelWorkBook.Worksheets.count;//得到sheet的个数
//var xlBook = excelApp.Workbooks.Add;
var xlBook = excelApp.Workbooks.open("C:\0105.xls");
var excelSheet_result = xlBook.Worksheets("客户名单");
//循环0105表中的客户姓名匹配查询用户资料表里的信息抓取过来
var len = (excelSheet_result.usedrange.rows.count);
for(var m=441;m<482;m++){
console.log("处理到第"+m+"个");
//匹配姓名
var j =(excelSheet.usedrange.rows.count);
var name = excelSheet_result.Cells(m,13).value;
for(var i=1;i<j+1;i++){
var name1 = excelSheet.Cells(i,3).value;
if(name1 == name){
var _val = (excelSheet.Cells(i,4).value);
var tmp = _val.split(' ');
var str1 = "";
for(var k=1;k<tmp.length;k++){
if(tmp[k]!=""){
str1 = (tmp[k]);
}
}
//拼音姓
excelSheet_result.Cells(m,6) = tmp[0];
//拼音名
excelSheet_result.Cells(m,7) = str1;
//出生日期
var cs = excelSheet.Cells(i,6).value;
excelSheet_result.Cells(m,11) = cs.replace('.','-').replace('.','-');
//性别
var sex = excelSheet.Cells(i,5).value;
if(sex == "男"){
excelSheet_result.Cells(m,12) = "M";
}else if(sex == "女"){
excelSheet_result.Cells(m,12) = "F";
}else{
excelSheet_result.Cells(m,12) = sex;
}
//护照号
excelSheet_result.Cells(m,14) = excelSheet.Cells(i,8).value;
//签发日期
var qf = excelSheet.Cells(i,9).value;
excelSheet_result.Cells(m,15) = qf.replace('.','-').replace('.','-');
//有效日期
//如果出生日期<2000,10年-1天，如果>2000年5年-1天
var year_temp = cs.split(".");
var year = year_temp[0];
if(Number(year)<2000){
excelSheet_result.Cells(m,16) = "=DATE(YEAR(O"+m+")+10,MONTH(O"+m+"),DAY(O"+m+")-1)";
}else{
excelSheet_result.Cells(m,16) = "=DATE(YEAR(O"+m+")+5,MONTH(O"+m+"),DAY(O"+m+")-1)";
}
//签发地
var address = (excelSheet.Cells(i,10).value).replace('省','').replace('市','');
excelSheet_result.Cells(m,17) = convertToPinyinLower(address);
//团号
excelSheet_result.Cells(m,31) = "W"+excelSheet.Cells(i,1).value;
//出生地
var addressbrithday = (excelSheet.Cells(i,7).value).replace('省','').replace('市','');
excelSheet_result.Cells(m,55) = addressbrithday;
}
}
}
//根据舱房编号来匹配舱房
/*
var j =(excelSheet.usedrange.rows.count);
for(var i=1;i<j+1;i++){
//姓名
excelSheet1.Cells(i,1) = (excelSheet.Cells(i,3).value);
var _val = (excelSheet.Cells(i,4).value);
var tmp = _val.split(' ');
var str1 = "";
for(var k=1;k<tmp.length;k++){
if(tmp[k]!=""){
str1 = (tmp[k]);
}
}
//拼音姓
excelSheet1.Cells(i,2) = tmp[0];
//拼音名
excelSheet1.Cells(i,3) = str1;
//性别
var sex = excelSheet.Cells(i,5).value;
if(sex == "男"){
excelSheet1.Cells(i,4) = "M";
}else if(sex == "女"){
excelSheet1.Cells(i,4) = "F";
}else{
excelSheet1.Cells(i,4) = sex;
}
//出生日期
var cs = excelSheet.Cells(i,6).value;
excelSheet1.Cells(i,5) = cs.replace('.','-').replace('.','-');
//出生地
excelSheet1.Cells(i,6) = excelSheet.Cells(i,7).value;
//护照号
excelSheet1.Cells(i,7) = excelSheet.Cells(i,8).value;
//签发日期
var qf = excelSheet.Cells(i,9).value;
excelSheet1.Cells(i,8) = qf.replace('.','-').replace('.','-');
//签发地
var address = (excelSheet.Cells(i,10).value).replace('省','').replace('市','');
if(i==1){
excelSheet1.Cells(i,9) = address;
}else{
excelSheet1.Cells(i,9) = convertToPinyinLower(address);
}
//团号
excelSheet1.Cells(i,10) = excelSheet.Cells(i,1).value;
}
excelSheet1.Cells(1,2) = "拼音姓";
excelSheet1.Cells(1,3) = "拼音名";
*/
//excelSheet1.Cells(1,2) = "拼音姓";
// excelSheet_result.Save();
excelSheet_result.SaveAs("C:\result.xls");
/*
for(var i=1;i<j+1;i++){
for(var k=1;k<100;k++){
if($("#dm"+k).html()){
if(excelSheet.Cells(i,1).value==$("#dm"+k).html()){
$("#w"+k+"1").val(excelSheet.Cells(i,4).value);
$("#w"+k+"2").val(excelSheet.Cells(i,5).value);
$("#w"+k+"3").val(excelSheet.Cells(i,6).value);
$("#w"+k+"4").val(excelSheet.Cells(i,7).value);
$("#w"+k+"5").val(excelSheet.Cells(i,8).value);
$("#w"+k+"6").val(excelSheet.Cells(i,9).value);
$("#w"+k+"7").val(excelSheet.Cells(i,10).value);
}
}
}
}*/
excelSheet=null;
excelWorkBook.close();
excelApp.Application.Quit();
excelApp=null;
}catch(e){
if(excelSheet !=null || excelSheet!=undefined){
excelSheet =null;
}
if(excelWorkBook != null || excelWorkBook!=undefined){
excelWorkBook.close();
}
if(excelApp != null || excelApp!=undefined){
excelApp.Application.Quit();
excelApp=null;
}
}
