PHP和Excel导入导出

 session_start();
/**
 * Parse Excel file and insert data into MySQL database
 *
 * @package xls2mysql
 */
function dump($vars, $label = '', $return = false)
{
    if (ini_get('html_errors')) {
        $content = "

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


  

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


  

  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
下载:	下载

阅读(2226) | 评论(0) | 转发(0) |

0

上一篇：js小例一个

下一篇：如何提高网页的效率（上篇）——提高网页效率的14条准则