Drupal reads Excel and imports database instance_PHP tutorial

PHPExcel is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, etc.

1. Drupal calls PHPExcel through Library
After downloading PHPExcel, upload it to the Drupal directory: sites/all/libraries/PHPExcel
If the libraries module is installed in your project, you can Called through libraries_load($name);.
If the libraries module is not installed, you can simply use the following code to call:

Copy the code The code is as follows:

require(" sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php");

Note that in order to ensure that Excel is fully imported, the program may take a long time to complete. So add at the beginning of the code:

Copy the code The code is as follows:

set_time_limit(0);

to ensure operation There is no time limit.
2. Drupal reads Excel and imports it into the database
Drupal reads the Excel content, writes it to the database, and prints the import result message after uploading the Excel file.
To sum up, the following points are as follows:
1. Drupal reads Excel multi-row and multi-column content, the number of columns is from 1 to n, and the number of rows is also 1 to n.
2.Drupal n fields are used to store Excel columns 1 to n according to the database structure. If Excel has many columns, the n column values ​​can be stored in 1 field.
3. What I solve here is to store Excel n column values ​​into MySQL n fields (n is not very large)

This is the function after the uploaded file is finally submitted to Drupal:

Copy the code The code is as follows:

function excel_upload_form_submit($form, &$form_state) {
set_time_limit(0);
$timestamp = time();
// Make sure the Excel file is uploaded
if ($file = file_save_upload('file')) {
$row = 0; //Number of parsed rows
$paseRows = 0; //Skip rows with no value
$ insertRows = 0; //Number of inserted rows
$table = array(
'dbfield1′,
'dbfield2',
'dbfield3,
'dbfield4′,
'dbfield5' ,
…
'dbfieldn',
);
require("sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php");
if(($handle = fopen ( $file->filepath, "r" )) !== FALSE) {
$PHPExcel = new PHPExcel ();
$PHPReader = new PHPExcel_Reader_Excel2007 ();
if (! $PHPReader-> ;canRead ( $file->filepath )) {
$PHPReader = new PHPExcel_Reader_Excel5 ();
if (! $PHPReader->canRead ( $file->filepath )) {
echo ' no Excel';
           return; ( 0 );
/**Get how many columns there are in total*/
$allColumn = $currentSheet->getHighestColumn();
//Get the total number of columns. If you don’t use this static method, the $ col is the largest English capital letter of the file column
$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn());
/**Get the total number of rows*/
$allRow = $currentSheet ->getHighestRow();
//Loop to read the contents of each cell.Note that rows start from 1 and columns start from A
for($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {
$token_db = $row_db = $field = array();
           $i = 0;
             //$cell = $currentSheet->getCell($addr)->getValue();
                                                         ;
$cell = trim($cell);
if($cell instanceof PHPExcel_RichText) {
//Rich text conversion string
$cell = $cell->__toString();
                                                                                              $field[] = $table[ $i];
                       $token_db[] = "'%s'";              , ";
                                                               field ) .', created) VALUES('. implode(', ', $token_db) .', %d)', array_merge($row_db, array($timestamp)));
               $insertRows++;
                                                                                                                                                                                                                  . ; ");
}
else {
drupal_set_message(t('File to import not found.'), 'error');
$form_state['redirect'] = 'admin/content/ db/import';
return;
}
}
?>


Pay attention to a few points in the above code part:



Copy the code

The code is as follows:


$allColumn = $currentSheet-> ;getHighestColumn(); //The obtained column is the array index of English uppercase letters.
$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); //Format the English uppercase letter index into a number, and the index value starts from 0.


This code supports reading Excel 2007 and previous formats.

http://www.bkjia.com/PHPjc/736822.html

www.bkjia.com

truehttp: //www.bkjia.com/PHPjc/736822.html

TechArticle

PHPExcel is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Exc...