To create an excel file using PHP needed a php class named PHPExcel 1.8.0. PHPExcel has been frequently discussed in php sites, whereas this article I would like to remind how easy create an excel file with php using a class from Balliauw Maarten and his team.
In addition to create an excel file, class PHPExcel can also be used to read the excel file. Specifically, this article will discuss how to create or writing an excel file using php.
Advantages use PHPExcel
1. Easy and simple to use.
2. Easy to setting the layout (color, background, border, etc.)
3. Easy to use functions in Excel
Basic example using PHPExcel to create an excel file
1. Download phpexcel class here
2. Create php file as create_excel.php.
3. Please copy PHPExcel.php in the same folder with create_excel.php.
4. Copy code below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | <?php error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); /** Include PHPExcel */ require_once 'PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Create new PHPExcel object $objPHPExcel->getProperties()->setCreator("Sigit prasetya n") ->setLastModifiedBy("Sigit prasetya n") ->setTitle("Creating file excel with php Test Document") ->setSubject("Creating file excel with php Test Document") ->setDescription("How to Create Excel file from PHP with PHPExcel 1.8.0 Classes by seegatesite.com.") ->setKeywords("phpexcel") ->setCategory("Test result file"); // create style $default_border = array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb'=>'1006A3') ); $style_header = array( 'borders' => array( 'bottom' => $default_border, 'left' => $default_border, 'top' => $default_border, 'right' => $default_border, ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb'=>'E1E0F7'), ), 'font' => array( 'bold' => true, 'size' => 16, ) ); $style_content = array( 'borders' => array( 'bottom' => $default_border, 'left' => $default_border, 'top' => $default_border, 'right' => $default_border, ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb'=>'eeeeee'), ), 'font' => array( 'size' => 12, ) ); // Create Header $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', 'NO') ->setCellValue('B1', 'PRODUCT CODE') ->setCellValue('C1', 'TITLE'); $objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray( $style_header ); // give style to header // Create Data $dataku=array( array('C001','Iphone 6'), array('C002','Samsung Galaxy S4'), array('C003','Nokia Lumia'), array('C004','Blackberry Curve')); $firststyle='A2'; for($i=0;$i<count($dataku);$i++) { $urut=$i+2; $num='A'.$urut; $code='B'.$urut; $title='C'.$urut; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($num, $i+1) ->setCellValue($code, $dataku[$i][0]) ->setCellValue($title, $dataku[$i][1]); $laststyle=$title; } $objPHPExcel->getActiveSheet()->getStyle($firststyle.':'.$laststyle)->applyFromArray( $style_content ); // give style to header // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle('Product'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="listproduct.xls"'); // file name of excel 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, 'Excel5'); $objWriter->save('php://output'); exit; ?> |
Explanation
1 2 3 4 5 6 7 | $objPHPExcel->getProperties()->setCreator("Sigit prasetya n") ->setLastModifiedBy("Sigit prasetya n") ->setTitle("Creating file excel with php Test Document") ->setSubject("Creating file excel with php Test Document") ->setDescription("How to Create Excel file from PHP with PHPExcel 1.8.0 Classes by seegatesite.com.") ->setKeywords("phpexcel") ->setCategory("Test result file"); |
With these code will create the detail file excel
To change color, border,font use this script below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $default_border = array( 'style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('rgb'=>'1006A3') ); $style_header = array( 'borders' => array( 'bottom' => $default_border, 'left' => $default_border, 'top' => $default_border, 'right' => $default_border, ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb'=>'E1E0F7'), ), 'font' => array( 'bold' => true, 'size' => 16, ) ); |
Thus tutorial how to writing excel file with PHPExcel class in php. Next time i will show you how to create or writing function excel in php. My another php tutorial click here
The following two tabs change content below.
This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.
Latest posts by Sigit Prasetya Nugroho (see all)
- How To Insert Ads In The Middle Post Blogspot Blogger - January 26, 2019
- Login Page – Tutorial CRUD Client and API Server Using JQuery And Lumen Part 2 - January 23, 2019
- Tutorial CRUD Client and API Server Using JQuery And Lumen Part 1 - January 4, 2019
set password to open a file. is it possible
hope the link below can solve your problem…
https://github.com/PHPOffice/PHPExcel/issues/942