PHP is yet most powerful language when it comes to deal with different data formats like SQL Results, XML Files etc. To improve the functionality of PHP, we use extensions. There are many extensions/packages which let us to read more different types of data formats. Today, we will be using one of them, i.e. phpexcelreader.
PHPExcelReader comes with two files oleread.inc and reader.php. We will need to include reader.php in order to use the package. For the sake of "simplicity", I have made some changes in example file and data. Grab it here:
Take a look at example.php, code is pretty much straight forward. As I said before, in order to use the package we need to include reader.php into our code.
Next, create a new object from the class Spreadsheet_Excel_Reader and give the excel file which you like to read.
All the rows and columns can be accessed through array variable sheets. We use two for loops to access each and every element of the sheet0.
If you are Microsoft Excel 2007 user, be sure about saving file in 1997-2003 .xls format else it will not work. Thanks for reading. :)
PHPExcelReader comes with two files oleread.inc and reader.php. We will need to include reader.php in order to use the package. For the sake of "simplicity", I have made some changes in example file and data. Grab it here:
Dowload PHPExcelReader.zip (not available now)
Sample Excel Spreadsheet
Take a look at example.php, code is pretty much straight forward. As I said before, in order to use the package we need to include reader.php into our code.
require_once('excel/reader.php');Next, create a new object from the class Spreadsheet_Excel_Reader and give the excel file which you like to read.
$xls = new Spreadsheet_Excel_Reader();
$xls->read('indiastamps_yearsets.xls');All the rows and columns can be accessed through array variable sheets. We use two for loops to access each and every element of the sheet0.
$xls->sheets[0]['cells'][$row][$column]
Result
If you are Microsoft Excel 2007 user, be sure about saving file in 1997-2003 .xls format else it will not work. Thanks for reading. :)
Nice Thankx :)
ReplyDeletenice. but problem with date when try to make file in open office.org excel format. when you create file in open office org calc and save it into Microsoft excel 97/2000/XP format. date will give fake information.
ReplyDelete@Jayu
ReplyDeleteI think this is happening because, when OpenOffice Export the file in Microsoft Excel .xls format, it replaces original Dates with its Numeric Date Values. Note here, Dates are actually saved as Numeric values in Excel file.
Eg. Date: 11/12/2009 having a numeric value of 40,129.00
You can try for some other Excel Exporter Software, or better use Microsoft Office 2003/07.
For this date problem we can use a class provided by codeplex,check this url:http://phpexcel.codeplex.com/releases/view/26478.
ReplyDeleteDownload this zip file->PHPExcel 1.7.4. if you extract this you'll find Date.php in 1.7.4.zip_FILES/Classes/PHPExcel/Shared.
Example:
excel cell value=>30-05-2009
by using this class(downloaded from itgeek) u will get a value like 39963
Now pass this value to Date.php class method like this:
$val = PHPExcel_Shared_Date::ExcelToPHP("39963");
the $val now contain a value like 1243641600 and pass this $val t o php date function,
$d =date("d/m/Y",$val);
if u echo $d, you will get 30/05/2009.
thanks a lot... i loved this solution... because this one is very easy to use and we need not change anything in the excel sheet.... really thanks GUNA... love u.
ReplyDeletenice stuff
ReplyDeletethanks GUNA ... i have browsing all day how i can import cell date format ... and finally i can fix it .... thank you ...
ReplyDelete