COM接口来实现php批量替换Excl

    $excelPath = "e:\\excel";
$total = $success = $jump = 0;
$objExcel = new COM("excel.application") or die("不能打开Excel应用程序");
$handle = opendir($excelPath); //打开当前目录
readdir($handle); //排除"."
readdir($handle); //排除 ".."
//循环读取文件
while ($file = readdir($handle)) {
    $total++;
    //生成源文件名
    $fileName = $excelPath . DIRECTORY_SEPARATOR .$file;
    $pathinfo = pathinfo($fileName);
    if(strtolower($pathinfo['extension']) != 'xls'){
        echo "已跳过:".$file.",文件格式不对";
        $jump++;
        continue;
    }
    echo "正在替换文件:".$file."\n";
    if(file_exists($fileName)){
        if(!$objExcel->Application->WorkBooks->Open($fileName)){
            echo $file."打开失败,跳过!";
            $jump++;
            continue;
        }
    }
    else{
        echo $fileName."文件不存在\n";
        $jump++;
        continue;
    }
    $totalRow = $objExcel->ActiveSheet->UsedRange->Rows->Count;
    for($i=1;$i<=$totalRow;$i++){
        $keyword = trim($objExcel->Worksheets(1)->Range("A".$i)->Value);//获取到值
        if(in_array($keyword,$replaceIn)){//判断后在这里进行替换
            $objExcel->Worksheets(1)->Range("B".$i)->Value = $replaceRule[$keyword];
            $objExcel->Worksheets(1)->Range("B".$i)->Font->Name = "黑体";
            $objExcel->Worksheets(1)->Range("B".$i)->Font->Size = 12;
        }
    }
    if(!$objExcel->ActiveWorkBook->Saved)
    {
        $objExcel->ActiveWorkBook->save();
        echo $file."保存成功!\n";
        $success++;
    }
    //关闭工作簿
    $objExcel->Quit();
}
echo "\n全部替换完成!共{$total}个,成功{$success}个,跳过{$jump}个!\n";
//清空对象
$objExcel = null;
exit;

需要注意的是,尽量把这个程序放在命令行下跑,别放在IIS或APACHE下面,因为是调用的系统组件COM,所以可能会出现各种奇葩的错误,这些错误的原因都是因为权限不足引起的,所以直接用管理员身份运行命令行,在命令行下跑就能把权限问题排除在外!


金俸毅
139 声望3 粉丝

开发只是乐趣