Php spreadsheet to array

PhpSpreadsheet: Работа с excel файлами (запись и чтение данных)

На смену PHPExcel пришла мощная библиотека PhpSpreadsheet от тех же разработчиков.

Создание exсel файла

use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setTitle('Worksheet 1'); $sheet->setCellValue('A1', 'Hello World !'); // Writer можно создать так: $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //$writer = new Xlsx($spreadsheet); $writer->save($path . '/export/worksheet__' . date('Ymd_h:i:s') . '.xlsx'); $spreadsheet->disconnectWorksheets();

Запись данных в exсel с помощью итератора

Более информативный пример со стилями документа, строк, ячеек по условию.

use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Style; try < $path = $path = realpath(__DIR__); // Способ #1. Стили для шапки $headerStyles = [ 'font'=>[ 'color'=>[ 'rgb' => '000' ], 'bold' => true, 'size' => 13 ], 'fill'=>[ 'fillType' => Fill::FILL_SOLID, 'startColor' => [ 'rgb' => Color::COLOR_CYAN ] ], ]; // Способ #2. Стили для данных $redStyle = new Style(false, true); $redStyle->getFill() ->setFillType(Fill::FILL_SOLID) ->getStartColor()->setARGB(Color::COLOR_RED); // Создание документа, листа и "писателя" $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // Название листа $sheet->setTitle('Worksheet 1'); $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // Стили документа по умолчанию $spreadsheet->getDefaultStyle() ->getFont() ->setName('Arial') ->setSize(12); // Дополнительные стили // . // Выравнивание по центру в строке //$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // Установка стилей для шапки $spreadsheet->getActiveSheet()->getStyle('A1:F1')->applyFromArray($headerStyles); // Шапка $headerItems = [ 'First Name', 'Last Name', 'Email', 'Gender', 'Age', 'Class' ]; // Добавление шапки $sheet->fromArray($headerItems); // A1 start // Данные из файла для примера (чаще всего из базы данных) $file = file_get_contents('student-data.json'); $studentData = json_decode($file,true); // Расчёт крайней правой точки листа $extremeCellValue = $sheet ->getCellByColumnAndRow( count($headerItems), count($studentData) + 1 ) ->getCoordinate(); // Форматирование ячеки по условию (если студенту меньше 18 лет) $conditionalStyles = []; $wizardFactory = new Wizard("A1:$extremeCellValue"); $textWizard = $wizardFactory->newRule(Wizard::CELL_VALUE); $textWizard->lessThan(18) ->setStyle($redStyle); $conditionalStyles[] = $textWizard->getConditional(); $spreadsheet->getActiveSheet() ->getStyle($textWizard->getCellRange()) ->setConditionalStyles($conditionalStyles); // Данные вставлять со 2-й строки $row = 2; foreach ($studentData as $student): unset($student['id']); // Исключаем id из данных (если необходимо) $sheet->fromArray($student, null, 'A'.$row); // A2 start $row++; endforeach; // Сохранение файла $writer->save($path . '/export/students_list__' . date('Ymd_h:i:s') . '.xlsx'); > catch (Exception $e) < $error = date('Y/m/d H:i:s') . ': ' . $e->getMessage() . PHP_EOL; error_log($error, 3, $path . '/runtime/app.log'); die($e->getMessage() . PHP_EOL); >

Чтение данных из файла exсel

use PhpOffice\PhpSpreadsheet\IOFactory; $reader = IOFactory::createReader('Xlsx'); $spreadsheet = $reader->load('students_list.xlsx'); // Только чтение данных $reader->setReadDataOnly(true); // Количество листов $sheetsCount = $spreadsheet->getSheetCount(); // Данные в виде массива $data = $spreadsheet->getActiveSheet()->toArray(); foreach ($data as $item): var_dump($item); endforeach;

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How I can get all rows as array? #97

How I can get all rows as array? #97

Comments

In PHPExcel I’m using the next code:

$file = PHPExcel_IOFactory::createReader('Excel2007'); $array = $objReader->load('file.xls');

How can I do the same in PhpSpreadsheet?

PS: I know this project is still unstable, but I would like to use this and not PHPExcel

The text was updated successfully, but these errors were encountered:

Well, you example output a object. I need a Array.

Well, you example output a object. I need a Array. 

Yes, that’s what I told you.

If you want an array, then you need to select which worksheet you want, and the use the toAarray() method, which will give you an array of the data on that worksheet. exactly the same as you needed to do with PHPExcel. And all of this is demonstrated in the examples

Thanks @MarkBaker I found a solution thank you.
Example:

$spreadsheet = PhpSpreadsheet\IOFactory::load( 'myfile.wherever' ); $worksheet = $spreadsheet->getActiveSheet(); $rows = []; foreach ($worksheet->getRowIterator() AS $row) < $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, $cells = []; foreach ($cellIterator as $cell) < $cells[] = $cell->getValue(); > $rows[] = $cells; > 

$worksheet = $spreadsheet->getActiveSheet(); $rows = $worksheet->toArray(); 

But be aware that building an array of cells in PHP memory when you already have the worksheet in PHP memory can use a lot of memory

for anyone dealing with this in the future, the method provided by @olaferlandsen actually is the only solution in certain cases:

1. When you have static values in all of your cells (like a typical CSV) — then the toArray() method works best.
2. However when you have formula-based fields like ‘=HYPERLINK(whatever)’ where you need to parse something in the formula itself — then the iteration method works better.

Thats what exactly i wanted

$worksheet = $spreadsheet->getActiveSheet(); $rows = $worksheet->toArray(); 

is even easier

But be aware that building an array of cells in PHP memory when you already have the worksheet in PHP memory can use a lot of memory

This answer doesn’t keep formula.

But this one #97 (comment) does the job.

Hi, I’m new to this so please forgive me if I ask a silly question. I’m able to import from an xlsx file, but when I put the data into an array, it enters the data as rows instead of columns.
I have 3 arrays, Name, Date and Number
For example:

John | 01/01/2016 | 100
Micheal | 02/01/2016 | 200
Margaret | 03/01/2016 | 300

I end up getting
Name -> John 01/01/2016 100
Date -> Micheal 02/01/2016 200
Number -> Margaret 03/01/2016 300

Instead of:
Name -> John Micheal Margaret
Date -> 01/01/2016 02/01/2016 03/01/2016
Number -> 100 200 300

How can I fix this? What am I doing wrong?

This should be first item in https://phpspreadsheet.readthedocs.io/en/latest/faq/ looking at the amount of upvotes. Also should be first example in documentation.

Well, you example output a object. I need a Array. 

Yes, that’s what I told you.

lol, in no way did you say that.

Источник