RE : [PHP] PHP & Rading excel files

I know exporting xls file to csv (or other tagged export) is not very difficult but in order to make a « all-users » application we must accept xls files. Cordialement, Jean-Philippe BENARD Consultant STERIA Infogérance (jean-philippe.benard-renexter@renault.com) -----Message d'origine----- De : Kevin Bruce [mailto:bruce@mdsg.umd.edu] Envoyé : lundi 29 septembre 2003 14:40 À : BENARD Jean-philippe Objet : Re: [PHP] PHP & Rading excel files I created a database dump that accepts tab delimited files. You'd have to export your xls file to a tab delimited file (which is a no-brainer). Here is the code (it was made to import into a user database, change it to suite your purposes): <? if(!$dump) { ?> There are currently <b><?php echo $total ?></b> entries in the <?php echo $user_admin ?> database.<br> Choose your <?php echo $mod_name ?> Admin action from the above menu. </font></p> <h1><font size="2">Database Import</font></h1> <font size="2"> <form action="<?php echo $_SERVER[PHP_SELF] ?>?dump=predump&category=<?php echo $category ?>" method="post" enctype="multipart/form-data" name="form1"> <font size="2"> Tab Delimited File Database Import <input type="file" name="file" accesskey="1"></label> <input name="user_level" type="hidden" id="user_level" value="<?php echo $category ?>"> <br> <input type="submit" name="Submit" value="Submit"> </font> </form> <?php } if($dump == "predump") { if(!$file_name) { echo "File did not upload! Please hit your back button and upload your import file."; } else { //read file $fp = fopen($file, "r"); $data = fread($fp, filesize($file)); fclose($fp); $fileC = explode("\r", $data); flush(); //get the 1st line, which usually contains the field names $sample = $fileC[0]; //get the longest entry as a sample of all fields, just in case the first line doesn't contain the field names $sample2 = max($fileC); $samplenum = explode("\t",$sample); $numfields = count($samplenum); $sample = "<td>" . str_replace("\t","&nbsp;</td><td>",$sample) . "&nbsp;</td>\n"; $sample2 = "<td>" . str_replace("\t","&nbsp;</td><td>",$sample2) . "&nbsp;</td>\n"; //set the form variable to carry over into next form $import = implode($fileC,"|"); ?> <form name="form2" method="post" action="<?php echo $_SERVER[PHP_SELF] ?>?dump=dump&category=<?php echo $category ?>"> <input name="category" type="hidden" value="<?php echo $category ?>"> <?php $fieldrop = fieldrop($dbh,$DBname,$tablename); ?> <table border="1" cellpadding="5" cellspacing="0" bordercolor="#6666CC" summary="Match up the database fields with the sample entry form this import"> <caption align="top"> Match up the database fields with the sample entry form this import <br> There are <?php echo $numfields - 1; ?>-<?php echo $numfields; ?> entries in this import. </caption> <tr bgcolor="#6666CC"> <?php for($n=0;$n<$numfields;$n++) { echo "<td> <div align=\"center\"> <select name=\"field[$n]\" id=\"field[$n]\"> echo $fieldrop </select> </div></td>"; } ?> </tr> <tr class="fonty"> <?php echo $sample ?> </tr> <tr class="fonty"> <?php echo $sample2 ?> </tr> <tr bgcolor="#6666CC"> <td colspan="15"> <div align="center"><font size="2"> <input name="numfields" type="hidden" value="<?php echo $numfields; ?>"> <input name="import" type="hidden" value="<?php echo base64_encode($import) ?>"> </font></div> <div align="center"><font size="2"><font size="2"> <br> <label> <input name="notify" type="checkbox" value="1" checked> Send welcome email to imported entries. (choosing this means it will take appximately <?php echo $numfields - 1; ?>-<?php echo $numfields; ?> seconds to complete the import)</label> <br> <input type="submit" name="Submit" value="Submit"> </font></font></div></td> </tr> </table> </form> <?php } } elseif($dump == "dump") { //set up the insert query $insert = ""; $maxfield = count($field); for($n=0; $n<$numfields;$n++) { $line = each($field); if($line[value]>'') { $insert .= $line[value] . ","; } } //remove last comma $insertlength = strlen($insert); $insert = substr($insert,0,$insertlength-1); //here's the insert part of the query $query = "INSERT INTO $tablename(nowelcomemail,user_level,$insert) "; //set up the values part //create main array $import = explode("|",base64_decode($import)); //set the beginning of the query $import2 = "VALUES "; //how many entries in the main array $maxfield = count($import)-1; next($import);//skip the first line (the fields list) //for each entry in main array, create an insert for($n=0; $n<$maxfield;$n++) { $line = each($import); //check to see if there is info in this entry if($line[value] > '') { //turn entry into array $entryarray = explode("\t",$line[value]); $entrynum = count($entryarray); //how many fields in this entry? if($entrynum == $numfields) //if the field count matches our field count, insert into DB { $import2 .= "('1','$category','"; //make sure the entry array has the same number of fields that we designated in the previous form $import2 .= str_replace("\t","','",addslashes($line[value])); $import2 .= "'), \n";// THERE! We have created the 'VALUES' part of the insert query } } } echo "Imported all entries<br>"; $import2length = strlen($import2); $import2 = substr($import2,0,$import2length-3);//get rid of last comma //Here is the actual importing $query2 = $query . $import2; mysql_query($query2,$dbh) OR die(mysql_error()); $query4 = "OPTIMIZE TABLE $tablename "; mysql_query($query4,$dbh) OR die(mysql_error()); } //function to list fields in database function fieldrop($dbh,$DBname,$tablename) { $dbresult2 = mysql_list_fields($DBname,$tablename,$dbh); $fields = "<option value=\"\" SELECTED>None</option>\n"; for($i=2;$i<mysql_num_fields($dbresult2);$i++) { $fieldname = mysql_field_name($dbresult2,$i); $fields .= "<option value=\"$fieldname\">$fieldname</option>\n"; } return $fields; } ?> > Hi ! > > It's possible to export data to excel (csv, xml, PEAR excel file > writing, ...) but is it possible to import data from excel ? (i.e.: I > want to get the data which is in Cell "A1" of sheet "Toto" in the posted > .xls file). > > Thanks in advance. > > (o_ BENARD Jean-Philippe - Consultant STERIA Infogérance > (o_ (o_ //\ RENAULT DTSI/ODPS/R@D * ALO * API : MLB 02C 1 14 > (/)_ (\)_ V_/_ 2 Av du vieil étang * 78181 MONTIGNY-LE-BRETONNEUX > Tél : +33 1-30-03-47-83 * Fax : +33 1-30-03-42-10 -- Kevin Bruce Educational Web Designer VIP K-16 Grant http://www.scienceinquiry.org bruce@mdsg.umd.edu Maryland Sea Grant College 4321 Hartwick Road, Suite 300 College Park, MD 20740 301.403.4220 ext. 25 OR (on Wednesdays and Fridays) 717.637.5370 AOL Instant Messenger screen name- mdsgkevin