| Код |
| <? include_once ("./includes.inc"); include_once ("./settings.inc"); include_once ("../../excel.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> </td> <td width="60%"><font color="#FFFFFF" size="+2">ABC Excel Parser Pro plugin</font></td> <td width="40%" align="right"><font color="#FFFFFF" size="+1">MS Excel->MySQL builder</font></td> <td> </td> </tr> </table> <?php // Outputting fileselect form (step 0) if ( $_POST['step'] == 0 ){ ?> <table width="100%" border="0" align="center" bgcolor="#7EA9D3"> <tr> <td> </td> <td> <p> </p> Excel <p> </p> </td> </tr> <tr> <td> </td> <td> <table border="0"> <form name="exc_upload" method="post" action="" enctype="multipart/form-data"> <tr><td>Excel :</td><td><input type="file" size=30 name="excel_file"></td></tr> <tr><td> :</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="" onClick=" javascript: if( (document.exc_upload.excel_file.value.length==0)) { alert(' '); return; }; submit(); "></td></tr> </form> </table> </td> </tr> <tr> <td> </td> <td align="right"> <p> </p> <a href="http://www.zakkis.ca" style="font-size: 9px; text-decoration: none; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">ZAKKIS Tech. 2003 All Rights Reserved.</a> </td> </tr> </table> <? } // excel ( 1) if ($_POST['step'] == 1) { echo "<br>"; // $excel_file = $_FILES['excel_file']; if( $excel_file ) $excel_file = $_FILES['excel_file']['tmp_name']; if( $excel_file == '' ) fatal(" "); 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(" "); if( filesize($excel_file)==0 ) fatal(" "); $fc = fread( $fh, filesize($excel_file) ); @fclose($fh); if( strlen($fc) < filesize($excel_file) ) fatal(" "); // excel $exc = new ExcelFileParser (); $res = $exc->ParseFromString($fc); switch ($res) { case 0: break; case 1: fatal(" "); case 2: fatal(", Excel"); case 3: fatal(" "); case 4: fatal(" "); case 5: fatal(" - Excel , Excel < 5.0"); case 6: fatal(" "); case 7: fatal(" Excel "); case 8: fatal(" "); default: fatal(" "); } // $ws_number = count($exc->worksheet['name']); if( $ws_number < 1 ) fatal(" Excel ."); $ws_number = 1; // , for ($ws_n = 0; $ws_n < $ws_number; $ws_n++) { $ws = $exc -> worksheet['data'][$ws_n]; // 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\"> : <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 ); // else fatal(" "); } } if ( $_POST['step'] == 2 ) { // mysql ( 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"; // if ( !$link = @mysql_connect ($db_host, $db_user, $db_pass) ) fatal(" . ."); if ( !$connect = mysql_select_db ($db_name ) ) fatal(" ."); if ( empty ($db_table) ) fatal(" ."); if ( !isset ($fieldcheck) ) fatal(" ."); if ( !is_array ($fieldcheck) ) fatal(" ."); $tbl_SQL .= "CREATE TABLE IF NOT EXISTS $db_table ( "; foreach ($fieldcheck as $fc) if ( empty ( $fieldname[$fc] ) ) fatal(" $fc."); else { // $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(" "); if( filesize($excel_file)==0 ) fatal(" "); $fc = fread( $fh, filesize($excel_file) ); @fclose($fh); if( strlen($fc) < filesize($excel_file) ) fatal(" "); $exc = new ExcelFileParser; $res = $exc->ParseFromString($fc); switch ($res) { case 0: break; case 1: fatal(" "); case 2: fatal(", Excel"); case 3: fatal(" "); case 4: fatal(" "); case 5: fatal(" - Excel , Excel < 5.0"); case 6: fatal(" "); case 7: fatal(" Excel "); case 8: fatal(" "); default: fatal(" "); } // $ws_number = count($exc->worksheet['name']); if( $ws_number < 1 ) fatal(" Excel ."); $ws_number = 1; // , for ($ws_n = 0; $ws_n < $ws_number; $ws_n++) { $ws = $exc -> worksheet['data'][$ws_n]; // $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(" "); } if (empty ( $SQL )) fatal(" "); // if ( isset($db_drop) ) { $drop_tbl_SQL = "DROP TABLE IF EXISTS $db_table"; if ( !mysql_query ($drop_tbl_SQL) ) fatal (" "); } // if ( !mysql_query ($tbl_SQL) ) fatal (" "); $sql_pref = "INSERT INTO " . $db_table . " SET "; $err = ""; $nmb = 0; // foreach ( $SQL as $sql ) { $sql = $sql_pref . $sql; if ( !mysql_query ($sql) ) { $err .= "<b>SQL </b> :<br>$sql <br>"; } else $nmb++; } if ( empty ($err) ) { echo <<<SUCC <br><br> <div align="center"> <b> , .</b><br><br> $nmb (), "$db_table"<br> <br><a href=""></a> </div> SUCC; } else echo "<br><br><font color=\"red\">$err</font><br><br><div align=\"center\"><a href=\"\"></a></div>"; @unlink ($excel_file); echo <<<ZAKKIS <br><br> <div align="right"> <a href="http://www.zakkis.ca" style="font-size: 9px; text-decoration: none; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">ZAKKIS Tech. 2003 All Rights Reserved.</a> </div> ZAKKIS; } ?> |
| Код |
| <?php function print_error( $msg ) { print <<<END <tr> <td colspan=5><font color=red><b>: </b></font>$msg</td> <td><font color=red><b></b></font></td> </tr> END; } function getHeader( $exc, $data ) { // $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'] ) { // case 0: $ind = $data['data']; if( $exc->sst[unicode][$ind] ) return uc2html($exc->sst['data'][$ind]); else return $exc->sst['data'][$ind]; // case 1: return (integer) $data['data']; // case 2: return (float) $data['data']; case 3: return gmdate("m-d-Y",$exc->xls2tstamp($data[data])); default: return ''; } } function fatal($msg = '') { echo '[ ]'; if( strlen($msg) > 0 ) echo ": $msg"; echo "<br>\n <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; // 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=" "> <input type="text" name="fieldname[$j]" value="$field" title=" "> </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=" "> <input type="text" name="fieldname[$j]" value="$field" title=" "> </td> HEADER; } } echo "</tr>"; foreach( $data as $i => $row ) { // SQL 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> :</td><td> <input type="text" name="db_table" value="$db_table"></td></tr> <tr><td> :</td><td><input type="checkbox" name="db_drop" checked></td></tr> <tr><td colspan="2"> <i> , .<br><font color="red"> , !</td></tr> <tr><td> :</td><td> <input type="text" size=30 name="db_host" value="$db_host"></td></tr> <tr><td> :</td><td> <input type="text" size=30 name="db_name" value="$db_name"></td></tr> <tr><td> :</td><td> <input type="text" size=30 name="db_user" value="$db_user"></td></tr> <tr><td> :</td><td> <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"> <input type="submit" name="submit" value="Output"></td></tr> </form> </table> <br> <div align="right"> <a href="http://www.zakkis.ca" style="font-size: 9px; text-decoration: none; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">ZAKKIS Tech. 2003 All Rights Reserved.</a> </div> FORM2; } function prepareTableData ( $exc, $ws, $fieldcheck, $fieldname ) { $data = $ws['cell']; foreach( $data as $i => $row ) { // SQL 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; } ?> |