[PHP] PHP操作Excel导出和导入,使用PHPExcel第三方类操作

来源：互联网发布：德芙的含义知乎编辑：程序博客网时间：2024/06/07 19:34

1. 引入PHPExcel类，去官网下载类库http://phpexcel.codeplex.com/

下载的官方文档，如上图。对于项目真正有用的事Classes文件夹，将Classes更名为PHPExcel放到自己项目类库中，以下是Thinkphp框架下的类库存放目录。

2. 下面导出的使用与实践

            //将数据导出到Excel表            引入必须的文件            require '../ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php';            require '../ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';            $res=M('stock_transshipment')->where(array('id'=>$id))->find();            //创建一个PHPExcel对象            $objPHPExcel = new \PHPExcel();            //设置文件的属性设置            $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")             ->setLastModifiedBy("Maarten Balliauw")             ->setTitle("Office 2007 XLSX Test Document")             ->setSubject("Office 2007 XLSX Test Document")             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")             ->setKeywords("office 2007 openxml php")             ->setCategory("Test result file");            //设置表格样式            $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');            $objPHPExcel->getActiveSheet()->mergeCells('A11:G11');            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);            $objPHPExcel->getActiveSheet()->getStyle('A11')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);            for($i=2;$i<=10;++$i){                $str="A".$i;                $objPHPExcel->getActiveSheet()->getStyle($str)->getFont()->setBold(true);            }            $objPHPExcel->getActiveSheet()->getStyle('A12')->getFont()->setBold(true);            $objPHPExcel->getActiveSheet()->getStyle('B12')->getFont()->setBold(true);            $objPHPExcel->getActiveSheet()->getStyle('C12')->getFont()->setBold(true);            $objPHPExcel->getActiveSheet()->getStyle('D12')->getFont()->setBold(true);            $objPHPExcel->getActiveSheet()->getStyle('E12')->getFont()->setBold(true);                        //为表格添加数据            $objPHPExcel->setActiveSheetIndex(0)                        ->setCellValue('A1', '调拨单明细')                        ->setCellValue('A2', '调拨单号')                        ->setCellValue('B2', $res['code'])                        ->setCellValue('A3', '调拨类型')                        ->setCellValue('B3', $res['type'])                        ->setCellValue('A4', '单据日期')                        ->setCellValue('B4', date("Y-m-d",$res['bill_date']))                        ->setCellValue('A5', '单据状态')                        ->setCellValue('B5', $res['status']==1?'已审核':'未审核')                        ->setCellValue('A6', '从门店')                         ->setCellValue('B6', get_warehouse_name($res['from_storage']))                        ->setCellValue('A7', '调至门店')                        ->setCellValue('B7',  get_warehouse_name($res['to_storage']))                        ->setCellValue('A8', '信息备注')                        ->setCellValue('B8', $res['remarks'])                        ->setCellValue('A9', '创建人')                        ->setCellValue('B9', $res['creater'])                        ->setCellValue('A10', '创建日期')                        ->setCellValue('B10', date("Y-m-d",$res['create_time']));                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A11', '商品明细');            $filename='调拨单';//文件名                       // 文件中的第一个表格的名            $objPHPExcel->getActiveSheet()->setTitle('调拨单');                        // 设置操作的表是第一个表            $objPHPExcel->setActiveSheetIndex(0);                // 直接在浏览器中输出文件(Excel2007)            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');            header('Content-Disposition: attachment;filename='.$filename.'.xlsx');            header('Cache-Control: max-age=0');            // If you're serving to IE 9, then the following may be needed            header('Cache-Control: max-age=1');                        // If you're serving to IE over SSL, then the following may be needed            header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past            header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified            header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1            header ('Pragma: public'); // HTTP/1.0            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');            $objWriter->save('php://output');            exit;

3. 导入Excel文件

表格格式

网页显示效果图：

实现代码：

 header("Content-type:text/html;charset=utf-8");        //读取Excel        require './ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';        $inputFileName="D:/readExcel.xlsx";        if (!file_exists($inputFileName)) {            exit("please check the file exist or not first ." . EOL);        }        $inputFileType = 'Excel2007';        $objPHPExcel = \PHPExcel_IOFactory::load("D:/readExcel.xlsx");        $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);        echo "<pre>";        //var_dump($sheetData);        echo "<table>";        foreach ($sheetData as $key=>$val){            if($key==0){                echo "<tr><th>",$val['A'],"</th><th>",$val['B'],"</th><th>",$val['C'],"</th><th>",$val['D'],"</th><th>",$val['E'],"</th></tr>";            }            else{                echo "<tr><td>",$val['A'],"</td><td>",$val['B'],"</td><td>",$val['C'],"</td><td>",$val['D'],"</td><td>",$val['E'],"</td></tr>";            }        }        echo "</table>";

这里只是很笼统的介绍，更多的功能参考PHPExcel中文使用文档

0 0