Работа с Excel чрез PHP

PHPExcel е библиотека за създаване и четене на данни от файлове във формат OpenXML (който се използва в MS Excel 2007). Може да се използва за четене от файлове, запис във файлове, форматиране на съдържание, работа с формули и т.н. PHPExcel изисква PHP 5.2 или по-нова версия с инсталирани библиотеки Zip, XML и GD2.

Инсталиране на PHPExcel

Първата стъпка е да изтеглите библиотеката. За да направите това, отидете на официалния уебсайт на библиотеката и изтеглете архива PHPExcel-1.7.8.zip. След разопаковането ще получим няколко файла и папки:

  • класове
  • Документация
  • Тестове
  • changelog.txt
  • install.txt
  • license.txt

Файловете са различни описания за предишни версии, лицензионно споразумение и много кратко ръководство за инсталиране. Освен това в папката Classes самата библиотека PHPExcel се съдържа директно - тази папка трябва да бъде копирана в корена на нашия скрипт.

Папката Документация съдържа документация за библиотеката на английски език. Папката Тестове съдържа примери за това как да използвате библиотеката.

Създайте Excel файл

И така, нека създадем файлmakeexcel.php и да започнем да работим с него. Първо, трябва да включим основния файл на библиотеката PHPExcel.php (който се намира в папката Classes) и да създадем обект на клас PHPExcel:

Настройки на работния лист на Excel

Документът на Excel е съставен от работни книги, а всяка работна книга от своя страна е съставена от листове. След това листът се състои от набор от клетки, достъпни чрез координати. Тоест имаме колони, които имат имена на букви (A, B, C и т.н.) и има редове, които са номерирани. Това означава, че за да получите достъп до първата клетка, трябва да посочите код A1. По същия начин ще използваме библиотеката за достъпкъм всяка клетка.

И така, първата стъпка е да изберете активния лист, на който ще показваме данни и да получим обекта на този лист:

С помощта на метода setActiveSheetIndex(0) указваме индекса (номера) на активния лист. Номерацията на листовете започва от нула. След това, използвайки метода getActiveSheet(), получаваме обекта на този активен лист, т.е. с други думи, получаваме достъп до него за работа. И запазваме този обект в променливата $aSheet.

Ако искате да посочите друг лист като активен, първо трябва да го създадете, като използвате метода:

След това по аналогия посочваме индекса и получаваме обекта на активния лист.

Първо задаваме ориентацията на листа с помощта на метода setOrientation(), на който предаваме константата на класа PHPExcel_Worksheet_PageSetup:

  • ORIENTATION_PORTRAIT - портрет
  • ORIENTATION_LANDSCAPE - пейзаж

Имайте предвид, че преди метода setOrientation() трябва да извикате метода getPageSetup(), който осигурява достъп до настройките на страницата.

След това извикваме метода SetPaperSize(), който ви позволява да зададете размера на страницата за печат. Предаваме му константата PAPERSIZE_A4 на класа PHPExcel_Worksheet_PageSetup като параметър. Което означава, че размерът на листа на страницата ще бъде зададен на A4.

След това задаваме полетата на документа, тоест отстъпите от краищата на документа. Отстъпите се задават в специални символни единици. Първо, обърнете внимание, ние извикваме метода getPageMargins() на обекта $aSheet, който ще върне обект от класа, отговорен за задаването на полетата на страницата. След това извикваме методите setTop(), setRight(), setLeft() и setBottom().

След това, използвайки метода setTitle('Price List'), задайте заглавието на нашия лист.

Ако е необходимо, можете да покажете горния и долния колонтитул при печатлист:

Обърнете внимание на предадените параметри:

  • за заглавката, подайте низа '&CTD TINKO: ценова листа' ; &C означава, че текстът трябва да е центриран.
  • за долния колонтитул, подайте низа '&L&B'.$aSheet->getTitle().'&RСтраницата &P от &N' ; това означава извеждане на заглавието на листа с удебелен шрифт ( &L&B ) отляво (метод $aSheet->getTitle()), след което извеждане на номера на страницата ( &P) от общия брой страници ( &N) вдясно ( &R).

След това задаваме настройките на шрифта по подразбиране:

  • setName('Arial') - задаване на името на шрифта;
  • setSize(8) - задаване на размера на шрифта.

Попълване на документа с данни

Първо, нека зададем ширината на колоните (в знакови единици), от които се нуждаем:

Сега нека попълним някои клетки с текст:

Тук първо обединяваме клетки от A1 към E1 с помощта на метода mergeCells(), след което задаваме височината на реда: първо осъществяваме достъп до ред 1 с помощта на метода getRowDimension('1'), след което задаваме височината — setRowHeight(20) . След това, като използвате метода setCellValue('A1','TD TINKO'), задайте стойността на клетка A1.

excel

След това нека напишем текущата дата в клетка D4:

Сега, използвайки метода setCellValue(), както и цикъла while(), нека попълним нашия ценоразпис с данни:

mysql_connect (DB_HOST, DB_USER, DB_PASS); mysql_query ( 'ЗАДАДЕНИ ИМЕНА utf8' ); mysql_select_db ( DB_NAME );

// Създаване на заглавка на лист с данни $aSheet -> setCellValue ('A6', '#'); $aSheet -> setCellValue ('B6', 'Код'); $aSheet -> setCellValue ('C6', 'Име'); $aSheet -> setCellValue ('D6', 'Описание'); $aSheet -> setCellValue ('E6','Цена' ) ;

$query = "ИЗБЕРЕТЕ `код`, `име`, `описание`, `цена` ОТ `продукти` WHERE 1 LIMIT 10" ; $res = mysql_query ( $query ) ;

$i = 1; докато ( $prd = mysql_fetch_assoc ( $res ) ) < $aSheet -> setCellValue ('A'. ($i + 6), $i); $aSheet -> setCellValue ('B'. ($i + 6), $prd ['код']); $aSheet -> setCellValue ('C'. ($i + 6), $prd ['име']); $aSheet -> setCellValue ('D'. ($i + 6), $prd ['описание']); $aSheet -> setCellValue ('E'. ($i + 6), $prd ['цена']); $i++; >

Оформяне на данни

Нека малко да украсим нашата ценова листа, тоест да добавим стилове към всяка клетка. За да направите това, трябва да създадете масив със стилове и да използвате метода applyFromArray(), за да приложите този масив към клетка (или клетки):

// масив със стилове $style_wrap = масив ( // граници 'borders' => масив ( // външна граница 'outline' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_THICK , 'color' => масив ( 'rg) b' =&g t; '006464' ) ) , // вътрешен 'allborders' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_THIN , 'color' => масив ( 'rgb' => 'CCCCCC' ) ) ) ) ;

$aSheet -> getStyle ('A1:F'. ($i + 5)) -> applyFromArray ($style_wrap);

Сега, по аналогия, приложете стилове към останалите клетки:

// Стилове на заглавка (първи ред) $style_header = масив ( // Шрифт 'font' => масив ( 'bold' => true , 'name' => 'Times New Roman' , 'size' => 15 , 'color' => масив ( 'rg b' => '006464' ) ) , // Подравняване 'alignment' => масив ( 'horizontal' =>PHPExcel_STYLE_ALIGNMENT :: HORIZONTAL_CENTER , 'vertical' => PHPExcel_STYLE_ALIGNMENT :: VERTICAL_CENTER , ) , // Цветно запълване 'fill' => масив ( 'type' => PHPExcel_STYLE_FILL :: FILL_SOLID, 'color' => масив ( 'rgb' => '99CCCC' ) ), 'borders' => масив ( 'bottom' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_THIN, 'color' => масив ( 'rgb' => '006464' ) ) ) ) ; $aSheet -> getStyle ( 'A1:E1' ) -> applyFromArray ( $style_header );

// Стилове за слогана на компанията (втори ред) $style_slogan = array ( // шрифт 'font' => масив ( 'bold' => true , 'italic' => true , 'name' => 'Times New Roman' , 'size' => 12 , 'color' => масив ( 'rgb' => '006464' ) ) , // подравняване 'alignment' => масив ( 'horizontal' => PHPExcel_STYLE_ALIGNMENT :: HORIZONTAL_CENTER , 'vertical' => ; PHPEx cel_STYLE_ALIGNMENT :: VERTICAL_CENTER , ) , // цветно запълване 'fill' => масив ( 'type' => PHPExcel_STYLE_FILL :: FILL_SOLID , 'color' => масив ( 'rgb' => '99CCCC' <1 3>) <1 3>), //рамки 'borders' => масив ( 'bottom' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_THIN , 'color' => масив ( 'rgb' => '006464' ) ) ) ) ; $aSheet -> getStyle ( 'A2:E2' ) -> applyFromArray ($style_slogan);

// Стилове за текста близо до датата $style_tdate = масив ( // подравняване 'alignment' => масив ( 'horizontal' => PHPExcel_STYLE_ALIGNMENT :: HORIZONTAL_RIGHT , ) , // цветно запълване 'fill' => масив ( 'type' = > PHPExcel_STYLE_FILL :: FILL_SOLID, 'цвят' => масив ( 'rgb' => 'EEEEEE' ) ) , // граници 'граници' => масив ( 'right' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_NONE ) ) ) ; $aSheet -> getStyle ('A4:D4') -> applyFromArray ($style_tdate);

// Стилове за датата $style_date = масив ( // цветно запълване 'fill' => масив ( 'type' => PHPExcel_STYLE_FILL :: FILL_SOLID , 'color' => масив ( 'rgb' => 'EEEEEE' ) ), ), / / граници 'borders' => масив ( 'left' => масив ( 'style' => PHPExcel_Style_Border :: BORDER_NONE ) ) , ) ; $aSheet -> getStyle ('E4') -> applyFromArray ($style_date);

// Стилове за заглавката на таблицата (шести ред) $style_hprice = масив ( // подравняване 'alignment' => масив ( 'horizontal' => PHPExcel_STYLE_ALIGNMENT :: HORIZONTAL_CENTER , ) , // цветно запълване 'fill' => масив ( <1 3>'type' => PHPExcel_STYLE_FILL :: FILL_SOLID, 'color' => масив ( 'rgb' => 'CFCFCF' ) ), //font 'font' =>array ( 'bold' => true , /* 'ita lic ' => true, */ 'name' => 'Times New Roman' , 'size' => 10 ) , ) ; $aSheet -> getStyle ( 'A6:E6' ) -> applyFromArray ( $style_hprice );

// Стилове за данните в таблицата с ценова листа $style_price = array ( 'alignment' => array ( 'horizontal' => PHPExcel_STYLE_ALIGNMENT :: HORIZONTAL_LEFT , ) ) ; $aSheet -> getStyle ('A7:E'. ($i + 5)) -> applyFromArray ( $style_price );

Запазване на документ

Остава само да запазим нашия документ:

Ако искате да покажете документа в браузъра

Първият хедър казва на браузъра типаот отвореното съдържание е Excel документ. Вторият казва на браузъра, че документът трябва да бъде предоставен на потребителя за изтегляне под името simple.xlsx.

Добавяне на формули

Формулата на Excel е математически израз, който се създава за изчисляване на резултат и може да зависи от съдържанието на други клетки. Формула в клетка на Excel може да съдържа данни, препратки към други клетки и индикация за действията, които трябва да бъдат извършени.

Използването на препратки към клетки позволява резултатите от формулите да бъдат преизчислени, когато съдържанието на клетките, включени във формулите, се промени. Формулите на Excel започват със знака =. Скобите ( ) могат да се използват за указване на реда на математическите операции.

Примери за формули на Excel: =27+36 , =A1+A2-AZ , =SUM(A1:A5) , =MAX(AZ:A5) , =(A1+A2)/AZ .

PHPExcel също поддържа добавяне на формули към клетки. Можете да зададете формулата по следния начин:

чрез

Четене на Excel файл

Най-простият вариант е да прочетете всички таблици (на всички листове) и да запишете данните в триизмерен масив:

// Включете библиотеката require_once 'Classes/PHPExcel.php' ; $pExcel = PHPExcel_IOFactory :: load ('simple.xlsx') ;

// Преминаване през работни листове на Excel foreach ( $pExcel -> getWorksheetIterator ( ) като $worksheet ) < // качване на данни от обект в масив $tables [ ] = $worksheet -> toArray(); >