Php batch import excel to database code-PHP source code

Source: Internet
Author: User
Php batch import excel to database code

Script ec (2); script

We used the extended ExcelFileParser to process it,





Excel Data Acquisition demonstration





Excel Data Acquisition demonstration








/**
* CopyRight (c) 2009,
* All rights reserved.
* File Name:
* Abstract:
*
* @ Author listen 8 [email = ixqbar@hotmail.com] ixqbar@hotmail.com [/email]
* @ 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 );
}
}
?>


/**
* CopyRight (c) 2009,
* All rights reserved.
* File name: excel Data Acquisition
* Abstract:
*
* @ Author listen 8 [email = ixqbar@hotmail.com] ixqbar@hotmail.com [/email]
* @ 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;
}
}

?>

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.