How to generate Excel file in cyrilic/cyrillic with Spreadsheet_Excel

How to generate Excel file in cyrilic/cyrillic with Spreadsheet_Excel_Writer Pear package in PHP

This article explains how to generate Excel files with Spreadsheet_Excel_Writer Pear package in PHP.

Installation

To use this package, you should first install it:

pear install Spreadsheet_Excel_Writer

As this package uses OLE package, you may need to install it too:

pear install OLE

Execute following commands to do the update:

pear channel-update pear.php.net
pear install -o OLE-0.5
pear install -o Spreadsheet_Excel_Writer-0.9.1

File generation

Here's a code sample for genrating file on the web server. myfile.xls is the filename (including path). The workbook contains one worksheet with a list of students. Function write() has 3 paramethers - first 2 determine coordinates of a cell, and the third is value to write.

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating workbook
$workbook = new Spreadsheet_Excel_Writer('myfile.xls');

// Adding worksheet
$worksheet =& $workbook->addWorksheet('students');

// Data input
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Grade');
$worksheet->write(1, 0, 'Ivancho');
$worksheet->write(1, 1, 7);
$worksheet->write(2, 0, 'Mariika');
$worksheet->write(2, 1, 7);
$worksheet->write(3, 0, 'Stoyancho');
$worksheet->write(3, 1, 8);

// Saving file
$workbook->close();
?>

Sending to browser

In most cases Excelexport is used to export some data to the user. We use send() function and don't pass fle name to the constructor:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating workbook
$workbook = new Spreadsheet_Excel_Writer();

// Sending headers to browser
$workbook->send('students.xls');

// Adding worksheet
$worksheet =& $workbook->addWorksheet('students');

// Data input
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Grade');
$worksheet->write(1, 0, 'Ivancho');
$worksheet->write(1, 1, 7);
$worksheet->write(2, 0, 'Mariika');
$worksheet->write(2, 1, 7);
$worksheet->write(3, 0, 'Stoyancho');
$worksheet->write(3, 1, 8);

// Sending the file
$workbook->close();
?>

And what about cyrilic?

If you use this to generate file with cyrilic symbols, you'll be disappointed. To make thing right, you should set UTF-8 charset and set document version to BIFF8, not BIFF5:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating workbook
$workbook = new Spreadsheet_Excel_Writer();
// Setting workbook version 8
$workbook->setVersion(8);
// Sending headers to browser
$workbook->send('students.xls');

// Adding worksheet
$worksheet =& $workbook->addWorksheet('students');
// Setting worksheet encoding to UTF-8
$worksheet->setInputEncoding('UTF-8');

// Data input
$worksheet->write(0, 0, 'Име');
$worksheet->write(0, 1, 'Клас');
$worksheet->write(1, 0, 'Иванчо');
$worksheet->write(1, 1, 7);
$worksheet->write(2, 0, 'Марийка');
$worksheet->write(2, 1, 7);
$worksheet->write(3, 0, 'Стоянчо');
$worksheet->write(3, 1, 8);

// Sending the file
$workbook->close();
?>

Comments:

Nguyen (11-06-2008 13:27) :
Thanks much for UTF-8 support

Thx (23-05-2009 09:23) :
Thank you very much for information about how to encode cyrlic :)

Andron (07-12-2009 13:14) :
Thanks!

Without $workbook->setVersion(8); text was not properly encoded.

adam (04-08-2010 10:48) :
very useful article

Unknown (05-02-2015 11:50) :
The Excel file is broken when the rows are bigger than 158 in worksheet

Back to articles list

This page was last modified on 2025-01-15 04:46:40

Ustrem.org