First clean up the idea first, ~ ~
First: You need to upload the file to the server.
Then: Read the Excel data column display
Then: Let the user select the corresponding relationship of the field
Then: Submit the data, read the correspondence of the field
Last: Bulk import data, delete temporary files
Altogether is the above five steps! We step-by-Step analysis ~ ~ ~
The first step: Download the Phpexcelparser4.rar in the attachment, this file is uploaded on the Excel thief server and displayed in Web Form! This is generally no problem! The problem is that the procedure is to save the table as a temporary table without actually saving it, so first change the program code to
Copy CodeThe code is as follows:
if (Trim ($_post["cmd"]) = = = "Upload")
{
$err _corr = "Unsupported format or file corrupted";
$excel _file_size;
$excel _file = $_files[' excel_file ');
$uploadservername = $UploadAbsPath. " tmpexcel/". $_files[' excel_file ' [' name '];
Echo ($uploadservername);
if (!is_writeable ($UploadAbsPath. " tmpexcel/"))
{
echo "Directory not writable!"; Exit
}
Else
{
echo "catalogue can be written!";
}
if (Move_uploaded_file ($_files[' excel_file ' [' tmp_name '], $uploadservername))
{
Echo ("Upload success");
}
Else
{
Echo ("Upload failed");
}
$excel _file= $uploadservername;
if ($excel _file)
$excel _file = $_files[' excel_file ' [' tmp_name '];
if ($excel _file = = ") Fatal (" No file uploaded ");
$exc = new Excelfileparser ("Debug.log", Abc_no_log);//abc_no_log abc_var_dump);
Echo ($excel _file. "|");
$style = $_post[' style '];
if ($style = = ' old ')
{
$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");
$time _start = Getmicrotime ();
$res = $exc->parsefromstring ($FC);
$time _end = Getmicrotime ();
}
ElseIf ($style = = ' segment ')
{
$time _start = Getmicrotime ();
$res = $exc->parsefromfile ($excel _file);
$time _end = Getmicrotime ();
}
Switch ($res) {
Case 0:break;
Case 1:fatal ("Can ' t Open file");
Case 2:fatal ("file too small to is an Excel File");
Case 3:fatal ("Error reading file header");
Case 4:fatal ("Error reading file");
Case 5:fatal ("This is not a 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");
}
/*
print '
print '
';
Exit
*/
Show_time ();
Echo <<
Legend:
");
/* print "Formats
";
foreach ($exc->format as $value) {
printf ("(%x)", Array_search ($value, $exc->format));
Print htmlentities ($value, ent_quotes);
Print "
";
}
Print "XFs
";
for ($i =0; $i xf[' format '); $i + +) {
printf ("(%x)", $i);
printf ("Format (%x) font (%x)", $exc->xf[' format '] [$i], $exc->xf[' font '] [$i]);
Print "
";
}
*/
}
The results are as follows:
The second step is to read the data out, the code is as follows:
Copy CodeThe code is as follows:
if ($_post["action"]== "Do")
{
Working with Data
Read Header Records first
$excel _file=$_post["Excel_file"];
$fh = @fopen ($excel _file, ' RB ');
$FC = Fread ($fh, FileSize ($excel _file));
@fclose ($FH);
Echo ("Execute". $excel _file);
$exc = new Excelfileparser ("Debug.log", Abc_no_log);//abc_no_log abc_var_dump);
Echo ($excel _file. "|");
$style = $_post[' style '];
if ($style = = ' old ')
{
$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");
$time _start = Getmicrotime ();
$res = $exc->parsefromstring ($FC);
$time _end = Getmicrotime ();
}
ElseIf ($style = = ' segment ')
{
$time _start = Getmicrotime ();
$res = $exc->parsefromfile ($excel _file);
$time _end = Getmicrotime ();
}
Switch ($res) {
Case 0:break;
Case 1:fatal ("Can ' t Open file");
Case 2:fatal ("file too small to is an Excel File");
Case 3:fatal ("Error reading file header");
Case 4:fatal ("Error reading file");
Case 5:fatal ("This is not a 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");
}
And after reading, if there is no error, you can cycle to the MSSQL to add data!
for ($ws _num=0; $ws _num worksheet[' name ']); $ws _num++)
{
Print "
Worksheet: \ "";
if ($exc->worksheet[' Unicode '] [$ws _num]) {
Print uc2html ($exc->worksheet[' name ' [$ws _num]);
} else
Print $exc->worksheet[' name ' [$ws _num];
//
print "\"";
$ws = $exc->worksheet[' data ' [$ws _num];
//
//
Print " \ n ";
$fkhxmnum = 0;
$fsfzhnum = 0;
$fyddhnum = 0;
$ftxdznum = 0;
$femailnum = 0;
$flxdhnum = 0;
$fkhahnum = 0;
$fbzxxnum = 0;
for ($j =0; $j <= $ws [' Max_col ']; $j + +) {
Print "";
if ($j >25) print chr ((int) ($J/26) +64);
Read column names first
$tmpcolum =trim ($_post["$j"]);
Echo ($tmpcolum. "|");
if ($tmpcolum = = "Fkhxm") $fkhxmnum = $j;
if ($tmpcolum = = "Fsfzh") $fsfzhnum = $j;
if ($tmpcolum = = "Fyddh") $fyddhnum = $j;
if ($tmpcolum = = "Ftxdz") $ftxdznum = $j;
if ($tmpcolum = = "FEMAIL") $femailnum = $j;
if ($tmpcolum = = "Flxdh") $flxdhnum = $j;
if ($tmpcolum = = "Fkhah") $fkhahnum = $j;
if ($tmpcolum = = "Fbzxx") $fbzxxnum = $j;
}
for ($i =0; $i <= $ws [' Max_row ']; $i + +) {
$FKHXM =
Echo ($fkhxmnum. $fsfzhnum $fyddhnum. $ftxdznum. $femailnum. $flxdhnum $fkhahnum. $fbzxxnum);
Print "". ($i + 1). "\ n ";
if (Isset ($ws [' cell '] [$i]) && is_array ($ws [' cell '] [$i]) {
if ($fkhxmnum!=0&& $ftxdznum!=0&& ($fyddhnum!=0| | $flxdhnum!=0))//Please specify the fields that are not empty as required here
{
$sql = "INSERT into K_qlkhxx (FKHXM,FSFZH,FYDDH,FTXDZ,FEMAIL,FLXDH, $fkhah, FBZXX,FGLRY,FGLRYXM,FDJRY,FFZDM) VALUES ('" . uc2html ($exc->sst[' data '] [$WS [' Cell '] [$i] [$fkhxmnum] [' data ']]. "', '". $exc->sst[' data ' [$WS [' Cell '] [$i] [ $fsfzhnum [' data ']]. "', '". $exc->sst[' data ' [$WS [' Cell '] [$i] [$fyddhnum] [' data ']. uc2html ($exc->sst[ ' Data '] [$WS [' Cell '] [$i] [$ftxdznum] [' data ']]. "', '". uc2html ($exc->sst[' data '] [$WS [' Cell '] [$i] [$femailnum] [' Data ']). "', '". $exc->sst[' data ' [$WS [' Cell '] [$i] [$flxdhnum] [' Data ']. "', '". uc2html ($exc->sst[' data '] [$WS [' Cell '] [$i] [$fkhahnum] [' Data ']]. "', '". uc2html ($exc->sst[' data '] [$WS [' Cell '] [$i] [$fbzxxnum] [' data ']]. "', '". $_session["UYHMC". " , ' ". $_session[" UYHXM "]." ', ' ". $_session[" UYHMC "]." ', ' ". $_session[" UBM "]." ') ";
Echo ($sql. "
");
}
$conn->query ($sql);
}
}
}
Import Complete Delete file
Unlink ($filename);
}
You notice no, I put the execution of the line commented out, as long as the removal of comments can be executed correctly!
The code used to package the download Http://xiazai.jb51.net/201002/yuanma/php_excel_mysql.rar
http://www.bkjia.com/PHPjc/321150.html www.bkjia.com true http://www.bkjia.com/PHPjc/321150.html techarticle First , to clean up the idea first, ~ ~ First: You need to upload the file to the server and then: Read the Excel data column display and then: Let the user select the corresponding relationship of the field then: Submit data, read the word ...
