How to import Excel data into MSSQL database under Linux _php tutorial

Source: Internet
Author: User
Tags fread unsupported
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 ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.