📜 ⬆️ ⬇️

XLS file generation

The authorities made XLS report files generate (uploads of orders, goods ordered, customer summary, etc.). There was no problem with plain text generation (CSV format, ";" - separator). But the Mac OS bosses at point-blank did not want to understand what they were trying to sniff. The output is to generate another format. I would become on html or xml, but one of the requirements was “beautiful fonts and color fills of cells”. Without Excel anywhere.

For the sake of fairness, I must say that I found the generation of the Excel format quite quickly: on the desktop ( http://habrahabr.ru/blogs/php/18726/ ) and on OpenNET ( http://www.opennet.ru/base/ dev / php_gen_excel.txt.html ). The created XLS files opened perfectly on the 3 tested axes (mac, win, ubuntu).

I also have to say that on Habré many were thrown by a link about PHP Spreadsheet_WriteExcel , but for some reason I couldn’t use it.
As a result, I stumbled upon http://phpexcel.codeplex.com/ -PHPExcel - OpenXML - Create Excel2007 documents in PHP - Spreadsheet engine.
It has everything that you can possibly need (IMHO): a normal dock, many examples, and most importantly, many possibilities.

I used a minimum in my tasks, but still:
1) Setting the cell size
2) Text color
3) Text size
4) Fonts
5) Fill color area
6) Frames
7) Insert into formula cells
8) Inserting active (clickable) links into cells
9) Work with different sheets
')
Well, much more.
There is nothing to understand there !!! Many examples allow almost copy-paste to create the necessary scripts.

And finally - an example of their own tests:

/**
* PHPExcel
*
* Copyright (C) 2006 - 2010 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.7.3c, 2010-06-01
*/

/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
require_once '../Classes/PHPExcel.php';
// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B2', 'world!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!');

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

Source: https://habr.com/ru/post/96369/


All Articles