excel导入导出用js好还是php好,PHP和Excel导入导出

// Outputting fileselect form (step 0)

if ( $_POST['step'] == 0 )

echo <

Excel file:

Use first row as fields name:

FORM;

// Processing excel file (step 1)

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

echo "

";

// 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");

$_SESSION['excel'] = $excel_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");

}

// Processing 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 "

Worksheet: $ws_name

";

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

";

//$excel_file = 'upload/' . $_FILES['excel_file']['name'];

//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";*/

//dump($_SESSION);

$excel_file = $_SESSION['excel'];

$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");

}

*/

$data = new Spreadsheet_Excel_Reader();

//$data->setOutputEncoding('latin1');

//echo $excel_file;

$data->read($excel_file);

// Pricessing worksheets

@ $db = mysql_connect('localhost', 'wwwshef_reynold', 'reynold') or die("Could not connect to database.");

mysql_query("set names 'latin1'");

mysql_select_db('wwwshef_reynold');

$tablename = "products, product_categories, product_colours, product_sizeweight";

$field = "p_var8, productsw_code, productcr_code, p_var1, productcr_size, productsw_desc, productsw_size, p_var3, productsw_price, productcr_price, products.img_ref";

//$condition = "product_id='$pre[1]' AND productcr_id='$pre[2]' AND productsw_id='$pre[3]' products.p_cat1='1' AND products.product_id=product_categories.cat_id AND products.product_id=productcr_ref AND products.product_id=productsw_ref";

//dump($data->sheets[0]['cells']);

for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {

/*

for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";

}

echo "\n";

*/  //echo $data->sheets[0]['cells'][$i][2];

$pre = explode('-',$data->sheets[0]['cells'][$i][1]);

$condition = "product_id='$pre[1]' AND productcr_id='$pre[2]' AND productsw_id='$pre[3]' AND productcr_ref='$pre[1]' AND productsw_ref='$pre[1]' AND products.p_cat1='1' AND products.product_id=product_categories.cat_id AND products.product_id=productcr_ref AND products.product_id=productsw_ref";

$sql = "UPDATE $tablename SET

`p_var8`   = '".$data->sheets[0]['cells'][$i][2]  ."',

`productsw_code`  = '".$data->sheets[0]['cells'][$i][3]   ."',

`productcr_code`  = '".$data->sheets[0]['cells'][$i][4]   ."',

`p_var1`    = '".$data->sheets[0]['cells'][$i][5]   ."',

`productcr_size`  = '".$data->sheets[0]['cells'][$i][6]   ."',

`productsw_desc`  = '".$data->sheets[0]['cells'][$i][7]   ."',

`productsw_size`  = '".$data->sheets[0]['cells'][$i][8]   ."',

`p_var3`    = '".$data->sheets[0]['cells'][$i][9]   ."',

`productsw_price` = '".$data->sheets[0]['cells'][$i][10]  ."',

`productcr_price` = '".$data->sheets[0]['cells'][$i][11]  ."',

products.img_ref = '".$data->sheets[0]['cells'][$i][12] ."'

WHERE $condition";

//echo $sql.'

';

$res = mysql_query($sql) or die("Have problem on Updating ,Please limit the line of excel file");

$nmb += mysql_affected_rows();

}

if ( isset($nmb) ) {

echo <

$nmb rows Updated into table "$tablename"

Continue

SUCC;

}

else  echo "

$err

echo <<

ZAKKIS;

}

/*

$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 .= "SQL error in :

$sql

";

}

else $nmb++;

}

if ( empty ($err) ) {

echo <

$nmb rows inserted into table "$db_table"

Continue

SUCC;

}

else  echo "

$err

echo <<

ZAKKIS;

} */

?>

文件:

inventory.rar

大小:

91KB

下载: