PHP PHPExcel_Reader_Excel2007::load方法代码示例

 /**
  * 获取数据
  * @param int $page 第几页数据
  */
 public function getData($page)
 {
     $this->_getReadFileObj();
     $phpExcel = $this->_instance->load($this->_filePath);
     $currentSheet = $phpExcel->getSheet($page);
     return $currentSheet->toArray();
 }

 /**
  * 读取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;
 }

 /**
  * @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 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');
 }

 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;
 }

 protected function import_data($parentFile, $childFile, $parentExts = 'xls', $childExts = 'xls')
 {
     //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
     import("Org.Util.PHPExcel");
     //创建PHPExcel对象，注意，不能少了\
     $PHPExcel = new \PHPExcel();
     //如果excel文件后缀名为.xls，导入这个Excel5类否则导入Excel2007
     if ($parentExts == 'xls' && $childExts == 'xls') {
         import("Org.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new \PHPExcel_Reader_Excel5();
     } else {
         if ($parentExts == 'xlsx' && $childExts == 'xlsx') {
             import("Org.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new \PHPExcel_Reader_Excel2007();
         }
     }
     //载入上传的文件
     $PHPExcel = $PHPReader->load($parentFile);
     $ParentExcel = $PHPReader->load($parentFile);
     $ChildExcel = $PHPReader->load($childFile);
     //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
     $currentSheet = $PHPExcel->getSheet(0);
     $parentCurrentSheet = $ParentExcel->getSheet(0);
     $childCurrentSheet = $ChildExcel->getSheet(0);
     //获取工作表总列数
     $allColumn = $currentSheet->getHighestColumn();
     $parentAllColumn = $parentCurrentSheet->getHighestColumn();
     $childAllColumn = $childCurrentSheet->getHighestColumn();
     //获取工作表总行数
     $allRow = $currentSheet->getHighestRow();
     $parentAllRow = $parentCurrentSheet->getHighestRow();
     $childAllRow = $childCurrentSheet->getHighestRow();
     //循环获取表中的数据，$j表示当前行，从哪行开始读取数据，索引值从0开始
     for ($j = 2; $j <= $allRow; $j++) {
         $str = "";
         //从A列读取数据
         for ($k = 'A'; $k <= $allColumn; $k++) {
             $str .= $PHPExcel->getActiveSheet()->getCell("{$k}{$j}")->getValue() . ',';
         }
         //获取的字符串截取成数组
         $strs = explode(",", $str);
         $this->install_data($strs);
     }
 }

 /**
  * 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;
 }

 /**
 * 
 * @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);
 }

 private function read_excel($filename, $extension = 'xls')
 {
     //导入PHPExcel类库
     import('ORG.Util.PHPExcel');
     $PHPExcel = new PHPExcel();
     //根据扩展名判断所用的解析方式
     if ($extension == 'xls') {
         import("ORG.Util.PHPExcel.Reader.Excel5");
         $PHPReader = new PHPExcel_Reader_Excel5();
     } else {
         if ($extension == 'xlsx') {
             import("ORG.Util.PHPExcel.Reader.Excel2007");
             $PHPReader = new PHPExcel_Reader_Excel2007();
         }
     }
     //读取excel文件
     $PHPExcel = $PHPReader->load($filename);
     //获取Sheet0中的数据
     $currentSheet = $PHPExcel->getSheet(0);
     //获取行数、列数
     $allColumn = $currentSheet->getHighestColumn();
     $allRow = $currentSheet->getHighestRow();
     //循环遍历excel表格，重点是getCell和getValue方法
     for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             $address = $currentColumn . $currentRow;
             $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
         }
     }
     //循环所得数据插入数据库，至于从2开始嘛，执行var_dump($data)你就懂了
     // echo "<pre>";
     // var_dump($data);die;
     for ($i = 2; $i < count($data); $i++) {
         $school = array('school' => $data[$i]['B']);
         $find = M('school')->where($school)->find();
         if ($find) {
             $school_id = $find['id'];
         } else {
             $school_id = M('school')->data($school)->add();
         }
         $joiner = array('username' => $data[$i]['C'], 'school_id' => $school_id);
         M('joiner')->data($joiner)->add();
     }
     //然而我并没有想出该怎么判断成功和失败
     $this->success('Excel导入成功');
 }

 public function anyExcel()
 {
     $pFile = iconv('utf-8', 'gbk', 'C:/Users/LJ/Desktop/北邮/灾难恢复方案评估表-关.xlsx');
     $reader = new \PHPExcel_Reader_Excel2007();
     $workbook = $reader->load($pFile);
     $sheet = $workbook->getSheet(0);
     $highestRow = $sheet->getHighestRow();
     $highestColumn = $sheet->getHighestColumn();
     $table = [];
     for ($row = 1; $row < $highestRow; $row++) {
         $table[] = [];
         for ($column = 'A'; $column < $highestColumn; $column++) {
             $coordinate = $column . $row;
             $table[$row - 1][] = $sheet->getCell($coordinate)->getValue();
         }
     }
     return view('bupt.user.excel', ['table' => $table]);
 }

 public function getWorkbook()
 {
     $data = $this->getReportData();
     $data = $this->formtable($data);
     $xls_reader = new PHPExcel_Reader_Excel2007();
     $workbook = $xls_reader->load(APPLICATION_PATH . 'modules/stat/templates/report1.xlsx');
     $workbook->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $this->getGuideNodeTitle($this->params['establishment']));
     if (count($data) > 2) {
         $workbook->getActiveSheet()->insertNewRowBefore(5, count($data) - 2);
     }
     foreach ($data as $rownum => $row) {
         $workbook->getActiveSheet()->setCellValueByColumnAndRow(0, $rownum + 4, $row['title']);
         foreach (array('CS', 'CS1', 'CS2', 'CS3', 'CS4', 'CS5', 'CS6', 'CS7', 'CIN', 'CIN1', 'CIN2', 'CIN3', 'CIN4', 'CIN5', 'CIN6', 'CIN7', 'COUT', 'COUT1', 'COUT2', 'COUT3', 'COUT4', 'COUT5', 'COUT6', 'COUT7') as $colnum => $column) {
             $workbook->getActiveSheet()->setCellValueByColumnAndRow($colnum + 1, $rownum + 4, array_key_exists($column, $row) ? $row[$column] : 0);
         }
     }
     return $workbook;
 }