PHP PHPExcel_Worksheet_MemoryDrawing::setOffsetY方法代码示例

 function export_xls_fin()
 {
     //get submit parameters
     $col_name = $this->input->get_post("ckod");
     $cou_id = $this->input->get_post("kursus");
     $current_sem = $this->input->get_post("semtr");
     $current_year = $this->input->get_post("tahun");
     $status_stu = $this->input->get_post("stts");
     //get values
     $list_fin = $this->m_report->get_fin_detail($col_name, $cou_id, $current_sem, $current_year, $status_stu);
     //for debug purpose only, safe to delete
     /*echo"<pre>";
     		print_r($list_fin);
     		echo"</pre>";
     		die();*/
     //load our new PHPExcel library
     $this->load->library('excel');
     //activate worksheet number 1
     $this->excel->setActiveSheetIndex(0);
     if ("" == $col_name && "" == $cou_id) {
         //name the worksheet
         $this->excel->getActiveSheet()->setTitle("Semua KV dan Kursus");
         $filename = 'FIN_Semua_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx';
         //save our workbook as this file name
     } else {
         if ("" == $col_name && "" != $cou_id) {
             //name the worksheet
             $this->excel->getActiveSheet()->setTitle("Semua KV");
             $filename = 'FIN_Kolej_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx';
             //save our workbook as this file name
         } else {
             if ("" != $col_name && "" == $cou_id) {
                 //name the worksheet
                 $this->excel->getActiveSheet()->setTitle("Semua Kursus");
                 $filename = 'FIN_Kursus_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx';
                 //save our workbook as this file name
             } else {
                 //name the worksheet
                 $this->excel->getActiveSheet()->setTitle($list_fin[0]->col_name . ' - ' . $list_fin[0]->cou_name);
                 $filename = 'FIN_Semester_' . $list_fin[0]->stu_current_sem . '.xlsx';
                 //save our workbook as this file name
             }
         }
     }
     $namakolej = "";
     $namakursus = "";
     if ($current_sem >= 4) {
         //set the informations
         $namakolej = $list_fin[0]->col_name;
         if ("" != $col_name && "" != $cou_id) {
             $namakursus = $list_fin[0]->cou_name;
             $this->excel->getActiveSheet()->setCellValue('B3', "KURSUS " . strtoupper($namakursus));
         } else {
             $this->excel->getActiveSheet()->setCellValue('B3', "SEMUA KURSUS");
         }
         $this->excel->getActiveSheet()->setCellValue('B2', "FAIL INDUK NAMA {$namakolej}");
         $this->excel->getActiveSheet()->setCellValue('B4', "SEMESTER {$current_sem} TAHUN {$current_year}");
         $this->excel->getActiveSheet()->mergeCells('B2:I2');
         $this->excel->getActiveSheet()->mergeCells('B3:I3');
         $this->excel->getActiveSheet()->mergeCells('B4:I4');
     } else {
         //set the informations
         $namakolej = $list_fin[0]->col_name;
         if ("" != $col_name && "" != $cou_id) {
             $namakursus = $list_fin[0]->cou_name;
             $this->excel->getActiveSheet()->setCellValue('B3', "KURSUS " . strtoupper($namakursus));
         } else {
             $this->excel->getActiveSheet()->setCellValue('B3', "SEMUA KURSUS");
         }
         $this->excel->getActiveSheet()->setCellValue('B2', "FAIL INDUK NAMA {$namakolej}");
         $this->excel->getActiveSheet()->setCellValue('B4', "SEMESTER {$current_sem} TAHUN {$current_year}");
         $this->excel->getActiveSheet()->mergeCells('B2:F2');
         $this->excel->getActiveSheet()->mergeCells('B3:F3');
         $this->excel->getActiveSheet()->mergeCells('B4:F4');
         if (file_exists("./uploaded/kvinfo/Logokolej_small.jpg")) {
             $gdImage = imagecreatefromjpeg('./uploaded/kvinfo/Logokolej_small.jpg');
             $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
             $objDrawing->setImageResource($gdImage);
             //$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); 	// image rendering.
             //$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);			// nanti image jadi low quality
             $objDrawing->setOffsetX(60);
             $objDrawing->setOffsetY(-5);
             $objDrawing->setCoordinates('G2');
             $objDrawing->setWorksheet($this->excel->getActiveSheet());
         }
         if (file_exists("./uploaded/kvinfo/Copkolej_medium.png")) {
             $copImage = imagecreatefrompng('./uploaded/kvinfo/Copkolej_medium.png');
             $transparent = imagecolorallocate($copImage, 0, 0, 0);
             // set color transperent utk image
             // Make the background transparent
             imagecolortransparent($copImage, $transparent);
             // kalau xde ni, background jadi hitam..
             $copDrawing = new PHPExcel_Worksheet_MemoryDrawing();
             $copDrawing->setImageResource($copImage);
             $copDrawing->setOffsetX(60);
             $copDrawing->setOffsetY(-5);
             $copDrawing->setCoordinates('H18');
             $copDrawing->setWorksheet($this->excel->getActiveSheet());
         }
         $this->excel->getActiveSheet()->mergeCellsByColumnAndRow($pColumn1 = 6, $pRow1 = 2, $pColumn2 = 8, $pRow2 = 4);
//.........这里部分代码省略.........

//.........这里部分代码省略.........
             if ($spContainer->getNestingLevel() > 1) {
                 continue;
             }
             // calculate the width and height of the shape
             list($startColumn, $startRow) = PHPExcel_Cell::coordinateFromString($spContainer->getStartCoordinates());
             list($endColumn, $endRow) = PHPExcel_Cell::coordinateFromString($spContainer->getEndCoordinates());
             $startOffsetX = $spContainer->getStartOffsetX();
             $startOffsetY = $spContainer->getStartOffsetY();
             $endOffsetX = $spContainer->getEndOffsetX();
             $endOffsetY = $spContainer->getEndOffsetY();
             $width = PHPExcel_Shared_Excel5::getDistanceX($this->_phpSheet, $startColumn, $startOffsetX, $endColumn, $endOffsetX);
             $height = PHPExcel_Shared_Excel5::getDistanceY($this->_phpSheet, $startRow, $startOffsetY, $endRow, $endOffsetY);
             // calculate offsetX and offsetY of the shape
             $offsetX = $startOffsetX * PHPExcel_Shared_Excel5::sizeCol($this->_phpSheet, $startColumn) / 1024;
             $offsetY = $startOffsetY * PHPExcel_Shared_Excel5::sizeRow($this->_phpSheet, $startRow) / 256;
             switch ($obj['type']) {
                 case 0x8:
                     // picture
                     // get index to BSE entry (1-based)
                     $BSEindex = $spContainer->getOPT(0x104);
                     $BSECollection = $escherWorkbook->getDggContainer()->getBstoreContainer()->getBSECollection();
                     $BSE = $BSECollection[$BSEindex - 1];
                     $blipType = $BSE->getBlipType();
                     // need check because some blip types are not supported by Escher reader such as EMF
                     if ($blip = $BSE->getBlip()) {
                         $ih = imagecreatefromstring($blip->getData());
                         $drawing = new PHPExcel_Worksheet_MemoryDrawing();
                         $drawing->setImageResource($ih);
                         // width, height, offsetX, offsetY
                         $drawing->setResizeProportional(false);
                         $drawing->setWidth($width);
                         $drawing->setHeight($height);
                         $drawing->setOffsetX($offsetX);
                         $drawing->setOffsetY($offsetY);
                         switch ($blipType) {
                             case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_JPEG:
                                 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
                                 $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_JPEG);
                                 break;
                             case PHPExcel_Shared_Escher_DggContainer_BstoreContainer_BSE::BLIPTYPE_PNG:
                                 $drawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
                                 $drawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_PNG);
                                 break;
                         }
                         $drawing->setWorksheet($this->_phpSheet);
                         $drawing->setCoordinates($spContainer->getStartCoordinates());
                     }
                     break;
                 default:
                     // other object type
                     break;
             }
         }
         // treat SHAREDFMLA records
         if ($this->_version == self::XLS_BIFF8) {
             foreach ($this->_sharedFormulaParts as $cell => $baseCell) {
                 $formula = $this->_getFormulaFromStructure($this->_sharedFormulas[$baseCell], $cell);
                 $this->_phpSheet->getCell($cell)->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
             }
         }
     }
     // add the named ranges (defined names)
     foreach ($this->_definedname as $definedName) {
         if ($definedName['isBuiltInName']) {
             switch ($definedName['name']) {
                 case pack('C', 0x6):

 function export_xls_jadual_kedatangan()
 {
     $abc = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL');
     $centreCode = $this->input->get('kodpusat');
     $semester = $this->input->get('semester');
     $year = $this->input->get('slct_tahun');
     $course = $this->input->get('slct_kursus');
     $module = $this->input->get('modul');
     $status = $this->input->get('statusID');
     $cC = explode("-", $centreCode);
     $data['student'] = $this->m_result->get_module_taken($cC[0], $semester, $year, $course, $status, $module);
     $data['nama_kursus'] = $this->m_result->ambik_nama_kursus($course);
     //$data['nama_modul'] = $this->m_result->get_module_jadual_kedatangan($course);
     /**FDPO - Safe to be deleted**/
     //echo('<pre>');print_r($module);echo('</pre>');
     //die();
     $index = sizeof($data['student']);
     //load our new PHPExcel library
     $this->load->library('excel');
     //activate worksheet number 1
     $this->excel->setActiveSheetIndex(0);
     //name the worksheet
     $this->excel->getActiveSheet()->setTitle("Jadual Kedatangan Calon");
     //$highlightCells = array();
     //header
     $excel_header = array('BIL', 'NAMA', 'ANGKA GILIRAN', 'TINDAKAN');
     //$excel_jadual = array();
     //$data['student'] = $this->m_result->get_module_taken($cC[0], $semester,$year,$course,$status,$module);
     $filename = 'Jadual Kedatangan Calon_' . $data['nama_kursus']->cou_name . '_' . $module . '.xls';
     //save our workbook as this file name
     //load the header into position A1
     $this->excel->getActiveSheet()->fromArray($excel_header, NULL, 'A10');
     //	die();
     $ttl = 0;
     $columnCount = 65;
     //masukkkan data disini
     $index = 1;
     $excel_data = array();
     //$j = 0;
     if (isset($data['student'])) {
         //array_push($r, $index);
         $tidakHadir = 1;
         $hospital = 2;
         foreach ($data['student'] as $p) {
             $r = array();
             array_push($r, $index);
             array_push($r, strval(name_strtoupper($p->stu_name)));
             array_push($r, $p->stu_matric_no);
             if ($p->na_status == $tidakHadir) {
                 array_push($r, "T");
             } else {
                 if ($p->na_status == $hospital) {
                     array_push($r, "H");
                 } else {
                     array_push($r, "/");
                 }
             }
             array_push($excel_data, $r);
             $index++;
         }
     }
     //echo "<pre>";
     //print_r($excel_data);
     //echo "</pre>";
     //die();
     //load the data into position C4
     $this->excel->getActiveSheet()->fromArray($excel_data, NULL, 'A11');
     //set the informations
     $this->excel->getActiveSheet()->setCellValue('A2', 'Jadual Kedatangan Calon');
     $this->excel->getActiveSheet()->mergeCells('A2:Z2');
     $this->excel->getActiveSheet()->setCellValue('A3', 'Kursus    :    ' . $data['nama_kursus']->cou_name);
     $this->excel->getActiveSheet()->mergeCells('A3:Z3');
     $this->excel->getActiveSheet()->setCellValue('A4', 'Semester     :   ' . $semester);
     $this->excel->getActiveSheet()->mergeCells('A4:Z4');
     $this->excel->getActiveSheet()->setCellValue('A5', 'T = Tidak Hadir');
     $this->excel->getActiveSheet()->mergeCells('A5:Z5');
     $this->excel->getActiveSheet()->setCellValue('A6', 'H = Hospital');
     $this->excel->getActiveSheet()->mergeCells('A6:Z6');
     $this->excel->getActiveSheet()->setCellValue('A7', '/ = Hadir');
     $this->excel->getActiveSheet()->mergeCells('A7:Z7');
     if (file_exists("./uploaded/kvinfo/Logokolej_small.jpg")) {
         $gdImage = imagecreatefromjpeg('./uploaded/kvinfo/Logokolej_small.jpg');
         $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
         $objDrawing->setImageResource($gdImage);
         $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
         // image rendering.
         $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
         // nanti image jadi low quality
         $objDrawing->setOffsetX(60);
         $objDrawing->setOffsetY(-5);
         $objDrawing->setCoordinates('C2');
         $objDrawing->setWorksheet($this->excel->getActiveSheet());
     }
     if (file_exists("./uploaded/kvinfo/Copkolej_medium.png")) {
         $copImage = imagecreatefrompng('./uploaded/kvinfo/Copkolej_medium.png');
         $transparent = imagecolorallocate($copImage, 0, 0, 0);
         // set color transperent utk image
         // Make the background transparent
         imagecolortransparent($copImage, $transparent);
         // kalau xde ni, background jadi hitam..
//.........这里部分代码省略.........

    $ews2->setCellValue('c' . $i, "=C" . ($i - 1) . "+G1*('Raw_Data'!C" . $i . "-C" . ($i - 1) . ")");
    $ews2->setCellValue('d' . $i, "=D" . ($i - 1) . "+G1*('Raw_Data'!D" . $i . "-D" . ($i - 1) . ")");
}
$ews2->getRowDimension(1)->setRowHeight(59);
$ews2->getColumnDimension('L')->setWidth(42);
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('Sample image');
$objDrawing->setDescription('Sample image');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
$objDrawing->setCoordinates('L1');
$objDrawing->setWidth(300);
$objDrawing->setHeight(38);
$objDrawing->setOffsetX(20);
$objDrawing->setOffsetY(25);
$objDrawing->setWorksheet($ews2);
$dsl2 = array(new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$B$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$C$1', NULL, 1), new PHPExcel_Chart_DataSeriesValues('String', 'Filtered_Data!$D$1', NULL, 1));
$xal2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$A$2:$A$' . $ca, NULL, $ca));
$dsv2 = array(new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$B$2:$B$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$C$2:$C$' . $ca, NULL, $ca), new PHPExcel_Chart_DataSeriesValues('Number', 'Filtered_Data!$D$2:$D$' . $ca, NULL, $ca));
$dsv2[0]->setPointMarker('none');
$dsv2[1]->setPointMarker('none');
$dsv2[2]->setPointMarker('none');
$ds2 = new PHPExcel_Chart_DataSeries(PHPExcel_Chart_DataSeries::TYPE_LINECHART, PHPExcel_Chart_DataSeries::GROUPING_STANDARD, range(0, count($dsv2) - 1), $dsl2, $xal2, $dsv2);
$pa2 = new PHPExcel_Chart_PlotArea(NULL, array($ds2));
$legend2 = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);
$chart2 = new PHPExcel_Chart('chart2', NULL, $legend2, $pa2, true, 0, NULL, NULL);
$chart2->setTopLeftPosition('F3');
$chart2->setBottomRightPosition('Z22');
$ews2->addChart($chart2);
$ea->setActiveSheetIndex(0);

function foo($products)
{
    // Подключаем класс для работы с excel
    require_once '../Classes/PHPExcel.php';
    // Подключаем класс для вывода данных в формате excel
    require_once '../Classes/PHPExcel/Writer/Excel5.php';
    // Создаем объект класса PHPExcel
    $xls = new PHPExcel();
    // Устанавливаем индекс активного листа
    $xls->setActiveSheetIndex(0);
    // Получаем активный лист
    $sheet = $xls->getActiveSheet();
    // Подписываем лист
    $sheet->setTitle('Таблица умножения');
    //Стиль для заголовков
    $styleHeaders = array('font' => array('bold' => true, 'size' => 14));
    //Тестовый json
    //$json = '{"a":1,"b":2,"c":3,"d":4,"e":5}';
    //$test_array = json_decode($json);
    //Тестовый массив
    //$test_array = array("a" => "orange", "b" => "banana", "c" => "apple");
    //Заголовки
    $sheet->getColumnDimension('A')->setWidth(10);
    //Центруем текст по горизонтали
    $sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("B1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("C1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //Задаем стили
    $sheet->getStyle("A1")->applyFromArray($styleHeaders);
    $sheet->getStyle("B1")->applyFromArray($styleHeaders);
    $sheet->getStyle("C1")->applyFromArray($styleHeaders);
    //Задаем высоту верхней строки
    $sheet->getRowDimension(1)->setRowHeight(50);
    //Заносим значения
    $sheet->setCellValueByColumnAndRow(0, 1, 'Фото');
    $sheet->setCellValueByColumnAndRow(1, 1, 'Наименование');
    $sheet->setCellValueByColumnAndRow(2, 1, 'Кол-во (шт.)');
    //Данные
    $row_count = 2;
    foreach ($products as &$prod) {
        //Изображение
        $imagePath = realpath(dirname(dirname(__FILE__))) . '/files/originals/' . $prod->image->url;
        if (file_exists($imagePath)) {
            $logo = new PHPExcel_Worksheet_MemoryDrawing();
            $logo->setName($prod->name);
            $gdImage = imagecreatefromjpeg($imagePath);
            $logo->setImageResource($gdImage);
            $logo->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
            $logo->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
            $logo->setHeight(50);
            $logo->setCoordinates("A" . $row_count);
            $logo->setOffsetX(2);
            $logo->setOffsetY(5);
            $sheet->getRowDimension($row_count)->setRowHeight(45);
            $logo->setWorksheet($sheet);
        }
        //$sheet->setCellValueByColumnAndRow(0, $row_count, $prod->image->url);
        $sheet->setCellValueByColumnAndRow(1, $row_count, $prod->name);
        $sheet->setCellValueByColumnAndRow(2, $row_count, $prod->summ);
        $sheet->getStyle("B" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle("B" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle("C" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle("C" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $row_count++;
    }
    //Автоматическая ширина столбцов
    foreach (range('B', 'D') as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    //$row_count=1;
    //foreach($test_array as $key => $value){
    //$sheet->setCellValueByColumnAndRow(0, $row_count, $key);
    //$sheet->setCellValueByColumnAndRow(1, $row_count, $value);
    //$row_count++;
    //}
    // Выводим HTTP-заголовки
    header("Expires: Mon, 1 Apr 1974 05:00:00 GMT");
    header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
    header("Cache-Control: no-cache, must-revalidate");
    header("Pragma: no-cache");
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=matrix.xls");
    // Выводим содержимое файла
    $objWriter = new PHPExcel_Writer_Excel5($xls);
    $objWriter->save('php://output');
    return $retval;
}

foreach ($products as &$prod) {
    //Изображение
    //$imagePath = realpath(dirname(dirname(__FILE__))) . '../files/originals/' . $prod->image->url;
    //$imagePath = realpath(dirname(dirname(__FILE__))) . '../files/originals/' . $prod->image->url;
    $imagePath = '/home/v/vdmgrup/rostokgroup.ru/public_html/files/originals/' . $prod->image->url;
    if (file_exists($imagePath)) {
        $logo = new PHPExcel_Worksheet_MemoryDrawing();
        $logo->setName($prod->name);
        $gdImage = imagecreatefromjpeg($imagePath);
        $logo->setImageResource($gdImage);
        $logo->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
        $logo->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
        $logo->setHeight(50);
        $logo->setCoordinates("A" . $row_count);
        $logo->setOffsetX(2);
        $logo->setOffsetY(5);
        $sheet->getRowDimension($row_count)->setRowHeight(45);
        $logo->setWorksheet($sheet);
    }
    //$sheet->setCellValueByColumnAndRow(0, $row_count, $prod->image->url);
    $sheet->setCellValueByColumnAndRow(1, $row_count, $prod->name);
    $sheet->setCellValueByColumnAndRow(2, $row_count, $prod->summ);
    $sheet->getStyle("B" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("B" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $sheet->getStyle("C" . $row_count)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle("C" . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $row_count++;
}
//Автоматическая ширина столбцов
foreach (range('B', 'D') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);

//.........这里部分代码省略.........
     }
     foreach ($jobs as $ticket) {
         $row = array($ticket['_id']);
         if (isset($static['last_update'])) {
             $row[] = date('d-m-Y H:i', Arr::get($ticket, 'last_update', $ticket['created']));
         }
         if (isset($static['last_submit'])) {
             $row[] = Arr::get($ticket, 'last_submit') ? date('d-m-Y H:i', $ticket['last_submit']) : '';
         }
         if (isset($static['status']) && Group::current('show_all_jobs')) {
             $row[] = Arr::get(Enums::$statuses, Arr::get($ticket, 'status', 0), 'Unknown');
         }
         if (isset($static['types'])) {
             if (Group::current('allow_assign')) {
                 $row[] = implode(', ', array_intersect_key($types, Arr::get($ticket, 'assigned', array())));
             } else {
                 $row[] = implode(', ', array_intersect_key($types, array_filter(Arr::get($ticket, 'assigned', array()), function ($x) {
                     return $x == User::current('company_id');
                 })));
             }
         }
         if (isset($static['companies'])) {
             $row[] = implode(', ', array_intersect_key($companies, array_flip(Arr::get($ticket, 'assigned', array()))));
         }
         if (isset($static['ex'])) {
             $row[] = implode(', ', array_intersect_key($companies, array_flip(Arr::get($ticket, 'ex', array()))));
         }
         if (isset($static['pending'])) {
             $row[] = Arr::get($submissions, $ticket['_id']);
         }
         if (isset($static['attachments'])) {
             $row[] = Arr::get($attachments, $ticket['_id']);
         }
         foreach (Columns::get_search() as $id => $type) {
             $row[] = Arr::path($ticket, array('data', $id)) ? Columns::output($ticket['data'][$id], Columns::get_type($id), true) : '';
         }
         if ($action == 'excel') {
             $i++;
             $sheet->fromArray($row, NULL, 'A' . $i);
             $x = count($row);
             if (isset($attachments_list[$ticket['_id']])) {
                 $sheet->getRowDimension($i)->setRowHeight(80);
             }
             foreach (Arr::get($attachments_list, $ticket['_id'], array()) as $image) {
                 if (!file_exists(DOCROOT . 'storage/' . $image . '.thumb')) {
                     if (!file_exists(DOCROOT . 'storage/' . $image)) {
                         continue;
                     }
                     $data = file_get_contents(DOCROOT . 'storage/' . $image);
                     $er = error_reporting(0);
                     $img = imagecreatefromstring($data);
                     error_reporting($er);
                     if (!$img) {
                         continue;
                     }
                     $x = imagesx($img);
                     $y = imagesy($img);
                     $size = max($x, $y);
                     $x = round($x / $size * 96);
                     $y = round($y / $size * 96);
                     $thumb = imagecreatetruecolor($x, $y);
                     imagealphablending($thumb, false);
                     imagesavealpha($thumb, true);
                     imagecopyresampled($thumb, $img, 0, 0, 0, 0, $x, $y, imagesx($img), imagesy($img));
                     imagepng($thumb, DOCROOT . 'storage/' . $image . '.thumb', 9);
                 }
                 $data = file_get_contents(DOCROOT . 'storage/' . $image . '.thumb');
                 $img = imagecreatefromstring($data);
                 $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
                 $objDrawing->setImageResource($img);
                 $coord = PHPExcel_Cell::stringFromColumnIndex($x++);
                 $objDrawing->setCoordinates($coord . $i);
                 $objDrawing->setOffsetY(5);
                 $objDrawing->setResizeProportional(true);
                 $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
                 $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                 $objDrawing->setWorksheet($sheet);
                 //$sheet->getCell($coord . $i)->setHyperlink(new PHPExcel_Cell_Hyperlink(URL::site('download/attachment/' . $image, 'http'), 'Show Image'));
             }
         } else {
             fputcsv($file, $row);
         }
     }
     if ($action == 'excel') {
         $name = tempnam(sys_get_temp_dir(), 'excel');
         header('Content-type: application/xlsx');
         header('Content-disposition: filename="SearchResults.xlsx"');
         $writer = new PHPExcel_Writer_Excel2007($excel);
         $writer->save($name);
         readfile($name);
         unlink($name);
     } else {
         fseek($file, 0);
         header('Content-type: text/csv');
         header('Content-disposition: filename="SearchResults.csv"');
         fpassthru($file);
         fclose($file);
     }
     die;
 }

    /**
	 * Panes are frozen? (in sheet currently being read). See WINDOW2 record.
	 *
	 * @var boolean
	 */
    private $_frozen;
    /**
	 * Fit printout to number of pages? (in sheet currently being read). See SHEETPR record.
	 *
	 * @var boolean
	 */
    private $_isFitToPages;
    /**
	 * Objects. One OBJ record contributes with one entry.
	 *
	 * @var array
	 */
    private $_objs;
    /**
	 * Text Objects. One TXO record corresponds with one entry.
	 *
	 * @var array
	 */
    private $_textObjects;
    /**
	 * Cell Annotations (BIFF8)
	 *
	 * @var array
	 */
    private $_cellNotes;
    /**
	 * The combined MSODRAWINGGROUP data
	 *
	 * @var string
	 */
    private $_drawingGroupData;
    /**
	 * The combined MSODRAWING data (per sheet)
	 *
	 * @var string
	 */
    private $_drawingData;
    /**
	 * Keep track of XF index
	 *
	 * @var int
	 */
    private $_xfIndex;
    /**
	 * Mapping of XF index (that is a cell XF) to final index in cellXf collection
	 *
	 * @var array
	 */
    private $_mapCellXfIndex;
    /**
	 * Mapping of XF index (that is a style XF) to final index in cellStyleXf collection
	 *
	 * @var array
	 */
    private $_mapCellStyleXfIndex;
    /**
	 * The shared formulas in a sheet. One SHAREDFMLA record contributes with one value.
	 *
	 * @var array
	 */
    private $_sharedFormulas;
    /**
	 * The shared formula parts in a sheet. One FORMULA record contributes with one value if it
	 * refers to a shared formula.
	 *
	 * @var array
	 */
    private $_sharedFormulaParts;
    /**
	 *	Read data only?
	 *		If this is true, then the Reader will only read data values for cells, it will not read any formatting information.
	 *		If false (the default) it will read data and formatting.
	 *
	 *	@return	boolean
	 */
    public function getReadDataOnly()
    {
        return $this->_readDataOnly;
    }
    /**
	 *	Set read data only
	 *		Set to true, to advise the Reader only to read data values for cells, and to ignore any formatting information.
	 *		Set to false (the default) to advise the Reader to read both data and formatting for cells.
	 *
	 *	@param	boolean	$pValue
	 *
	 *	@return	PHPExcel_Reader_Excel5
	 */
    public function setReadDataOnly($pValue = false)
    {
        $this->_readDataOnly = $pValue;
        return $this;
    }
    /**
	 *	Get which sheets to load
//.........这里部分代码省略.........