excel操作类

最新推荐文章于 2023-05-09 16:55:34 发布

帝都-小帅

最新推荐文章于 2023-05-09 16:55:34 发布

阅读量581

点赞数

分类专栏：抓取文章标签： PHPEXCEL

本文链接：https://blog.csdn.net/cakexuexi/article/details/49095485

版权

抓取专栏收录该内容

7 篇文章 0 订阅

订阅专栏

<?php
/**
* excel操作
*/
//header("Content-type: text/html; charset=utf-8");
$excel_root_dir = dirname(__FILE__);
include_once $excel_root_dir.'/../PHPExcel.php';
class ExcelChexingDealerSimply{

   /**
     * 生成excel
     * @param $list
     */
   public function createExcel($list,$excelName='brand') {
        // 创建新的PHPExcel对象
        $objPHPExcel = new PHPExcel();
        // 设置属性
        $objPHPExcel->getProperties()->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->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");

       $letter = range('A','Z');
       // 字体宽度
       foreach($letter as $key => $val){
           $objPHPExcel->getActiveSheet()->getColumnDimension($val)->setWidth(30);
       }

        // 设置行高度
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('7')->setRowHeight(20);

        // 设置水平居中
       foreach($letter as $key => $val){
           $objPHPExcel->getActiveSheet()->getStyle($val)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
       }

        // 表头
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '车型ID')
               ->setCellValue('B1', '品牌')
               ->setCellValue('C1', '车系')
               ->setCellValue('D1', '车型')
               ->setCellValue('E1', '经销商');
        // 内容
       $i = 2;

       foreach($list as $k => $v ){
           $objPHPExcel->getActiveSheet(0)->setCellValue('A'.$i, $v['chexing_id']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('B'.$i, $v['pinpai']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('C'.$i, $v['chexi']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('D'.$i, $v['chexing']);
           $objPHPExcel->getActiveSheet(0)->setCellValue('E'.$i, $v['yiche_dealer']);
           ++$i;
       }

        // 重命名工作表
        $objPHPExcel->getActiveSheet()->setTitle('list');
        // 第一张工作表组活跃表索引,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);


        // 输出
       /*
                ob_end_clean();//清除缓冲区,避免乱码
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $excelName . '.xls"');
                header('Cache-Control: max-age=0');
                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //2007excel 后缀xls
                $objWriter->save('php://output');
       */

       $filePath = $excelName.'.xls';
       //echo $filePath;die();
       $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //2007excel 后缀xls
       $objWriter->save($filePath);

    }

   /**
     * 读取excel
   * @param $filename excel文件地址
   * $encode 编码格式
     * return $list
     */
   public function readByExcel($filename){
       $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
       $objPHPExcel = $objReader->load($filename);
       $sheet = $objPHPExcel->getSheet(0);
       $highestRow = $sheet->getHighestRow();           //取得总行数
       $highestColumn = $sheet->getHighestColumn(); //取得总列数

       $objWorksheet = $objPHPExcel->getActiveSheet();
       $highestRow = $objWorksheet->getHighestRow();
       //echo 'highestRow='.$highestRow;
       //echo "<br>";
       $highestColumn = $objWorksheet->getHighestColumn();
       $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
       //echo 'highestColumnIndex='.$highestColumnIndex;
       //echo "<br>";
       $headtitle=array();
       //对应关系数组
       $mapArr=array();
       for ($row = 1;$row <= $highestRow;$row++)
       {
           //不要标题
           if($row < 2){
               continue;
           }
           //循环获得数据
           $strs=array();
           //注意highestColumnIndex的列数索引从0开始
           for ($col = 0;$col < $highestColumnIndex;$col++)
           {
               $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
           }
           $mapArr[$strs[1]] = $strs[0];
           //ksort($mapArr);
       }
       return $mapArr;
   }

}//class end