php - PHPExcel reader -- help required -

-

i m using phpexcel read data excel sheet , store in mysql table, till m able upload .xls .xlsx file , after uploading xls got below table structure of data

name    start_date              end_date               city 1 11/25/2011 3:30:00 pm   11/29/2011 4:40:00   jaipur 2 10/22/2011 5:30:00 pm   10/25/2011 6:30:00   kota 3  3/10/2011 2:30:00 pm 3/11/2011 12:30:00   bikaner                                                     chandigarh

now have problems, please suggest me optimized method

  1. how sheet name ( bcoz in 1 excel there 7 sheets )

  2. for store these data db, below code snippet

    $inputfilename = "test.xls";   $inputfiletype = phpexcel_iofactory::identify($inputfilename);   $objreader = phpexcel_iofactory::createreader($inputfiletype);   $objreader->setreaddataonly(true);   /**  load $inputfilename 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   ($row = 1; $row <= $highestrow; ++$row) {       ($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 returns

array ( [0] => array     (         [0] => name         [1] => start_date         [2] => end_date         [3] => city         [4] =>              )  [1] => array     (         [0] => 1           [1] => 40568.645833333         [2] => 40570.5         [3] => jaipur         [4] =>      ) [2] => array     (         [0] => 2          [1] => 40568.645833333         [2] => 40570.5         [3] => kota         [4] =>      ) [3] => array     (         [0] => 3         [1] => 40568.645833333         [2] => 40570.5         [3] => bikaner         [4] =>       )   [4] => array     (         [0] =>          [1] =>          [2] =>          [3] => chandigarh         [4] =>      )  )

i need

  • header of each excel sheet (i.e. first row) become key of array($arr_data) ,
  • rest become value of array.
  • time changed integer value, shoud same in excel sheet
  • blank field ( i.e.blank header column ) of array in row should truncated (here [4] )
  • if first field of excel sheet (or combined condition on fields) not fulfilled row should not added array

i.e. desired array should 

array       (       [0] => array       (         [name] => 1         [start_date] => 11/25/2011 3:30:00 pm         [end_date] => 11/29/2011 4:40:00         [city] => jaipur       )       [1] => array       (         [name] => 2         [start_date] => 10/22/2011 5:30:00 pm         [end_date] => 10/25/2011 6:30:00         [city] => kota       )       [2] => array       (         [name] => 3         [start_date] => 3/10/2011 2:30:00 pm         [end_date] => 3/11/2011 12:30:00         [city] => bikaner       )   )

and after store data db using mysql action on desired array.

  • is there other short method store above data in db

note: please not refer manual( really bad )... tell me methods name..

update

@mark solution, helps me lot, still problems there

  • how handle empty/blank cell in excel sheet..bcoz when cell empty display notice

notice: undefined index: c in c:\xampp\htdocs\xls_reader\tests\excel2007.php on line 60
notice: undefined index: d in c:\xampp\htdocs\xls_reader\tests\excel2007.php on line 60

whereas line 60 

foreach($headingsarray $columnkey => $columnheading) {      $nameddataarray[$r][$columnheading] = $datarow[$row]$columnkey]; }
  • how set conditions before retrieving array of complete data i.e. if want if first , second column empty/blank in row row should not added in our desired array

thanks

how sheet name ( bcoz in 1 excel there 7 sheets )?

to current active sheet:

$sheetname = $objphpexcel->getactivesheet()->gettitle();

time changed integer value, shoud same in excel sheet

look @ phpexcel_shared_date::exceltophp($exceldate) or phpexcel_shared_date::exceltophpobject($exceldate) convert date/time values php timestamp or datetime object

have @ $objphpexcel->getactivesheet()->toarray() method rather looping through rwos , columns yourself. if want use toarray formatted argument though, don't use $objreader->setreaddataonly(true); otherwise phpexcel can't distinguish between number , date/time. latest svn code has added rangetoarray() method worksheet object, allows read row (or block of cells) @ time, e.g. $objphpexcel->getactivesheet()->rangetoarray('a1:a4')

the rest of questions php array manipulation

edit

ps. instead of telling manual really bad... tell how can improve it.

edit 2

using latest svn code take advantage of rangetoarray() method:

$objworksheet = $objphpexcel->setactivesheetindex(0); $highestrow = $objworksheet->gethighestrow(); $highestcolumn = $objworksheet->gethighestcolumn();  $headingsarray = $objworksheet->rangetoarray('a1:'.$highestcolumn.'1',null, true, true, true); $headingsarray = $headingsarray[1];  $r = -1; $nameddataarray = array(); ($row = 2; $row <= $highestrow; ++$row) {     $datarow = $objworksheet->rangetoarray('a'.$row.':'.$highestcolumn.$row,null, true, true, true);     if ((isset($datarow[$row]['a'])) && ($datarow[$row]['a'] > '')) {         ++$r;         foreach($headingsarray $columnkey => $columnheading) {             $nameddataarray[$r][$columnheading] = $datarow[$row][$columnkey];         }     } }  echo '<pre>'; var_dump($nameddataarray); echo '</pre><hr />';

Comments

Post a Comment

Popular posts from this blog

java - SNMP4J General Variable Binding Error -

-
i trying use snmp4j snmp bulkget. when ever use snmp4j make call returned responseevent error says "general variable binding error" , data returned equal null. to debug: print out, console, exact oid , version number using in snmp4j. use printed out data snmpwalk on command line. valid results. know sending snmp4j correct pdu correct oid, version number, max repititions, etc. i have used snmp4j , code wrote succesffuly monitor other devices years. don't know different time. leaving me stumped. why "general variable binding error?" causes error? ideas debug? can reproduced? if so, first action should using wireshark or microsoft network monitor capture network packets. my guess agent gives generr response, not surprising, http://www.ietf.org/rfc/rfc1157.txt
Read more

windows - Python Service Installation - "Could not find PythonClass entry" -

-
i working on building python service. inducing error, not sure inducing it. ps c:\...> python .\file.py debug debugging service myservice - press ctrl+c stop. error 0xc00000f4 - not find service's pythonclass entry in registry error 1814 - specified resource name cannot found in image file. error 0xc0000080 - not locate module name in python class string (ie, no '.') this error coming pythonservicemessages.mc in pythonwin32. it turns out error result caused exception being thrown , python script dying off.
Read more

Determine if a XmlNode is empty or null in C#? -

-
the following code takes xmlnode data type , populates dataset object xmlnode content. write dataset's content file. public void populatedataset(xmlnode node) { xmlnodereader reader = new xmlnodereader(node); dataset ds = new dataset(); ds.readxml(reader); system.guid guid = system.guid.newguid(); string name = string.format("{0}{1}_{2}.xml", utility.xmloutputpath, utility.xmloutputfilename, guid.tostring()); //need write "node empty" file if xmlnode object empty of null ds.writexml(name, xmlwritemode.ignoreschema); } the problem encountered 1 scenario not write content file. how determine if xmlnode object null or empty? you can check if node parameter null or has innertext or innerxml properties null or empty, when enter method before creating xmlnodereader .
Read more