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.
<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.
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.
$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");
$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);
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>";
<?
// 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"
);
*/
?>
© 2004. All Rights Reserved. This class is a commercial software, if you are interested in purchasing it - contact me.