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

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
>
>