Date/time issue PHPExcel

Topics: Developer Forum, User Forum
Apr 24, 2012 at 9:05 AM


I m using PHPExcel (version 1.7.6) to read data from Excel sheet and store in mysql table, till now i m able to upload .xls as well as .xlsx file and after uploading the xls i got below table structure of data

name    start_date              end_date               city
one 11/25/2011 3:30:00 PM   11/29/2011 4:40:00 AM   Jaipur
two 10/22/2011 5:30:00 PM   10/25/2011 6:30:00 AM   Kota
three  3/10/2011 2:30:00 PM 3/11/2011 12:30:00 AM   Bikaner
                                                    chandigarh

now i have some problems, please suggest me the optimized method

    how do we get the sheet name ( bcoz in one excel there are 7 sheets )

    for now what i do to store these data into db, below is the code snippet

    $inputFileName = "test.xls"; 
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName); 
    $objReader = PHPExcel_IOFactory::createReader($inputFileType); 
    $objReader->setReadDataOnly(true); 
    /**  Load $inputFileName to a PHPExcel Object  **/ 
    $objPHPExcel = $objReader->load($inputFileName); 
    $total_sheets=$objPHPExcel->getSheetCount(); // here 4 
    $allSheetName=$objPHPExcel->getSheetNames(); // array ([0]=>'student',[1]=>'teacher',[2]=>'school',[3]=>'college') 
    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0); // first sheet 
    $highestRow = $objWorksheet->getHighestRow(); // here 5 
    $highestColumn = $objWorksheet->getHighestColumn(); // here 'E' 
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  // here 5 
    for ($row = 1; $row <= $highestRow; ++$row) { 
        for ($col = 0; $col <= $highestColumnIndex; ++$col) { 
        $value=$objWorksheet->getCellByColumnAndRow($col, $row)->getValue(); 
            if(is_array($arr_data) ) { $arr_data[$row-1][$col]=$value; } 
        } 
    }
    print_r($arr_data);

 

and this returns

Array
(
[0] => Array
    (
        [0] => name
        [1] => start_date
        [2] => end_date
        [3] => city
        [4] =>        
    )

[1] => Array
    (
        [0] => one 
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Jaipur
        [4] =>
    )
[2] => Array
    (
        [0] => two
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Kota
        [4] =>
    )
[3] => Array
    (
        [0] => three
        [1] => 40568.645833333
        [2] => 40570.5
        [3] => Bikaner
        [4] =>
     )
  [4] => Array
    (
        [0] =>
        [1] =>
        [2] =>
        [3] => Chandigarh
        [4] =>
    )

)

 

i.e. desired array should look like

Array 
    ( 
    [0] => Array 
    ( 
      [name] => one 
      [start_date] => 11/25/2011 3:30:00 PM 
      [end_date] => 11/29/2011 4:40:00 AM 
      [city] => Jaipur 
    ) 
    [1] => Array 
    ( 
      [name] => two 
      [start_date] => 10/22/2011 5:30:00 PM 
      [end_date] => 10/25/2011 6:30:00 AM 
      [city] => Kota 
    ) 
    [2] => Array 
    ( 
      [name] => three 
      [start_date] => 3/10/2011 2:30:00 PM 
      [end_date] => 3/11/2011 12:30:00 AM 
      [city] => Bikaner 
    ) 
)

Please give solution for above result.

Thanks

Ragav

Coordinator
Apr 24, 2012 at 4:59 PM

Those numeric values are the actual Excel timestamp values for those dates. You can use the date manipulation functions in PHPExcel_Shared_Date to convert to PHP date/timestamps or PHP DateTime objects and then use PHP's date-formatting functions to make them human-readable; use getFormattedValue() to retrieve the cell value; or look at using the Worksheets toArray(), rangeToArray() or similar methods that offer a formatted argument.