_php instance of method to import Excel data into MSSQL database under Linux

Source: Internet
Author: User
Tags chr float number fread mssql rar strlen unsupported
First, clean up the idea first, ~ ~
First: We need to upload the file to the server.
Then: Read Excel data columns to display
Then: Let the user select the corresponding relationship of the field
Then: Submit the data, read the corresponding relationship of the field
Last: Bulk import data, delete temporary files
Altogether is the above five steps! Let's step through the analysis ~ ~ ~
The first step: Download the attachment of the Phpexcelparser4.rar, this file is uploaded to the Excel server and the Web display! 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 Code code 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 "Directory writable!";
}
if (Move_uploaded_file ($_files[' excel_file '] [' tmp_name '], $uploadservername))
{
Echo ("Upload succeeded");
}
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 being an Excel file");
Case 3:fatal ("Error reading file header");
Case 4:fatal ("Error reading file");
Case 5:fatal (' This is ' not ' 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 ' <pre> ';
Print_r ($EXC);
print ' </pre> ';
Exit
*/

Show_time ();

Echo <<<leg
<b>Legend:</b><br><br>
<form name= ' doform ' action= ' method= ' post ' >
<input type= ' hidden ' name= ' action ' value= ' do ' >
<input type= ' hidden ' name= ' excel_file ' value= $excel _file>
<input type= ' hidden ' name= ' style ' value= $style >
<table border=1 cellspacing=0 cellpadding=0>
<tr><td>data type</td><td>description</td></tr>
&LT;TR&GT;&LT;TD class=empty> </td><td Class=index>an empty cell</td></tr>
&LT;TR&GT;&LT;TD CLASS=DT_STRING&GT;ABCABC&LT;/TD&GT;&LT;TD class=index>string</td></tr>
&LT;TR&GT;&LT;TD CLASS=DT_INT&GT;12345&LT;/TD&GT;&LT;TD class=index>integer</td></tr>
&LT;TR&GT;&LT;TD CLASS=DT_FLOAT&GT;123.45&LT;/TD&GT;&LT;TD class=index>float</td></tr>
&LT;TR&GT;&LT;TD CLASS=DT_DATE&GT;123.45&LT;/TD&GT;&LT;TD class=index>date</td></tr>
<table>
<br><br>

LEG;
/*
print "<pre>";
Print_r ($exc->worksheet);
Print_r ($exc->sst);
print "</pre>";
*/
For ($ws _num=0 $ws _num<count ($exc->worksheet[' name '); $ws _num++)
{
print "<b>worksheet: \" ";
if ($exc->worksheet[' Unicode '] [$ws _num]) {
Print uc2html ($exc->worksheet[' name ' [$ws _num]);
} else
Print $exc->worksheet[' name ' [$ws _num];

print "\" </b> ";
$ws = $exc->worksheet[' data '] [$ws _num];

if (Is_array ($WS) &&
Isset ($ws [' Max_row ']) && isset ($ws [' Max_col '])) {
echo "\n<br><br><table border=1 cellspacing=0 cellpadding=2>\n";

Print "<tr><td> </td>\n";
For ($j =0 $j <= $ws [' Max_col ']; $j + +) {
Print "<td class=index>";
if ($j >25) print chr ((int) ($J/26) +64);
Here to display a drop-down list to display the data
Note that the circular data <br/>
Echo ("\n<select name= '". $j. "' > ");
Echo ("\n<option value= ' 0 ' > do not select </option>");
Echo ("\n<option value= ' FKHXM ' > Customer name </option>");
Echo ("\n<option value= ' Fsfzh ' > Identification number </option>");
Echo ("\n<option value= ' Fyddh ' > Mobile </option>");
Echo ("\n<option value= ' Ftxdz ' > Communication address </option>");
Echo ("\n<option value= ' FEMAIL ' >Email</option>");
Echo ("\n<option value= ' FLXDH ' > Tel </option>");
Echo ("\n<option value= ' Fkhah ' > Customer Love </option>");
Echo ("\n<option value= ' fbzxx ' > Memo info </option>");
Echo ("</select>");
print "</td>";
}

Print "<tr><td> </td>\n";
For ($j =0 $j <= $ws [' Max_col ']; $j + +) {
Print "<td class=index>";
if ($j >25) print chr ((int) ($J/26) +64);
Print Chr ($j% 26) + 65). "Column name </td>";
}

Table Header Output Complete
if ($ws [' Max_row ']>9)
{
$shownum = 9;
}
Else
{
$shownum = $ws [' max_row '];//output only the first 10 data
}
for ($i =0; $i <= $shownum; $i + +) {
Print "<tr><td class=index>". ($i + 1). " </td>\n ";
if (Isset ($ws [' cell '] [$i]) && is_array ($ws [' cell '] [$i])] {
For ($j =0 $j <= $ws [' Max_col ']; $j + +) {

if (Is_array ($ws [' cell '] [$i]) &&
(Isset ($ws [' cell '] [$i] [$j])
){

Print cell data
Print "<td class=\" ";
$data = $ws [' cell '] [$i] [$j];

$font = $ws [' cell '] [$i] [$j] [' Font '];
$style = "style = '". Excelfont::exceltocss ($exc->fonts[$font]). "'";

Switch ($data [' type ']) {
String
Case 0:
Print "dt_string\" ". $style." > ";
$ind = $data [' Data '];
if ($exc->sst[' Unicode '] [$ind]) {
$s = uc2html ($exc->sst[' data '] [$ind]);
} else
$s = $exc->sst[' data '] [$ind];
if (strlen ($s)) ==0)
Print "";
Else
Print $s;
Break
Integer number
Case 1:
Print "dt_int\" ". $style." > ";
Print $data [' data '];
Break
Float number
Case 2:
Print "Dt_float\" ". $style." > ";
echo $data [' data '];
Break
Date
Case 3:
Print "Dt_date\" ". $style." > ";

$ret = $data [Data];//str_replace ("00:00:00", "" ", Gmdate (" D-m-y h:i:s ", $exc->xls2tstamp ($data [data]));
Echo ($ret);
Break
Default
Print "dt_unknown\" ". $style." > ";
Break
}
print "</td>\n";
} else {
Print "<td class=empty> </td>\n";
}
}
} else {
Print an empty row
for ($j =0; $j <= $ws [' Max_col ']; $j + +)
Print "<td class=empty> </td>";
print "\ n";
}
print "</tr>\n";
}

echo "</table><br>\n";
} else {
EMTPTY Worksheet
Print "<b>-empty</b><br>\n";
}
print "<br>";

}
Echo ("<input type= ' submit ' name= ' Submit ' value= ' convert '/>");
Echo ("</form>");
/* print "formats<br>";
foreach ($exc->format as $value) {
printf ("(%x)", Array_search ($value, $exc->format));
Print htmlentities ($value, ent_quotes);
print "<br>";
}

print "xfs<br>";
For ($i =0 $i <count ($exc->xf[' format '); $i + +) {
printf ("(%x)", $i);
printf ("Format (%x) font (%x)", $exc->xf[' format '] [$i], $exc->xf[' font ' [$i]);

print "<br>";
}
*/
}

The operation effect is as follows:

The second step is to read the data out, the code is as follows:
Copy Code code as follows:

if ($_post["action"]== "Do")
{
Working with Data
Read the header record 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 being an Excel file");
Case 3:fatal ("Error reading file header");
Case 4:fatal ("Error reading file");
Case 5:fatal (' This is ' not ' 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 read finished, if there is no error can be recycled to the MSSQL to add data!
For ($ws _num=0 $ws _num<count ($exc->worksheet[' name '); $ws _num++)
{
print "<b>worksheet: \" ";
if ($exc->worksheet[' Unicode '] [$ws _num]) {
Print uc2html ($exc->worksheet[' name ' [$ws _num]);
} else
Print $exc->worksheet[' name ' [$ws _num];
//
print "\" </b> ";
$ws = $exc->worksheet[' data '] [$ws _num];
//
//
Print "<tr><td> </td>\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 "<td class=index>";
if ($j >25) print chr ((int) ($J/26) +64);
Read the column name 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 "<tr><td class=index>". ($i + 1). " </td>\n ";
if (Isset ($ws [' cell '] [$i]) && is_array ($ws [' cell '] [$i])] {
if ($fkhxmnum!=0&& $ftxdznum!=0&& ($fyddhnum!=0| | $flxdhnum!=0))//Please specify here the required fields that are not empty
{
$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.) <br> ");
}
$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 annotation can be done correctly!
The code used to package the download Http://xiazai.jb51.net/201002/yuanma/php_excel_mysql.rar
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.