PHP PHPExcel_Reader_Excel2007类代码示例

 /**
  * @param $excelFileName
  * @param $excelFileExts
  * @return array
  * @throws \PHPExcel_Exception
  */
 private function _excelParse($excelFileName, $excelFileExts)
 {
     import("Org.Util.PHPExcel");
     import("Org.Util.PHPExcel.Shared.Date");
     $PHPDate = new \PHPExcel_Shared_Date();
     if ($excelFileExts == 'xls') {
         import("Org.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } else {
         if ($excelFileExts == 'xlsx') {
             import("Org.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new \PHPExcel_Reader_Excel2007();
         }
     }
     $PHPExcel = $PHPReader->load($excelFileName);
     // 获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     // 获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     // 获取总行数
     $allRow = $currentSheet->getHighestRow();
     // 循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         // 从哪列开始，A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             // 数据坐标
             $address = $currentColumn . $currentRow;
             $excelData[$currentRow][$currentColumn] = (string) $currentSheet->getCell($address)->getValue();
         }
     }
     return ['path' => $excelFileName, 'data' => $excelData];
 }

 public function reader($filePath = null, $index = 2)
 {
     if ($filePath == null) {
         $filePath = $_FILES['file']['tmp_name'];
     }
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return 'no Excel';
         }
     }
     $PHPExcel = $PHPReader->load($filePath);
     $rows = array();
     $sheet = $PHPExcel->getSheet(0);
     $allColumn = $sheet->getHighestColumn();
     $allRow = $sheet->getHighestRow();
     $allCell = $this->AT[$allColumn];
     for ($row = $index; $row <= $allRow; $row++) {
         $arr = array();
         for ($cell = 0; $cell <= $allCell; $cell++) {
             $val = $sheet->getCellByColumnAndRow($cell, $row)->getValue();
             $arr[$this->A[$cell]] = $val;
         }
         $rows[] = $arr;
     }
     return $rows;
 }

 /**
  * 读取excel信息
  * xls,xlsx,
  * 
  * @param string $fileName         文件绝对路径
  * @param bool $dataArea	       excel区域 
  *
  * @return array() | string
  */
 public static function readExcel($fileName = '', $contentType = false, $dataArea = false)
 {
     ini_set('memory_limit', '300M');
     $ext = strtolower(substr(strrchr($fileName, '.'), 1));
     //2003 excel
     if ($ext == 'xls') {
         $objReader = new PHPExcel_Reader_Excel5();
     }
     //2007 excel
     if ($ext == 'xlsx') {
         $objReader = new PHPExcel_Reader_Excel2007();
     }
     $objPHPExcel = $objReader->load($fileName);
     if ($dataArea == true) {
         $objPHPExcel->setActiveSheetIndex(3);
     }
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     if ($contentType) {
         //返回字符串
         $str = '';
         foreach ($sheetData as $k => $v) {
             foreach ($v as $kk => $vv) {
                 $str .= $vv;
             }
         }
         return $str;
     }
     return $sheetData;
 }

function updateData($filename, $encode = 'utf-8')
{
    $PHPReader = new PHPExcel_Reader_Excel2007();
    if (!$PHPReader->canRead($filename)) {
        $PHPReader = new PHPExcel_Reader_Excel5();
        if (!$PHPReader->canRead($filename)) {
            echo 'no Excel';
            return;
        }
    }
    $PHPExcel = $PHPReader->load($filename);
    $currentSheet = $PHPExcel->getSheet(0);
    $allColumn = $currentSheet->getHighestColumn();
    $allRow = $currentSheet->getHighestRow();
    $excelData = ['name' => $currentSheet->getCellByColumnAndRow(A, 1)->getValue(), 'version' => $currentSheet->getCellByColumnAndRow(A, 2)->getValue()];
    $keys = [];
    for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
        $keys[$currentColumn] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, 5)->getValue();
    }
    for ($currentRow = 6; $currentRow <= $allRow; $currentRow++) {
        $tmp = [];
        for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
            $tmpv = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
            $tmp[$keys[$currentColumn]] = in_array($keys[$currentColumn], ['Id', 'Exp', 'Hp', 'Attack', 'Defense']) ? intval($tmpv) : floatval($tmpv);
        }
        if (!empty($tmp)) {
            $excelData['list'][] = $tmp;
        }
    }
    return $excelData;
}

 public function testSave()
 {
     $objReader = new \PHPExcel_Reader_Excel2007();
     $excel = $objReader->load('tests/metadata/template/simple.xlsx');
     $actual = $this->sut->save($excel, 'test.xlsx');
     $this->assertEquals('tests/metadata/template/test.xlsx', $actual);
     $this->assertFileExists('tests/metadata/template/test.xlsx');
 }

 /**
  * 导入代理商信息
  * @method import
  */
 public function import()
 {
     $file_info = R('Public/UploadExcel');
     //上传excel并返回上传路径
     if (!file_exists($file_info)) {
         //上传失败则输出
         $error_info['status'] = 0;
         $error_info['info'] = $file_info;
         die(json_encode($error_info));
     }
     import('Common.Tools.PHPExcel.PHPExcel');
     $PHPExcel = new \PHPExcel();
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($file_info)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
     }
     if (!$PHPReader->canRead($file_info)) {
         $error_info['status'] = 0;
         $error_info['info'] = '无法读取文件';
         die(json_encode($error_info));
     }
     $PHPExcel = $PHPReader->load($file_info);
     $currentSheet = $PHPExcel->getSheet(0);
     $rowTotal = $currentSheet->getHighestRow();
     //总行数
     $columnTotal = $currentSheet->getHighestColumn();
     //总列数
     $columnTotal = \PHPExcel_Cell::columnIndexFromString($columnTotal);
     //字母列转换为数字列
     $_list = array();
     for ($i = 1; $i <= $rowTotal; $i++) {
         for ($j = 0; $j <= $columnTotal; $j++) {
             $val = $currentSheet->getCellByColumnAndRow($j, $i)->getValue();
             if (!empty($val)) {
                 $_list[$i - 1][] = $val;
             }
         }
     }
     foreach ($_list as $_k => $_v) {
         $list[$_k]['name'] = $_v[0];
         $list[$_k]['mobile'] = $_v[1];
         $list[$_k]['id_card'] = $_v[2];
         $list[$_k]['wechat_number'] = $_v[3];
         $list[$_k]['class'] = $_v[4];
         $list[$_k]['authorize_code'] = $_v[5];
         $list[$_k]['ctime'] = now();
         $list[$_k]['mtime'] = now();
     }
     $result = D('Agent')->addAll($list);
     if ($result) {
         $error_info['status'] = 1;
         $error_info['info'] = '导入成功';
     } else {
         $error_info['status'] = 0;
         $error_info['info'] = '导入数据失败';
     }
     die(json_encode($error_info));
 }

 public function getWorkbook($isExcelFile = false)
 {
     $data = $this->getReportData();
     $xls_reader = new PHPExcel_Reader_Excel2007();
     $workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report6.xlsx');
     $sheet = $workbook->getActiveSheet();
     $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(2, 4);
     // В шаблоне пока не работает
     //    $sheet->getSheetView()->setZoomScale(120);
     $guide_vuz = $this->getEstablishments('GUIDE_STRUCTURE', 'SORTORDER');
     $guide_eduform = $this->getGuideItems('T_EDUFORM');
     $guide_speciality = $this->getSpecialities(false, false, 'CT.SORTORDER', 'SORTORDER');
     $eduFormStr = mb_strtolower($guide_eduform[$this->params['eduform']], 'utf8');
     $endDate = new DateTime($this->getAddValueDate($this->params['period'], '_ADD_ENDDATE'));
     $endDateStr = $endDate->format('d.m.Y');
     $sheet->setCellValue('A1', sprintf($sheet->getCell('A1')->getValue(), $endDateStr, $eduFormStr));
     $lastEstNum = -1;
     $vuznum = 1;
     $current_row = 5;
     $sheet->insertNewRowBefore($current_row + 1, count($data));
     foreach ($data as $datarow) {
         $curEstNum = $datarow['ESTABLISHMENT_NUM'];
         if ($lastEstNum != $curEstNum) {
             $lastEstNum = $curEstNum;
             if ($vuznum > 1) {
                 // Разделительная линия между вузами
                 $sheet->insertNewRowBefore($current_row + 1, 1);
                 $sheet->getRowDimension($current_row)->setRowHeight(0.75);
                 $current_row++;
             }
             $sheet->insertNewRowBefore($current_row + 1, 1);
             $sheet->setCellValueByColumnAndRow(0, $current_row, $vuznum);
             $sheet->setCellValueByColumnAndRow(1, $current_row, $guide_vuz[$curEstNum]);
             $sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
             $sheet->getStyle("B{$current_row}:J{$current_row}")->getFont()->setBold(true);
             $sheet->getStyle("A{$current_row}:J{$current_row}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB($this->colorLevel1);
             $current_row++;
             $vuznum++;
         }
         //      $sheet->getRowDimension($current_row)->setOutlineLevel(1);//->setVisible(false);
         if (!$datarow['SPECIALITY_NUM']) {
             $sheet->setCellValueByColumnAndRow(1, $current_row, 'Вакантних місць немає');
             $sheet->mergeCellsByColumnAndRow(1, $current_row, 9, $current_row);
             $sheet->getStyle("B{$current_row}:J{$current_row}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
             $current_row++;
             continue;
         }
         $sheet->setCellValueByColumnAndRow(1, $current_row, $guide_speciality[$datarow['SPECIALITY_NUM']]);
         $sheet->setCellValueByColumnAndRow(2, $current_row, "=SUM(D{$current_row}:J{$current_row})");
         for ($col = 1; $col <= 7; $col++) {
             $sheet->setCellValueByColumnAndRow($col + 2, $current_row, $datarow["C{$col}"]);
         }
         $current_row++;
     }
     $sheet->removeRow($current_row, 1);
     return $workbook;
 }

 /**
  * Reads the file into a PHPExcel object.
  * 
  * @param string $filename The file to parse.
  * 
  * @return \PHPExcel Returns the PHPExcel object for the file.
  * 
  * @throws ParseException when the reader encounters an error.
  */
 private static function readFile(string $filename) : \PHPExcel
 {
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $xlsx = $reader->load($filename);
     } catch (\Exception $e) {
         throw new ParseException($e->getMessage());
     }
     return $xlsx;
 }

 public static function readExcel($filePath)
 {
     self::init();
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return null;
         }
     }
     return $PHPReader->load($filePath);
 }

 /**
  * 
  *函数名：		exportToExcelWithHeader
  *输入：		模板excel的路径 $filePath，例如 'C:\\123.xls'
  *输入：		二维数组  要导出的内容 $dataList
  * 			array(
  *				array(key1=>$value1,key2=>$value2,...),
  *				array(key1=>$value1,key2=>$value2,...),...
  *			)
  *输入：		一维数组  生成excel的头部信息 $headerList 
  *			array(数据库前台字段key1，数据库前台字段key2,...)
  *输入：		string $OutputFileName 输出文件名(建议输出文件名带时间戳)
  *输入：		$outputType 默认0输出到浏览器，输入其他保存到文件服务器并成功返回相对地址，失败返回NULL
  *创建人：          石昌民
  *创建时间：       2015-9-30
  */
 public function exportToExcelWithHeader($filePath, $dataList, $headerList, $OutputFileName, $outputType = 0)
 {
     require './ThinkPHP/Library/Org/PHPExcel/PHPExcel.php';
     //实例化Excel读取类
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     $fileWebService = new FileWebController($this->userID);
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new \PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return false;
         }
     }
     //读取Excel
     $PHPExcel = $PHPReader->load($filePath);
     /**读取excel文件中的第一个工作表*/
     $currentSheet = $PHPExcel->getSheet(0);
     /**取得一共有多少行*/
     $allRow = $currentSheet->getHighestRow();
     $allRow++;
     //实例化Excel写入类
     $PHPWriter = new \PHPExcel_Writer_Excel5($PHPExcel);
     //遍历数据列表中的内容,从第allRow行开始
     $i = $allRow;
     foreach ($dataList as $key => $value) {
         $position = 'A';
         foreach ($headerList as $headerKey => $headerValue) {
             $currentSheet->setCellValueExplicit("{$position}" . $i, $dataList[$key][$headerValue], \PHPExcel_Cell_DataType::TYPE_STRING);
             //headerKey为数据库前台字段
             $position++;
         }
         $i++;
     }
     ob_end_clean();
     //清除缓冲区,避免乱码
     //决定是导出到浏览器还是文件服务器
     if ($outputType !== 0) {
         $path = Constant::WEBSERVER_EXCEL_SAVEPATH . "{$OutputFileName}.xls";
         $PHPWriter->save($path);
         // 					dump($path);
         // 					dump(file_exists($path));
         $fileURL = $fileWebService->uploadFile($path, "{$OutputFileName}.xls");
         if ($fileURL == false) {
             $fileURL = null;
         }
         return $fileURL;
         return false;
     } else {
         header('Content-Type: application/vnd.ms-excel');
         header("Content-Disposition: attachment;filename={$OutputFileName}.xls");
         header('Cache-Control: max-age=0');
         $PHPWriter->save('php://output');
     }
 }

 /**
 * 
 * @param string $filePath
 * @return
 */
 private function readExcel($filePath)
 {
     $filePath = $this->dir() . $filePath;
     if (!is_file($filePath)) {
         return FALSE;
     }
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     if ($PHPReader->canRead($filePath)) {
         return $PHPReader->load($filePath);
     }
     return FALSE;
 }

 public function readExcel($inputFileName)
 {
     echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using PHPExcel_Reader_Excel5<br />';
     //        $objReader = new PHPExcel_Reader_Excel5();
     $objReader = new PHPExcel_Reader_Excel2007();
     //	$objReader = new PHPExcel_Reader_Excel2003XML();
     //	$objReader = new PHPExcel_Reader_OOCalc();
     //	$objReader = new PHPExcel_Reader_SYLK();
     //	$objReader = new PHPExcel_Reader_Gnumeric();
     //	$objReader = new PHPExcel_Reader_CSV();
     $objPHPExcel = $objReader->load($inputFileName);
     //        echo '<hr />';
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
     return $sheetData;
 }

 public function goods_import($filename, $exts = 'xls')
 {
     //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
     require_once APP_PATH . 'PHPExcel/PHPExcel.php';
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel5.php';
     // 用于其他低版本xls
     require_once APP_PATH . 'PHPExcel/PHPExcel/Reader/Excel2007.php';
     // 用于 excel-2007 格式
     //创建PHPExcel对象，注意，不能少了\
     $PHPExcel = new PHPExcel();
     //如果excel文件后缀名为.xls，导入这个类
     if ($exts == 'xls') {
         $PHPReader = new PHPExcel_Reader_Excel5();
     } elseif ($exts == 'xlsx') {
         $PHPReader = new PHPExcel_Reader_Excel2007();
     } else {
         exit('错误的格式');
     }
     //载入文件
     $PHPExcel = $PHPReader->load($filename);
     //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     //获取总列数
     $allColumn = $currentSheet->getHighestColumn();
     //获取总行数
     $allRow = $currentSheet->getHighestRow();
     //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         //从哪列开始，A表示第一列
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             //数据坐标
             $address = $currentColumn . $currentRow;
             //读取到的数据，保存到数组$arr中
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     foreach ($data as $info) {
         $order_id = $info['A'];
         $name = $info['G'];
         $num = $info['H'];
         if (!empty($order_id) && !empty($name) && !empty($num)) {
             $data['id'] = $order_id;
             $data['order_info'] = json_encode(array('num' => $num, 'name' => $name));
             $result = M("Order")->save($data);
             $result = R("Api/Api/publish", array($order_id));
         }
     }
 }

 /**
  * @dataProvider providerDump
  */
 public function testDump(Config $config, $withDate, $generatedValues, $expectedFile, $expected)
 {
     $dumper = new ExcelDumper();
     $dumper->initialize($config, self::$cacheDir, $withDate);
     foreach ($generatedValues as $row) {
         $dumper->dumpRow($row);
     }
     $filename = $dumper->finalize();
     $this->assertFileExists($filename);
     $ext = 'xlsx';
     $this->assertRegExp('/' . basename($expectedFile, '.' . $ext) . '_\\d{4}-\\d{2}-\\d{2}_\\d{2}-\\d{2}-\\d{2}.' . $ext . '/', basename($filename));
     $reader = new \PHPExcel_Reader_Excel2007();
     $excel = $reader->load(self::$fixtures . '/' . $expectedFile);
     $data = $excel->getActiveSheet()->toArray();
     $this->assertEquals($expected, $data);
 }

 /**
  * Loads source data from local file into memory.
  */
 private function load_source_data_from_file()
 {
     require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
     // NOTE: In development it takes 24 Seconds to load and uses 318 MB of memory.
     $start = microtime(true);
     debug('Loading source file.');
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $this->source_loaded = $reader->load($this->source_file_path);
         unset($reader);
     } catch (\Exception $e) {
         throw new \Exception('Error loading source data from ' . "{$this->source_file_path}: {$e->getMessage()}");
     }
     $this->profile($start);
 }