Get Cell Value with column name in Php Excel

问题:

This is my excel sheet it has got lots of columns but i'm breaking down for ease in understanding the question

I'm reading Excel Sheet using PHP Excel and using rangeToArray() which give me all row from excel but i want the output as

Column as Key:Cell Value as Value

Currently I'm get output as

Col Index :Cell Value

So my Question is which is that function in Php Excel which return array with Column name and it Cell value?

try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file"'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();
for ($row = 2; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL,
                                            TRUE,
                                            FALSE);
    printArr($rowData);
    printArr("-------");

}

I get output as

Array
(
    [0] => Array
        (
            [0] => 27745186
            [1] => 42058
            [2] => DELL INTERNATIONAL SERVICES INDIA PVT LTD
            ...
            ...
         )
    [1] => Array
        (
            [0] => 27745186
            [1] => 42058
            [2] => DELL INTERNATIONAL SERVICES INDIA PVT LTD
            ...
            ...
         )
)

Desire Output

Array
(
    [0] => Array
        (
            [Invoice_no] => 27745186
            [Invoice Date] => 42058
            [Description] => DELL INTERNATIONAL SERVICES INDIA PVT LTD
            ...
            ...
         )
    [1] => Array
        (
            [Invoice_no] => 27745186
            [Invoice Date] => 42058
            [Description] => DELL INTERNATIONAL SERVICES INDIA PVT LTD
            ...
            ...
         )
)

回答1:

There's nothing magic in PHPExcel, but it's pretty easy to do in standard PHP

Retrieve the headings/keys that you want from the first row

$headings = $sheet->rangeToArray('A1:' . $highestColumn . 1,
                                            NULL,
                                            TRUE,
                                            FALSE);

Then reset the standard numeric keys to be the headings values inside your reading loop for each data row

for ($row = 2; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL,
                                            TRUE,
                                            FALSE);
    $rowData[0] = array_combine($headings[0], $rowData[0]);
}

回答2:

foreach ($cell_collection as $cell) 
 {
 $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
 $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
 $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
 if ($row == 1) 
 {
    $header[$row][$column] = $data_value;
 } 
 else 
 {
    $arr_data[$row][$column] = $data_value;
 }
 }

回答3:

I create this function for don't to write the title of reference like A1, A2, B1, B2. And works.

public function createExcel($titulos, $datos, $tituloLibro = 'The title')
{
    $phpExcelObject = //instance it;
    $phpExcelObject->getProperties()->setCreator("Your name")
        ->setLastModifiedBy("yourweb.com")
        ->setTitle($tituloLibro)
        ->setSubject($tituloLibro)
        ->setDescription($tituloLibro)
        ->setKeywords("")
        ->setCategory($tituloLibro);
    $i = 0;
    $phpExcelObject->setActiveSheetIndex(0);
    foreach ($titulos as $titulo)
    {
        $phpExcelObject->getActiveSheet()->getCellByColumnAndRow($i,1)->setValue($titulo);
        $i++;
    }
    $j = 2;
    foreach ($datos as $filas)
    {
        $i = 0;
        foreach ($filas as $fila)
        {
            $phpExcelObject->getActiveSheet()->getCellByColumnAndRow($i,$j)->setValue($fila);
            $i++;
        }
        $j++;
    }
    // your logic

}