Excel -> MySql (php)

despre limbajul de programare PHP

Mesaj necititde dj_trail » Mie 17 Dec 2003 1:52 pm

Cum as putea introduce datele dintr-un fisier excel (.xls) in mysql folosind php ?
dj_trail
 
Mesaje: 89
Membru din: Mie 28 Mai 2003 3:00 am
Localitate: Iasi

Mesaj necititde grasu » Mie 17 Dec 2003 3:57 pm

http://www.evolt.org/article/Using_MySQ ... /20/26896/

si google

<font size=-1>[ Acest Mesaj(e) a fost editat de: grasu pe 2003-12-17 14:58 ]</font>
grasu
 
Mesaje: 147
Membru din: Mie 05 Feb 2003 3:00 am

Mesaj necititde claudiuc1 » Joi 26 Oct 2006 10:35 am

moshule, ai gasit ce cautai? ca vad ca toate scripturile sau softurile sunt cu bani, iar cele trial sunt criptate
claudiuc1
 
Mesaje: 19
Membru din: Lun 20 Feb 2006 3:00 am

Mesaj necititde flachishor » Joi 26 Oct 2006 5:25 pm

Din excel le poti salva cvs si de-acolo le bagi rapid in MySQL cu PHP.
flachishor
 
Mesaje: 378
Membru din: Vin 07 Apr 2006 3:00 am
Localitate: Iasi

Mesaj necititde mariancraciun » Vin 08 Dec 2006 2:19 pm

Tii neaparat sa fol php? exista Navicat si versiunea trial merge perfect (linux win mac).
Parca si phpmyadmin putea.. nu?
mariancraciun
 
Mesaje: 21
Membru din: Mar 24 Mai 2005 3:00 am

Mesaj necititde dantte » Lun 09 Mar 2009 10:33 am

sper sa nu supar moderatorul sau alta lume cu acest post fiindca este o leak cam lung .

iata rezolvarea la problema ta :

creezi includes.inc continind urmatorul cod :

<?php

function print_error( $msg )
{
print <<<END
<tr>
<td colspan=5><font color=red><b>Error: </b></font>$msg</td>
<td><font color=red><b>Rejected</b></font></td>
</tr>

END;
}

function getHeader( $exc, $data )
{
// string

$ind = $data['data'];
if( $exc->sst[unicode][$ind] )
return convertUnicodeString ($exc->sst['data'][$ind]);
else
return $exc->sst['data'][$ind];

}


function convertUnicodeString( $str )
{
for( $i=0; $i<strlen($str)/2; $i++ )
{
$no = $i*2;
$hi = ord( $str[$no+1] );
$lo = $str[$no];

if( $hi != 0 )
continue;
elseif( ! ctype_alnum( $lo ) )
continue;
else
$result .= $lo;
}

return $result;
}

function uc2html($str) {
$ret = '';
for( $i=0; $i<strlen($str)/2; $i++ ) {
$charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);
$ret .= '&#'.$charcode;
}
return $ret;
}



function get( $exc, $data )
{
switch( $data['type'] )
{
// string
case 0:
$ind = $data['data'];
if( $exc->sst[unicode][$ind] )
return uc2html($exc->sst['data'][$ind]);
else
return $exc->sst['data'][$ind];

// integer
case 1:
return (integer) $data['data'];

// float
case 2:
return (float) $data['data'];
case 3:
return gmdate("m-d-Y",$exc->xls2tstamp($data[data]));

default:
return '';
}
}



function fatal($msg = '') {
echo '[Fatal error]';
if( strlen($msg) > 0 )
echo ": $msg";
echo "<br>nScript terminated<br>n";
if( $f_opened) @fclose($fh);
exit();
}


function getTableData ( $ws, $exc ) {


global $excel_file, $db_table;
global $db_host, $db_name, $db_user, $db_pass;

$data = $ws['cell'];

echo <<<FORM

<form action="" method="POST" name="db_export">
<table border="0" cellspacing="1" cellpadding="2" align="center" bgcolor="#666666">
<tr bgcolor="#f1f1f1">

FORM;

// Form fieldnames

if ( !$_POST['useheaders'] ) {
for ( $j = 0; $j <= $ws['max_col']; $j++ ) {

$field = "field" . $j;

echo <<<HEADER

<td>
<input type="checkbox" name="fieldcheck[$j]" value="$j" checked title="Check to proceed this field">
<input type="text" name="fieldname[$j]" value="$field" title="Field name">
</td>

HEADER;
}
}
else {
for ( $j = 0; $j <= $ws['max_col']; $j++ ) {

$field = getHeader ( $exc, $data[0][$j] );

$field = ereg_replace ( "^[0-9]+", "", $field );

if (empty ($field) )
$field = "field" . $j;

echo <<<HEADER

<td>
<input type="checkbox" name="fieldcheck[$j]" value="$j" checked title="Check to proceed this field">
<input type="text" name="fieldname[$j]" value="$field" title="Field name">
</td>

HEADER;
}
}


echo "</tr>";

foreach( $data as $i => $row ) {


if ( $i == 0 && $_POST['useheaders'] )
continue;

echo "<tr bgcolor="#ffffff">";

for ( $j = 0; $j <= $ws['max_col']; $j++ ) {

$cell = get ( $exc, $row[$j] );
echo "<td>$cell</td>";

}

echo "</tr>";
$i++;
}

if ( empty ( $db_table ) )
$db_table = "Table1";

echo <<<FORM2

</table><br>
<table align="center" width="390">
<tr><td>Table name:</td><td>&nbsp;<input type="text" name="db_table" value="$db_table"></td></tr>
<tr><td>Drop table if exists:</td><td><input type="checkbox" name="db_drop" checked></td></tr>
<tr><td colspan="2">
<i>Uncheck this option to add data into the existing table.<br><font color="red">
Note that if you have mismatch in fieldnames in database and fieldnames in outputting data will be errors!</td></tr>
<tr><td>Database host:</td><td>&nbsp;<input type="text" size=30 name="db_host" value="$db_host"></td></tr>
<tr><td>Database name:</td><td>&nbsp;<input type="text" size=30 name="db_name" value="$db_name"></td></tr>
<tr><td>Database user:</td><td>&nbsp;<input type="text" size=30 name="db_user" value="$db_user"></td></tr>
<tr><td>Database password:</td><td>&nbsp;<input type="password" size=30 name="db_pass" value="$db_pass"></td></tr>
<tr><td></td><td><input type="hidden" name="excel_file" value="$excel_file">
<input type="hidden" name="step" value="2">
&nbsp;<input type="submit" name="submit" value="Output"></td></tr>
</form>
</table>
<br>&nbsp;
FORM2;

}



function prepareTableData ( $exc, $ws, $fieldcheck, $fieldname ) {


$data = $ws['cell'];

foreach( $data as $i => $row ) { // Output data and prepare SQL instructions


if ( $i == 0 && $_POST['useheaders'] )
continue;

$SQL[$i] = "";

for ( $j = 0; $j <= $ws['max_col']; $j++ ) {

if ( isset($fieldcheck[$j]) ) {


$SQL[$i] .= $fieldname[$j];
$SQL[$i] .= "="";
$SQL[$i] .= addslashes ( get ( $exc, $row[$j] ) );
$SQL[$i] .= """;

$SQL[$i] .= ",";
}


}

$SQL[$i] = rtrim($SQL[$i], ',');

$i++;
}

return $SQL;

}

?>

setezi conectarea la baza de date :

creezi settings.php continind urmatorul cod :

$db_user = ""; // Database username
$db_pass = ""; // Database password
$db_name = ""; // Database name
$db_host = "localhost"; // Database host
$db_port = ""; // Database port
$parser_path = "../../"; // Path to Excel parser without /


iar acum miraculosul index.php care contine

<?php

include_once ("./includes.inc");
include_once ("./settings.inc");
include_once ("$parser_path/excelparser.php");


if ( !isset($_POST['step']) )
$_POST['step'] = 0;

?>

<html>
<head>

<STYLE>
<!--
body, table, tr, td {font-size: 12px; font-family: Verdana, MS sans serif, Arial, Helvetica, sans-serif}
td.index {font-size: 10px; color: #000000; font-weight: bold}
td.empty {font-size: 10px; color: #000000; font-weight: bold}
td.dt_string {font-size: 10px; color: #000090; font-weight: bold}
td.dt_int {font-size: 10px; color: #909000; font-weight: bold}
td.dt_float {font-size: 10px; color: #007000; font-weight: bold}
td.dt_unknown {font-size: 10px; background-color: #f0d0d0; font-weight: bold}
td.empty {font-size: 10px; background-color: #f0f0f0; font-weight: bold}
-->
</STYLE>
</head>
<body text="#000000" link="#000000" vlink="#000000" alink="#000000" topmargin="0" leftmargin="2" marginwidth="0" marginheight="0">

<table width="100%" align="center" bgcolor="#006699">
<tr>
<td>&nbsp;</td>
<td width="60%"><font color="#FFFFFF" size="+2">ABC Excel Parser </font></td>
<td width="40%" align="right"><font color="#FFFFFF" size="+1">MS Excel->MySQL builder</font></td>
<td>&nbsp;</td>
</tr>
</table>

<?php

// Outputting fileselect form (step 0)

if ( $_POST['step'] == 0 )
echo <<<FORM
<table width="100%" border="0" align="center" bgcolor="#7EA9D3">
<tr>
<td>&nbsp;</td>
<td>
<p>&nbsp;</p>
Select Excel file from your local computer
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>

<table border="0">
<form name="exc_upload" method="post" action="" enctype="multipart/form-data">

<tr><td>Excel file:</td><td><input type="file" size=30 name="excel_file"></td></tr>
<tr><td>Use first row as fields name:</td><td><input type="checkbox" name="useheaders"></td></tr>
<tr><td colspan="2" align="right">
<input type="hidden" name="step" value="1">
<input type="button" value="Next" onClick="
javascript:
if( (document.exc_upload.excel_file.value.length==0))
{ alert('You must specify filename first'); return; }; submit();
"></td></tr>


</form>
</table>

</td>
</tr>


<tr>
<td>&nbsp;</td>
<td align="right">
<p>&nbsp;</p>

</td>
</tr>
</table>

FORM;

// Processing excel file (step 1)

if ( $_POST['step'] == 1 ) {

echo "<br>";

// Uploading file

$excel_file = $_FILES['excel_file'];
if( $excel_file )
$excel_file = $_FILES['excel_file']['tmp_name'];

if( $excel_file == '' ) fatal("No file uploaded");

move_uploaded_file( $excel_file, 'upload/' . $_FILES['excel_file']['name']);
$excel_file = 'upload/' . $_FILES['excel_file']['name'];


$fh = @fopen ($excel_file,'rb');
if( !$fh ) fatal("No file uploaded");
if( filesize($excel_file)==0 ) fatal("No file uploaded");

$fc = fread( $fh, filesize($excel_file) );
@fclose($fh);
if( strlen($fc) < filesize($excel_file) )
fatal("Cannot read file");


// Check excel file

$exc = new ExcelFileParser ();
$res = $exc->ParseFromString($fc);

switch ($res) {
case 0: break;
case 1: fatal("Can't open file");
case 2: fatal("File too small to be an Excel file");
case 3: fatal("Error reading file header");
case 4: fatal("Error reading file");
case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
case 6: fatal("File corrupted");
case 7: fatal("No Excel data found in file");
case 8: fatal("Unsupported file version");

default:
fatal("Unknown error");
}


// Pricessing worksheets

$ws_number = count($exc->worksheet['name']);
if( $ws_number < 1 ) fatal("No worksheets in Excel file.");

$ws_number = 1; // Setting to process only the first worksheet

for ($ws_n = 0; $ws_n < $ws_number; $ws_n++) {

$ws = $exc -> worksheet['data'][$ws_n]; // Get worksheet data

if ( !$exc->worksheet['unicode'][$ws_n] )
$db_table = $ws_name = $exc -> worksheet['name'][$ws_n];
else {
$ws_name = uc2html( $exc -> worksheet['name'][$ws_n] );
$db_table = convertUnicodeString ( $exc -> worksheet['name'][$ws_n] );
}

echo "<div align="center">Worksheet: <b>$ws_name</b></div><br>";


$max_row = $ws['max_row'];
$max_col = $ws['max_col'];

if ( $max_row > 0 && $max_col > 0 )
getTableData ( &$ws, &$exc ); // Get structure and data of worksheet
else fatal("Empty worksheet");

}

}

if ( $_POST['step'] == 2 ) { // Adding data into mysql (step 2)

echo "<br>";

extract ($_POST);

$db_table = ereg_replace ( "[^a-zA-Z0-9$]", "", $db_table );
$db_table = ereg_replace ( "^[0-9]+", "", $db_table );

if ( empty ( $db_table ) )
$db_table = "Table1";

// Database connect check

if ( !$link = @mysql_connect ($db_host, $db_user, $db_pass) )
fatal("Database connection error. Please check connection settings.");

if ( !$connect = mysql_select_db ($db_name ) )
fatal("Wrong database name.");

if ( empty ($db_table) )
fatal("Empty table name.");

if ( !isset ($fieldcheck) )
fatal("No fields selected.");

if ( !is_array ($fieldcheck) )
fatal("No fields selected.");

$tbl_SQL .= "CREATE TABLE IF NOT EXISTS $db_table ( ";

foreach ($fieldcheck as $fc)
if ( empty ( $fieldname[$fc] ) )
fatal("Empty fieldname for selected field $fc.");
else {
// Prepare table structure

$fieldname[$fc] = ereg_replace ( "[^a-zA-Z0-9$]", "", $fieldname[$fc] );
$fieldname[$fc] = ereg_replace ( "^[0-9]+", "", $fieldname[$fc] );
if ( empty ( $fieldname[$fc] ) )
$fieldname[$fc] = "field" . $fc;

$tbl_SQL .= $fieldname[$fc] . " text NOT NULL,";

}

$tbl_SQL = rtrim($tbl_SQL, ',');

$tbl_SQL .= ") TYPE=MyISAM";


$fh = @fopen ($excel_file,'rb');
if( !$fh ) fatal("No file uploaded");
if( filesize($excel_file)==0 ) fatal("No file uploaded");

$fc = fread( $fh, filesize($excel_file) );
@fclose($fh);
if( strlen($fc) < filesize($excel_file) )
fatal("Cannot read file");


$exc = new ExcelFileParser;
$res = $exc->ParseFromString($fc);

switch ($res) {
case 0: break;
case 1: fatal("Can't open file");
case 2: fatal("File too small to be an Excel file");
case 3: fatal("Error reading file header");
case 4: fatal("Error reading file");
case 5: fatal("This is not an Excel file or file stored in Excel < 5.0");
case 6: fatal("File corrupted");
case 7: fatal("No Excel data found in file");
case 8: fatal("Unsupported file version");

default:
fatal("Unknown error");
}

// Pricessing worksheets

$ws_number = count($exc->worksheet['name']);
if( $ws_number < 1 ) fatal("No worksheets in Excel file.");

$ws_number = 1; // Setting to process only the first worksheet

for ($ws_n = 0; $ws_n < $ws_number; $ws_n++) {

$ws = $exc -> worksheet['data'][$ws_n]; // Get worksheet data

$max_row = $ws['max_row'];
$max_col = $ws['max_col'];

if ( $max_row > 0 && $max_col > 0 )
$SQL = prepareTableData ( &$exc, &$ws, $fieldcheck, $fieldname );
else fatal("Empty worksheet");

}


if (empty ( $SQL ))
fatal("Output table error");


// Output data into database


// Drop table

if ( isset($db_drop) ) {

$drop_tbl_SQL = "DROP TABLE IF EXISTS $db_table";

if ( !mysql_query ($drop_tbl_SQL) )
fatal ("Drop table error");

}

// Create table

if ( !mysql_query ($tbl_SQL) )
fatal ("Create table error");

$sql_pref = "INSERT INTO " . $db_table . " SET ";

$err = "";
$nmb = 0; // Number of inserted rows

foreach ( $SQL as $sql ) {

$sql = $sql_pref . $sql;

if ( !mysql_query ($sql) ) {
$err .= "<b>SQL error in</b> :<br>$sql <br>";

}
else $nmb++;

}

if ( empty ($err) ) {
echo <<<SUCC
<br><br>
<div align="center">
<b>Output operations processed successfully.</b><br><br>
$nmb rows inserted into table "$db_table"<br>
<br><a href="">Begin</a>
</div>
SUCC;
}
else echo "<br><br><font color="red">$err</font><br><br><div align="center"><a href="">Begin</a></div>";

@unlink ($excel_file);

echo <<<ZAKKIS

<br><br>


ZAKKIS;

}

?>

toate aceasta treaba bineteles ca ai mai trebuie un mic script care o sa til dau prin mail. trimite un PM cu YM
dantte
 
Mesaje: 22
Membru din: Vin 26 Ian 2007 3:00 am


Înapoi la PHP

Cine este conectat

Utilizatorii ce navighează pe acest forum: Niciun utilizator înregistrat şi 2 vizitatori

cron