/**
* Generate CSV with headers form an array
* @param $filename
* @param array $data
* @param array $headers
*/
public function generateWithHeaders($filename, array $data, array $headers)
{
return $this->generator->create($filename, function ($excel) use($data, $headers) {
$excel->sheet('aidStream', function ($sheet) use($data, $headers) {
$sheet->fromArray($data, '', '', '', $headers);
});
})->export($this->defaultOutputFormat);
}
/**
* Execute the job.
* @param Excel $excel
*/
public function handle(Excel $excel)
{
$excel->create($this->product->code, function ($excel) {
$excel->sheet($this->product->code, function ($sheet) {
$codes = $this->codes or $this->product->codes()->get(['code']);
$sheet->fromArray($codes);
});
})->download('xlsx');
}
/**
* Export to Excel data sheet
*
* @return \Maatwebsite\Excel\Excel
* */
public function excel(Excel $xls)
{
$carbon = Carbon::now();
// $doc = $excel->create('Bookings-Excel-Sheet'.$carbon , function($excel){
// $excel->sheet('Bookings-excel-sheet' , function($sheet){
//
// $bookings = BookingsModel::all();
// $sheet->loadView('admin/bookings/partials/bookings-part',['bookings' => $bookings ]);
// });
// });
$doc = $xls->create('Bookings-Excel' . $carbon, function ($excel) {
$excel->sheet('booking', function ($sheet) {
$bookings = BookingsModel::all();
$sheet->loadView('admin.bookings.partials.bookings-part', ['bookings' => $bookings]);
});
})->download();
return $doc;
}
/**
* Create a new file
*
* @param $filename
* @param callable|null $callback
* @return \Maatwebsite\Excel\LaravelExcelWriter
* @static
*/
public static function create($filename, $callback = null)
{
return \Maatwebsite\Excel\Excel::create($filename, $callback);
}
public function exportExcel()
{
$prodi = Input::get('kode_prodi');
$gangep = Input::get('gangep');
$tahun = Input::get('tahun_ajaran');
if (empty($prodi) || empty($gangep) || empty($tahun)) {
return Redirect::to('administrator/rekapitulasi/cetakLaporan')->with('msge', 'Maaf.....<br>Inputan tidak boleh ada yang kosong!');
} else {
$rekap1 = DB::table('v_interprestasi')->where('kode_prodi', '=', $prodi)->where('gangep', '=', $gangep)->where('tahun_ajaran', '=', $tahun)->get();
Excel::create('Laporan Evaluasi Proses Belajar Mengajar', function ($excel) use($rekap1) {
// Set the properties
$excel->setTitle('Evaluasi Proses Belajar Mengajar')->setCreator('STMIK Bumigora Mataram');
$excel->sheet('Data PBM', function ($sheet) use($rekap1) {
$row = 1;
$sheet->row($row, array('No', 'Kode Prodi', 'Nama Matakuliah', 'Nama Dosen', 'Semester', 'Kelas', 'Total Mahasiswa', 'Total Mahasiswa Isi', 'Total Mahasiswa Tidak Isi', 'Presentase Index', 'Keterangan', 'Tahun Ajaran'));
$no = 1;
foreach ($rekap1 as $val) {
$sheet->row(++$row, array($no++, $val->kode_prodi, $val->nama_matakuliah, $val->nama_dosen, $val->semester, $val->kelas, $val->tot_mahasiswa, $val->tot_mhs_isi, $val->tot_mhs_tdk_isi, $val->PresentaseIndex, $val->keterangan, $val->tahun_ajaran));
}
});
})->export('xls');
}
}
/**
* Echo in a jqGrid compatible format the data requested by a grid.
*
* @param RepositoryInterface $dataRepository
* An implementation of the RepositoryInterface
* @param array $postedData
* All jqGrid posted data
* @return string
* String of a jqGrid compatible data format: xml, json, jsonp, array, xmlstring, jsonstring.
*/
public function encodeRequestedData(RepositoryInterface $Repository, $postedData)
{
// $page = $postedData['page']; // get the requested page
// $limit = $postedData['rows']; // get how many rows we want to have into the grid
// $sidx = $postedData['sidx']; // get index row - i.e. user click to sort
// $sord = $postedData['sord']; // get the direction
if (isset($postedData['page'])) {
$page = $postedData['page'];
// get the requested page
} else {
$page = 1;
}
if (isset($postedData['rows'])) {
$limit = $postedData['rows'];
// get how many rows we want to have into the grid
} else {
$limit = null;
}
if (isset($postedData['sidx'])) {
$sidx = $postedData['sidx'];
// get index row - i.e. user click to sort
} else {
$sidx = null;
}
if (isset($postedData['sord'])) {
$sord = $postedData['sord'];
// get the direction
}
if (isset($postedData['filters']) && !empty($postedData['filters'])) {
$filters = json_decode(str_replace('\'', '"', $postedData['filters']), true);
}
if (!$sidx || empty($sidx)) {
$sidx = null;
$sord = null;
}
if (isset($filters['rules']) && is_array($filters['rules'])) {
foreach ($filters['rules'] as &$filter) {
switch ($filter['op']) {
case 'eq':
//equal
$filter['op'] = '=';
break;
case 'ne':
//not equal
$filter['op'] = '!=';
break;
case 'lt':
//less
$filter['op'] = '<';
break;
case 'le':
//less or equal
$filter['op'] = '<=';
break;
case 'gt':
//greater
$filter['op'] = '>';
break;
case 'ge':
//greater or equal
$filter['op'] = '>=';
break;
case 'bw':
//begins with
$filter['op'] = 'like';
$filter['data'] = $filter['data'] . '%';
break;
case 'bn':
//does not begin with
$filter['op'] = 'not like';
$filter['data'] = $filter['data'] . '%';
break;
case 'in':
//is in
$filter['op'] = 'is in';
break;
case 'ni':
//is not in
$filter['op'] = 'is not in';
break;
case 'ew':
//ends with
$filter['op'] = 'like';
$filter['data'] = '%' . $filter['data'];
break;
case 'en':
//does not end with
$filter['op'] = 'not like';
$filter['data'] = '%' . $filter['data'];
break;
case 'cn':
//contains
$filter['op'] = 'like';
$filter['data'] = '%' . $filter['data'] . '%';
break;
case 'nc':
//does not contains
$filter['op'] = 'not like';
$filter['data'] = '%' . $filter['data'] . '%';
break;
}
}
} else {
$filters['rules'] = array();
}
$count = $Repository->getTotalNumberOfRows($filters['rules']);
if (empty($limit)) {
$limit = $count;
}
if (!is_int($count)) {
throw new Exception('The method getTotalNumberOfRows must return an integer');
}
if ($count > 0) {
$totalPages = ceil($count / $limit);
} else {
$totalPages = 0;
}
if ($page > $totalPages) {
$page = $totalPages;
}
if ($limit < 0) {
$limit = 0;
}
$start = $limit * $page - $limit;
if ($start < 0) {
$start = 0;
}
$limit = $limit * $page;
if (empty($postedData['pivotRows'])) {
$rows = $Repository->getRows($limit, $start, $sidx, $sord, $filters['rules']);
} else {
$rows = json_decode($postedData['pivotRows'], true);
}
if (!is_array($rows) || isset($rows[0]) && !is_array($rows[0])) {
throw new Exception('The method getRows must return an array of arrays, example: array(array("column1" => "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2"))');
}
if (isset($postedData['exportFormat'])) {
$this->Excel->create($postedData['name'], function ($Excel) use($rows, $postedData) {
foreach (json_decode($postedData['fileProperties'], true) as $key => $value) {
$method = 'set' . ucfirst($key);
$Excel->{$method}($value);
}
$Excel->sheet($postedData['name'], function ($Sheet) use($rows, $postedData) {
$columnCounter = 0;
foreach (json_decode($postedData['model'], true) as $a => $model) {
if (isset($model['hidden']) && $model['hidden'] !== true) {
$columnCounter++;
}
if (isset($model['hidedlg']) && $model['hidedlg'] === true) {
continue;
}
if (empty($postedData['pivot'])) {
foreach ($rows as $b => &$row) {
if (isset($model['hidden']) && $model['hidden'] === true) {
unset($row[$model['name']]);
} else {
if (isset($model['label'])) {
$row = array_add($row, $model['label'], $row[$model['name']]);
unset($row[$model['name']]);
} else {
$temp = $row[$model['name']];
unset($row[$model['name']]);
$row = array_add($row, $model['name'], $temp);
}
}
}
}
if (isset($model['align']) && isset($model['hidden']) && $model['hidden'] !== true) {
$Sheet->getStyle($this->num_to_letter($columnCounter, true))->getAlignment()->applyFromArray(array('horizontal' => $model['align']));
}
}
foreach (json_decode($postedData['sheetProperties'], true) as $key => $value) {
$method = 'set' . ucfirst($key);
$Sheet->{$method}($value);
}
$Sheet->fromArray($rows);
$Sheet->row(1, function ($Row) {
$Row->setFontWeight('bold');
});
});
})->export($postedData['exportFormat']);
} else {
echo json_encode(array('page' => $page, 'total' => $totalPages, 'records' => $count, 'rows' => $rows));
}
}
/**
* Echo in a jqGrid compatible format the data requested by a grid.
*
* @param RepositoryInterface $dataRepository
* An implementation of the RepositoryInterface
* @param array $postedData
* All jqGrid posted data
* @return string
* String of a jqGrid compatible data format: xml, json, jsonp, array, xmlstring, jsonstring.
*/
public function encodeRequestedData(RepositoryInterface $Repository, $postedData)
{
// $page = $postedData['page']; // get the requested page
// $limit = $postedData['rows']; // get how many rows we want to have into the grid
// $sidx = $postedData['sidx']; // get index row - i.e. user click to sort
// $sord = $postedData['sord']; // get the direction
if (isset($postedData['page'])) {
$page = $postedData['page'];
// get the requested page
} else {
$page = 1;
}
if (isset($postedData['rows'])) {
$limit = $postedData['rows'];
// get how many rows we want to have into the grid
} else {
$limit = null;
}
if (isset($postedData['sidx'])) {
$sidx = $postedData['sidx'];
// get index row - i.e. user click to sort
} else {
$sidx = null;
}
if (isset($postedData['sord'])) {
$sord = $postedData['sord'];
// get the direction
}
if (isset($postedData['filters']) && !empty($postedData['filters'])) {
$filters = json_decode(str_replace('\'', '"', $postedData['filters']), true);
}
if (isset($postedData['nodeid'])) {
$nodeId = $postedData['nodeid'];
} else {
$nodeId = null;
}
if (isset($postedData['n_level'])) {
$nodeLevel = $postedData['n_level'];
} else {
$nodeLevel = null;
}
if (isset($postedData['exportFormat'])) {
$exporting = true;
} else {
$exporting = false;
}
if (!$sidx || empty($sidx)) {
$sidx = null;
$sord = null;
}
if (isset($filters['rules']) && is_array($filters['rules'])) {
foreach ($filters['rules'] as &$filter) {
switch ($filter['op']) {
case 'eq':
//equal
$filter['op'] = '=';
break;
case 'ne':
//not equal
$filter['op'] = '!=';
break;
case 'lt':
//less
$filter['op'] = '<';
break;
case 'le':
//less or equal
$filter['op'] = '<=';
break;
case 'gt':
//greater
$filter['op'] = '>';
break;
case 'ge':
//greater or equal
$filter['op'] = '>=';
break;
case 'bw':
//begins with
$filter['op'] = 'like';
$filter['data'] = $filter['data'] . '%';
break;
case 'bn':
//does not begin with
$filter['op'] = 'not like';
$filter['data'] = $filter['data'] . '%';
break;
case 'in':
//is in
$filter['op'] = 'is in';
break;
case 'ni':
//is not in
$filter['op'] = 'is not in';
break;
case 'ew':
//ends with
$filter['op'] = 'like';
$filter['data'] = '%' . $filter['data'];
break;
case 'en':
//does not end with
$filter['op'] = 'not like';
$filter['data'] = '%' . $filter['data'];
break;
case 'cn':
//contains
$filter['op'] = 'like';
$filter['data'] = '%' . $filter['data'] . '%';
break;
case 'nc':
//does not contains
$filter['op'] = 'not like';
$filter['data'] = '%' . $filter['data'] . '%';
break;
case 'nu':
//is null
$filter['op'] = 'is null';
$filter['data'] = '';
break;
case 'nn':
//is not null
$filter['op'] = 'is not null';
$filter['data'] = '';
break;
}
}
} else {
$filters['rules'] = array();
}
$count = $Repository->getTotalNumberOfRows($filters['rules']);
if (empty($limit)) {
$limit = $count;
}
if (!is_int($count)) {
throw new Exception('The method getTotalNumberOfRows must return an integer');
}
if ($count > 0) {
$totalPages = ceil($count / $limit);
} else {
$totalPages = 0;
}
if ($page > $totalPages) {
$page = $totalPages;
}
if ($limit < 0) {
$limit = 0;
}
$start = $limit * $page - $limit;
if ($start < 0) {
$start = 0;
}
$limit = $limit * $page;
if (empty($postedData['pivotRows'])) {
$rows = $Repository->getRows($limit, $start, $sidx, $sord, $filters['rules'], $nodeId, $nodeLevel, $exporting);
} else {
$rows = json_decode($postedData['pivotRows'], true);
}
if (!is_array($rows) || isset($rows[0]) && !is_array($rows[0])) {
throw new Exception('The method getRows must return an array of arrays, example: array(array("column1" => "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2"))');
}
if ($exporting) {
$this->Excel->create($postedData['name'], function ($Excel) use($rows, $postedData) {
foreach (json_decode($postedData['fileProperties'], true) as $key => $value) {
$method = 'set' . ucfirst($key);
$Excel->{$method}($value);
}
$Excel->sheet($postedData['name'], function ($Sheet) use($rows, $postedData) {
$groupingView = json_decode($postedData['groupingView'], true);
$groupHeaders = json_decode($postedData['groupHeaders'], true);
$columnsPositions = array();
$groupFieldName = '';
$columnCounter = 0;
foreach (json_decode($postedData['model'], true) as $a => $model) {
if (!empty($groupingView) && $groupingView['groupField'][0] == $model['name']) {
if (isset($model['hidden']) && $model['hidden'] === true) {
$groupFieldHidden = true;
} else {
$groupFieldHidden = false;
}
$groupFieldName = $model['name'];
if (isset($model['label'])) {
$groupFieldLabel = $model['label'];
} else {
$groupFieldLabel = $model['name'];
}
}
if (isset($model['hidden']) && $model['hidden'] !== true) {
$columnCounter++;
$columnsPositions[$model['name']] = $columnCounter;
}
if (isset($model['hidedlg']) && $model['hidedlg'] === true) {
continue;
}
if (empty($postedData['pivot'])) {
foreach ($rows as $index => &$row) {
if (isset($model['hidden']) && $model['hidden'] === true && $model['name'] != $groupFieldName) {
unset($row[$model['name']]);
} else {
if (isset($model['label'])) {
$row[$model['label']] = $row[$model['name']];
unset($row[$model['name']]);
} else {
$temp = $row[$model['name']];
unset($row[$model['name']]);
$row[$model['name']] = $temp;
}
}
}
}
if (isset($model['align']) && isset($model['hidden']) && $model['hidden'] !== true) {
$Sheet->getStyle($this->num_to_letter($columnCounter, true))->getAlignment()->applyFromArray(array('horizontal' => $model['align']));
}
}
foreach (json_decode($postedData['sheetProperties'], true) as $key => $value) {
$method = 'set' . ucfirst($key);
$Sheet->{$method}($value);
}
if (!empty($groupingView)) {
$groupedRows = $groupedRowsNumbers = array();
$rowCounter = 0;
foreach ($rows as $index => &$row) {
if ($rowCounter == 0) {
$currentgroupFieldValue = $row[$groupFieldLabel];
if ($groupFieldHidden) {
unset($row[$groupFieldLabel]);
}
$firstColumnName = key($row);
$groupedRow = $row;
foreach ($groupedRow as $label => &$cell) {
if ($firstColumnName == $label) {
$cell = $currentgroupFieldValue;
} else {
$cell = '';
}
}
$rowCounter = 2;
if (!empty($groupHeaders)) {
$rowCounter++;
}
array_push($groupedRows, $groupedRow);
array_push($groupedRowsNumbers, $rowCounter);
} else {
if ($row[$groupFieldLabel] != $currentgroupFieldValue) {
$currentgroupFieldValue = $groupedRow[$firstColumnName] = $row[$groupFieldLabel];
$rowCounter++;
array_push($groupedRows, $groupedRow);
array_push($groupedRowsNumbers, $rowCounter);
}
if ($groupFieldHidden) {
unset($row[$groupFieldLabel]);
}
}
array_push($groupedRows, $row);
$rowCounter++;
}
$lastCellLetter = $this->num_to_letter($columnCounter, true);
foreach ($groupedRowsNumbers as $index => $groupedRowsNumber) {
$Sheet->mergeCells("A{$groupedRowsNumber}:{$lastCellLetter}{$groupedRowsNumber}");
}
$rows = $groupedRows;
}
if (empty($groupHeaders)) {
$Sheet->fromArray($rows);
} else {
$headers = $firstHeader = array();
for ($i = 0; $i < count($columnsPositions); $i++) {
$firstHeader[$i] = '';
}
foreach ($groupHeaders as $index => $groupHeader) {
$firstHeader[$columnsPositions[$groupHeader['startColumnName']] - 1] = $groupHeader['titleText'];
$Sheet->mergeCells($this->num_to_letter($columnsPositions[$groupHeader['startColumnName']], true) . '1:' . $this->num_to_letter($columnsPositions[$groupHeader['startColumnName']] + $groupHeader['numberOfColumns'] - 1, true) . '1');
}
array_push($headers, $firstHeader);
array_push($headers, array_keys($rows[0]));
$Sheet->fromArray(array_merge($headers, $rows), null, 'A1', false, false);
$Sheet->row(2, function ($Row) {
$Row->setFontWeight('bold');
});
}
$Sheet->row(1, function ($Row) {
$Row->setFontWeight('bold');
});
});
})->export($postedData['exportFormat']);
} else {
echo json_encode(array('page' => $page, 'total' => $totalPages, 'records' => $count, 'rows' => $rows));
}
}
/**
* Create the json response.
*
* @return mixed
*/
public function export()
{
$this->filename = 'export-' . date('Y-m-d');
$result = [];
/** @var IndexResult $item */
foreach ($this->getIndexBuilder()->getResult() as $item) {
foreach ($item->getFields() as $field) {
$value = $field->getValue();
if (!is_string($value)) {
$value = $value->toArray();
}
$result[$item->getIdentifier()][$field->getName()] = $value;
}
}
Excel::create($this->getFilename(), function ($excel) use($result) {
$excel->setTitle('Export');
$excel->setCreator('Bauhaus')->setCompany('KraftHaus');
$excel->sheet('Excel sheet', function ($sheet) use($result) {
$sheet->setOrientation('landscape');
$sheet->fromArray($result);
});
})->download('xls');
}
