Creating array out of columns

Topics: Developer Forum, Project Management Forum, User Forum
Oct 1, 2012 at 6:59 PM
Edited Oct 18, 2012 at 4:12 PM

I was wondering is it possible to create an array from Excel Columns. I am trying to import data from an excel spreadsheet into a db, and  I need to find a solution because setting cells as variable is not efficient enough. With that said, how would I create an array out of columns?

Oct 2, 2012 at 7:24 AM
Edited Oct 2, 2012 at 7:29 AM

Hi! I'm using this to create an array:

 

// array contains letters of column names
$arrColumns = array(0=>'A',1=>'B',2=>'C',3=>'D',4=>'E',5=>'F',6=>'G',7=>'H',8=>'I',9=>'J',10=>'K',11=>'L',12=>'M',13=>'N',14=>'O',15=>'P',16=>'Q',17=>'R',18=>'S',19=>'T',20=>'U',21=>'V',22=>'W',23=>'X',24=>'Y',25=>'Z',26=>'AA',27=>'AB',28=>'AC',29=>'AD',30=>'AE',31=>'AF',32=>'AG',33=>'AH',34=>'AI',35=>'AJ',36=>'AK',37=>'AL',38=>'AM',39=>'AN',40=>'AO',41=>'AP',42=>'AQ',43=>'AR',44=>'AS',45=>'AT',46=>'AU',47=>'AV',48=>'AW',49=>'AX',50=>'AY',51=>'AZ',52=>'BA',53=>'BB',54=>'BC',55=>'BD',56=>'BE',57=>'BF',58=>'BG',59=>'BH',60=>'BI',61=>'BJ',62=>'BK',63=>'BL',64=>'BM',65=>'BN',66=>'BO',67=>'BP',68=>'BQ',69=>'BR',70=>'BS',71=>'BT',72=>'BU',73=>'BV',74=>'BW',75=>'BX',76=>'BY',77=>'BZ');

$objPHPExcel = PHPExcel_IOFactory::load($_FILES['file1']['tmp_name']);
$objPHPExcel->setActiveSheetIndex(0);
$aSheet = $objPHPExcel->getActiveSheet();

// get number of last Row
$countRows = $aSheet->getHighestRow();

//get number of last column
$highestColumn = $aSheet->getHighestColumn();
$countCols = PHPExcel_Cell::columnIndexFromString($highestColumn);
$cells = array();
for ($i = 1; $i <= $countRows; $i++) { for ($y=0; $y<$countCols; $y++) { $cells[$arrColumns[$y]][$i] = $aSheet->getCell($arrColumns[$y].$i)->getValue(); } }


Result: we have an array $cells whith rows and columns

Coordinator
Oct 2, 2012 at 7:32 AM

When reading data from a worksheet, why not use the worksheet's toArray() or rangeToArray() methods instead?

Oct 2, 2012 at 7:41 AM
Edited Oct 2, 2012 at 7:47 AM

Because i make some changes while creating my array. The full code is:

 

for ($i = 1; $i <= $countRows; $i++) {
    $cels['B'][$i] = $aSheet->getCell('B'.$i)->getValue();
    $cels['C'][$i] = $aSheet->getCell('C'.$i)->getValue();
    if ($cels['B'][$i]) {
        $otdel = $cels['B'][$i];
    }
    if ($cels['C'][$i]) {
        $name = $cels['C'][$i];
    }
    for ($y=3; $y<$countCols; $y++) {
        $cels[$arrColumns[$y]][$i] = $aSheet->getCell($arrColumns[$y].$i)->getValue();
    }
    
    if ($cels['E'][$i]>"09:15:00")
        $cels[$col1][$i] = 1;
    
    if ($cels['F'][$i]<"18:00:00")
        $cels[$col2][$i] = 1;
    
    if ($cels['G'][$i]<"08:45:00")
        $cels[$col3][$i] = 1;
    
    if ($i == 1) {
        $cels['A'][$i] = 'Department';
        $cels['B'][$i] = 'Room';
        $cels['C'][$i] = 'Name';
        $cels[$col1][$i] = 'A';
        $cels[$col2][$i] = 'B';
        $cels[$col3][$i] = 'C';
    } else {
        $cels['A'][$i] = $roomDepts[$otdel];
        $cels['B'][$i] = $otdel;
        $cels['C'][$i] = $name;
    }
}

 

Something like this:)

And i didn't know that such functions are exists:):)

And i'm not pretending for absolute rightness.

And sorry for my terrible English

Oct 4, 2012 at 6:57 PM

Thanks!! I have one question......how do you reference your columns to your array? I think I am saying that right. Please excuse me if I am not using the right terminology. For example I am using  $array = array(0=>'A',1=>'B',2=>'C',3=>'D',4=>'E',5=>'F',6=>'G',7=>'H',8=>'I',9=>'J',10=>'K') and if I echo this statement I will get the values in this associative array instead of the values in the columns. Please be patient with me. I am fairly new to programming.

Coordinator
Oct 4, 2012 at 10:12 PM

There's a couple of helper functions that convert column letters to numbers and vice-versa:

PHPExcel_Cell::columnIndexFromString()

and

PHPExcel_Cell::stringFromColumnIndex()

Note that (due to a historic quirk) columnIndexFromString() returns a numeric column value starting from 1, while stringFromColumnIndex() starts from 0.

 

 

Oct 4, 2012 at 11:23 PM

Thank You Much!

Oct 8, 2012 at 7:03 PM
Edited Oct 8, 2012 at 10:04 PM

Hey guys,

            I am still trying to tackle my array problem and I still can't get it. I want to read a workbook that has 6 spreadsheets, and dump all the data in the workbook into my database. I just can't seem to get my array correct. I tried to print_r my array and my results show up as follows......Array ( [A] => Array ( [2] => Spillway ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) [G] => Array ( [2] => Hosy ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) [G] => Array ( [2] => Hosy ) [H] => Array ( [2] => Mr. ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) [G] => Array ( [2] => Hosy ) [H] => Array ( [2] => Mr. ) [I] => Array ( [2] => BK-11125 ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) [G] => Array ( [2] => Hosy ) [H] => Array ( [2] => Mr. ) [I] => Array ( [2] => BK-11125 ) [J] => Array ( [2] => Lol ) ) Array ( [A] => Array ( [2] => Spillway ) [B] => Array ( [2] => $10000 ) [C] => Array ( [2] => 2012-09-23 ) [D] => Array ( [2] => 2012-09-02 ) [E] => Array ( [2] => Smithereen ) [F] => Array ( [2] => A ) [G] => Array ( [2] => Hosy ) [H] => Array ( [2] => Mr. ) [I] => Array ( [2] => BK-11125 ) [J] => Array ( [2] => Lol ) [K] => Array ( [2] => 2012-09-02 ) )........With that said, this is sample data from my excel spreadsheet. I am not sure why my array is displaying like this. When I do an insert the field in my DB just reads array! Can someone help my solve my issue!! Please!! What am I doing wrong or not doing at all!!?? I appreciate any input! Thanks!! Here is the code I am working with so far

 

require_once 'CachedObjectStorageFactory.php';
require_once 'Settings.php';
require_once 'IOFactory.php';
// Cell caching to reduce memory usage.
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_in_memory;
$cacheSettings = array( ' memoryCacheSize ' => '8MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objPHPExcel = PHPExcel_IOFactory::load("spreadsheet.xls");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    $cells = array();
    $Sheet = $objPHPExcel->getActiveSheet();
      echo '<br><table border="1"><tr>';
   
    $ExcelColumns = array(0=>'A',1=>'B',2=>'C',3=>'D',4=>'E',5=>'F',6=>'G',7=>'H',8=>'I',9=>'J',10=>'K');
    for ($row = 1; $row <= $highestRow; ++ $row) {
         echo '<tr>';
        for ($col = 0 ; $col <= $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
              echo '<td>' . $val . '<br></td>';
  			for ($i = 2; $i <= $highestRow; $i++) {
   			for ($y=0; $y<$highestColumnIndex; $y++) {
    $cells[$ExcelColumns[$y]][$i] = $Sheet->getCell($ExcelColumns[$y].$i)->getFormattedValue();
       	print_r($cells);
       			
    }
    echo '</tr>'; 
}
    echo '</table>';			
     		
$dbc = mysql_query("INSERT INTO table (Name) VALUES ('$cells')") or die (mysql_error());	

Oct 9, 2012 at 8:47 AM

how about replacing your query with the following:

 

$value = array_shift($cells);
$dbc = mysql_query("INSERT INTO table (Name) VALUES ('" . $value . "')") or die (mysql_error());

Oct 9, 2012 at 3:56 PM
Edited Oct 9, 2012 at 5:23 PM

When I execute my code and do my insert the field I am inserting into is empty. With that said, I don't think I am too far off because when I do a var dump or a print r I can see results..as mentioned in earlier posts.