When working with data import and export for example, file formats like csv and excel are commonly used. Data can be exported to csv format and imported elsewhere. Also data might be entered manually in an excel file and then imported in the database. So there might be a need to convert excel files to csv format, so that they can be parsed easily and processed.
Converting excel files to csv is very easily done using a popular library called PHPExcel. Lets take a look at an example right away.
/** Convert excel file to csv */ //Various excel formats supported by PHPExcel library $excel_readers = array( 'Excel5' , 'Excel2003XML' , 'Excel2007' ); require_once('lib/PHPExcel/PHPExcel.php'); $reader = PHPExcel_IOFactory::createReader('Excel5'); $reader->setReadDataOnly(true); $path = 'data.xls'; $excel = $reader->load($path); $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV'); $writer->save('data.csv'); echo 'File saved to csv format';
The above piece of code will convert the file data.xls to csv format and save it. PHPExcel can read various versions of excel format like 2000, 2003 and 2007. So it needs to be told the format of the excel file that is being converted.
The above technique has some limitations. For example it only converts the first sheet of the excel file to csv. And any additional excel based formatting is lost. However this is a limitation of the csv format itself so does not matter.
PHPExcel can be downloaded fromhttp://phpexcel.codeplex.com/