/**
* @return \PHPExcel
*/
protected function mockWorkbook()
{
$workbook = new \PHPExcel();
$workbook->disconnectWorksheets();
$workbook->getProperties()->setTitle('mocked');
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
$workbook->addSheet($sheet);
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->fromArray([['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test'], ['test', 'test', 'test']]);
$workbook->addSheet($sheet);
$workbook->setActiveSheetIndex(0);
return $workbook;
}
/**
* @return \PHPExcel
*/
protected function mockRow()
{
$workbook = new \PHPExcel();
$workbook->disconnectWorksheets();
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->fromArray([['a1', 'b1', 'c1']]);
$row = new \PHPExcel_Worksheet_Row($sheet, 1);
return $row;
}
/**
* @return \PHPExcel_Worksheet
*/
protected function mockSheet()
{
$workbook = new \PHPExcel();
$workbook->disconnectWorksheets();
$sheet = new \PHPExcel_Worksheet($workbook);
$sheet->setTitle('mocked');
$sheet->fromArray([['a1', 'b1'], ['a2', 'b2']]);
return $sheet;
}
/**
* Fill worksheet from values in array
*
* @param array $source Source array
* @param mixed $nullValue Value in source array that stands for blank cell
* @param string $startCell Insert array starting from this cell address as the top left coordinate
* @param bool $strictNullComparison Apply strict comparison when testing for null values in the array
* @throws PhpExcelException
* @return Worksheet
*/
public function fromArray(array $source = null, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)
{
foreach ($source as &$row) {
if (is_array($row)) {
foreach ($row as $key => $value) {
if ($value instanceof DateTime) {
$row[$key] = PhpOffice_PHPExcel_Shared_Date::PHPToExcel($value);
}
}
}
}
try {
$this->sheet->fromArray($source, $nullValue, $startCell, $strictNullComparison);
} catch (PhpOffice_PHPExcel_Exception $ex) {
throw new PhpExcelException('Unable to paste the array to worksheet', $ex->getCode(), $ex);
}
return $this;
}
];
if(!isset($recap[$elm->id_produit]))
{
$recap[$elm->id_produit]=[$elm->id_produit,$elm->categorie,$elm->produit,0.0];
}
if($elm->quantite_reel===null)
{
$recap[$elm->id_produit][3] += $elm->quantite_commande;
}
}
$sheet = new PHPExcel_Worksheet($xls,"commande ".$commande->id_commande);
$sheet->fromArray(
$grid2, // The data to set
NULL, // Array values with this value will not be set
'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$sheet->fromArray(
$grid, // The data to set
NULL, // Array values with this value will not be set
'A4' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
);
$xls->addSheet($sheet);
$nbre_commandes = $nbre_commandes+1;
}
$recapsheet->SetCellValue('F1', $nbre_commandes);
$recapsheet->fromArray(
$recap, // The data to set
NULL, // Array values with this value will not be set
'A1' // Top left coordinate of the worksheet range where we want to set these values (default is A1)
/**
* Write the content of the "DC Inventory" worksheet
*
* @param PHPExcel_Worksheet $worksheet
* @param array $sheetProps properties of the worksheet
* @param array $invData array with the inventory data
*/
function writeDCInvContent($worksheet, $sheetProps, $invData)
{
$colIdx = $sheetProps['ColIdx'];
// first line is the header for the worksheet
$worksheet->fromArray($invData, null, 'A2');
ReportStats::get()->report('Info', 'Number of Inventory entries ' . count($invData));
$highestRow = count($invData);
foreach ($sheetProps['ExpStr'] as $colName) {
$colLetter = $colIdx[$colName][1];
for ($row = 0; $row < $highestRow; $row++) {
$worksheet->setCellValueExplicit($colLetter . ($row + 2), $invData[$row][$colName], PHPExcel_Cell_DataType::TYPE_STRING);
}
}
// unset($invData);
}
/**
* creates another worksheet
*
* @return PHPExcel_Worksheet objWorksheet
*/
public function createProductArray(&$objPHPExcel)
{
$produkteAufgaben = new PHPExcel_Worksheet($objPHPExcel);
$produkteAufgaben->setTitle('Aufgaben Produkte');
$firstSheet = $objPHPExcel->getSheet(0);
$anzahlTeilnehmer = $firstSheet->getCell('C4')->getValue();
$endzeile = $anzahlTeilnehmer + 6;
$lastColumnRawData = $firstSheet->getHighestColumn();
$aufgabenwerte = $firstSheet->rangeToArray('G7:' . $lastColumnRawData . $endzeile, 0, true, false);
//$produkteAufgaben->fromArray($aufgabenwerte, NULL, 'A1', true);
$transponierteAufgabenwerte = PHPExcel_Calculation_LookupRef::TRANSPOSE($aufgabenwerte);
//$produkteAufgaben->fromArray($transponierteAufgabenwerte, NULL, 'A150', true);
$endmatrix = PHPExcel_Calculation_MathTrig::MMULT($transponierteAufgabenwerte, $aufgabenwerte);
$produkteAufgaben->fromArray($endmatrix, NULL, 'A1', true);
$lastColumnMMULTData = $produkteAufgaben->getHighestColumn();
$lastRowMMULTData = $produkteAufgaben->getHighestRow();
$maxColumn = $lastColumnMMULTData;
$maxColumn++;
$writeRow = $lastRowMMULTData + 2;
for ($column = 'A'; $column != $maxColumn; $column++) {
$cell = $produkteAufgaben->getCell($column . $writeRow);
$cell->setValue('=SUM(' . $column . '1:' . $column . $lastRowMMULTData . ')');
}
$objPHPExcel->addSheet($produkteAufgaben);
}
public function export_Uncomplete($file_name, $test = false)
{
error_reporting(E_ALL);
ini_set("display_errors", 1);
ini_set('max_execution_time', 60);
ini_set('memory_limit', '256M');
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
$cacheSettings = array('memoryCacheSize ' => '256MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$this->excel = new PHPExcel();
$registrant_data = $this->getArrayData();
$worksheet = new PHPExcel_Worksheet();
$worksheet->setTitle('Data');
$worksheet->setCellValue('A1', 'Nomor Pendaftaran');
$worksheet->getColumnDimension('A')->setAutoSize(true);
$worksheet->setCellValue('B1', 'Nama');
$worksheet->getColumnDimension('B')->setAutoSize(true);
$worksheet->setCellValue('C1', 'I/A');
$worksheet->getColumnDimension('C')->setAutoSize(true);
$worksheet->setCellValue('D1', 'Asal Sekolah');
$worksheet->getColumnDimension('D')->setAutoSize(true);
$worksheet->setCellValue('E1', 'Contact');
$worksheet->getColumnDimension('E')->setAutoSize(true);
$worksheet->setCellValue('F1', 'Status Kekurangan');
$worksheet->getColumnDimension('F')->setAutoSize(true);
$row_iterate = 2;
foreach ($registrant_data as $registrant) {
if (!$registrant['completed']) {
$row = [];
$row[] = $registrant['id'];
$row[] = strtoupper($registrant['name']);
$row[] = $registrant['gender'] == 'L' ? 'Ikhwan' : 'Akhwat';
$row[] = strtoupper($registrant['previousSchool']);
$row[] = $registrant['cp'];
$row[] = $registrant['status'];
$worksheet->fromArray($row, '', 'A' . $row_iterate);
$row_iterate++;
}
}
$this->excel->removeSheetByIndex(0);
$this->excel->addSheet($worksheet);
if ($test) {
return true;
} else {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $file_name . '.xls"');
header('Cache-Control: max-age=0');
$objWriter = new PHPExcel_Writer_Excel5($this->excel);
$objWriter->save('php://output');
exit;
}
}
/**
* Default callback worksheet which simply exports raw data without any formatting.
*
* @param PHPExcel_Worksheet $worksheet
* @param array|IDataProvider $dataProvider
*
* @return PHPExcel_Worksheet
* @throws PHPExcel_Exception
*/
protected function defaultCallback(\PHPExcel_Worksheet $worksheet, $dataProvider)
{
$_data = [];
if ($dataProvider instanceof \CActiveDataProvider) {
foreach ($dataProvider->getData() as $model) {
if ($model instanceof \CActiveRecord) {
$_data[] = $model->getAttributes(null);
}
}
if (!empty($_data)) {
$_keys = array_keys($_data[0]);
$_data = array_merge([$_keys], $_data);
}
return $worksheet->fromArray($_data);
}
if ($dataProvider instanceof \IDataProvider) {
$_data =& $dataProvider->getData();
} elseif (is_array($dataProvider)) {
$_data = $dataProvider;
} else {
$_data = [$dataProvider];
}
foreach ($_data as $index => $value) {
if ($value instanceof \CActiveRecord) {
$_data[$index] = $value->getAttributes(null);
} elseif (!is_array($value) && !is_scalar($value) && !is_null($value)) {
unset($_data[$index]);
}
}
return $worksheet->fromArray($_data);
}
