ExcelFileParser can be used to import data in excel to the database in batches _ PHP Tutorial

ExcelFileParser can be used to import data in excel to the database in batches. ExcelFileParser can be used to import data in excel to the database in batches and submit the form! The DOCTYPEhtmlPUBLIC-W3CDTDXHTML1.0TransitionalENwww.w3.orgTRxhtml1DTDxhtml ExcelFileParser processes excel to obtain data that can be imported to the database in batches

Submit form

Excel data acquisition demonstration

Excel data acquisition demonstration

Submit for processing
[Php]
/**
* CopyRight (c) 2009,
* All rights reserved.
* File name:
* Abstract:
*
* @ Author drawing eight ixqbar@hotmail.com
* @ Version
*/

Class IndexAction extends Action
{
/**
* Constructor
*/
Public function _ construct ()
{
Parent: :__ construct ();
}
/**
* Default index page
*/
Public function index ()
{
$ This-> display ();
}
/**
* Submit for processing
*/
Public function parse ()
{
/**
* $ _ FILES array description
* Array (n ){
* ["Form file box name"] => array (5 ){
* ["Name"] => name of the submitted file
* ["Type"] => The submitted file type is "application/vnd. ms-Excel"
* ["Tmp_name"] => temporary file name
* ["Error"] => error (0 Success 1. the file size exceeds the value of upload_max_filesize2. the file size exceeds that of MAX_FILE3. the file size is incomplete. 4. the file is not uploaded)
* ["Size"] => file size (unit: KB)
*}
*}
*/
$ Return = array (0 ,'');
/**
* Determine whether to submit
* Is_uploaded_file (file name) is used to determine whether the specified file is uploaded using the POST method to prevent unauthorized submission. it is usually used together with move_upload_file to save the uploaded file to the specified path.
*/
If (! Isset ($ _ FILES) |! Is_uploaded_file ($ _ FILES ['Excel '] ['tmp _ name'])
{
$ Return = array (1, 'illegal submission ');
}
// Process
If (0 = $ return [0])
{
Import ('@. Util. ExcelParser ');
$ Excel = new ExcelParser ($ _ FILES ['Excel '] ['tmp _ name']);
$ Return = $ excel-> main ();
}
// Output processing
Print_r ($ return );
}
}
?>
[/Php]

Processing class
[Php]
/**
* CopyRight (c) 2009,
* All rights reserved.
* File name: excel data acquisition
* Abstract:
*
* @ Author drawing eight ixqbar@hotmail.com
* @ Version 0.1
*/
Class ExcelParser
{
Private $ _ data = array (0 ,'');
Private $ _ excel_handle;
Private $ _ excel = array ();
/**
* Constructor
* @ Param $ Filename: name of the temporary file to be uploaded
*/
Public function _ construct ($ filename)
{
/**
* Introduce the excelparser class
* The common method is
* Requires path. 'excelparser. php ';
* Import is a built-in import class method for ThinkPHP.
*/
Import ('@. Util. PHPExcelParser. excelparser', '', '. php ');
$ This-> _ excel_handle = new ExcelFileParser ();
// Error retrieval
$ This-> checkErrors ($ filename );
}
/**
* Error verification
*/
Private function checkErrors ($ filename)
{
/**
* Method 1
*/
$ Error_code = $ this-> _ excel_handle-> ParseFromFile ($ filename );
/**
* Method 2
* $ File_handle = fopen ($ this-> _ filename, 'RB ');
* $ Content = fread ($ file_handle, filesize ($ this-> _ filename ));
* Fclose ($ file_handle );
* $ Error_code = $ this-> _ excel-> ParseFromString ($ content );
* Unset ($ content, $ file_handle );
*/
Switch ($ error_code)
{
Case 0:
// Do not handle no errors
Break;
Case 1:
$ This-> _ data = array (1, 'File read error (Linux read/write permission )');
Break;
Case 2:
$ This-> _ data = array (1, 'File is too small ');
Break;
Case 3:
$ This-> _ data = array (1, 'failed to read the Excel header ');
Break;
Case 4:
$ This-> _ data = array (1, 'File read error ');
Break;
Case 5:
$ This-> _ data = array (1, 'File may be Null ');
Break;
Case 6:
$ This-> _ data = array (1, 'incomplete Files ');
Break;
Case 7:
$ This-> _ data = array (1, 'data reading error ');
Break;
Case 8:
$ This-> _ data = array (1, 'version error ');
Break;
}
Unset ($ error_code );
}
/**
* Obtain Excel information
*/
Private function getExcelInfo ()
{
If (1 = $ this-> _ data [0]) return;
/**
* Get the number of sheet
* Obtain the rows and columns corresponding to the sheet unit.
*/
$ This-> _ excel ['sheet _ number'] = count ($ this-> _ excel_handle-> worksheet ['name']);
For ($ I = 0; $ I <$ this-> _ excel ['sheet _ number']; $ I ++)
{
/**
* Row-on-column
* Note: the count starts from 0.
*/
$ Row = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['max _ row'];
$ Col = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['max _ col'];
$ This-> _ excel ['row _ number'] [$ I] = ($ row = NULL )? 0: ++ $ row;
$ This-> _ excel ['Col _ number'] [$ I] = ($ col = NULL )? 0: ++ $ col;
Unset ($ row, $ col );
}
}
/**
* Chinese processing functions
* @ Return
*/
Private function uc2html ($ str)
{
$ Ret = '';
For ($ I = 0; $ I {
$ Charcode = ord ($ str [$ I * 2]) + 256 * ord ($ str [$ I * 2 + 1]);
$ Ret. = '& #'. $ charcode .';';
}
Return mb_convert_encoding ($ ret, 'utf-8', 'HTML-ENTITIES ');
}
/**
* Excel data acquisition
*/
Private function getExcelData ()
{
If (1 = $ this-> _ data [0]) return;

// Modify the tag
$ This-> _ data [0] = 1;
// Obtain data
For ($ I = 0; $ I <$ this-> _ excel ['sheet _ number']; $ I ++)
{
/**
* Loop over rows
*/
For ($ j = 0; $ j <$ this-> _ excel ['row _ number'] [$ I]; $ j ++)
{
/**
* Column loop
*/
For ($ k = 0; $ k <$ this-> _ excel ['Col _ number'] [$ I]; $ k ++)
{
/**
* Array (4 ){
* ["Type"] => type [0 character type 1 integer 2 floating point 3 date]
* ["Font"] => font
* ["Data"] => data
*...
*}
*/
$ Data = $ this-> _ excel_handle-> worksheet ['data'] [$ I] ['cell '] [$ j] [$ k];
Switch ($ data ['type'])
{
Case 0:
// Character type
If ($ this-> _ excel_handle-> sst ['unicode '] [$ data ['data'])
{
// Chinese processing
$ Data ['data'] = $ this-> uc2html ($ this-> _ excel_handle-> sst ['data'] [$ data ['data']);
}
Else
{
$ Data ['data'] = $ this-> _ excel_handle-> sst ['data'] [$ data ['data'];
}
Break;
Case 1:
// Integer
// TODO
Break;
Case 2:
// Floating point number
// TODO
Break;
Case 3:
// Date
// TODO
Break;
}
$ This-> _ data [1] [$ I] [$ j] [$ k] = $ data ['data'];
Unset ($ data );
}
}
}
}
/**
* Main function
* @ Return array (identifier, content s)
*/
Public function main ()
{
// Obtain Excel information
$ This-> getExcelInfo ();
// Obtain Excel data
$ This-> getExcelData ();
Return $ this-> _ data;
}
}

?>

Batch can be used for batch import to database submission form! DOCTYPE html PUBLIC-// W3C // dtd xhtml 1.0 Transitional // EN http://www.w3.org/TR/xhtml1/DTD/xhtml...