PHP和Excel导入导出

\n";
        if ($label != '') {
            $content .= "{$label} :\n";
        }
        $content .= htmlspecialchars(print_r($vars, true));
        $content .= "\n\n";
    } else {
        $content = $label . " :\n" . print_r($vars, true);
    }
    if ($return) { return $content; }
    echo $content;
    return null;
}
include_once ("./includes.inc");
include_once ("./settings.inc");
include_once ("$parser_path/excelparser.php");
require_once 'reader.php';
if ( !isset($_POST['step']) )
$_POST['step'] = 0;

?>
 
Upload Sports A to Z Products

 
 
// Outputting fileselect form (step 0)
if ( $_POST['step'] == 0 )
echo
 
 
Select Excel file from your local computer
 
 
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) 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
  default:
   fatal("Unknown error");
}

  
// Processing worksheets

$ws_number = count($exc->worksheet['name']);
if( $ws_number  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) 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
  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 sheets[0]['numRows']; $i++) {
  /*
   for ($j = 1; $j 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
  Output operations processed successfully.
  $nmb rows Updated into table "$tablename"
  
Continue
  
SUCC;
}
else  echo "
$err
Continue";

@unlink ($excel_file);
echo
ZAKKIS;

}
/*
$ws_number = count($exc->worksheet['name']);
if( $ws_number  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
  Output operations processed successfully.
  $nmb rows inserted into table "$db_table"
  
Continue
  
SUCC;
}
else  echo "
$err
Continue";

@unlink ($excel_file);
echo
ZAKKIS;

} */
  
?>



文件:
inventory.rar
大小:
91KB
下载:
下载