使用PHPExcel阅读包含合并单元格的Excel工作表 [英] Read excel sheet containing merged cells using PHPExcel

问题描述

我想完整地阅读Excel工作表，并使用AJAX将每一行发送到另一页进行处理.因此，我使用以下代码将excel工作表数据转换为JSON数组(库中提供的参考PHPExcel示例):

I want to read an excel sheet completely and using AJAX send each row to another page for processing. So I have used the following code for converting the excel sheet data into JSON array(Reference PHPExcel example provided in Library):

<?php
error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Asia/Kolkata');
set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel-1.8/Classes/');
require_once 'PHPExcel/IOFactory.php';

$inputFileType = PHPExcel_IOFactory::identify($fileLocation);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly("SHEETNAME");
$objPHPExcel = $objReader->load($fileLocation);

$data = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
?>

此处 $ filelocation 是要读取的上载文件的位置，以使用AJAX将行分别发送到另一页.我在javascript中使用 $ data 作为

Here $filelocation is the location of the uploaded file which is to be read for sending the rows individually using AJAX to another page. I am using $data in javascript as

DataToBeUploaded=<?php echo json_encode($data);?>;

但是excel工作表包含一些合并的单元格，因此PHPExcel无法读取这些合并的单元格中的值.因此，这些单元格中的值被读取为NULL.

But the excel sheet contains some merged cells so PHPExcel is not able to read the values in these merged cells. Hence values in these cells are read as NULL.

有没有一种方法可以对所有后续单元格使用合并单元格的左上单元格值?(实际上，在我的情况下，单元仅在垂直方向合并)

Is there a way where I can use the merged cells' upper left cell value for all of the subsequent cells? (Actually in my case cells are merged vertically only)

例如.我有(假设行从1编号，列从A编号)

Eg. I have (Assume rows are numbered from 1 and columns from A)

PHPExcel在这里的读法是:

Here PHPExcel reads this as:

data[1][A]='abc'
$data[1][B]='123'

$data[2][A]=''
$data[2][B]='456'

$data[3][A]=''
$data[3][B]='789'

我希望代码片段生成以下值:

I want the snippet to result in these values:

data[1][A]='abc'
$data[1][B]='123'

$data[2][A]='abc'
$data[2][B]='456'

$data[3][A]='abc'
$data[3][B]='789'

推荐答案

引用 https://github.com/PHPOffice/PHPExcel/issues/643

我写了以下代码段:

$referenceRow=array();
for ( $row = 2; $row <= $noOfBooks; $row++ ){
     for ( $col = 0; $col < 7; $col++ ){
         if (!$objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isInMergeRange() || $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isMergeRangeValueCell()) {
             // Cell is not merged cell
             $data[$row][$col] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->getCalculatedValue();

             $referenceRow[$col]=$data[$row][$col];  
             //This will store the value of cell in $referenceRow so that if the next row is merged then it will use this value for the attribute
          } else {
             // Cell is part of a merge-range
             $data[$row][$col]=$referenceRow[$col];  
             //The value stored for this column in $referenceRow in one of the previous iterations is the value of the merged cell
          }

      }
 }

这将完全按照要求提供结果

This will give the result exactly as required

这篇关于使用PHPExcel阅读包含合并单元格的Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！