Excel Parser Professional EXCAL profiling
Installation installation ------------
To Install and Use this Script You Need INSTALLED AND PRIPER CONFIGUREDPHP. Script WAS TESTED ON PHP VESION> = 4.3.x. To install and use this script Your configuration must be adapted to the following requirements Your PHP version is going to 4.3 or less
Description Description -----------
This Parser Can Import Data from Excel File Versions from Excel 5.0 To Excel 2000 and XP. This tool can be converted from the Excel file (version must be 5.0 or more) to convert the ONLY STRING AND NUMBERS WILL BE IMPORTED. But he will only output Character with Digital String Can Be in Compressed Unicode (8-bits per character) or uncompressed Unicode (16-bits per character) Formats. Character will be compressed into Unicode encoding or other coding format
New Object Creation creates a new object -------------------
Requires 'ExcelParser.php'; // Import Class Library File
$ EXCEL = New ExcelfileParser ($ logfilename, $ logType); // instance class
$ Logfilename - optional parameter (filename for debug logging) Default value - empty string, no logging.// parameter options: log filename $ logtype - log severity Default value ABC_NO_LOG - disable logging.// generate a log type, default no log type can be one of the following: // log type will be selected ABC_CRITICAL ABC_ERROR ABC_ALERT ABC_WARNING ABC_NOTICE ABC_INFO ABC_DEBUG ABC_TRACE ABC_VAR_DUMP ABC_NO_LOG Bitwise combination of these flags is not allowed from the following You can use only one of them..
Two methods are used for processing Excel data: // There are two methods for data analysis excel ParseFromFile ($ filename) This function is optimized for memory usage, but the script takes more times to be completely executed and to parse the selected data. This function is suitable for use in memory, and he will spend more time in memory and analyzing the selected data. ParseFromString ($ contents) This function is not optimized for memory usage, but the script takes less times to be completely executed and to parse the selected data. Use when parsing speed is critical (PHP memory limit can be exceed for huge files and script will Terminate!). This function is not suitable for running in memory, but this script only needs to spend a small time to analyze the selected data. When you are key, you can use him very well, PHP memory may be The big file is timeout (the next sentence doesn't know what it means, it feels no corresponding to it before and after)
Examples: $ error_code = $ excel-> parsefromfile ($ filename)
$ fd = fopen ($ filename, 'rb'); $ content = fread ($ fd, filesis); fclose ($ fd); $ error_code = $ excel-> parsefromstring ($ content); unset ($ Content, $ fd);
Errors Codes: // Error Code Calculation Table 0 - No Errors // No Error 1 - File Read Error // Fail 2 - File Is Too Small To Be An Excel File // For the Excel File, he is too Small (not Excel file) 3 - Excel file head reading error 4 - file read error // File Error 5 - Not Excel File or Excel Version Earlier Than Excel 5.0 // No Excel file or Is his version is too low 6 - Corrupted file // Damage 7 - Data NOT FOUND // Data Did not find 8 - Unknown file version // Do not know file version
NOTE: Error 7 is Displayed When No Excel Data IS Found In Ole2 File Format (Like MS Word)
Object Fields // seems to be the meaning of member variables ----------------
INT $ EXCEL-> BIFF_VERSIONEXCEL FILE BIFF VERSION (7 = Excel 5-7, 8 = 2000, 10 = XP) // This execl file version number
Array $ Excel-> Worksheetan Array, Containing Lists Data // Returns list data
Array $ Excel-> Format An Array, Containing Data About The Styles Used To Format Cell // Returns an array, custom data format (may be a definition field or what to get data) TABLES ------
$ worksheet_number = Excel Worksheet Number - To View The Quantity of Accessible Worksheets, Use Count ($ EXCEL-> Worksheet ['Name'])
Boolean $ Excel-> Worksheet ['Unicode'] [$ Worksheet_Number] if The Data Is Saved in Uncompressed Unicode, The ITHIS FIELD HAS TRUE VALUE, FALSE IS Used for Compressed Unicode
String $ Excel-> Worksheet ['name'] [$ Worksheet_Number] Worksheet Name In Compressed or Uncompressed Unicode
INT $ EXCEL-> Worksheet ['Data'] [$ Worksheet_Number] ['Biff_Version'] Biff List Version
Column -------
Array $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Cell'] An Array Containing The Cells Data for the Chosen Worksheet.
$ row = row number (Begins from 0) $ col = column number (begins from 0) in Excel The Column Have Names As 'A', 'B', 'C', ETC
INT $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Max_row'] Maximum Row Number (! But Not Maximum Row Quantity!). For Example, The List Has Data in Rows 1,3,8 - Maximum Value 8,
Quantity of rows- 3
INT $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Max_col'] Maximum Column Number (The Same As For Rows, See Supra)
INT $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Cell'] [$ ROW] [$ col] ['Type'] The data type, contained in the Cell:
0 - String1 - Integer2 - Float3 - Date
Mixed $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Cell'] [$ ROW] [$ col] ['Data'] if Data Type IS 0, Cell Contains String Index in Sstiffs Type IS 1 , Cell Contains INTEGER IF DATA TYPE IS 2, Cell Contains Float IF Data Type IS 3, Cell Contains Date in Excel Presentationint $ EXC-> Worksheet ['Data'] [$ Worksheet_Number] ['Cell'] [$ ROW] [$ COL] ['font'] -Contains the index of font sale in the Cell
FONTS -----
The Fonts That Are Used in Workbook Are Stored in $ Excel-> FONTS Array.They Look in The Following Way:
$ font = $ excel-> fonts [$ 有Dex];
$ font ['size'] - Font Size in Points $ font ['italic'] - Is Font Italic True / false $ font ['strikeout'] is font strikeout true / false $ font ['bold'] is Font Bold True / false $ font [ 'script'] - may have the following constant values:... XF_SCRIPT_NONE - normal font XF_SCRIPT_SUPERSCRIPT - SUPERSCRIPT inscription is set XF_SCRIPT_SUBSCRIPT - SUPERSCRIPT inscription is set $ font [ 'underline'] - may have the following constant values :..... XF_UNDERLINE_NONE - normal font XF_UNDERLINE_SINGLE - single underlining is set XF_UNDERLINE_DOUBLE - double underlining is set XF_UNDERLINE_SINGLE_ACCOUNTING - underline single accounting is set XF_UNDERLINE_DOUBLE_ACCOUNTING - underline double accounting is set $ font [ 'name'] - fonts name.
Shared string table (sst): -------------------------- All string data is saved in this table of computer. The memory... Cells Do Not Contain The Strings Itself,
But the strings indexs in this table.
The access to the indexs is performed by:
IF ($ Excel-> Worksheet ['Data'] [$ Worksheet_Number] ['Cell'] [$ ROW] [$ COL] ['Type'] == 0) {$ IND = $ EXC-> Worksheet ['Data '] [$ Worksheet_Number] [' Cell '] [$ COL] [' Data '];} Array $ Excel-> Sstan Array, Containing Strings
Boolean $ Excel-> SST ['Unicode'] [$ IND] Logical Value, Showing WHETER THE DATA IS Presented in Uncompressed (True) Or Compressed (false) Unicode
String $ Excel-> SST ['Data'] [$ IND] STRING DATA
Date ----
The date is saved in Excel Format - (The Number of Days Starting from Year 1900) And Might Be Converted in TimeStamp
$ EXCEL-> XLS2TSTAMP ($ xlsdate)
You may work with returned data using common unix timestamp and format it with standart toolset for date in PHP. Because on the Windows platform the minimum timestamp value (null second) is 1,1,1970 this function processes correctly onlythe following dates.
If you need to process the earlier data, the future of the function:
$ RET = $ EXCEL-> GetDateArray ($ xlsdate);
WHERE:
$ RET ['Day'] = Day of Month. $ RET ['Month'] = Month. $ RET ['Year'] = YEAR.
Debug ------ in Order to make Parser to Write a logfile - The class must be created with the following parameters: excelfileparser ('logfile.txt', log_level);
Log_level May Have The FOLLOWING VALUES: ABC_CRITICALABC_ERROR ABC_ALERTABC_WARNINGABC_NORETABC_INFOABC_DEBUGABC_TRACEABC_VAR_DUMP
The Lower The Level, The More Messages Will Be Put in log by paser.
WITH ABC_NO_LOG - NO Logging Will Performed.
EXAMPLE -------
See sample.php file for working example.
Things Missed In this Release -----------------------------
. There is some restrictions in parser In this version hidden flag of columns, rows and worksheets are not processed Parser not understanding some cell formatsfor now Author plans to add this things in future versions.BUGS ---- Waiting for your comments..: )