Php excel on linux

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.

PHP Extension interface to the Excel writing/reading library

iliaal/php_excel

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.markdown

This extension uses libXl library to provide API for generating or parsing all Excel files.

libxl is a high-performance mechanism for working with Excel files and is able to generate output readable on Blackberries, iPhone, Office Products, Numbers, etc.

Read the information in the CONTRIBUTOR.md document.

Please see the docs/ and the tests/ directory.

# change into php source files directory cd php-5.x.x # clone repository into php extension dir git clone https://github.com/iliaal/php_excel.git ext/excel # to build php7 module, you should use php7 git branch cd ext/excel && git checkout php7 && cd ../.. # rebuild configure ./buildconf --force # replace with the file path to the extracted libxl files # on a 32-bit platform use ./configure --with-excel --with-libxl-incdir=PATH>/libxl-3.6.0.1/include_c --with-libxl-libdir=PATH>/libxl-3.6.0.1/lib # on a 64-bit platform use ./configure --with-excel --with-libxl-incdir=PATH>/libxl-3.6.0.1/include_c --with-libxl-libdir=PATH>/libxl-3.6.0.1/lib64

Pre-build packages for Windows can be downloaded here.

 // init excel work book as xlsx $useXlsxFormat = true; $xlBook = new \ExcelBook('', '', $useXlsxFormat); $xlBook->setLocale('UTF-8'); // add sheet to work book $xlSheet1 = $xlBook->addSheet('Sheet1'); // create a small sample data set $dataset = [ [1, 1500, 'John', 'Doe'], [2, 750, 'Jane', 'Doe'] ]; // write data set to sheet $row = 1; foreach($dataset as $item)< $xlSheet1->writeRow($row, $item); $row++; > // write sum formula under data set $col = 1; $xlSheet1->write($row, $col, '=SUM(B1:B3)'); // add second sheet to work book $xlSheet2 = $xlBook->addSheet('Sheet2'); // add a date with specific date format to second sheet $row = 1; $col = 0; $date = new \DateTime('2014-08-02'); $dateFormat = new \ExcelFormat($xlBook); $dateFormat->numberFormat(\ExcelFormat::NUMFORMAT_DATE); $xlSheet2->write($row, $col, $date->getTimestamp(), $dateFormat, \ExcelFormat::AS_DATE); // save workbook $xlBook->save('test.xlsx');

To prevent unveiling your credentials in your code you can save them in your php.ini file. They will be automatically fetched by the extension and you can pass null instead of your credentials new \ExcelBook(null, null, $useXlsxFormat) .

; optional settings for excel extension [excel] excel.license_name="" excel.license_key="" excel.skip_empty=0

Formulas written but no values readable

Excel stores value and formula for each cell while LibXL stores only the formula. This means if you create an Excel sheet with php_excel and write a formula like =SUM(A1:B1) in cell C1 you can’t read the value of the calculation by reading cell C1 in a later step. There has been observations that this can also affect the OS pre-view of Excel files. You can circumvent this by opening and saving the file directly in Excel or using the COM classes to open and save the Excel file via PHP. (In both cases Excel is required!)

multibyte characters in credentials

If your credentials does not work properly because of multibyte characters you can compile php_excel with —with-xml —with-libxml —with-iconv and your credentials will be automatically utf8_decoded() before using with LibXL.

If you compile php_excel as a shared extension on Linux you need to provide the path to the libxml directory. e.g. on Ubuntu you need to compile with —with-libxml-dir=/usr/include/libxml2 .

Источник

PHPExcel: Manipulate Excel Spreadsheets with PHP on Linux

Have you ever faced a situation when you need to manipulate Excel spreadsheets with PHP on the server that is running Linux? If you had a Windows Server you could use PHP COM extensions. However they are unavailable on Linux.

Hopefully, there is a solution. It is called Open XML. It’s a new format of Microsoft Office documents introduced in Microsoft Office 2007. Basically, an Open XML file is a ZIP archive that contains XML files that represent the document mark-up. You can view it yourself or read some documentation, my article isn’t about Open XML, but about the PHP library for working with Excel 2007 files. It also supports Excel 97 format by incor porating a PEAR library.

The library is called PHPExcel. It allows you to read/write Excel spreadsheets, save them in many formats including PDF and HTML. It supports formulas,styles and etc.

I have these two simple examples that show you how to create an Excel spreadsheet and save it in several formats and how to read a spreadsheet and display it on the HTML page.

//Here we set the include path and load the librarires
set_include_path(get_include_path() . PATH_SEPARATOR . ‘../PhpExcel2007/Classes/’);
require_once(‘PHPExcel.php’);
require_once(‘PHPExcel/IOFactory.php’);

$excel = new PHPExcel();
$excel->setActiveSheetIndex(0); //we are selecting a worksheet
$excel->getActiveSheet()->setTitle(‘Products’); //renaming it

//here we fill in the header row
$excel->getActiveSheet()->setCellValue(‘A1’, ‘Title’);
$excel->getActiveSheet()->setCellValue(‘B1’, ‘Price’);
$excel->getActiveSheet()->setCellValue(‘C1’, ‘Quanity’);
$excel->getActiveSheet()->setCellValue(‘D1’, ‘Total price’);

//here we put some values
$excel->getActiveSheet()->setCellValue(‘A2’, ‘Fictional TV set’);
$excel->getActiveSheet()->setCellValue(‘B2’, 300);
$excel->getActiveSheet()->setCellValue(‘C2’, 1500);
$excel->getActiveSheet()->setCellValue(‘D2’, ‘=B2*C2’); //this is how we put formulas, just like using Excel

$excel->getActiveSheet()->setCellValue(‘A3’, ‘Fictional mobile phone’);
$excel->getActiveSheet()->setCellValue(‘B3’, 200);
$excel->getActiveSheet()->setCellValue(‘C3’, 5000);
$excel->getActiveSheet()->setCellValue(‘D3’, ‘=B3*C3’);

$excel->getActiveSheet()->setCellValue(‘A4’, ‘Fictional laptop’);
$excel->getActiveSheet()->setCellValue(‘B4’, 1000);
$excel->getActiveSheet()->setCellValue(‘C4’, 2000);
$excel->getActiveSheet()->setCellValue(‘D4’, ‘=B4*C4’);

//some summarizing formulas
$excel->getActiveSheet()->setCellValue(‘C5’, ‘=SUM(C2:C4)’);
$excel->getActiveSheet()->setCellValue(‘D5’, ‘=SUM(D2:D4)’);

//Now we save the created document in the Exce 2007 format
$excelWriter = PHPExcel_IOFactory::createWriter($excel, ‘Excel2007’);
$excelWriter->save(‘Products.xlsx’);

//in PDF
$pdfWriter = PHPExcel_IOFactory::createWriter($excel, ‘PDF’);
$pdfWriter->save(‘Products.pdf’);

//in HTML
$htmlWriter = PHPExcel_IOFactory::createWriter($excel, ‘HTML’);
$htmlWriter->save(‘Products.html’);

//and in the old binary format
$excelBinaryWriter = PHPExcel_IOFactory::createWriter($excel, ‘Excel5’);
$excelBinaryWriter->save(‘Products.xls’);

//Here we set the include path and load the librarires
set_include_path(get_include_path() . PATH_SEPARATOR . ‘../PhpExcel2007/Classes/’);
require_once(‘PHPExcel.php’);
require_once(‘PHPExcel/IOFactory.php’);

$excelReader = PHPExcel_IOFactory::createReader(‘Excel2007’); //we instantiate a reader object
$excel = $excelReader->load(‘Products.xlsx’); //and load the document

print(‘

‘);
print($excel->getActiveSheet()->getCell(‘A’ . $i)->getValue()); //this is how we get a simple value
print(‘

‘);

print(‘

‘);
print($excel->getActiveSheet()->getCell(‘B’ . $i)->getValue());
print(‘

‘);

print(‘

‘);
print($excel->getActiveSheet()->getCell(‘C’ . $i)->getValue());
print(‘

‘);

print(‘

‘);
print($excel->getActiveSheet()->getCell(‘D’ . $i)->getCalculatedValue()); //this is how we get a calculated value
print(‘

‘);

PHPExcel is a very poweful library which is easy and well documentated. Of course you can use it not only on Linux, but on any operating system, including Windows.

The opinions expressed herein are my own personal opinions and do not represent my employer’s view in any way. My personal thoughts tend to change, hence the articles in this blog might not provide an accurate reflection of my present standpoint.

Источник

Ubuntu — любимая ОС

Основная задача этого блога, показать как работать в Ubuntu комфортно и с удовольствием!

PHP работа с Excel

По просьбам читателей сегодня пишу пост о дружбе MS Excel’я и PHP , сразу скажу чтобы потом не закидали камнями что дружат они ущербно , минут десять я потратил, чтобы найти разработку с помощью которой можно создавать excel’евские таблицы и ещё около часа искал класс с помощью которого это можно будет прочитать )) Начну с создания таблицы.

Есть класс под названием Excel Writer с помощью которого можно с лёгкостью создать excel’евский файлик вот таким образом

include("excelwriter.inc.php"); $excel=new ExcelWriter("myXls.xls"); if($excel==false) echo $excel->error; $myArr=array("Name","Last Name","Address","Age"); $excel->writeLine($myArr); $myArr=array("Sriram","Pandit","23 mayur vihar",24); $excel->writeLine($myArr); $excel->writeRow(); $excel->writeCol("Manoj"); $excel->writeCol("Tiwari"); $excel->writeCol("80 Preet Vihar"); $excel->writeCol(24); $excel->writeRow(); $excel->writeCol("Harish"); $excel->writeCol("Chauhan"); $excel->writeCol("115 Shyam Park Main"); $excel->writeCol(22); $myArr=array("Tapan","Chauhan","1st Floor Vasundhra",25); $excel->writeLine($myArr); $excel->close(); echo "data is write into myXls.xls Successfully.";

В папке со скриптом у меня появился файл myXls.xls , который открылся двумя экселями 2007 и 2003

Для того чтобы читать таблицы созданные в excel’е вам понадобиться класс под названием Spreadsheet Excel Reader , для его работы необходим модуль PEAR, но на линукс хостинге у меня всё работало и так, без дополнительных установок. Еще в файле Excel/reader.php 31 строчку я заменил с
require_once ‘Spreadsheet/Excel/Reader/OLERead.php’;
на
require_once ‘Excel/oleread.inc’;
Пример у меня выглядел следующим образом:

require_once ('Excel/reader.php'); $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('cp-1251'); $data->setUTFEncoder('mb'); $data->read('filename.xls'); echo (''); for ($i = 1; $i < = $data->sheets[0]['numRows']; $i++) < echo(''); for ($j = 1; $j < = $data->sheets[0]['numCols']; $j++) < echo (''); > echo(''); > echo ('
'.$data->sheets[0]['cells'][$i][$j].'
');

Скрипт отлично распарсил созданный мною файл и выдал на экран такую же таблицу только в HTML’е.
Скачать класс:
http://downloads.sourceforge.net/phpexce lreader/phpExcelReader.zip

Я бы не советовал организовывать серьёзные проекты на классе Spreadsheet Excel Reader , т.к. во время поиска увидел массу людей не довольных его работой, он плохо справляется с файлами состоящих из нескольких листов или с вычисляемыми полями. Встретил одну коммерческую разработку, которая творит чудеса и даже разбивает результат по листам ,вот демо проекта.

Источник