PHP PHPExcel_Shared_Font::setAutoSizeMethod方法代码示例

 public function __construct(LoggerInterface $logger, PHPExcelFactory $phpExcel)
 {
     parent::__construct($logger);
     $this->phpexcel = $phpExcel;
     $this->handle = $this->phpexcel->createPHPExcelObject();
     \PHPExcel_Shared_Font::setAutoSizeMethod(\PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
 }

 function create_excel($data = array(), $category)
 {
     $data['product_details'] = $data;
     $category_name = $this->products_model->get_category_details($category)->category_name;
     //activate worksheet number 1
     $this->excel->setActiveSheetIndex(0);
     //name the worksheet
     $this->excel->getActiveSheet()->setTitle($category_name . '-Product Catalog');
     //set cell A1 content with some text
     $this->excel->getActiveSheet()->setCellValue('A1', $category_name);
     $testArray = ['Product Name', 'Description', 'Price per Unit', 'Color'];
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     foreach (range('A', 'D') as $columnID) {
         $this->excel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
     }
     $this->excel->getActiveSheet()->getStyle("A2:D2")->getFont()->setBold(true);
     //change the font size
     $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
     //make the font become bold
     $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
     //merge cell A1 until D1
     $this->excel->getActiveSheet()->mergeCells('A1:D1');
     // //set aligment to center for that merged cell (A1 to D1)
     $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     $this->excel->getActiveSheet()->fromArray($testArray, NULL, 'A2');
     $product_details = array();
     foreach ($data['product_details'] as $key => $value) {
         $clean_data = array();
         foreach ($value as $k => $v) {
             if ($k == 'product_name' || $k == 'description' || $k == 'price' || $k == 'color') {
                 $clean_data[$k] = $v;
             }
         }
         $product_details[] = $clean_data;
     }
     $this->excel->getActiveSheet()->fromArray($product_details, NULL, 'A3');
     $filename = date("d-m-Y-h:i:s") . '.xls';
     //save our workbook as this file name
     header('Content-Type: application/vnd.ms-excel');
     //mime type
     header('Content-Disposition: attachment;filename="' . $filename . '"');
     //tell browser what's the file name
     header('Cache-Control: max-age=0');
     //no cache
     //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
     //if you want to save it as .XLSX Excel 2007 format
     $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
     //force user to download the Excel file without writing it to server's HD
     $objWriter->save('php://output');
 }

 *
 * ----------------------------------------------------------------------
 */
$t_display = $this->getVar('t_display');
$va_display_list = $this->getVar('display_list');
$vo_result = $this->getVar('result');
$vn_items_per_page = $this->getVar('current_items_per_page');
$vs_current_sort = $this->getVar('current_sort');
$vn_ratio_pixels_to_excel_height = 0.85;
$vn_ratio_pixels_to_excel_width = 0.135;
$va_supercol_a_to_z = range('A', 'Z');
$vs_supercol = '';
$va_a_to_z = range('A', 'Z');
$workbook = new PHPExcel();
// more accurate (but slower) automatic cell size calculation
PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
$o_sheet = $workbook->getActiveSheet();
// mise en forme
$columntitlestyle = array('font' => array('name' => 'Arial', 'size' => 12, 'bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => true, 'shrinkToFit' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THICK)));
$cellstyle = array('font' => array('name' => 'Arial', 'size' => 11, 'bold' => false), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'wrap' => true, 'shrinkToFit' => true), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$o_sheet->getDefaultStyle()->applyFromArray($cellstyle);
$o_sheet->setTitle("CollectiveAccess");
$vn_line = 1;
$vs_column = reset($va_a_to_z);
// Column headers
$o_sheet->getRowDimension($vn_line)->setRowHeight(30);
foreach ($va_display_list as $vn_placement_id => $va_info) {
    if ($vs_column) {
        $o_sheet->setCellValue($vs_supercol . $vs_column . $vn_line, $va_info['display']);
        $o_sheet->getStyle($vs_supercol . $vs_column . $vn_line)->applyFromArray($columntitlestyle);
        if (!($vs_column = next($va_a_to_z))) {

 //
 $xlWS->setSelectedCells('A1');
 // List of generated files
 $files = array();
 if (ExportFileFormat::isExcel()) {
     $files[] = $filename = $base_filename . '.xlsx';
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     save($xl, $filename, 'PHPExcel_Writer_Excel2007');
 }
 if (ExportFileFormat::isPDF()) {
     $files[] = $filename = $base_filename . '.pdf';
     // PDF output relies on ShowGridlines and not PrintGridlines
     $xlWS->setShowGridlines(false);
     // With 'exact' autosize method, columns are too narrow, causing cell
     // contents (e.g. phone numbers) to wrap
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_APPROX);
     save($xl, $filename, 'PHPExcel_Writer_PDF');
 }
 // ------------------------------------------------------------------------
 // Send e-mail
 //
 if ($opt['send'] || $opt['debug']) {
     if ($send_email) {
         $recipients = array('to' => array_unique($recipients), 'bcc' => array_column($profs, 'email'));
         echo '  ';
         send_email($classe_fullname, $recipients, MAIL_BODY_SEND_CLASSLIST, $files, 'info@apecove.ch,APECOVE');
     }
     if (!$opt['keep']) {
         foreach ($files as $filename) {
             unlink($filename);
         }

 private function createXLSDate()
 {
     include_once APPPATH . 'third_party/PHPExcel/PHPExcel.php';
     $objPHPExcel = new PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Inmediative")->setLastModifiedBy("Inmediative")->setTitle($this->config->item('client', 'app'))->setSubject($this->config->item('client', 'app'))->setDescription($this->config->item('client', 'app'))->setKeywords($this->config->item('client', 'app'))->setCategory("Result file");
     $alphabet = range('A', 'Z');
     // Add some data
     $fields = array('DESTINATARIO DEL ENVÍO', 'DIRECCION', 'CIUDAD', 'REGION/ESTADO', 'C.P.', 'PAIS', 'TELEFONO', 'EMAIL', 'PESO', 'TRACK', 'Lista de productos', 'CODIGO', 'FECHA', 'DNI/CIF/ ID NUMBER/UE VAT', 'CLIENTE', 'DIRECCION COMPLETA', 'BASE 4%', 'IVA 4%', 'BASE 10%', 'IVA 10%', 'BASE 21%', 'IVA 21%', 'TOTAL', 'GASTOS ENVIO', 'CATEGORÍA');
     $dd = date('Y-m-d', strtotime('-1 week'));
     $sql = "select id_cart from cart where id_state = 3 AND modified > '{$dd}' order by modified";
     $result = $this->db->query($sql)->result();
     $k = 2;
     $sheet = $objPHPExcel->setActiveSheetIndex(0);
     foreach ($fields as $ke => $value) {
         $sheet->setCellValue($alphabet[$ke] . '1', $value);
     }
     foreach ($result as $row) {
         $idc = $row->id_cart;
         $fdata = $this->Cart->DataCart($idc);
         $cdata = $this->Cart->DataCartComplete($idc);
         $cartItems = $this->Cart->ListItems($idc);
         if (!$fdata || !$cdata) {
             die('NO XLS');
         }
         $itemInvoice = $this->Data->GetCountry($fdata['id_country']);
         $type = 'iva_ind';
         if ($fdata['type'] == 'company') {
             $type = 'iva_emp';
         }
         if ($fdata['id_region']) {
             $ret = $this->Data->GetRegion($fdata['id_region'], $fdata['id_country']);
             if ($ret) {
                 $itemInvoice = $ret;
             }
         }
         $key = 0;
         $productsArr = array();
         $catArr = array();
         foreach ($cartItems as $c) {
             $productsArr[] = $c->title . ($c->size ? " {$c->size}" : "") . ' x' . $c->items;
             $catArr[] = strtoupper($c->category);
         }
         $products = implode(', ', $productsArr);
         $categories = implode(', ', $catArr);
         $dateTimeZone = new DateTimeZone('Europe/Madrid');
         $dateTime = new DateTime();
         $dateTime->setTimestamp(mysql_to_unix($cdata->modified));
         $dateTime->setTimeZone($dateTimeZone);
         $code = $cdata->invoice_code . str_pad($cdata->code, 4, "0", STR_PAD_LEFT);
         $sheet = $objPHPExcel->setActiveSheetIndex(0);
         if ($fdata['dname']) {
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['dname'] . ' ' . $fdata['dlastname']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['daddress']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['dcity']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['dprovince']);
             $sheet->setCellValue($alphabet[$key++] . $k, ' ' . $fdata['dcp']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['country2_name']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['dphone']);
         } else {
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['name'] . ' ' . $fdata['lastname']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['address']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['city']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['province']);
             $sheet->setCellValue($alphabet[$key++] . $k, ' ' . $fdata['cp']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['country_name']);
             $sheet->setCellValue($alphabet[$key++] . $k, $fdata['phone']);
         }
         $sheet->setCellValue($alphabet[$key++] . $k, $fdata['mail']);
         $sheet->setCellValue($alphabet[$key++] . $k, '');
         $sheet->setCellValue($alphabet[$key++] . $k, '');
         $sheet->setCellValue($alphabet[$key++] . $k, $products);
         $sheet->setCellValue($alphabet[$key++] . $k, $code);
         $sheet->setCellValue($alphabet[$key++] . $k, $dateTime->format('d/m/Y H:i:s'));
         $sheet->setCellValue($alphabet[$key++] . $k, $fdata['taxid'] ? $fdata['taxid'] : $fdata['vat']);
         $sheet->setCellValue($alphabet[$key++] . $k, $fdata['name'] . ' ' . $fdata['lastname']);
         $sheet->setCellValue($alphabet[$key++] . $k, $fdata['address'] . ', ' . $fdata['city'] . ', ' . $fdata['province'] . ', ' . $fdata['cp'] . ', ' . $fdata['country_name']);
         if ($itemInvoice && round($itemInvoice->{$type})) {
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax4t - $cdata->tax4, true, false));
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax4, true, false));
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax10t - $cdata->tax10, true, false));
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax10, true, false));
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax21t - $cdata->tax21, true, false));
             $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->tax21, true, false));
         } else {
             $sheet->setCellValue($alphabet[$key++] . $k, '');
             $sheet->setCellValue($alphabet[$key++] . $k, '');
             $sheet->setCellValue($alphabet[$key++] . $k, '');
             $sheet->setCellValue($alphabet[$key++] . $k, '');
             $sheet->setCellValue($alphabet[$key++] . $k, '');
             $sheet->setCellValue($alphabet[$key++] . $k, '');
         }
         $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->total, true, false));
         $sheet->setCellValue($alphabet[$key++] . $k, prep_costd($cdata->shipping_notax, true, false));
         $sheet->setCellValue($alphabet[$key++] . $k, $categories);
         $k++;
     }
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     $objPHPExcel->setActiveSheetIndex(0);
     foreach (range('A', $objPHPExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
//.........这里部分代码省略.........

 protected function exportExcel($props)
 {
     $props = array_merge(array('title' => '', 'subtitle' => '', 'headers' => null, 'data' => null), $props);
     if ($props['subtitle']) {
         $props['title'] .= ' - ' . $props['subtitle'];
     }
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     $logoPath = 'images/logo_academy.png';
     $props['doc'] = array_merge(array('title' => 'Carus', 'subject' => 'Reporte', 'description' => 'Reporte de sistema', 'keywords' => 'Reporte', 'category' => 'Reporte'), isset($props['doc']) && is_array($props['doc']) ? $props['doc'] : array());
     $title = preg_replace('/([^a-z0-9áéíóúàèìòùâêîôûñ\\-_ ]+)/i', '', isset($props['title']) ? $props['title'] : $props['doc']['title']);
     if (strlen($title) > 30) {
         $title = substr($title, 0, strripos($title, " ", 30 - strlen($title)));
     }
     $objPHPExcel = new PHPExcel();
     $objPHPExcel->getProperties()->setCreator("Imagen Digital")->setLastModifiedBy("Imagen Digital")->setTitle($props['doc']['title'])->setSubject($props['doc']['subject'])->setDescription($props['doc']['description'])->setKeywords($props['doc']['keywords'])->setCategory($props['doc']['category']);
     $objPHPExcel->setActiveSheetIndex(0);
     $objPHPExcel->getDefaultStyle()->getFont()->setName('Verdana')->setSize(8)->setColor(new PHPExcel_Style_Color('FF333333'));
     $sheet = $objPHPExcel->getActiveSheet();
     // Set page orientation, size, Print Area and Fit To Pages
     $objPageSetup = new PHPExcel_Worksheet_PageSetup();
     $objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER);
     $objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
     $objPageSetup->setFitToWidth(1);
     $sheet->setPageSetup($objPageSetup);
     $sheet->setTitle($title);
     $sheet->getDefaultStyle()->applyFromArray(array('alignment' => array('wrap' => false)));
     $objDrawing = new PHPExcel_Worksheet_Drawing();
     $objDrawing->setWorksheet($sheet);
     $objDrawing->setName("logo");
     $objDrawing->setDescription("Carus");
     $objDrawing->setPath($logoPath);
     $objDrawing->setCoordinates('A1');
     $objDrawing->setWidth(148);
     $objDrawing->setHeight(30);
     $sheet->getRowDimension(1)->setRowHeight(20);
     $styleTitle = array('font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFFFF'), 'size' => 8, 'name' => 'Verdana'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FF333333')), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF333333'))), 'alignment' => array('wrap' => false, 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
     $styleText = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FF0B7C98'))), 'alignment' => array('vertical' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
     $col = 0;
     $fil = 3;
     $colMax = 0;
     $sheet->setCellValueByColumnAndRow($col, $fil, $title);
     $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle);
     $fil++;
     foreach ($props['headers'] as $value) {
         $sheet->setCellValueByColumnAndRow($col, $fil, $value);
         $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleTitle);
         $col++;
     }
     $colMax = max($colMax, $col);
     $fil++;
     foreach ($props['data'] as $row) {
         $col = 0;
         foreach ($props['headers'] as $key => $value) {
             $sheet->setCellValueByColumnAndRow($col, $fil, $row[$value]);
             $sheet->getStyleByColumnAndRow($col, $fil)->applyFromArray($styleText);
             $col++;
         }
         $fil++;
     }
     $colMax = max($colMax, $col);
     $sheet->mergeCellsByColumnAndRow(0, 1, $colMax - 1, 1);
     $sheet->mergeCellsByColumnAndRow(0, 3, $colMax - 1, 3);
     for ($c = 0; $c < $colMax; $c++) {
         $sheet->getColumnDimensionByColumn($c)->setAutoSize(true);
         $calculatedWidth = $sheet->getColumnDimensionByColumn($c)->getWidth();
         $sheet->getColumnDimensionByColumn($c)->setAutoSize(false);
         $sheet->getColumnDimensionByColumn($c)->setWidth(max((int) $calculatedWidth * 1.05, 12));
     }
     $sheet->calculateColumnWidths();
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     //header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
     header('Cache-Control: max-age=0');
     // If you're serving to IE 9, then the following may be needed
     header('Cache-Control: max-age=1');
     // If you're serving to IE over SSL, then the following may be needed
     header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
     // Date in the past
     header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
     // always modified
     header('Cache-Control: cache, must-revalidate');
     // HTTP/1.1
     header('Pragma: public');
     // HTTP/1.0
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
     $objWriter->save('php://output');
     exit;
 }

function smamo_excel_export($priv)
{
    $response = array();
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', FALSE);
    ini_set('display_startup_errors', FALSE);
    date_default_timezone_set('Europe/Copenhagen');
    define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
    /** Include PHPExcel */
    require_once get_template_directory() . '/functions/admin/phpexcel/PHPExcel.php';
    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
    $titleTimeFormat = date_i18n('d-m-Y-H-i');
    // Set document properties
    //echo date('H:i:s') , " Set document properties" , EOL;
    $objPHPExcel->getProperties()->setCreator("FSTA Serveren")->setLastModifiedBy("FSTA Serveren")->setTitle("Deltagerliste-" . $titleTimeFormat)->setSubject("Deltagerliste oprettet " . $titleTimeFormat)->setDescription("Deltagerliste oprettet " . $titleTimeFormat)->setKeywords("")->setCategory("");
    // Get posts
    $i = 1;
    $oldI = 1;
    $args = array('posts_per_page' => -1, 'offset' => 0, 'category' => isset($_POST['cat']) ? wp_strip_all_tags($_POST['cat']) : '', 'orderby' => 'post_date', 'order' => 'ASC', 'post_type' => isset($_POST['post_type']) ? wp_strip_all_tags($_POST['post_type']) : 'post', 'post_status' => isset($_POST['post_status']) ? wp_strip_all_tags($_POST['post_status']) : 'publish', 'suppress_filters' => true);
    $myposts = get_posts($args);
    if (isset($_POST['post_type']) && $_POST['post_type'] === 'medlem' && $priv === true) {
        require_once get_template_directory() . '/functions/ajax/excel/medlem.php';
    } else {
        if (isset($_POST['post_type']) && $_POST['post_type'] === 'medlem') {
            require_once get_template_directory() . '/functions/ajax/excel/nopriv-medlem.php';
        } else {
            if (isset($_POST['post_type']) && $_POST['post_type'] === 'tilmelding') {
                require get_template_directory() . '/functions/ajax/excel/tilmelding.php';
            } else {
                $response['file'] = get_bloginfo('url');
                wp_die(json_encode($response));
            }
        }
    }
    // Rename worksheet
    $objPHPExcel->getActiveSheet()->setTitle($list_type_name['upper'] . '-' . $titleTimeFormat);
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
    foreach (range('B', 'Q') as $columnID) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
    }
    // Save Excel 95 file
    $callStartTime = microtime(true);
    $save_file_to = str_replace(__FILE__, $list_type_name['lower'] . '-' . $titleTimeFormat . '.xls', __FILE__);
    $exports_path = '/wp-content/exports/' . $save_file_to;
    $full_path = ABSPATH . $exports_path;
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($full_path);
    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;
    // Echo Gemt sti
    $response['file'] = get_bloginfo('url') . $exports_path;
    if (is_user_logged_in()) {
        $response['user'] = get_currentuserinfo();
    }
    echo json_encode($response);
    exit;
}

 private static function ExcelApplyValues($sheet, $rows, $params = [])
 {
     /* Границы таблицы */
     $ramka = array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN)));
     /* Жирный шрифт для шапки таблицы */
     $font = array('font' => array('bold' => true), 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
     $Matlog = new Matlog();
     $Employeelog = new Employeelog();
     $Traflog = new Traflog();
     $attributeslabels = array_merge($Matlog->attributeLabels(), $Employeelog->attributeLabels(), $Traflog->attributeLabels());
     if (count($rows) > 0) {
         $col = 0;
         foreach (array_keys($rows[0]) as $attr) {
             if (!in_array($attr, ['idmatlog', 'idemployeelog'])) {
                 $sheet->setCellValueByColumnAndRow($col, 1, $attributeslabels[$attr]);
                 $sheet->getStyleByColumnAndRow($col, 1)->applyFromArray($ramka);
                 $sheet->getStyleByColumnAndRow($col, 1)->applyFromArray($font);
                 $col++;
             }
         }
     }
     if (count($rows) > 0) {
         foreach ($rows as $i => $row) {
             $col = 0;
             foreach ($row as $attr => $value) {
                 if (!in_array($attr, ['idmatlog', 'idemployeelog'])) {
                     if (isset($params['date']) && in_array($attr, $params['date']) && $value !== NULL) {
                         $rows[$i][$attr] = date('d.m.Y', strtotime($value));
                     }
                     if (isset($params['datetime']) && in_array($attr, $params['datetime']) && $value !== NULL) {
                         $rows[$i][$attr] = date('d.m.Y H:i:s', strtotime($value));
                     }
                     if (isset($params['case']) && in_array($attr, array_keys($params['case']))) {
                         $rows[$i][$attr] = $params['case'][$attr][$value];
                     }
                     $sheet->getStyleByColumnAndRow($col, $i + 2)->applyFromArray($ramka);
                     if (isset($params['string']) && in_array($attr, $params['string'])) {
                         $sheet->getStyleByColumnAndRow($col, $i + 2)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                         $sheet->setCellValueExplicitByColumnAndRow($col, $i + 2, $rows[$i][$attr], \PHPExcel_Cell_DataType::TYPE_STRING);
                     } else {
                         $sheet->setCellValueByColumnAndRow($col, $i + 2, $rows[$i][$attr]);
                     }
                     $col++;
                 }
             }
         }
     }
     if (count($rows) > 0) {
         $c = count(array_keys($rows[0]));
         // ??????????????????
         /* Авторазмер колонок Excel */
         \PHPExcel_Shared_Font::setAutoSizeMethod(\PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
         foreach (range(0, $c) as $col) {
             $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
             $sheet->calculateColumnWidths();
             if ($sheet->getColumnDimensionByColumn($col)->getWidth() > 70) {
                 $sheet->getColumnDimensionByColumn($col)->setAutoSize(false);
                 $sheet->getColumnDimensionByColumn($col)->setWidth(70);
             }
         }
     }
 }

 protected function datenExportierenMentees($monat = 1, $jahr = 2013)
 {
     $startTstamp = mktime(0, 0, 0, $monat, 1, $jahr);
     $felder = array();
     $seite = PID_MENTEES;
     $exportListe = $this->exportFelder['MENTEES'];
     $where = 'deleted=0 AND hidden=0 AND pid=' . $seite . ' AND tstamp>=' . $startTstamp;
     $where = 'deleted=0 AND hidden=0 AND pid=' . $seite;
     $abfrageFelder = $GLOBALS['TYPO3_DB']->exec_SELECTquery('uid', 'tx_powermail_mails', $where, '', 'uid');
     $eintraege = array();
     while ($daten = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($abfrageFelder)) {
         $eintraege[] = $daten[uid];
         foreach ($exportListe as $feldId => $feldTitel) {
             $wert = $this->gibFormularFeldwert($daten[uid], $feldId, 'AAA');
             $zeile[] = '"' . iconv('UTF-8', 'CP1252', $wert) . '"';
         }
     }
     if (count($eintraege) == 0) {
         return '<br/><div style="padding-left: 20px;"><span class="error">Momentan sind keine Anmeldungen für Mentees vorhanden!</span></div><br />';
     }
     $exportTitle = 'Anmeldungen Mentees';
     $workbook = t3lib_div::makeInstance('PHPExcel');
     $workbook->getProperties()->setTitle($exportTitle)->setSubject($exportTitle);
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     $sheet = $workbook->getActiveSheet();
     $sheet->setTitle('Anmeldungen Mentees');
     $spalte = 0;
     $anzSpalten = count($exportListe);
     foreach ($exportListe as $feldId => $feldTitel) {
         $feldTitel = trim(str_replace(':', '', $feldTitel));
         $sheet->setCellValueByColumnAndRow($spalte, 1, $feldTitel);
         $spalte++;
     }
     for ($zeile = 0; $zeile < count($eintraege); $zeile++) {
         $spalte = 0;
         foreach ($exportListe as $feldId => $feldTitel) {
             $wert = $this->gibFormularFeldwert($eintraege[$zeile], $feldId);
             $sheet->setCellValueByColumnAndRow($spalte, $zeile + 2, $wert);
             $spalte++;
         }
     }
     for ($spalte = 0; $spalte < $anzSpalten; $spalte++) {
         $sheet->getColumnDimension(chr(ord('A') + $spalte))->setAutoSize(true);
         //			$sheet->getColumnDimension(chr(ord('A')+ $spalte))->setWidth(50);
     }
     $dateiname = 'anmeldedaten_export_mentees.xls';
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="' . $dateiname . '"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel5');
     $objWriter->save('php://output');
     exit;
 }

//.........这里部分代码省略.........
		' . $tab_notes_feedback . '
	</table>';
                $mpdf->WriteHTML($tab_notes);
                if ($generate_html_file) {
                    $html_contents .= $tab_notes . '<hr noshade>';
                }
            }
            // $quiz_count if grades = 0
            if ($GENERATE_EXCEL) {
                $SheetCount = $objPHPExcel->getSheetCount();
                $objPHPExcel->createSheet(NULL, $SheetCount);
                $objPHPExcel->setActiveSheetIndex($SheetCount);
                $objPHPExcel->getActiveSheet()->setTitle(get_string('evaluation', 'quiz_nitroreportpdf'));
                $objPHPExcel->getActiveSheet()->setCellValue('A1', get_string('evaluation', 'quiz_nitroreportpdf'))->setCellValue('A2', get_string('percents', 'quiz_nitroreportpdf'))->setCellValue('C2', get_string('points', 'quiz_nitroreportpdf'))->setCellValue('A3', get_string('from', 'quiz_nitroreportpdf'))->setCellValue('B3', get_string('to', 'quiz_nitroreportpdf'))->setCellValue('C3', get_string('from', 'quiz_nitroreportpdf'))->setCellValue('D3', get_string('to', 'quiz_nitroreportpdf'))->setCellValue('E2', get_string('grade', 'quiz_nitroreportpdf'));
                $objPHPExcel->getActiveSheet()->mergeCells('A1:E1')->mergeCells('A2:B2')->mergeCells('C2:D2')->mergeCells('E2:E3');
                $objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->setBold(true);
                for ($i = 0; $i < count($tab_notes2); $i++) {
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . (4 + $i), $tab_notes2[$i]['mingrade_precent'])->setCellValue('B' . (4 + $i), $tab_notes2[$i]['maxgrade_precent'])->setCellValue('C' . (4 + $i), $tab_notes2[$i]['mingrade_points'])->setCellValue('D' . (4 + $i), $tab_notes2[$i]['maxgrade_points'])->setCellValue('E' . (4 + $i), strip_tags($tab_notes2[$i]['feedback']));
                    if ($i % 2 == 1) {
                        $objPHPExcel->getActiveSheet()->getStyle('A' . (4 + $i) . ':E' . (4 + $i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                        $objPHPExcel->getActiveSheet()->getStyle('A' . (4 + $i) . ':E' . (4 + $i))->getFill()->getStartColor()->setRGB('FFFFA1');
                    }
                    $objPHPExcel->getActiveSheet()->getRowDimension(4 + $i)->setRowHeight(19.83);
                }
                $objPHPExcel->getActiveSheet()->getStyle('A1:E' . (4 + $i))->getFont()->setSize(14);
                $objPHPExcel->getActiveSheet()->getStyle('A1:E' . (4 + $i))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(19.83);
                $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(19.83);
                $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(19.83);
                $objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFill()->getStartColor()->setRGB('0057AF');
                $objPHPExcel->getActiveSheet()->getStyle('A1:E3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
                PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
                $objPHPExcel->getActiveSheet()->getStyle('A1:E' . (4 + $i))->getAlignment()->setWrapText(false);
                $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(-1);
                $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(-1);
                $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(-1);
                $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(-1);
                $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(-1);
                $styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb' => '000000'))));
                $objPHPExcel->getActiveSheet()->getStyle('A1:E8')->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->getProtection()->setPassword(substr(hash('sha512', rand()), 0, 12));
                $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
                $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
                $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&D, &T &R &P / &N');
            }
            /*	6. Quiz filled by exams		*/
            $this->SetBarWidth(number_format(floor(6 * (100 / $PROGRESSBAR_PARTS)), 2, '.', ''));
            @ob_flush();
            @flush();
            $mpdf->AddPage();
            $mpdf->Bookmark('6. ' . get_string('exam_tests', 'quiz_nitroreportpdf'), 0);
            $NREQ = '<p style="text-align: center;font-weight: bold;font-size:14pt;text-transform:uppercase;">' . get_string('exam_tests', 'quiz_nitroreportpdf') . '</p><p></p>';
            $mpdf->WriteHTML($NREQ);
            if ($generate_html_file) {
                $html_contents .= $NREQ;
            }
            $tab_users = array();
            /*	5. Get users who filled exam		*/
            $quiz_users = $DB->get_records_sql('SELECT DISTINCT(qa.userid) AS userid  FROM {quiz_attempts} qa,{user} u WHERE qa.quiz="' . $quizid . '" AND userid=u.id AND qa.state="finished" ORDER BY u.lastname ASC, u.firstname ASC, u.username ASC');
            if (count($quiz_users) == 0) {
                $progress_user = number_format(floor(7 * (100 / $PROGRESSBAR_PARTS)), 2, '.', '');
            } else {
                $progress_user = number_format(floor(7 * (100 / $PROGRESSBAR_PARTS) / count($quiz_users)), 2, '.', '');
            }

 private function ExcelApplyValues($sheet, $rows, $params = [])
 {
     /* Границы таблицы */
     $ramka = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
     /* Жирный шрифт для шапки таблицы */
     $font = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
     foreach ($rows->fields as $col => $attr) {
         $sheet->setCellValueByColumnAndRow($col, 1, $attr->label);
         $sheet->getStyleByColumnAndRow($col, 1)->applyFromArray($ramka);
         $sheet->getStyleByColumnAndRow($col, 1)->applyFromArray($font);
     }
     if (count((array) $rows) > 0) {
         foreach ($rows->values as $i => $row) {
             foreach ($rows->fields as $col => $attr) {
                 if (isset($params['date']) && in_array($attr->fieldname, $params['date']) && $rows->values[$i][$attr->fieldname] !== NULL) {
                     $rows->values[$i][$attr->fieldname] = date('d.m.Y', strtotime($rows->values[$i][$attr->fieldname]));
                 }
                 if (isset($params['case']) && in_array($attr->fieldname, array_keys($params['case']))) {
                     $rows->values[$i][$attr->fieldname] = $params['case'][$attr->fieldname][$rows->values[$i][$attr->fieldname]];
                 }
                 $sheet->getStyleByColumnAndRow($col, $i + 2)->applyFromArray($ramka);
                 if (isset($params['string']) && in_array($attr->fieldname, $params['string'])) {
                     $sheet->getStyleByColumnAndRow($col, $i + 2)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                     $sheet->setCellValueExplicitByColumnAndRow($col, $i + 2, $rows->values[$i][$attr->fieldname], PHPExcel_Cell_DataType::TYPE_STRING);
                 } else {
                     $sheet->setCellValueByColumnAndRow($col, $i + 2, $rows->values[$i][$attr->fieldname]);
                 }
             }
         }
     }
     $c = count($rows->fields);
     /* Авторазмер колонок Excel */
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     foreach (range(0, $c) as $col) {
         $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
         $sheet->calculateColumnWidths();
         if ($sheet->getColumnDimensionByColumn($col)->getWidth() > 70) {
             $sheet->getColumnDimensionByColumn($col)->setAutoSize(false);
             $sheet->getColumnDimensionByColumn($col)->setWidth(70);
         }
     }
 }

 function bearbeiterExportierenExcel(&$bearbeiterDaten)
 {
     $jahr = date('Y');
     $monat = date('m');
     $tag = date('d');
     $sheetname = 'TYPO3_Redakteure' . '_' . $jahr . '_' . $monat . '_' . $tag;
     $dateiname = $sheetname . '.xlsx';
     $spaltenTitel = array('Name', 'E-Mail', 'Seitenbereiche', 'letzter Login');
     $phpExcelService = t3lib_div::makeInstance('tx_phpexcel_service');
     $phpExcel = $phpExcelService->getPHPExcel();
     $phpExcel->getProperties()->setTitle('TYPO3_Redakteure')->setSubject('TYPO3 Redakteure');
     $phpExcel->getActiveSheet()->setTitle('TYPO3 Redakteure');
     $sheet = $phpExcel->getActiveSheet();
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     $spalte = 0;
     foreach ($spaltenTitel as $titelText) {
         $objRichText = $phpExcelService->getInstanceOf('PHPExcel_RichText');
         $titel = $objRichText->createTextRun($titelText);
         $titel->getFont()->setSize('12');
         $titel->getFont()->setName('Arial');
         $titel->getFont()->setBold(true);
         $zelle = chr(ord('A') + $spalte) . '1';
         $sheet->setCellValue($zelle, $objRichText);
         $sheet->getStyle($zelle)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $spalte++;
     }
     $zeile = 2;
     foreach ($bearbeiterDaten as $daten) {
         $sheet->setCellValueByColumnAndRow(0, $zeile, $this->cleanStringForExport($daten['name']));
         $sheet->setCellValueByColumnAndRow(1, $zeile, $this->cleanStringForExport($daten['email']));
         $sheet->setCellValueByColumnAndRow(2, $zeile, $this->cleanStringForExport($daten['seiten']));
         $sheet->setCellValueByColumnAndRow(3, $zeile, $this->cleanStringForExport($daten['lastLogin']));
         $sheet->getStyle('C' . $zeile)->getAlignment()->setWrapText(true);
         $zeile++;
     }
     $sheet->getDefaultRowDimension()->setRowHeight(-1);
     $sheet->getColumnDimension('A')->setWidth(30);
     $sheet->getColumnDimension('B')->setWidth(30);
     $sheet->getColumnDimension('C')->setWidth(80);
     header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="' . $dateiname . '"');
     /*
     header('Content-Disposition: attachment;filename="' . $dateiname . '"');
     header('Cache-Control: max-age=0');
     */
     $excelWriter = $phpExcelService->getInstanceOf('PHPExcel_Writer_Excel5', $phpExcel);
     $excelWriter->save('php://output');
     exit;
 }

 public function exportTerminPlan(&$terminKalender)
 {
     $dateiname = 'termine_gesundheitstag.xls';
     $phpExcelService = t3lib_div::makeInstance('tx_phpexcel_service');
     $phpExcel = $phpExcelService->getPHPExcel();
     $title = 'Anmeldungen - Gesundheitstag 2015';
     $phpExcel->getProperties()->setTitle($title)->setSubject($title);
     PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
     // das erste worksheet anwaehlen
     $sheet = $phpExcel->getActiveSheet();
     $sheet->setTitle('Anmeldungen2015');
     $zeile = 1;
     foreach ($terminKalender as $veranstaltungsDaten) {
         $titelEintrag = $veranstaltungsDaten['title'] . ' - (Raum ' . $veranstaltungsDaten['raum'] . ')';
         $objRichText = new PHPExcel_RichText();
         $cellTitle = $objRichText->createTextRun($titelEintrag);
         $cellTitle->getFont()->setSize('14');
         $cellTitle->getFont()->setName('Arial');
         $sheet->setCellValueByColumnAndRow(0, $zeile, $objRichText);
         $zelle = 'A' . $zeile;
         $zellRaum = 'A' . $zeile . ':B' . $zeile;
         $sheet->mergeCells($zellRaum);
         $sheet->getStyle($zelle)->getAlignment()->setWrapText(true);
         $sheet->getRowDimension($zeile)->setRowHeight(40);
         $zeile++;
         $objRichText = new PHPExcel_RichText();
         $cellTitle = $objRichText->createTextRun('Uhrzeit');
         $cellTitle->getFont()->setSize('12');
         $cellTitle->getFont()->setName('Arial');
         $sheet->setCellValueByColumnAndRow(0, $zeile, $objRichText);
         $zelle = 'A' . $zeile;
         $sheet->getStyle($zelle)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $sheet->getStyle($zelle)->getAlignment()->setWrapText(true);
         $objRichText = new PHPExcel_RichText();
         $cellTitle = $objRichText->createTextRun('Person');
         $cellTitle->getFont()->setSize('12');
         $cellTitle->getFont()->setName('Arial');
         $sheet->setCellValueByColumnAndRow(1, $zeile, $objRichText);
         $zelle = 'B' . $zeile;
         $sheet->getStyle($zelle)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
         $zelle = 'A' . $zeile;
         $sheet->getStyle($zelle)->getAlignment()->setWrapText(true);
         $sheet->getRowDimension($zeile)->setRowHeight(40);
         $zeile++;
         foreach ($veranstaltungsDaten['belegung'] as $terminDaten) {
             $sheet->setCellValueByColumnAndRow(0, $zeile, $terminDaten['zeit']);
             $zelle = 'A' . $zeile;
             $sheet->getStyle($zelle)->getAlignment()->setWrapText(true);
             $zelle = 'B' . $zeile;
             $sheet->setCellValueByColumnAndRow(1, $zeile, $terminDaten['user']);
             $sheet->getStyle($zelle)->getAlignment()->setWrapText(true);
             $zeile++;
         }
     }
     $sheet->getColumnDimension('A')->setWidth(15);
     $sheet->getColumnDimension('B')->setWidth(80);
     header('Content-Type: application/vnd.ms-excel');
     header('Content-Disposition: attachment;filename="' . $dateiname . '"');
     header('Cache-Control: max-age=0');
     $excelWriter = $phpExcelService->getInstanceOf('PHPExcel_Writer_Excel2007', $phpExcel);
     $excelWriter->save('php://output');
     exit;
 }