/** * 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; } } if ($wpdb->insert($dbprefix . "dues_data", $rowData, array('%s', '%s', '%s', '%s', '%s'))) { $recordcount++; } } } $error_output = ob_get_clean(); if (!$error_output) { ?> <div class="updated"><p><strong>Import successful. Imported <?php esc_html_e($recordcount); ?> records.</strong></p></div><?php } else { ?> <div class="error"><p><strong>Import partially successful. Imported <?php esc_html_e($recordcount); ?> of <?php esc_html_e($row->getRowIndex() - 2); ?> records.</strong></p> <p>Errors follow:</p> <?php echo $error_output; ?> </div><?php } update_option('oadueslookup_last_update', $wpdb->get_var("SELECT DATE_FORMAT(MAX(dues_paid_date), '%Y-%m-%d') FROM {$dbprefix}dues_data")); } else { ?> <div class="error"><p><strong>Invalid file upload.</strong> Not an XLSX file.</p></div><?php } } // // HANDLE SETTINGS SCREEN UPDATES // if (isset($_POST[$hidden_field_name]) && $_POST[$hidden_field_name] == 'oadueslookup-settings') { $slug = $_POST['oadueslookup_slug']; $dues_url = $_POST['oadueslookup_dues_url']; $dues_register = $_POST['oadueslookup_dues_register']; $dues_register_msg = $_POST['oadueslookup_dues_register_msg']; $update_url = $_POST['oadueslookup_update_url']; $update_link_text = $_POST['oadueslookup_update_option_link_text']; $update_option_text = $_POST['oadueslookup_update_option_text']; $help_email = $_POST['oadueslookup_help_email']; # $help_email is the only one that throws an error if it doesn't # validate. The others we just silently fix so they're something # valid. The user will see the result on the form. if (!is_email($help_email)) { ?> <div class="error"><p><strong>'<?php esc_html_e($help_email); ?> ' is not a valid email address.</strong></p></div><?php } else { $foundchanges = 0; $slug = sanitize_title($slug); if ($slug != get_option('oadueslookup_slug')) { update_option('oadueslookup_slug', $slug); $foundchanges = 1; } $dues_url = esc_url_raw($dues_url); if ($dues_url != get_option('oadueslookup_dues_url')) { update_option('oadueslookup_dues_url', $dues_url); $foundchanges = 1; } if ($dues_register != get_option('oadueslookup_dues_register')) { update_option('oadueslookup_dues_register', $dues_register); $foundchanges = 1; } $dues_register_msg = sanitize_text_field($dues_register_msg); if ($dues_register_msg != get_option('oadueslookup_dues_register_msg')) { update_option('oadueslookup_dues_register_msg', $dues_register_msg); $foundchanges = 1; } $update_url = esc_url_raw($update_url); if ($update_url != get_option('oadueslookup_update_url')) { update_option('oadueslookup_update_url', $update_url); $foundchanges = 1; } $update_link_text = sanitize_text_field($update_link_text); if ($update_link_text != get_option('oadueslookup_update_option_link_text')) { update_option('oadueslookup_update_option_link_text', $update_link_text); $foundchanges = 1; } $update_option_text = sanitize_text_field($update_option_text); if ($update_option_text != get_option('oadueslookup_update_option_text')) { update_option('oadueslookup_update_option_text', $update_option_text); $foundchanges = 1; } $help_email = sanitize_email($help_email); if ($help_email != get_option('oadueslookup_help_email')) { update_option('oadueslookup_help_email', $help_email); $foundchanges = 1; } if ($foundchanges) { ?> <div class="updated"><p><strong>Changes saved.</strong></p></div><?php } } } // ============================ // screens and forms start here // ============================ // // MAIN SETTINGS SCREEN // echo '<div class="wrap">'; // header echo "<h2>" . __('OA Dues Lookup Settings', 'oadueslookup') . "</h2>"; // settings form ?> <h3 class="oalm">Import data from OALM</h3> <p>Export file from OALM Must contain at least the following columns:<br> BSA ID, Dues Yr., Dues Pd. Dt., Level, Reg. Audit Date, Reg. Audit Result<br> Any additional columns will be ignored.</p> <p><a href="http://github.com/justdave/oadueslookup/wiki">How to create the export file in OALM</a></p> <form action="" method="post" enctype="multipart/form-data"> <label for="oalm_file">Click Browse, then select the xlsx file exported from OALM's "Export Members", then click "Upload":</label><br> <input type="file" name="oalm_file" id="oalm_file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"> <input type="submit" class="button button-primary" name="submit" value="Upload"><br> <p><strong>Last import:</strong> <?php $last_import = get_option('oadueslookup_last_import'); if ($last_import == '1900-01-01') { echo "Never"; } else { esc_html_e($last_import); } ?> </p> </form> <h3 class="oalm">Lookup Page Settings</h3> <form name="oadueslookup-settings" method="post" action=""> <input type="hidden" name="<?php echo $hidden_field_name; ?> " value="oadueslookup-settings"> <table class="form-table"> <tbody> <tr> <th scope="row"><label for="oadueslookup_slug">Dues Page Slug</label></th> <td><code><?php echo esc_html(get_option("home")); ?> /</code><input id="oadueslookup_slug" name="oadueslookup_slug" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_slug")); ?> "> <p class="description">The name appended to your Site URL to reach the lookup page.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_url">Dues Payment URL</label></th> <td><input id="oadueslookup_dues_url" name="oadueslookup_dues_url" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_dues_url")); ?> "> <p class="description">The URL to send members to for actually paying their dues.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_register">Registration Required?</label></th> <td><input id="oadueslookup_dues_register" name="oadueslookup_dues_register" class="code" type="checkbox" value="1"<?php checked(1 == esc_html(get_option('oadueslookup_dues_register'))); ?> "> <p class="description">Does the dues payment site require the user to register before paying?</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_register_msg">Registration Required Message</label></th> <td><input id="oadueslookup_dues_register_msg" name="oadueslookup_dues_register_msg" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_dues_register_msg")); ?> "> <p class="description">The instruction text to display informing the user that they need to register before paying dues.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_url">Update Contact Info URL</label></th> <td><input id="oadueslookup_update_url" name="oadueslookup_update_url" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_url")); ?> "> <p class="description">The URL to send members to for updating their contact information.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_option_link_text">Update Contact Link Info Text</label></th> <td><input id="oadueslookup_update_option_link_text" name="oadueslookup_update_option_link_text" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_option_link_text")); ?> "> <p class="description">The text to appear in the hyperlink to the Update Contact Information URL.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_option_text">Section Label</label></th> <td><input id="oadueslookup_update_option_text" name="oadueslookup_update_option_text" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_option_text")); ?> "> <p class="description">The label or option on the Update Contact Information page to direct the user to.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_help_email">Help Email</label></th> <td><input id="oadueslookup_help_email" name="oadueslookup_help_email" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_help_email")); ?> "> <p class="description">The email address for members to ask questions.</p> </td> </tr> </tbody> </table> <p class="submit"><input id="submit" class="button button-primary" type="submit" value="Save Changes" name="submit"></p> </form> <?php echo "</div>"; }
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); }