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:

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
                $objSheet = $objPHPExcel->getActiveSheet();

Toggle expand and hide columns A to C default expand:
                foreach (range('A','C') as $column) {

Toggle expand and hide columns A to C default hide:
                foreach (range('A','C') as $column) {


Toggle expand and hide row 1

 Set autosize
                foreach (range('A','Z') as $column) {

Set font size
Set font bold
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
Set alignment left

Set column width
Set cell background color lightgreen
         '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");
Write out EXCEL file
            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/');
                //set the course offered id as page name
              $filename = " Export_".date("Ymd").".xls";
              header('Content-Disposition: attachment; filename= "'.$filename.'"');
             header("Content-Transfer-Encoding: binary");
              $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

No comments:

Post a Comment