Skip to content

Instantly share code, notes, and snippets.

@KrotovRoman

KrotovRoman/example

Created May 7, 2018 03:35
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save KrotovRoman/f59cd68f9e4e8e13c875dcc1ef5b67ba to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save KrotovRoman/f59cd68f9e4e8e13c875dcc1ef5b67ba to your computer and use it in GitHub Desktop.
Download ZIP
Raw
example
/*
* Экспортировать выплаты сотрудникам в EXCEL
*/
public function export_pays_xls($pays) {
if ($pays->num_rows() <= 0) {
return FALSE;
}
$this->load->model("Lead_model");
$username = $this->ion_auth->user()->row()->username;
$this->xls->setActiveSheetIndex(0); // Устанавливаем индекс активного листа
$this->sheet = $this->xls->getActiveSheet(); // Получаем активный лист
$this->sheet->setTitle('Начисления '.date("d-m-Y")); // Подписываем лист
//устанавливаем высоту строки
$this->sheet->getRowDimension(1)->setRowHeight(40);
$this->sheet->setCellValue("A1", 'Отчет о начислениях зарплаты за заявки');
// Объединяем ячейки
$this->sheet->mergeCells('A1:E1');
//задаем шрифт заголовка
$style = array(
'font' => array(
'bold' => true,
'color' => array('rgb' => '000000'),
'size' => 14,
'name' => 'Arial'
));
$this->sheet->getStyle('A1')->applyFromArray($style);
//выравниваем по центру
//$this->sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getRowDimension(2)->setRowHeight(20);
$this->sheet->setCellValue("A2", 'Создан');
$this->sheet->setCellValue("B2", date("d-m-Y"));
//устанавливаем высоту строки
//$this->sheet->getRowDimension(4)->setRowHeight(20);
//задать авторширину колонки
$this->sheet->getColumnDimension('A')->setWidth(10);
$this->sheet->getColumnDimension('B')->setWidth(12);
$this->sheet->getColumnDimension('C')->setWidth(20);
$this->sheet->getColumnDimension('D')->setWidth(12);
$this->sheet->getColumnDimension('E')->setWidth(20);
$this->sheet->getColumnDimension('F')->setAutoSize(TRUE);
$this->sheet->getColumnDimension('G')->setAutoSize(TRUE);
$this->sheet->getColumnDimension('H')->setAutoSize(TRUE);
//перенос по словам
$this->sheet->getStyle("A4")->getAlignment()->setWrapText(TRUE);
$this->sheet->getStyle("B4")->getAlignment()->setWrapText(TRUE);
$this->sheet->getStyle("C4")->getAlignment()->setWrapText(TRUE);
$this->sheet->getStyle("D4")->getAlignment()->setWrapText(TRUE);
$this->sheet->getStyle("E4")->getAlignment()->setWrapText(TRUE);
// Вставляем заголовки
$this->sheet->setCellValue("A4", '№ заявки');
$this->sheet->setCellValue("B4", 'Начислено ('. $this->main_currency['shortname'] . ')');
$this->sheet->setCellValue("C4", 'Услуга');
$this->sheet->setCellValue("D4", 'Оплатил ('. $this->main_currency['shortname'] . ')');
$this->sheet->setCellValue("E4", 'Данные заявки');
//$this->sheet->setCellValue("F4", 'Рассрочка?');
//$this->sheet->setCellValue("G4", 'Форма заявки');
//$this->sheet->setCellValue("H4", 'Сотрудник');
//задаем цвета заголовков
$this->sheet->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('A4')->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle('B4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('B4')->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle('C4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('C4')->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle('D4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('D4')->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle('E4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('E4')->getFill()->getStartColor()->setRGB('FCF7B6');
// $this->sheet->getStyle('F4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// $this->sheet->getStyle('F4')->getFill()->getStartColor()->setRGB('FCF7B6');
//
// $this->sheet->getStyle('G4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// $this->sheet->getStyle('G4')->getFill()->getStartColor()->setRGB('FCF7B6');
//
// $this->sheet->getStyle('H4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// $this->sheet->getStyle('H4')->getFill()->getStartColor()->setRGB('FCF7B6');
//рамка
$ramka_top = array(
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array(
'rgb' => '000000'
)
)
);
$lead_count = 0;
$summa = 0;
$summa_bye = 0;
$i = 4;
foreach ($pays->result_array() as $item) {
$i++;
$lead_count++;
//выравнивание по вертикали по центру
$this->sheet->getStyle("A" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->sheet->getStyle("B" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->sheet->getStyle("C" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->sheet->getStyle("D" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->sheet->getStyle("E" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//№ заявки
$this->sheet->setCellValueExplicit("A" . $i, $item['id_lead'], PHPExcel_Cell_DataType::TYPE_STRING); //указываем тип ячейки string
//сумма
$sum = number_format($item['summa'], 2, ',', ' ');
$this->sheet->setCellValueExplicit("B" . $i, $sum, PHPExcel_Cell_DataType::TYPE_STRING); //указываем тип ячейки string
$summa+= $item['summa'];
$data_buy = $this->db->get_where('lead_byes', array('id_lead' => $item['id_lead'], 'id_service' => $item['id_service']));
$item['summa_bye'] = ($data_buy->num_rows() > 0 ? $data_buy->row()->summa : 0);
//название услуги
$this->sheet->setCellValue("C" . $i, $item['name']);
$sum_b = number_format($item['summa_bye'], 2, ',', ' '); // . ($item['for_surcharge'] > 0 ? " (в рассрочку)" : "")
$this->sheet->setCellValueExplicit("D" . $i, $sum_b, PHPExcel_Cell_DataType::TYPE_STRING); //указываем тип ячейки string
$summa_bye+= $item['summa_bye'];
//данные заявки
$fields_lead_string = "";
$data_fields = $this->Lead_model->get_fields_values($item['id_lead']);
foreach($data_fields as $item_data){
//$fields_lead_string.= $item_data['name']. " : ";
$fields_lead_string.= $item_data['value'];
$fields_lead_string.=" | ";
}
$fields_lead_string = trim($fields_lead_string);
$this->sheet->setCellValue("E" . $i, $fields_lead_string);
//задаем перенос по словам
$this->sheet->getStyle("E" . $i)->getAlignment()->setWrapText(TRUE);
// $this->sheet->setCellValue("F" . $i, ($item['for_surcharge'] > 0 ? "да" : "нет"));
// $this->sheet->setCellValue("G" . $i, $item['form_name']);
// $this->sheet->setCellValue("H" . $i, $item['first_name']. " " . $item['last_name']);
//применяем рамку
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("B" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("C" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("D" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("E" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("F" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("G" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("H" . $i)->getBorders()->applyFromArray($ramka_top);
}
$i++;
//применяем рамку
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("B" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("C" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("D" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("E" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("F" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("G" . $i)->getBorders()->applyFromArray($ramka_top);
// $this->sheet->getStyle("H" . $i)->getBorders()->applyFromArray($ramka_top);
$i++;
//закрашиваем
$this->sheet->getStyle("A" . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle("A" . $i)->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle("B" . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle("B" . $i)->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle("C" . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle("C" . $i)->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle("D" . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle("D" . $i)->getFill()->getStartColor()->setRGB('FCF7B6');
$this->sheet->getStyle("E" . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle("E" . $i)->getFill()->getStartColor()->setRGB('FCF7B6');
//задаем высоту
$this->sheet->getRowDimension($i)->setRowHeight(20);
$this->sheet->setCellValue("A" . $i, 'Продаж:');
$this->sheet->setCellValue("B" . $i, 'К выплате ('. $this->main_currency['shortname'] . ')');
$this->sheet->setCellValue("D" . $i, 'Доход с продаж ('. $this->main_currency['shortname'] . ')');
//применяем рамку
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("B" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("C" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("D" . $i)->getBorders()->applyFromArray($ramka_top);
$this->sheet->getStyle("E" . $i)->getBorders()->applyFromArray($ramka_top);
$i++;
//записываем данные
$this->sheet->setCellValue("A" . $i, $lead_count);
$this->sheet->setCellValue("B" . $i, number_format($summa, 2, ',', ' '));
$this->sheet->setCellValue("D" . $i, number_format($summa_bye, 2, ',', ' '));
//рамка
$ramka_bottom = array(
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array(
' rgb' => '000000'
)
)
);
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_bottom);
$this->sheet->getStyle("B" . $i)->getBorders()->applyFromArray($ramka_bottom);
$this->sheet->getStyle("C" . $i)->getBorders()->applyFromArray($ramka_bottom);
$this->sheet->getStyle("D" . $i)->getBorders()->applyFromArray($ramka_bottom);
$this->sheet->getStyle("E" . $i)->getBorders()->applyFromArray($ramka_bottom);
// Выравнивание текста
$this->sheet->getStyle("A" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle("B" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle("C" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->sheet->getStyle("D" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i+=2;
$this->sheet->setCellValue("A" . $i, 'Дата:');
$this->sheet->setCellValue("D" . $i, 'Подпись:');
////////////////////////записываем данные заказа в файл/////////////////
$temp_file = tempnam(sys_get_temp_dir(), 'tmp'); //создаем временный файл
$this->objWriter = new PHPExcel_Writer_Excel2007($this->xls); //PHPExcel_Writer_Excel5($this->xls); //для XLS
$this->objWriter->save($temp_file);
$this->load->helper('download');
force_download('Начисления '.date("d-m-Y").'.xlsx', file_get_contents($temp_file)); //отправляем на скачивание
$this->clear_tmp(sys_get_temp_dir() . "/"); //почистим все что скопилось во временной папке старше 30 минут
}
Raw
excel
//задать авторширину колонки
$this->sheet->getColumnDimension('A')->setWidth(10);
//выравнивание по ширине
$this->sheet->getColumnDimension('G')->setAutoSize(TRUE);
//перенос по словам
$this->sheet->getStyle("A4")->getAlignment()->setWrapText(TRUE);
//цвет заголовка
$this->sheet->getStyle('A4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$this->sheet->getStyle('A4')->getFill()->getStartColor()->setRGB('FCF7B6');
//рамка
$ramka_top = array(
'top' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array(
'rgb' => '000000'
)
)
);
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_top);
//рамка
$ramka_bottom = array(
'bottom' => array(
'style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array(
' rgb' => '000000'
)
)
);
$this->sheet->getStyle("A" . $i)->getBorders()->applyFromArray($ramka_bottom);
//выравнивание по вертикали по центру
$this->sheet->getStyle("A" . $i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// Выравнивание текста
$this->sheet->getStyle("A" . $i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment