function scriptUpload($f3) { $this->ensureAdmin($f3); $db = $f3->get('db'); Logger::Info($f3, "AdminPost.scriptUpload", "Starting import..."); $dummy = $f3->get('FILES'); $uploadedFile = $dummy["sqlFile"]; if ($uploadedFile["error"] > 0) { Logger::Error($f3, "AdminPost.scriptUpload", $uploadedFile["error"]); echo "Error: " . $uploadedFile["error"] . "<br>"; } else { try { $db->beginTransaction(); $importFileName = $f3->get('ROOT') . '/imports/import_' . date('Y-m-d_H\\hi\\m') . '.xlsx'; copy($uploadedFile["tmp_name"], $importFileName); Logger::Info($f3, "AdminPost.scriptUpload", "Import file: " . $importFileName); $importResult = ExcelImportExport::importFromExcel2007($db, $uploadedFile["tmp_name"]); $db->commit(); $f3->set('scriptResult', $importResult); Logger::Info($f3, "AdminPost.scriptUpload", "Import finished"); } catch (Exception $e) { $db->rollBack(); $err = ExcelImportExport::$lastExecutedStatement . ' :: ' . $e->getMessage(); Logger::Error($f3, "AdminPost.scriptUpload", "Import failed: " . $err); $f3->set('scriptResult', $err); } echo Template::instance()->render('adminIndex.htm'); } }
function updateImportForm(Form $form) { /* @var $owner ModelAdmin */ $owner = $this->owner; $class = $owner->modelClass; // Overwrite model imports $importerClasses = $owner->stat('model_importers'); if (is_null($importerClasses)) { $models = $owner->getManagedModels(); foreach ($models as $modelName => $options) { $importerClasses[$modelName] = 'ExcelBulkLoader'; } $owner->set_stat('model_importers', $importerClasses); } $modelSNG = singleton($class); $modelName = $modelSNG->i18n_singular_name(); $fields = $form->Fields(); $content = _t('ModelAdminExcelExtension.DownloadSample', '<div class="field"><a href="{link}">Download sample file</a></div>', array('link' => $owner->Link($class . '/downloadsample'))); $file = $fields->dataFieldByName('_CsvFile'); if ($file) { $file->setDescription(ExcelImportExport::getValidExtensionsText()); $file->getValidator()->setAllowedExtensions(ExcelImportExport::getValidExtensions()); } $fields->removeByName("SpecFor{$modelName}"); $fields->insertAfter('EmptyBeforeImport', new LiteralField("SampleFor{$modelName}", $content)); if (!$modelSNG->canDelete()) { $fields->removeByName('EmptyBeforeImport'); } $actions = $form->Actions(); $import = $actions->dataFieldByName('action_import'); if ($import) { $import->setTitle(_t('ModelAdminExcelExtension.ImportExcel', "Import from Excel")); } }
function dumpDatabase($f3) { $db = $f3->get('db'); Logger::Info($f3, "AdminGet.dumpDatabase", "Exporting the DB"); $exportFileName = $f3->get('ROOT') . '/exports/export_' . date('Y-m-d_H\\hi\\m') . '.xlsx'; ExcelImportExport::exportToExcel2007($db, $exportFileName); Logger::Info($f3, "AdminGet.dumpDatabase", "Export file: {$exportFileName}"); if (!Web::instance()->send($exportFileName)) { $f3->error(404); } }
public static function sampleFileForClass($class) { $excel = new PHPExcel(); $sheet = $excel->getActiveSheet(); $row = 1; $col = 0; foreach (ExcelImportExport::allFieldsForClass($class) as $header) { $sheet->setCellValueByColumnAndRow($col, $row, $header); $col++; } $fileName = "sample-file-for-{$class}.xlsx"; $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $fileName . '"'); ob_clean(); $writer->save('php://output'); exit; }
public function __construct($controller, $name, $fields = null, $actions = null, $validator = null) { if (!$fields) { $helpHtml = _t('ExcelMemberImportForm.Help1', '<p><a href="{link}">Download sample file</a></p>', array('link' => $controller->Link('downloadsample/Member'))); $helpHtml .= _t('ExcelMemberImportForm.Help2', '<ul>' . '<li>Existing users are matched by their unique <em>Email</em> property, and updated with any new values from ' . 'the imported file.</li>' . '<li>Groups can be assigned by the <em>Groups</em> column. Groups are identified by their <em>Code</em> property, ' . 'multiple groups can be separated by comma. Existing group memberships are not cleared.</li>' . '</ul>'); $importer = new MemberCsvBulkLoader(); $importSpec = $importer->getImportSpec(); $helpHtml = sprintf($helpHtml, implode(', ', array_keys($importSpec['fields']))); $extensions = array('csv', 'xls', 'xlsx', 'ods', 'txt'); $fields = new FieldList(new LiteralField('Help', $helpHtml), $fileField = new FileField('File', _t('ExcelMemberImportForm.FileFieldLabel', 'File <small><br/>(allowed extensions: {extensions})</small>', array('extensions' => implode(', ', $extensions))))); $fileField->getValidator()->setAllowedExtensions(ExcelImportExport::getValidExtensions()); } if (!$actions) { $action = new FormAction('doImport', _t('ExcelMemberImportForm.BtnImport', 'Import from file')); $action->addExtraClass('ss-ui-button'); $actions = new FieldList($action); } if (!$validator) { $validator = new RequiredFields('File'); } parent::__construct($controller, $name, $fields, $actions, $validator); $this->addExtraClass('cms'); $this->addExtraClass('import-form'); }
private static function importTableFromExcel($objPHPExcel, $tableName) { $activeSheet = $objPHPExcel->getSheetByName($tableName); //$temp = $activeSheet->GetCell('A1')->getValue(); $highestRow = $activeSheet->getHighestRow(); // e.g. 10 $highestColumn = $activeSheet->getHighestColumn(); // e.g 'F' $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5 $insertTemplate = 'INSERT INTO ' . $tableName . ' ('; $strFormatBuilder = ''; for ($col = 0; $col < $highestColumnIndex; $col++) { $columnName = $activeSheet->getCellByColumnAndRow($col, 1)->getValue(); if ($columnName && strlen(trim($columnName)) > 0) { $insertTemplate = $insertTemplate . $columnName; if (strpos(strtolower($columnName), '_id') === false) { $strFormatBuilder = $strFormatBuilder . "'%s'"; } else { $strFormatBuilder = $strFormatBuilder . "%d"; } if ($col < $highestColumnIndex - 1) { $insertTemplate = $insertTemplate . ', '; $strFormatBuilder = $strFormatBuilder . ', '; } } else { $highestColumnIndex = $col; if (ExcelImportExport::stringEndsWith($insertTemplate, ', ')) { $insertTemplate = substr($insertTemplate, 0, -2); } if (ExcelImportExport::stringEndsWith($strFormatBuilder, ', ')) { $strFormatBuilder = substr($strFormatBuilder, 0, -2); } } } $insertTemplate = $insertTemplate . ') VALUES (' . $strFormatBuilder . ')'; $insertStatements = array(); for ($row = 2; $row <= $highestRow; ++$row) { $id = $activeSheet->getCellByColumnAndRow(0, $row)->getValue(); if ($id) { $colValues = array(); for ($col = 0; $col < $highestColumnIndex; $col++) { $colValues[] = $activeSheet->getCellByColumnAndRow($col, $row)->getValue(); } $colValues = ExcelImportExport::mysql_escape_mimic($colValues); array_unshift($colValues, $insertTemplate); $insertString = call_user_func_array('sprintf', $colValues); //$temp = $temp . ' -- '. $insertString; $insertStatements[] = $insertString; } } return $insertStatements; }
/** * Generate export fields for Excel. * * @param GridField $gridField * @return PHPExcel */ public function generateExportFileData($gridField) { $class = $gridField->getModelClass(); $columns = $this->exportColumns ? $this->exportColumns : ExcelImportExport::exportFieldsForClass($class); $fileData = ''; $singl = singleton($class); $singular = $class ? $singl->i18n_singular_name() : ''; $plural = $class ? $singl->i18n_plural_name() : ''; $filter = new FileNameFilter(); if ($this->exportName) { $this->exportName = $filter->filter($this->exportName); } else { $this->exportName = $filter->filter('export-' . $plural); } $excel = new PHPExcel(); $excelProperties = $excel->getProperties(); $excelProperties->setTitle($this->exportName); $sheet = $excel->getActiveSheet(); if ($plural) { $sheet->setTitle($plural); } $row = 1; $col = 0; if ($this->hasHeader) { $headers = array(); // determine the headers. If a field is callable (e.g. anonymous function) then use the // source name as the header instead foreach ($columns as $columnSource => $columnHeader) { $headers[] = !is_string($columnHeader) && is_callable($columnHeader) ? $columnSource : $columnHeader; } foreach ($headers as $header) { $sheet->setCellValueByColumnAndRow($col, $row, $header); $col++; } $endcol = PHPExcel_Cell::stringFromColumnIndex($col - 1); $sheet->setAutoFilter("A1:{$endcol}1"); $sheet->getStyle("A1:{$endcol}1")->getFont()->setBold(true); $col = 0; $row++; } // Autosize $cellIterator = $sheet->getRowIterator()->current()->getCellIterator(); try { $cellIterator->setIterateOnlyExistingCells(true); } catch (Exception $ex) { continue; } foreach ($cellIterator as $cell) { $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true); } //Remove GridFieldPaginator as we're going to export the entire list. $gridField->getConfig()->removeComponentsByType('GridFieldPaginator'); $items = $gridField->getManipulatedList(); // @todo should GridFieldComponents change behaviour based on whether others are available in the config? foreach ($gridField->getConfig()->getComponents() as $component) { if ($component instanceof GridFieldFilterHeader || $component instanceof GridFieldSortableHeader) { $items = $component->getManipulatedData($gridField, $items); } } foreach ($items->limit(null) as $item) { if (!$item->hasMethod('canView') || $item->canView()) { foreach ($columns as $columnSource => $columnHeader) { if (!is_string($columnHeader) && is_callable($columnHeader)) { if ($item->hasMethod($columnSource)) { $relObj = $item->{$columnSource}(); } else { $relObj = $item->relObject($columnSource); } $value = $columnHeader($relObj); } else { $value = $gridField->getDataFieldValue($item, $columnSource); if ($value === null) { $value = $gridField->getDataFieldValue($item, $columnHeader); } } $value = str_replace(array("\r", "\n"), "\n", $value); $sheet->setCellValueByColumnAndRow($col, $row, $value); $col++; } } if ($item->hasMethod('destroy')) { $item->destroy(); } $col = 0; $row++; } return $excel; }
<?php $f3 = (require 'lib/base.php'); $f3->set('UI', 'ui/'); $f3->set('AUTOLOAD', 'autoload/'); $f3->set('UPLOADS', $f3->get('tmp')); $dbFile = 'db/data.sqlite'; $isNewDb = !file_exists($dbFile) || filesize($dbFile) === 0; $f3->set('db', new DB\SQL('sqlite:' . $dbFile)); $f3->set('log', new DB\SQL('sqlite:db/log.sqlite')); if ($isNewDb) { ExcelImportExport::importFromExcel2007($f3->get('db'), 'db/bootstrap.xlsx'); } Logger::Initialize($f3->get('log')); //$f3->set('DEBUG',3); //############### Set routes ################ $f3->route('GET /', 'UserGet->index'); $f3->route('GET /lehrer/@id', 'LehrerGet->lehrer'); $f3->route('POST /lehrer/@id/@action', 'LehrerPost->@action'); $f3->route('GET /@action', 'UserGet->@action'); $f3->route('GET /@action', 'UserGet->@action'); $f3->route('POST /@action', 'UserPost->@action'); $f3->route('GET /admin/absences/@id', 'AdminGet->absences'); $f3->route('POST /admin/absences/@id/@action', 'AdminPost->@action'); $f3->route('GET /admin/@action', 'AdminGet->@action'); $f3->route('POST /admin/@action', 'AdminPost->@action'); //############### START F3 ################ $f3->run();
protected function setupGridField(GridField $gridfield, Config_ForClass $classConfig) { if (!$gridfield) { return; } $config = $gridfield->getConfig(); $class = $gridfield->getModelClass(); // More item per page $paginator = $config->getComponentByType('GridFieldPaginator'); $paginator->setItemsPerPage(50); // Bulk manage if ($classConfig->bulk_manage && class_exists('GridFieldBulkManager')) { $already = $config->getComponentByType('GridFieldBulkManager'); if (!$already) { $config->addComponent($bulkManager = new GridFieldBulkManager()); $bulkManager->removeBulkAction('unLink'); } } // Better export if ($classConfig->export_csv) { /* @var $export GridFieldExportButton */ $export = $config->getComponentByType('GridFieldExportButton'); $export->setExportColumns(ExcelImportExport::exportFieldsForClass($class)); } else { $config->removeComponentsByType('GridFieldExportButton'); } if ($classConfig->export_excel) { if ($class == 'Group') { $config->addComponent(new GridFieldButtonRow('after')); } $config->addComponent(new ExcelGridFieldExportButton('buttons-after-left')); } }