Need formatting help

Topics: Developer Forum
May 18, 2011 at 10:49 PM

I need to read a heavily formatted Excel 2007 file, perform checks on column headers, number of columns etc, have the dates in all the cells converted to MM/DD/YYYY format and write all of the data to csv to be mapped into an oracle table. The major steps , I used.

1)  a)  $objReader->setReadDataOnly(true);

      b) if( PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($j, $i) )){
            $cell = PHPExcel_Shared_Date::ExcelToPHPObject($objWorksheet->getCellByColumnAndRow($j, $i)->getValue())->format('m/d/Y');
            $cell =  $objWorksheet->getCellByColumnAndRow($j, $i)->getCalculatedValue(); AKT

The problem I am facing with above option is : the file being heavily formatted has formatting associated with even blank cells. Which causes the upload to hang. The solution that works is - copy all the cells which have data into a new Excel 2007 sheet and upload it ( this works fine ).

2) Option 2 is to ignore the formatting so that blank cells with formatting are not read.

     a) $objReader->setReadDataOnly(true);

The problem with option 2) is that the date values are now the Excel number that is stored for any date ( as now isDateTime() does not work ).


I need a workaround to this issue and not sure what can be done about this. Is there a way that I can go with option 1) but still avoid the reading of blank cells which have attached formatting ?

Any help will be greatly appreciated.

May 23, 2011 at 9:11 AM

I will appreciate a response to this query.


May 23, 2011 at 12:11 PM

The way I have done it is like your option 2 with code like this:

if(strlen($date) == 5) { //Excel
        $date = PHPExcel_Style_NumberFormat::toFormattedString($date, "M/D/YYYY");

May 24, 2011 at 9:22 PM

Don't set $objReader->setReadDataOnly(true) if you need to be able to identify dates using isDateTime();

If your workbook contains multiple worksheets, you could try loading a worksheet at a time.

If this is one large worksheet, you could try reading the workbook in "chunks" using a Read Filter.

    $inputFileType = 'Excel5'; 
    $inputFileName = './sampleData/example2.xls';

    /**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
    class chunkReadFilter implements PHPExcel_Reader_IReadFilter {
        private $_startRow = 0;
        private $_endRow = 0;

        /**  Set the list of rows that we want to read  */ 
        public function setRows($startRow, $chunkSize) { 
            $this->_startRow    = $startRow; 
            $this->_endRow      = $startRow + $chunkSize;

        public function readCell($column, $row, $worksheetName = '') {
            //  Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow 
            if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { 
               return true;
            return false;

    /**  Create a new Reader of the type defined in $inputFileType  **/
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    /**  Define how many rows we want to read for each "chunk"  **/ 
    $chunkSize = 1024;
    /**  Create a new Instance of our Read Filter  **/ 
    $chunkFilter = new chunkReadFilter(); 
    /**  Tell the Reader that we want to use the Read Filter that we've Instantiated  **/ 

    /**  Loop to read our worksheet in "chunk size" blocks  **/ 
    /**  $startRow is set to 2 initially because we always read the headings in row #1  **/
    for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) { 
        /**  Tell the Read Filter, the limits on which rows we want to read this iteration  **/ 
        /**  Load only the rows that match our filter from $inputFileName to a PHPExcel Object  **/ 
        $objPHPExcel = $objReader->load($inputFileName); 
        //    Do some processing here 
        //    Free up some of the memory