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

Mon, 29 Sep 2003 06:40:20 -0700

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
([EMAIL PROTECTED])

-----Message d'origine-----
De : Kevin Bruce [mailto:[EMAIL PROTECTED] 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/[EMAIL PROTECTED] * 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




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to