PHP PHPExcel_Reader_Excel2007::setLoadSheetsOnly方法代码示例

 /**
  * Export to the Excel template using the PHPExcel library
  *
  * @param \GIB\GradingTool\Domain\Model\Project $project
  */
 public function exportExcelGradingAction(\GIB\GradingTool\Domain\Model\Project $project)
 {
     // Write Grading results
     $grading = $this->submissionService->getProcessedSubmission($project);
     if ($grading['hasError']) {
         // Don't export the Grading if is has errors
         $message = new \TYPO3\Flow\Error\Message('The Grading has errors and therefore it cannot be exported. Review and correct the Grading.', \TYPO3\Flow\Error\Message::SEVERITY_ERROR);
         $this->flashMessageContainer->addMessage($message);
         $this->redirect('index', 'Standard');
     }
     // the uploaded export template
     $templateFilePathAndFileName = \TYPO3\Flow\Utility\Files::concatenatePaths(array($this->settings['export']['excel']['templatePath'], $this->settings['export']['excel']['templateFileName']));
     $excelReader = new \PHPExcel_Reader_Excel2007();
     $excelReader->setLoadSheetsOnly($this->settings['export']['excel']['worksheetLabel']);
     $phpExcel = $excelReader->load($templateFilePathAndFileName);
     $worksheet = $phpExcel->getSheetByName($this->settings['export']['excel']['worksheetLabel']);
     $firstSectionColumn = $this->settings['export']['excel']['firstSectionColumn'];
     // we need to subtract 1 because of https://github.com/PHPOffice/PHPExcel/issues/307
     $columnNumber = \PHPExcel_Cell::columnIndexFromString($firstSectionColumn) - 1;
     foreach ($grading['sections'] as $section) {
         $row = $this->settings['export']['excel']['sectionLabelFirstRow'];
         $column = \PHPExcel_Cell::stringFromColumnIndex($columnNumber);
         $worksheet->getCell($column . $row)->setValue($section['label']);
         foreach ($section['questions'] as $question) {
             $row++;
             if (isset($question['score'])) {
                 if ($question['score'] === 'N/A') {
                     // N/A is value 5 in Excel tool
                     $worksheet->getCell($column . $row)->setValue('5');
                 } else {
                     $worksheet->getCell($column . $row)->setValue($question['score']);
                 }
             }
         }
         $columnNumber++;
     }
     // Write project title
     $worksheet->getCell($this->settings['export']['excel']['projectTitleCell'])->setValue($project->getProjectTitle());
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="grading.xlsx"');
     header('Cache-Control: max-age=0');
     /** @var \PHPExcel_Writer_Excel2007 $excelWriter */
     $excelWriter = \PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
     $excelWriter->save('php://output');
 }

function oadueslookup_options()
{
    global $wpdb;
    $dbprefix = $wpdb->prefix . "oalm_";
    $hidden_field_name = 'oalm_submit_hidden';
    if (!current_user_can('manage_options')) {
        wp_die(__('You do not have sufficient permissions to access this page.'));
    }
    // =========================
    // form processing code here
    // =========================
    if (isset($_FILES['oalm_file'])) {
        #echo "<h3>Processing file upload</h3>";
        #echo "<strong>Processing File:</strong> " . esc_html($_FILES['oalm_file']['name']) . "<br>";
        #echo "<strong>Type:</strong> " . esc_html($_FILES['oalm_file']['type']) . "<br>";
        if (preg_match('/\\.xlsx$/', $_FILES['oalm_file']['name'])) {
            /** PHPExcel */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel.php';
            /** PHPExcel_Writer_Excel2007 */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel/Writer/Excel2007.php';
            $objReader = new PHPExcel_Reader_Excel2007();
            $objReader->setReadDataOnly(true);
            $objReader->setLoadSheetsOnly(array("All"));
            $objPHPExcel = $objReader->load($_FILES["oalm_file"]["tmp_name"]);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $columnMap = array('BSA ID' => 'bsaid', 'Dues Yr.' => 'max_dues_year', 'Dues Pd. Dt.' => 'dues_paid_date', 'Level' => 'level', 'Reg. Audit Date' => 'reg_audit_date', 'Reg. Audit Result' => 'reg_audit_result');
            $complete = 0;
            $recordcount = 0;
            $error_output = "";
            foreach ($objWorksheet->getRowIterator() as $row) {
                $rowData = array();
                if ($row->getRowIndex() == 1) {
                    # this is the header row, grab the headings
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $cellValue = $cell->getValue();
                        if (isset($columnMap[$cellValue])) {
                            $rowData[$columnMap[$cellValue]] = 1;
                            #echo "Found column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        } else {
                            #echo "Discarding unknown column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        }
                    }
                    $missingColumns = array();
                    foreach ($columnMap as $key => $value) {
                        if (!isset($rowData[$value])) {
                            $missingColumns[] = $key;
                        }
                    }
                    if ($missingColumns) {
                        ?>
<div class="error"><p><strong>Import failed.</strong></p><p>Missing required columns: <?php 
                        esc_html_e(implode(", ", $missingColumns));
                        ?>
</div><?php 
                        $complete = 1;
                        # Don't show "may have failed" box at the bottom
                        break;
                    } else {
                        #echo "<strong>Data format validated:</strong> Importing new data...<br>" . PHP_EOL;
                        # we just validated that we have a good data file, nuke the existing data
                        $wpdb->show_errors();
                        ob_start();
                        $wpdb->query("TRUNCATE TABLE {$dbprefix}dues_data");
                        update_option('oadueslookup_last_import', $wpdb->get_var("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')"));
                        # re-insert the test data
                        oadueslookup_insert_sample_data();
                        # now we're ready for the incoming from the rest of the file.
                    }
                } else {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $columnName = $objWorksheet->getCell($cell->getColumn() . "1")->getValue();
                        $value = "";
                        if ($columnName == "Dues Pd. Dt.") {
                            # this is a date field, and we have to work miracles to turn it into a mysql-compatible date
                            $date = $cell->getValue();
                            $dateint = intval($date);
                            $dateintVal = (int) $dateint;
                            $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                        } else {
                            if ($columnName == "Reg. Audit Date") {
                                # this is also a date field, but can be empty
                                $date = $cell->getValue();
                                if (!$date) {
                                    $value = get_option('oadueslookup_last_import');
                                } else {
                                    $dateint = intval($date);
                                    $dateintVal = (int) $dateint;
                                    $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                                }
                            } else {
                                $value = $cell->getValue();
                            }
                        }
                        if (isset($columnMap[$columnName])) {
                            $rowData[$columnMap[$columnName]] = $value;
                        }
//.........这里部分代码省略.........

 public function uploadAction()
 {
     //     $data = $this->getRequest()->getPost();
     $file = $_FILES['xls'];
     if ($file['error'] == 4) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Please choose file to upload.'));
         $this->_redirect('*/*/');
         return;
     } elseif ($file['error']) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Upload error. Please try again.'));
         $this->_redirect('*/*/');
         return;
     } elseif (!preg_match('/\\.xlsx{0,1}$/', $file['name'])) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('You can only use XLS or XLSX files.'));
         $this->_redirect('*/*/');
         return;
     }
     $name = $file['name'];
     $ext = preg_match('/xlsx$/', $name) ? 'xlsx' : 'xls';
     $xls_file_name = Mage::getBaseDir() . "/custom/add_products/xls/import.{$ext}";
     @unlink($xls_file_name);
     move_uploaded_file($file['tmp_name'], $xls_file_name);
     try {
         require_once Mage::getBaseDir() . "/custom/include/PHPExcel/PHPExcel.php";
         $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
         $cacheSettings = array('cacheTime' => 6000);
         PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
         if ($ext == 'xls') {
             $objReader = new PHPExcel_Reader_Excel5();
         } else {
             $objReader = new PHPExcel_Reader_Excel2007();
         }
         $objReader->setReadDataOnly(true);
         $names = $objReader->listWorksheetNames($xls_file_name);
         // ww($CUSTOM);
         $DIR = Mage::getBaseDir() . "/custom/add_products/";
         $files = array();
         foreach ($names as $key => $name) {
             $name = strtolower($name);
             $name = preg_replace('/\\d$/', '', $name);
             $files[$name] = $name;
         }
         foreach ($files as $file) {
             @unlink($DIR . 'csv/' . $file . '.csv');
         }
         $i = 0;
         foreach ($names as $key => $name) {
             $file_name = strtolower($name);
             $file_name = preg_replace('/\\d$/', '', $file_name);
             $i++;
             $file_path = $DIR . 'csv/' . $file_name . '.csv';
             $skip_first_row = false;
             if (file_exists($file_path)) {
                 //         wlog("Appending: $file_name.csv", false);
                 $skip_first_row = true;
             }
             /* else wlog("Creating: $file_name.csv", false);*/
             $fp = fopen($file_path, 'a');
             $objReader->setLoadSheetsOnly(array($name));
             $objPHPExcel = $objReader->load($xls_file_name);
             $objWorksheet = $objPHPExcel->getActiveSheet();
             $j = 0;
             foreach ($objWorksheet->getRowIterator() as $row) {
                 $j++;
                 if ($j == 1 && $skip_first_row) {
                     continue;
                 }
                 $cellIterator = $row->getCellIterator();
                 $cellIterator->setIterateOnlyExistingCells(false);
                 $fields = array();
                 foreach ($cellIterator as $cell) {
                     $value = $cell->getValue();
                     $fields[] = $value;
                 }
                 fputcsv($fp, $fields);
             }
             fclose($fp);
         }
     } catch (Exception $e) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Error processing file.<br/>%s', $e->getMessage()));
         $this->_redirect('*/*/');
         return;
     }
     Mage::getSingleton('core/session')->addSuccess(Mage::helper('maxfurniture')->__('File uploaded and conferted to CSV files successfully.'));
     $this->_redirect('*/*/');
 }

        break;
    default:
        foreach ($files as $key => $filename) {
            printf("%2d. {$filename}\n", $key + 1, $filename);
        }
        $choice = readline("Sélection du fichier à importer: ");
        if (!array_key_exists(--$choice, $files)) {
            exit_error("Fichier non trouvé");
        }
        $filename = $files[$choice];
        echo "\n";
}
echo "Importation de '{$filename}'\n";
// Initialize Excel Reader
$xlReader = new PHPExcel_Reader_Excel2007();
$xlReader->setLoadSheetsOnly(IMPORT_COTI_SHEET_NAME)->setReadDataOnly(true);
// Retrieve header row
$xlReader->setReadFilter(new ReadFilterFirstRow());
$xl = $xlReader->load($filename);
$xlWS = $xl->getSheet(0);
$headers = array();
foreach ($xlWS->getRowIterator(1)->current()->getCellIterator() as $cell) {
    $headers[$cell->getColumn()] = $cell->getValue();
}
// Read the rest of the data file
try {
    $xlReader->setReadFilter(new ReadFilterEmailCoti($headers));
} catch (ColumnNotDefinedException $e) {
    print "ERREUR: " . $e->getMessage() . "\n";
    die(1);
}