PHP Excel写入追加内容

/**

     * 在已有excel表中插入数据例子

     */

 

    public function readyExcel($filename){

        vendor("PHPExcel.PHPExcel");

        $inputFileName = $filename;//excel文件路径

        date_default_timezone_set('PRC');

        // 读取excel文件

        try {

            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);

            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);

            $objPHPExcel = $objReader->load($inputFileName);

        } catch(\Exception $e) {

            die('加载文件发生错误："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

        }

        $array = [

            ['A','B','C','D','E','F'],

            ['A1','B1','C1','D1','E1','F1'],

            ['A2','B2','C2','D2','E2','F2']

        ];

        $baseRow=17;      //指定插入到第17行后

        foreach($array as $index=>$dataRow){

            $row= $baseRow +$index;    //$row是循环操作行的行号

            $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);  //在操作行的号前加一空行，这空行的行号就变成了当前的行号

            //对应的列都附上数据和编号

            $objPHPExcel->getActiveSheet()->setCellValue( 'A'.$row,$dataRow[0]);

            $objPHPExcel->getActiveSheet()->setCellValue( 'B'.$row,$dataRow[1]);

            $objPHPExcel->getActiveSheet()->setCellValue( 'C'.$row,$dataRow[2]);

            $objPHPExcel->getActiveSheet()->setCellValue( 'D'.$row,$dataRow[3]);

            $objPHPExcel->getActiveSheet()->setCellValue( 'E'.$row, $dataRow[4]);

            $objPHPExcel->getActiveSheet()->setCellValue( 'F'.$row, $dataRow[5]);

        }

        ob_end_clean();//清除缓存区，解决乱码问题

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="Bill.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output');

        exit;

    }

 

更改格式范例

/**
 * excel插入一条数据
 * @param type $filename    文件路径
 * @param type $data        插入数据,数据格式[[a,b,c],[a1,b2,a3]]
 * @param type $baseRow     插入行数
 */
function readyExcel($filename, $data, $baseRow = 17) {
    require_once './Public/Classes/PHPExcel.php'; //导入excel必须
    $inputFileName = $filename; //excel文件路径
    date_default_timezone_set('PRC');
    // 读取excel文件
    try {
        $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
        $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch (\Exception $e) {
        die('加载文件发生错误："' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }
    foreach ($data as $index => $dataRow) {
        $row = $baseRow + $index;    //$row是循环操作行的行号
        $objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 1);  //在操作行的号前加一空行，这空行的行号就变成了当前的行号
        //对应的列都附上数据和编号
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $dataRow[0]);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $dataRow[1]);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $dataRow[2]);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $dataRow[3]);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $dataRow[4]);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $dataRow[5]);
    }
    ob_end_clean(); //清除缓存区，解决乱码问题
//    header('Content-Type: application/vnd.ms-excel');
//    header('Content-Disposition: attachment;filename="Bill.xls"');
//    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//    $objWriter->save('php://output');
    $objWriter->save($filename);
}