28
2015
PHPExcel – export + import pliku XLS
Przedstawię rozwiązanie problemu rozpoznawania oraz oznaczania nazw kolumn przy pomocy atrybutu rangeName. Przy okazji opiszę pewne haczyki, które warto znać przy zabawie z dokumentami Excela używając biblioteki PHPExcel.
Wygląd dokumentu po eksporcie
Po atrybucie range name (tutaj products_price) będziemy mogli poprawnie rozpoznać kolumnę pomimo zmiany wartości komórki z pierwszego wiersza (Cena brutto).
Utworzenie obiektu PHPExcel
Wrzucamy na serwer klasy PHPExcel i ładujemy je. Dodatkowo tworzymy instancję obiektu wraz z wyborem aktywnego arkusza, na którym będziemy pracować.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
require('TWOJA_SCIEZKA/phpexcel/PHPExcel.php'); $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties() ->setCreator("XXX") ->setLastModifiedBy("XXX") ->setTitle("XXX") ->setSubject("XXX") ->setDescription("XXX"); $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Lista produktów'); |
Oznaczenie kolumn i eksport do pliku
Sam export do pliku jest prosty, natomiast zaplanowanie kodu do poprawnego oznaczenia kolumn wymaga drobnego przemyślenia.
Powinno to działać tak, aby użytkownik mógł zmienić nazwę tytułową kolumny bez utraty informacji czego dana kolumna dotyczy. Będzie nam to potrzebne przy imporcie, abyśmy nadal wiedzieli za co dana kolumna jest odpowiedzialna.
Użyjemy do tego atrybutu rangeName.
Tworzymy tablicę z literami, jako odpowiednik Excela, np:
|
1 |
$letterArray = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'); |
Następnie należy dołożyć mapowanie pól produktów przesłanych z bazy do czytelnych dla użytkownika.
|
1 2 3 4 |
$columnName = array( 'products_id' => 'ID produktu', 'products_price' => 'Cena produktu' ); |
Przesyłamy POSTem tablicę kolumn tabeli z bazy danych.
|
1 2 3 4 5 |
$productsTableColumns = Array ( [products_id] => products_id [products_price] => products_price ) |
Nazywamy odpowiednio kolumny (komórki pierwszego wiesza).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
$columnLetter = array(); $i = 0; foreach ( $productsTableColumns as $k => $v ) { switch($k) { case 'products_id': $width = 7; break; case 'products_price': $width = 7; break; default: $width = 100; break; } $columnLetter[$k] = $letterArray[$i]; $objPHPExcel->getActiveSheet()->setCellValue($letterArray[$i].'1', $columnName[$v]); $objPHPExcel->addNamedRange(new PHPExcel_NamedRange($k, $objPHPExcel->getActiveSheet(), $letterArray[$i].'1')); $objPHPExcel->getActiveSheet()->getStyle($letterArray[$i].'1')->getFont()->setBold(true)->setSize(16); $objPHPExcel->getActiveSheet()->getColumnDimension($letterArray[$i])->setWidth($width); $i++; } |
Wypełniamy pozostałe komórki wartościami products_id oraz products_price.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$i = 2; while ( $f = mysql_fetch_array($selectTabelaProducts_id_price) ) { foreach ( $f as $k => $v ) { if ( $columnLetter[$k] != '' ) { $objPHPExcel->getActiveSheet()->setCellValue($columnLetter[$k].$i, $v); } } $i++; } |
Zapis dokumentu do pliku.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// Redirect output to a client’s web browser (Excel2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="NAZWA_PLIKU_' . date('Y-m-d_H\hi\ms\s') . '.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; |
Import danych z pliku Excel XLS/XLSX
Pobieramy kolejność kolumn wg. rangeName.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$objPHPExcel = PHPExcel_IOFactory::load($_FILES['fileExcel']['tmp_name']); // start pobieranie w kolejności nazw kolumn foreach ( $objPHPExcel->getNamedRanges() as $v ) { $rangeLetter = preg_replace('#[^A-Z]#', '', $v->getRange()); $importProductsParsedColumns[(array_search($rangeLetter, $letterArray)+1)] = $v->getName(); } ksort($importProductsParsedColumns); // koniec pobieranie w kolejności nazw kolumn $importProductsConfirm = $objPHPExcel->getActiveSheet()->getRowIterator(); |
Tworzenie w HTML tabeli wynikowej po przeparsowaniu pliku. Nazwy kolumn.
|
1 2 3 4 5 6 7 |
foreach ( $importProductsParsedColumns as $column ) { echo ' <th>' . $columnName[$column] . '</th> '; } |
Generowanie pozostałych komórek tabeli HTML z wartościami produktów.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
$importProductsParsedRows = array(); // tworzenie całej struktury danych $wiersz[$kolumna], można po serializacji przesłać dalej POSTem $i = 0; foreach ( $importProductsConfirm as $k => $row ) { $importProductsParsedRows[$i] = array(); if ( $k > 1 ) { echo ' <tr> <td>' . ($i+1) . '</td> '; $j = 1; $columnNumber = 1; $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); foreach ( $cellIterator as $cell ) { $cell = trim($cell->getCalculatedValue()); echo '<td>' . $cell . ' </td>'; $importProductsParsedRows[$i][$importProductsParsedColumns[$j]] = $cell; $j++; } $i++; echo ' </tr> '; } } print_r($importProductsParsedRows); // wyświetla poprawność przeparsowanych danych, dla debugowania |
Dodatkowe informacje
- Opis klasy NamedRange.
- Pełna dokumentacja phpexcel.codeplex.com.
- …i jeszcze więcej docs’ów :) github.com/PHPOffice/PHPExcel/wiki/User-documentation
- Wiem, że brakuje pełnych komentarzy do kodu, ale dla chcącego nic trudnego. Chodziło tu o opisanie idei jak można poradzić sobie oznaczeniem kolumn wraz ich poprawnym importem.
Autor wpisu: Arkadiusz Krakiewicz
Programista i właściciel sklepu jednocześnie, dzięki temu rozumie potrzeby osób prowadzących sprzedaż w internecie. Od 2006 związany z eCommerce. Zaczynał jako freelancer tworząc sklepy oraz strony www. Aktualnie zajmuje się: programowanie w PHP, Symfony 2, Android, PrestaShop, osCommerce, OpenCart, WordPress.
1 komentarz + Dodaj komentarz
Dodaj komentarz
O mnie
Arkadiusz Krakiewicz
Programista aplikacji webowych dla branży eCommerce. Jednocześnie jako właściciel sklepu zna od "podszewki" potrzeby osób prowadzących sprzedaż w internecie.
W swoich rozwiązaniach stawia na funkcjonalność i wydajność aplikacji pod kątem obsługi klienta.
Najnowsze wpisy
- Drzewo kategorii – budowa struktury od dołu do góry
- PHPExcel – export + import pliku XLS
- WordPress + Polylang – Tworzenie klasy CSS z ID strony/wpisu w wybranym języku
- Mailing – Poprawna konstrukcja szablonu maila HTML/CSS i prawidłowy wygląd w programie pocztowym
- Szukasz programisty PHP dla e-commerce?
Prowadzisz wartościowego bloga z którego dużo się nauczyłam.
Czekam na kolejne, wartościowe wpisy!