Re: RE : [PHP] PHP & Rading excel files

Given that excel (and generaly any microsoft app/bloatware) puts so much junk into the files that sometimes excel itself cannot read it it is virtualy impossible to write a pure php solution. The easiest path is to use COM and run excel on the server, but this rules out any non windows server. Or try to dig into openoffice and wrap its excel routines in a php extension. BENARD Jean-philippe wrote:

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