Monday, June 8, 2015

phpexcel toggle expand and hide column in EXCEL and summary



 PHP has built-in feature to read and write CSV files. PHPExcel extends this feature and allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, ... This project is built around Microsoft's OpenXML standard and PHP.

PHPExcel can be downloaded from:
 https://phpexcel.codeplex.com/

To start PHPExcel, PHPExcel library path should be included first, for example installed in _classes
                 include_once "./_classes/PHPExcel.php";
                include_once './_classes/PHPExcel/Writer/Excel2007.php';


                $objPHPExcel = new PHPExcel();

                //choose the first page
                $objPHPExcel->setActiveSheetIndex(0);
                $objSheet = $objPHPExcel->getActiveSheet();

Toggle expand and hide columns A to C default expand:
                foreach (range('A','C') as $column) {
                  $objSheet->getColumnDimension($column)->setOutlineLevel(1);
                  $objSheet->getColumnDimension($column)->setCollapsed(true);
                }

Toggle expand and hide columns A to C default hide:
                foreach (range('A','C') as $column) {
                  $objSheet->getColumnDimension($column)->setOutlineLevel(1);

                 $objSheet->getColumnDimension($column)->setVisible(false);
                  $objSheet->getColumnDimension($column)->setCollapsed(true);
                }

Toggle expand and hide row 1
               $objSheet->getRowDimension(1)->setOutlineLevel(1);     
              $objSheet->getRowDimension(1)->setVisible(false);
             $objSheet->getRowDimension(1)->setCollapsed(true);
 

 Set autosize
                foreach (range('A','Z') as $column) {
                        $objSheet->getColumnDimension($column)->setAutoSize(true);
                }

Set font size
                $objSheet->getStyle("A1:AM1")->getFont()->setSize(14);
Set font bold
               $objSheet->getStyle("A1:AM1")->getFont()->setBold(true);
Set column A1 header  value
              $objSheet->SetCellValue('A1', 'faculty');
Set cell column 1 row 2 value '4'
   $objSheet->setCellValueByColumnAndRow(1, 2, '2');
Set number 1 as 0001 instead of 1
   $objSheet->getStyle('F1:F'.$currentRow)->getNumberFormat()->setFormatCode('0000');
Set alignment left
               $objSheet->getDefaultStyle()
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

Set column width
  $objSheet->getColumnDimension('A1')->setWidth(15);
Set cell background color lightgreen
        $objSheet->getStyle("A1:AM1")->applyFromArray(
          array(
         'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'C3FDB8')
          )
         ));

Set  line break using double quote to parse \n in PHP
    $objSheet->SetCellValue('N1', "Instr\n contact \n hrs");
       $objSheet->getStyle('N1')->getAlignment()->setWrapText(true);
Write out EXCEL file
           ini_set('zlib.output_compression','Off');
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            //the folowing two lines make sure it is saved as a xls file
             header('Content-type: application/vnd.ms-excel');
                //set the course offered id as page name
              $objSheet->setTitle("Export");
              $filename = " Export_".date("Ymd").".xls";
              header('Content-Disposition: attachment; filename= "'.$filename.'"');
             header("Content-Transfer-Encoding: binary");
              $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
               $objWriter->save('php://output');


No comments:

Post a Comment