Php Imports excel data to mysql. Example: 1. import the excel file require_once (db. php) with only one sheet, reference the database instantiation class require_once (reader. php), and import the excel class $ datanewSpreadsheet instance to the application:
1. import an excel file with only one sheet
Require_once ("db. php"); // reference the database instantiation class
Require_once ("reader. php"); // The excel class imported by the application
$ Data = new Spreadsheet_Excel_Reader (); // instantiate the class
$ Data-> setOutputEncoding ('utf-8'); // sets the encoding.
$ Data-> read ($ _ FILES ["excel"] ["tmp_name"]); // read a temporary excel file
If ($ data-> sheets [0] ['numrows ']> 0) {// Determine whether the number of rows in excel is greater than 0. $ data-> sheet [0] ['numrows '] indicates the total number of rows in excel.
For ($ I = 2; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++) {// Insert execl data into the database $ I indicates reading from row $ I of excel
$ SQL = "insert into 'user' ('User _ name', 'phone', 'User _ email ', 'password') values (
'{$ Data-> sheets [0] ['cells'] [$ I] [1]} ', // $ I is the row number in excel
'{$ Data-> sheets [0] ['cells'] [$ I] [2]} ',
'{$ Data-> sheets [0] ['cells'] [$ I] [3]} ',
'123'
)";
$ Db-> query ($ SQL );
}
}
2. import an excel file with multiple sheets
In fact, it is the same as importing a sheet. if there are two sheets (and so on)
Require_once ("db. php"); // reference the database instantiation class
Require_once ("reader. php"); // The excel class imported by the application
$ Data = new Spreadsheet_Excel_Reader (); // instantiate the class
$ Data-> setOutputEncoding ('utf-8'); // sets the encoding.
$ Data-> read ($ _ FILES ["excel"] ["tmp_name"]); // read a temporary excel file
If ($ data-> sheets [0] ['numrows ']> 0) {// Determine whether the number of rows in excel is greater than 0. $ data-> sheet [0] ['numrows '] indicates the total number of rows in excel. here, $ data-> sheet [0] the first sheet in excel
For ($ I = 2; $ I <= $ data-> sheets [0] ['numrows ']; $ I ++) {// Insert execl data into the database $ I indicates reading from row $ I of excel
$ SQL = "insert into 'user' ('User _ name', 'phone', 'User _ email ', 'password') values (
'{$ Data-> sheets [0] ['cells'] [$ I] [1]} ', // $ I is the row number in excel
'{$ Data-> sheets [0] ['cells'] [$ I] [2]} ',
'{$ Data-> sheets [0] ['cells'] [$ I] [3]} ',
'123'
)";
$ Db-> query ($ SQL );
}
If ($ data-> sheets [1] ['numrows ']> 0) {// Determine whether the number of rows in excel is greater than 0. $ data-> sheet [0] ['numrows '] indicates the total number of rows in excel. here, $ data-> sheet [1] second sheets
For ($ I = 2; $ I <= $ data-> sheets [1] ['numrows ']; $ I ++) {// Insert execl data into the database $ I indicates reading from row $ I of excel
$ SQL = "insert into 'user' ('User _ name', 'phone', 'User _ email ', 'password') values (
'{$ Data-> sheets [1] ['cells'] [$ I] [1]} ', // $ I is the row number in excel
'{$ Data-> sheets [1] ['cells'] [$ I] [2]} ',
'{$ Data-> sheets [1] ['cells'] [$ I] [3]} ',
'123'
)";
$ Db-> query ($ SQL );
}
}
Lifecycle 1. import the excel file require_once (db. php); // reference the database instantiation class require_once (reader. php); // The excel import class $ data = new Spreadsheet...