MultiSheet Import with INSERT values coming from different sheets

Topics: Developer Forum
Feb 16, 2015 at 4:50 PM
Hello All,

With all the searching I have done, I cant seem to find quite the right direction for my project.

I am trying to import a file that has multiple sheets, all with data that needs to be added to a given record. I will try my best to explain what the format is a where I am stuck.

So the file has three sheets, Summary, Data, and Rates. On the summary page, a project number is input (12345) This number is unique. The data page the number of hours are plugged in with a date and based off of the column they are in, who worked the hours (ex 1/1/2015, Manager column,8 hrs....etc) Then on the Rates page, the position would be listed in column A with a rate (ex Manager $35.00)

Basically I need to be able to import the rate from the Rates page on cell A7 to a particular column in my database like Manager_Base_Rate then from the Data page, import the hours worked from cell G6 to Manager_ST_Hours then all associate that with a unique porject 12345 from the Summary page.

Now I can get the data to be displayed in all its unformatted glory after i import it, however, I am running into a wall when I try to figure out how to import this information.

A kick in the right direction/examples/ideas would be appreciated.

Also I have thought (if its possible) that maybe I should have phpexcel import the file and add a new sheet to the workbook and that would be full of formulas with one row. Then just building off of the one row. Im not sure if that is smarter or not.

Feb 20, 2015 at 8:08 PM
Edited Feb 20, 2015 at 8:09 PM
After a lot of lost sleep and feeling as if i was just so close - I found the following and modified it to my use the best.
require 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $worksheetTitle     = $worksheet->getTitle();
            $highestRow         = $worksheet->getHighestRow();
            $highestColumn      = $worksheet->getHighestColumn(''); 
            $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
            $nrColumns = ord($highestColumn) - 64;
            //$arrayData = $worksheet->toArray(); old function
            $arrayData[$worksheet->getTitle()] = $worksheet->toArray(null,false,true,true);
This loops through each sheet and gives you an array with letters and numbers.

For my application a quick echo of a few items I figure to be useful for those using this.
echo ($arrayData["Data"]['4']['B']);
The trick I had to wrap my mind around was the fact that phpexcel i giving you a multi-dimensional array vs a different array for each sheet.

As a side note- sheets with spaces in their names work fine just make sure you have "".

Also passing arrayData into a session array I felt was smart to keep me from meddling with the phpexcel coding.
$_SESSION['arrayData'] = $arrayData;
And I currently spit it out for myself to look at
echo "<pre>";
echo "</pre>";
Once i got this concept it was mostly painless to build my sql etc.

I really hopes this helps someone else.