XLS Reader Class


http://paggard.com/projects/xls.reader/

Additional materials


[^ up]

Introduction


XLS Reader is a PHP class, that makes it possible to read binary XLS files (used by MS Excel) using PHP only. This class does not require any additional PHP modules or COM objects and work an all operating systems without any dependences if MS Excel is installed or not.

With this script you will be able to easily present your reports, price lists etc. on your web sites without any additional programming.

The script is able to convert XLS files created by MS Excel to HTML tables, preserving almost all formatting properties. This means that you can format the spreadsheet in Excel as you like it - using different font and cells formatting properties and the script will convert your XLS file into HTML as close as possible.

Currently the following formatting properties are supported by XLS Reader Class:

The Class is very simple to use, as it has its own built-in excel2html converter, which allows you to convert either whole WorkBook (including all the WorkSheets) at once, or to get HTML code only for needed SpreadSheets.

XLS Reader Class has special configuration file, where you will be able to tune the final look of the converted table. You can even turn off almost all the formatting settings that are stored in XLS file and use your own formatting for the table by utilizing CSS rules. You can read more about configuration file in the special section of this document.

Important! - the class can not handle XLS files larger than 7mb. When trying to read a larger file - an error will be displayed.


[^ up]

Usage Example

The Class can be used in several ways, from the very simple to advanced. The simplest example of the usage is given below:



<HTML><HEAD><TITLE>XLS Reader - simple usage example </TITLE>
</HEAD>
<BODY>
<br>
<STYLE TYPE="text/css" MEDIA=screen>
<!--
.xls_rd tr td { /* main CSS rules for table, rows and cells */
    font: normal 12px Verdana, Geneva, Arial, Helvetica, sans-serif;
    color: black;
}
.xls_rd .idx { /* rules for index cells */
    background-color: #EBEBEB;
    font: bold 11px Verdana, Geneva, Arial, Helvetica, sans-serif;
    color: #6B6B6B;
    text-align: center;
    vertical-align: middle;
}
.xls_rd .xls_int { /* rules for integer numbers */
    text-align: right;
}
.xls_rd .xls_dbl { /* rules for float numbers */
    text-align: right;
}
-->
</STYLE>
<br>
<?

require_once("xls_reader/cl_xls_reader.php");

$file "demo.xls";

$xls = new xls_reader(); // create class object
$xls->read_file($file);  // read xls file

$html_code $xls->workbook->get_workbook_html();
echo 
$html_code;

?>
</BODY></HTML>

As you can see, in the beginning of the file some CSS rules are given - you can alter them to get the final HTML look exactly as you need id, but in general - the given settings will do just fine. You can experiment with these rules to get the understanding how they work.


[^ up]

Main class methods

The following methods can be used to operate the class and Workbook structure. New methods can be written to meet possible requirements of the users. If you feel that some feature would be useful - you can request it and I will investigate the possibility of implementing it in the class.

To use the class you should first include the class files, which are stored in the 'xls_reader' folder. To do this only one file need to be included:


require_once "xls_reader/cl_xls_reader.php";

NOTE! Other required files will be automatically included by the main file, if not, then you have wrong setting of the 'include_path' variable in PHP. In this case you can include all the files manually:


require_once "xls_reader/cl_xls_reader.php";

require_once "xls_reader/hdr_xls_reader.php";

require_once "xls_reader/cl_xls_container.php";

require_once "xls_reader/str_xls_book.php";

After all fles will be included you will need to create a class object instance:


$xls = new xls_reader();

There are two ways to read XLS binary data - from a file which is stored on disk, or from stream.


[^ up]

Reading file from disk


$xls->read_file("file_name");

This method reads xls file from the given location and stores all the data within workbook structure. This method is preferable for most cases except for opening xls files from remote web servers, in this case read_string($file_stream) method should be used.

Example:


$xls = new xls_reader();

$xls->read_file("demo.xls");


[^ up]

Reading file from stream


$xls->read_string($file_stream);

There are cases when it is needed to have the ability to read the data not from a file, but from string. For example, this method should be used to read XLS files from remote web servers.

Example:


$fd = fopen("http://www.server.com/some_file.xls", "rb");

while(!feof($fd)) {

	$xls_stream .= fread($fd, 1024);

}

$xls = new xls_reader();

$xls->read_string($xls_stream);


[^ up]

Workbook object

After an XLS file is read by the class, all the data is stored in the special structure 'workbook' within the class object. This structure has its own methods to operate with the stored data. If you will need to customize output of the read data - you can write your own methods having the given ones as examples (templates).

$xls-> workbook->get_sheets_array();
This method returns an array with worksheets along with their indexes.

Example:


$xls = new xls_reader();

$xls->read_file("demo.xls");

$sheets = $xls->workbook->get_sheets_array();

$sheets variable with contain something like this:


Array (

	[0] = "First Sheet"

	[1] = "Second Sheet"

	. . .

)

$xls->workbook->get_workbook_html();
This method returns the whole workbook data converted into a single HTML code. The output of this function depends on the settings from configuration file.

Example:


$xls = new xls_reader();

$xls->read_file("demo.xls");

echo $xls->workbook->get_workbook_html();

$xls-> workbook->get_sheet_html($sheet_index);
This method returns single worksheet data converted to HTML. To use this function you need to know the sheet index of the needed worksheet. This index you can get by get_sheets_array() method.

Example:


$xls = new xls_reader();

$xls->read_file("demo.xls");

echo $xls->workbook->get_sheet_html(1);

this will print HTML table for worksheet which has index = 1

$xls->workbook->get_workbook_array();
This method returns the whole workbook data in the form of PHP array structure. This way of output can be easily used if you need to have the raw data of the XLS (for example - if you need to store it into MySQL database).

The returned array has all the workbook sheets in the structure of array is as follows:

Array

(

    [0] => Array

        (

            [SHEET_NAME] => First Sheet

            [SHEET_DATA] => Array

                (

                    [0] => Array

                        (

                            [0] => Row1Cell1 data

                            [1] => Row1Cell2 data

                            [2] => Row1Cell3 data

                        )



                    [1] => Array

                        (

                            [0] => Row2Cell1 data

                            [1] => Row2Cell2 data

                            [2] => Row2Cell3 data

                        )

                )

        )

    [1] => Array

        (

            [SHEET_NAME] => Second Sheet

            [SHEET_DATA] => Array

                (

                    [0] => Array

                        (

                            [0] => Row1Cell1 data

                            [1] => Row1Cell2 data

                            [2] => Row1Cell3 data

                            [3] => Row1Cell4 data

                        )

                )

        )

)

Example:


$xls = new xls_reader();

$xls->read_file("demo.xls");

$workbook_array = $xls->workbook->get_workbook_array();

echo "<pre>";

print_r($workbook_array);

echo "</pre>";


[^ up]

Configuration File

Here you can find an example configuration file which is used by XLS Reader Class. This file is stored in the same folder with the class files.



<?


//   CONFIGURATION FILE FOR XLS READER CLASS

// Default Table Settings
$border 1;              // tavle border
$cellpadding 2;         // cellpadding for the table
$cellspacing 0;         // cellspacing for the table
$TableBgColor "white";  // defauld background color

$Width2PixelsRate 6.5;  // default mm->pixels conversion rate.
                          // exel has all the cell widths set im millimeters
                          // mm will be converted to pixels using this rate

$DrawIndex true;        // if TRUe (1) - index sells will be drawn for columns and rows

$DrawSheetsList true;   // if TRUE (1) - function get_workbook_html() will also create
                          // WorkSheets list proir to printing tables

$DrawSheetName true;    // if TRUE (1) - sheet name will be drawn before every table
                          // sheetnames are drawn in <h4> ... </h4> - so you can tune the look of
                          // the Sheet names using CSS rules

//------------------------------------------------
// Using the setting below you will be able to disable
// formatting that is stored in XLS file

$OverrideIntAlign false;    // if TRUE (1) - alignmet for INTEGERS that is stored in XLS file
                              // will be disabled. You will be able to control INTEGRES fromatting
                              // using .xls_int {...} CSS rule

$OverrideDoubleAlign false// if TRUE (1) - alignmet for DOUBLES that is stored in XLS file
                              // will be disabled. You will be able to control DOUBLES fromatting
                              // using .xls_dbl {...} CSS rule

$OverrideAlign false;       // if TRUE (1) - horizontal alignment that is stored in XLS file
                              // will be ignored

$OverrideVAlign false;      // if TRUE (1) - vertical alignment that is stored in XLS file
                              // will be ignored

$OverrideBgColor false;     // if TRUE (1) - background cell color that is stored in XLS file
                              // will be ignored

$OverrideWidth false;       // if TRUE (1) - cell width that is stored in XLS file
                              // will be ignored

//------------------------------------------------
// Using the setting below you will be able to ignore
// font settings that are stored in XLS file

$OverrideFontAll false;        // if TRUE (1) - ignores all font settings stored in XLS
$OverrideFontColor false;      // if TRUE (1) - ignores font color stored in XLS
$OverrideFontBold false;       // if TRUE (1) - ignores font weight stored in XLS
$OverrideFontSize false;       // if TRUE (1) - ignores font size stored in XLS
$OverrideFontName false;       // if TRUE (1) - ignores font name stored in XLS
$OverrideFontItallic false;    // if TRUE (1) - ignores itallic font setting stored in XLS
$OverrideFontUnderline false;  // if TRUE (1) - ignores underline font setting stored in XLS
$OverrideFontScript true;      // if TRUE (1) - ignores script font setting stored in XLS


//------------------------------------------------
// In this section you can provide localy names for months
// they will be used to represent dates
// if ommited - English names will be used.
/*
$MonthsNames = Array (
   "January", "February",
   "March", "April", "May",
   "June", "July", "August",
   "September", "October", "November",
   "December"
);
$MonthsNamesShort = Array (
   "Jan", "Feb",
   "Mar", "Apr", "May",
   "Jun", "Jul", "Aug",
   "Sep", "Oct", "Nov",
   "Dec"
);
*/

?>


[^ up]

Author & Copyright

Vadim Kiparenko paggard@paggard.com
If you will have any question of suggestions - feel free to contact me directly - I'll try to respond as soon as possible.

© 2004. All Rights Reserved. This class is a commercial software, if you are interested in purchasing it - contact me.