将数组数据转换为Excel

Question

I am converting array data into Excel using PHPExcel. When Excel file is created, data is stored row wise but I want to store data column wise. My array is given below:

array (size=3)
  0 => string '8801755568952' (length=13)
  1 => string '8801755556987' (length=13)
  2 => string '8801755587985' (length=13)

My array to Excel conversion code is:

$objPHPExcel->getActiveSheet()->fromArray($csv_data, null, 'A1')  
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    return $objWriter->save($xls_output_path);

My output is:

8801755568952 8801755556987 8801755587985

My desired output is:

8801755568952 
8801755556987 
8801755587985

Answer 1

If you pass a simple 1-dimensional array to the fromArray() method, then PHPExcel assumes that it is a single row, which is what you're seeing.

Either convert your array to a 2-dimensional array

As PHPExcel will treat these values as numeric, and they're larger than the limit for a 32-bit signed integer in PHP (and you're probably running 32-bit PHP), they'll be cast to float, and you'll potentially lose accuracy, so I wouldn't use this approach myself.

If you do want to convert the array to a 2-dimensional array, then use:

$csv_data = call_user_func_array(
    'array_map',
    array_merge(array(NULL), [$csv_data])
);

Or just loop over it writing to each row individually

$row = 1;
foreach($csv_data as $cellData) {
    $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$row++, $cellData);
}

The use of setCellValueExplicit() will force the values to be treated as a string, so nothing will be cast to float, so no loss of precision