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'));
}
}
