How to import excel Data to the mssql database in Linux

First, clear your ideas. First ,~~
First, you need to upload the file to the server.
Then: Read the excel Data column and display it
Then, let the user select the corresponding field relationship.
Then: submit the data and read the correspondence between fields.
Finally, import data in batches and delete temporary files.
The above five steps are in total! Step by step analysis ~~~
Step 1: Download phpexcelparser4.rar in the attachment. This file is uploaded to the excel stealing server and displayed on the web! This is generally okay! The problem is that the program saves the table as a temporary table but does not actually save it. Therefore, you must first change the program code 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 cannot be written! "; Exit;
}
Else
{
Echo "Directory Writable! ";
}
If (move_uploaded_file ($ _ FILES ['excel _ file'] ['tmp _ name'], $ uploadservername ))
{
Echo ("uploaded successfully ");
}
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 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 already upted ");
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>
<Form name = 'terraform 'Action = ''method = 'post'>
<Input type = 'did' name = 'action' value = 'do '>
<Input type = 'den den 'name = 'excel _ file' value = $ excel_file>
<Input type = 'siden' name = 'style' value = $ style>
<Table border = 1 cellspacing = 0 cellpadding = 0>
<Tr> <td> Data type </td> <td> Description </td> </tr>
<Tr> <td class = empty> </td> <td class = index> An empty cell </td> </tr>
<Tr> <td class = dt_string> ABCabc </td> <td class = index> String </td> </tr>
<Tr> <td class = dt_int> 12345 </td> <td class = index> Integer </td> </tr>
<Tr> <td class = dt_float> 123.45 </td> <td class = index> Float </td> </tr>
<Tr> <td class = dt_date> 123.45 </td> <td class = index> Date </td> </tr>
<Table>
<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> <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 );
// A drop-down list is displayed to display data.
// Cyclic data <br/>
Echo ("\ n <select name = '". $ j. "'> ");
Echo ("\ n <option value = '0'> not selected </option> ");
Echo ("\ n <option value = 'fkhxm '> customer name </option> ");
Echo ("\ n <option value = 'fsfzh '> ID card number </option> ");
Echo ("\ n <option value = 'fyddh'> mobile phone number </option> ");
Echo ("\ n <option value = 'ftxdz'> communication address </option> ");
Echo ("\ n <option value = 'femail'> Email </option> ");
Echo ("\ n <option value = 'flxdh '> contact number </option> ");
Echo ("\ n <option value = 'fkhah'> customer hobbies </option> ");
Echo ("\ n <option value = 'fbzxx'> remarks </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> ";
}

// The output of the header is complete.
If ($ ws ['max _ row']> 9)
{
$ Shownum = 9;
}
Else
{
$ Shownum = $ ws ['max _ row']; // only the first 10 data records are output.
}
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 (trim ($ 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 = 'convertible '/> ");
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 running effect is as follows:

The second step is to read the data. The Code is as follows:Copy codeThe Code is as follows: if ($ _ POST ["action"] = "do ")
{
// Process 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 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 already upted ");
Case 7: fatal ("No Excel data found in file ");
Case 8: fatal ("Unsupported file version ");

Default:
Fatal ("Unknown error ");
}
// After reading, you can add data to MSSQL cyclically if there is no error!
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) // specify a required non-empty field 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. "<br> ");
}
// $ Conn-> Query ($ SQL );
}
}
}
// The file is deleted after the import.
Unlink ($ filename );

}

You do not know. I have commented out the line for execution. You only need to remove the comment and you will be able to correctly execute it!
The code used to package download http://xiazai.jb51.net/201002/yuanma/php_excel_mysql.rar