/**
* Write Cols
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param PHPExcel_Worksheet $pSheet Worksheet
* @throws Exception
*/
private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
{
// cols
$objWriter->startElement('cols');
// Check if there is at least one column dimension specified. If not, create one.
if (count($pSheet->getColumnDimensions()) == 0) {
$pSheet->getColumnDimension('A')->setWidth(10);
}
$pSheet->calculateColumnWidths();
// Loop trough column dimensions
foreach ($pSheet->getColumnDimensions() as $colDimension) {
// col
$objWriter->startElement('col');
$objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
$objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
if ($colDimension->getWidth() < 0) {
// No width set, apply default of 10
$objWriter->writeAttribute('width', '10');
} else {
// Width set
$objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth()));
}
// Column visibility
if ($colDimension->getVisible() == false) {
$objWriter->writeAttribute('hidden', 'true');
}
// Auto size?
if ($colDimension->getAutoSize()) {
$objWriter->writeAttribute('bestFit', 'true');
} else {
$objWriter->writeAttribute('customWidth', 'true');
}
// Collapsed
if ($colDimension->getCollapsed() == true) {
$objWriter->writeAttribute('collapsed', 'true');
}
// Outline level
if ($colDimension->getOutlineLevel() > 0) {
$objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
}
$objWriter->endElement();
}
$objWriter->endElement();
}
protected function _signupSheet($event, $includeEndingTerms, $includeNotEndingTerms)
{
$sheet = new PHPExcel_Worksheet($this->_excelDoc, 'Signup Sheet for Workshop ' . $event['workshopTitle']);
// Set up the margins so the header doesn't bleed into the page
$sheet->getPageMargins()->setTop(1.5);
// Make a three column page layout
$sheet->getColumnDimension('A')->setWidth(16);
$sheet->getColumnDimension('B')->setWidth(16);
$sheet->getColumnDimension('C')->setWidth(45);
$config = new Zend_Config_Xml(APPLICATION_PATH . '/configs/config.xml', 'production');
$date = new DateTime($event['date']);
$startTime = new DateTime($event['startTime']);
$endTime = new DateTime($event['endTime']);
// Set the header on odd pages.
// The code formatting is off because the header doesn't ignore spaces.
/*
* Format:
* Title
* Room name
* date('D, M d, Y') (startTime('g:i A') - endTime('g:i A'))
* Instructors
*
*/
$sheet->getHeaderFooter()->setOddHeader('&C&B&14' . $event['workshopTitle'] . '&14&B&12 ' . chr(10) . $event['location'] . chr(10) . $date->format('l, M d, Y') . '(' . $startTime->format('g:i A') . ' - ' . $endTime->format('g:i A') . ')' . chr(10) . 'Instructor: ' . implode(',', $event['instructors']) . '&12&C');
// Write Column Headers for the table
$sheet->setCellValue('A1', 'First Name');
$sheet->setCellValue('B1', 'Last Name');
$sheet->setCellValue('C1', 'Signature');
// reformat it a little bit in a simpler way for us to use it in our
// spreadsheet printin' loop
$rows = array();
foreach ($event['attendeeList'] as $a) {
$rows[] = array($a['firstName'], $a['lastName']);
}
$signin = new PHPExcel_Style();
$signin->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$rowCounter = 3;
foreach ($rows as $row) {
$row = array_values($row);
// put the totals in the row
$char = self::A;
foreach ($row as $cell) {
$sheet->setCellValue(chr($char) . $rowCounter, $cell);
$char++;
}
$rowCounter++;
}
$tableHeaderStyle = new PHPExcel_Style();
$tableHeaderStyle->applyFromArray($this->_tableHeaderStyleArray);
$tableBodyStyle = new PHPExcel_Style();
$tableBodyStyle->applyFromArray($this->_contentStyleArray);
$sheet->setSharedStyle($tableHeaderStyle, 'A1:C1');
$sheet->setSharedStyle($tableBodyStyle, 'A3:B' . ($rowCounter - 1));
$sheet->setSharedStyle($signin, 'C3:C' . ($rowCounter - 1));
return $sheet;
}
private function buildHead()
{
$column_index = 1;
foreach ($this->analyze->getCriteriaNames() as $criterion_name) {
$column_name = Utility::getNameFromNumber($column_index++);
$this->sheet->setCellValue($column_name . $this->row_index, $criterion_name);
$this->sheet->getColumnDimension($column_name)->setWidth(30, 0);
$this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
}
$this->row_index++;
}
private function buildHead()
{
$column_index = 0;
foreach ($this->responseTableView->getHeaderData() as $header) {
$column_name = Utility::getNameFromNumber($column_index++);
$this->sheet->setCellValue($column_name . $this->row_index, $header);
$this->sheet->getColumnDimension($column_name)->setWidth(30, 0);
$this->sheet->getStyle($column_name . $this->row_index)->getFont()->setBold(true);
}
$this->row_index++;
}
/**
*
*/
protected function _styleWorkSheet()
{
$highestColumn = $this->_mainSheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($column = $this->_startCol; $column < $highestColumnIndex; $column++) {
$this->_mainSheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($column))->setAutoSize(true);
}
$this->_mainSheet->getColumnDimension('B')->setWidth(3);
}
/**
* do layouting (cell borders, font styles, background colors,
* cell sizing, page setup)
*/
protected function doLayouting()
{
$this->formatDataRowsByType();
$this->addCellBorders();
$this->formatHeaders();
$this->formatSums();
// auto-width columns:
for ($x = 0; $x < count($this->activeColumns_arr); $x++) {
$this->sheet->getColumnDimension(self::excelColumnAddr($x))->setAutoSize(true);
}
// auto-height rows:
foreach ($this->sheet->getRowDimensions() as $rd) {
$rd->setRowHeight(-1);
}
// fixed header when scrolling down in Excel:
$this->sheet->freezePane(self::excelAddr(0, self::EXCEL_HEADER_OFFSET));
$pageSetup = $this->sheet->getPageSetup();
// when printing, show header on every page:
$pageSetup->setRowsToRepeatAtTopByStartAndEnd(1, self::EXCEL_HEADER_OFFSET);
// increase chances that page can be printed out without seperating a row's data over more than one page:
$pageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
}
/**
* Set dimensions of row or column
*
* @param \PHPExcel_Worksheet $excelWorksheet
* @param $excelElement
* @param $documentElement
*/
protected function setDimensions(\PHPExcel_Worksheet $excelWorksheet, $excelElement, $documentElement)
{
$dimensions = $this->getDimensionsArray($documentElement);
if (isset($dimensions['column']) && $excelElement instanceof \PHPExcel_Cell) {
foreach ($dimensions['column'] as $columnKey => $columnValue) {
$method = 'set' . ucfirst($columnKey);
$excelWorksheet->getColumnDimension($excelElement->getColumn())->{$method}($columnValue);
}
}
if (isset($dimensions['row'])) {
foreach ($dimensions['row'] as $rowKey => $rowValue) {
$method = 'set' . ucfirst($rowKey);
if ($excelElement instanceof \PHPExcel_Cell) {
$excelWorksheet->getRowDimension($excelElement->getRow())->{$method}($rowValue);
} elseif ($excelElement instanceof \PHPExcel_Worksheet_Row) {
$excelWorksheet->getRowDimension($excelElement->getRowIndex())->{$method}($rowValue);
}
}
}
}
private function addTransactionPage(PHPExcel_Worksheet $activeSheet)
{
$activeSheet->setTitle('Транзакции')->setCellValue('A12', 'Статус')->setCellValue('B12', 'Тип')->setCellValue('C12', 'ID')->setCellValue('D12', 'Дата')->setCellValue('E12', 'IP')->setCellValue('F12', 'ГЕО')->setCellValue('G12', 'URL цели')->setCellValue('H12', 'Источник')->setCellValue('I12', 'Материал')->setCellValue('J12', 'Выплата')->setCellValue('K12', 'Вознаграждение')->setCellValue('L12', 'Зароботок')->setCellValue('M12', 'Цель');
$row = 13;
$availableStatuses = ActionsLog::getAvailableStatuses();
foreach ($this->transactionData['rows'] as $tr) {
$activeSheet->setCellValue('A' . $row, $availableStatuses[$tr['status']])->setCellValue('B' . $row, $tr['source_type_name'])->setCellValue('C' . $row, $tr['id'])->setCellValue('D' . $row, Yii::app()->dateFormatter->formatDateTime($tr['date']))->setCellValue('E' . $row, $tr['ip'])->setCellValue('F' . $row, $tr['geo'])->setCellValue('G' . $row, $tr['target_url_decoded'])->setCellValue('H' . $row, $tr['source_name'])->setCellValue('I' . $row, $tr['target_name'])->setCellValue('J' . $row, $tr['payment'])->setCellValue('K' . $row, $tr['reward'])->setCellValue('L' . $row, $tr['debit'])->setCellValue('M' . $row, $tr['action_name']);
$row++;
}
$activeSheet->setCellValue('J' . $row, $this->transactionData['total']['payment'])->setCellValue('K' . $row, $this->transactionData['total']['reward'])->setCellValue('L' . $row, $this->transactionData['total']['debit']);
$activeSheet->getColumnDimension('A')->setWidth(16.3 * 1.05);
$activeSheet->getColumnDimension('B')->setWidth(16.43 * 1.05);
$activeSheet->getColumnDimension('C')->setWidth(5 * 1.05);
$activeSheet->getColumnDimension('D')->setWidth(17.86 * 1.05);
$activeSheet->getColumnDimension('E')->setWidth(14.14 * 1.05);
$activeSheet->getColumnDimension('F')->setWidth(34 * 1.05);
$activeSheet->getColumnDimension('G')->setWidth(31 * 1.05);
$activeSheet->getColumnDimension('H')->setWidth(30.86 * 1.05);
$activeSheet->getColumnDimension('I')->setWidth(19.14 * 1.05);
$activeSheet->getColumnDimension('J')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('K')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('L')->setWidth(8.57 * 1.05);
$activeSheet->getColumnDimension('M')->setWidth(30.7 * 1.05);
$activeSheet->getStyle('A12:M' . $row)->getAlignment()->setWrapText(true);
$this->formatTable($activeSheet, 'A', '12', 'M', $row, array('formatTotal' => true, 'innerRowHeight' => -1, 'headerRowHeight' => 27));
$this->addLogo($activeSheet);
$this->setHeader($activeSheet, $this->getHeaders());
$this->setPageFit($activeSheet, self::FIT_TO_WIDTH, PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
}
/**
* Format the columns of the worksheet according to the attribute definition
*
* @param PHPExcel_Worksheet $worksheet
* @param array $columns
*/
function formatWSColumns($worksheet, $columns)
{
$fmt_code = array('T' => PHPExcel_Style_NumberFormat::FORMAT_TEXT, 'D' => 'yyyy-mm-dd', 'N' => PHPExcel_Style_NumberFormat::FORMAT_NUMBER, 'F' => '0.0', 'P' => '0.0%', '' => null);
$highestRow = $worksheet->getHighestRow();
$colFmt = $columns[0][1];
$colidx = 0;
$start = 0;
$idx = 0;
foreach ($columns as $col) {
if ($col[2]) {
// set the column width if a specific value is defined
$colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx);
$worksheet->getColumnDimension($colLetter)->setWidth($col[2]);
}
if ($col[3] and $col[3] == 'wrap') {
// set text wrapping attribute if requested
$colLetter = PHPExcel_Cell::stringFromColumnIndex($colidx);
$range = $colLetter . '2:' . $colLetter . $highestRow;
$worksheet->getStyle($range)->getAlignment()->setWrapText(true);
}
if ($colFmt != $col[1]) {
// assign the format to the range if a format is explicitly required
$start_col = PHPExcel_Cell::stringFromColumnIndex($start);
$end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1);
$range = $start_col . '2:' . $end_col . $highestRow;
$colFmtSpec = $fmt_code[$colFmt];
if ($colFmtSpec) {
$worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec);
}
$colFmt = $col[1];
$start = $start + $idx;
$idx = 1;
} else {
// still in the same format range
$idx += 1;
}
$colidx++;
}
$start_col = PHPExcel_Cell::stringFromColumnIndex($start);
$end_col = PHPExcel_Cell::stringFromColumnIndex($start + $idx - 1);
$range = $start_col . '2:' . $end_col . $highestRow;
$colFmtSpec = $fmt_code[$colFmt];
if ($colFmtSpec) {
// assign up to the end the format if explicitly required
$worksheet->getStyle($range)->getNumberFormat()->setFormatCode($colFmtSpec);
}
}
/**
* Inserts data into worksheet and returns it
*
* @return PHPExcel_Worksheet
*/
public function render()
{
// Set worksheet header
$this->_set_row(1, $this->columns, TRUE);
//set header style
$obj_style = new PHPExcel_Style();
$style = Kohana::$config->load('phpexcel.header');
$obj_style->applyFromArray($style);
$column_dim = PHPExcel_Cell::stringFromColumnIndex(count($this->columns) - 1);
$this->_worksheet->setSharedStyle($obj_style, 'A1:' . $column_dim . '1');
// Set data
$rows = 0;
foreach ($this->data as $row => $data) {
$this->_set_row($row + 2, $data);
$rows++;
}
// Set column styles and width
$column = 0;
foreach (array_keys($this->columns) as $key) {
$column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
$format = Arr::get($this->formats, $key);
if ($format !== NULL) {
$this->_worksheet->getStyle($column_dim . 2 . ':' . $column_dim . (2 + $rows))->getNumberFormat()->setFormatCode($format);
}
if ($this->auto_size === TRUE) {
$this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
}
$column++;
}
return $this->_worksheet;
}
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;
}
}
function setExcelHeader2(PHPExcel_Worksheet $sheet)
{
//$sheet->setCellValue('A1', 'Site Name');
$sheet->setCellValue('A1', '订单号');
$sheet->setCellValue('B1', '型号');
$sheet->setCellValue('C1', '订单价格');
$sheet->setCellValue('D1', '收货人姓名');
//$sheet->setCellValue('D1', 'MODEL');
$sheet->setCellValue('E1', '送货地址');
$sheet->setCellValue('F1', '邮编');
$sheet->setCellValue('G1', '客户电话');
$sheet->setCellValue('H1', '电子邮件');
$sheet->setCellValue('I1', '备注');
//$sheet->getColumnDimension('A')->setWidth(30);
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(20);
$sheet->getColumnDimension('C')->setWidth(10);
$sheet->getColumnDimension('D')->setWidth(30);
//$sheet->getColumnDimension('D')->setWidth(20);
$sheet->getColumnDimension('E')->setWidth(40);
$sheet->getColumnDimension('F')->setWidth(10);
$sheet->getColumnDimension('G')->setWidth(20);
$sheet->getColumnDimension('H')->setWidth(20);
$sheet->getColumnDimension('I')->setWidth(40);
}
/**
* Inserts data into worksheet and returns it
*
* @return PHPExcel_Worksheet
*/
public function render()
{
// Set worksheet header
if ($this->include_names) {
$this->_set_row(1, $this->columns, TRUE);
$offset = 2;
} else {
$offset = 1;
}
// Set data
$rows = 0;
foreach ($this->data as $row => $data) {
$this->_set_row($row + $offset, $data);
$rows++;
}
// Set column styles and width
$column = 0;
foreach ($this->columns as $key => $name) {
$column_dim = PHPExcel_Cell::stringFromColumnIndex($column);
$format = Arr::get($this->formats, $key);
if ($format !== NULL) {
$this->_worksheet->getStyle($column_dim . $offset . ':' . $column_dim . ($offset + $rows))->getNumberFormat()->setFormatCode($format);
}
if ($this->auto_size === TRUE) {
$this->_worksheet->getColumnDimension($column_dim)->setAutoSize(TRUE);
}
$column++;
}
return $this->_worksheet;
}
