• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
seegatesite header

Seegatesite.com

Seegatesite.com - Programming Tutorial , Sharing , How and Learn Together

  • TOOLS
    • Bootstrap Navbar Online Generator
    • Customize Sidebar Menu Bootstrap 3
    • Bootstrap Demo
  • ADVERTISE
  • CATEGORIES
    • Android
    • Blogging Tips
    • Database
    • CSS
    • Info Gadget
    • Javascript
    • Linux
    • PHP
    • Various
    • WordPress
  • Q&A
  • PHP
  • JAVASCRIPT
  • JQUERY
  • ANGULAR
  • WORDPRESS
  • SEO
  • REACT
🏠 » PHP » How to Create Excel file from PHP with PHPExcel 1.8.0 Classes

How to Create Excel file from PHP with PHPExcel 1.8.0 Classes

By Sigit Prasetya Nugroho ∙ November 27, 2014 ∙ PHP ∙ 3 Comments

Share : TwitterFacebookTelegramWhatsapp

create excel with php

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.

How to writing Excel file from PHP with PHPExcel 1.8.0 Classes

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.

Related Articles :

  • How To Remove Special Character In String PHP Regex Replace
  • How To Resolve No ‘Access-Control-Allow-Origin’ Header In Lumen
  • Create a Sales Form / POS – Tutorial Build Point Of Sale With PHP, PDO, MySQL And Jquery Part 5

Table of Contents

  • 1  Advantages use PHPExcel 
    • 1.1  Basic example using PHPExcel to create an excel file 

 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

writing detail file excel with phpexcel in php

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

Another PHP Related Post :

  • Tutorial Create Simple POS Using ReactJS And Laravel Lumen Part 1
  • How To Replace String With Another String In PHP
  • Login Page – Tutorial CRUD Client and API Server Using JQuery And Lumen Part 2
  • Tutorial CRUD Client and API Server Using JQuery And Lumen Part 1
  • How To Solve Problems Illegal mix of collations (latin1_swedish_ci,IMPLICIT) In Laravel
  • How To Resolve No ‘Access-Control-Allow-Origin’ Header In Lumen

Avatar for Sigit Prasetya Nugroho

About Sigit Prasetya Nugroho

This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

Reader Interactions

Comments

  1. Avatar for kvrkvr says

    June 22, 2018 at 9:23 am

    set password to open a file. is it possible

    Reply
    • Avatar for Sigit Prasetya NugrohoSigit Prasetya Nugroho says

      June 23, 2018 at 1:03 am

      hope the link below can solve your problem…

      https://github.com/PHPOffice/PHPExcel/issues/942

      Reply
  2. Avatar for pratimapratima says

    March 16, 2020 at 2:48 pm

    this code is not working

    Reply
    • Avatar for Sigit Prasetya NugrohoSigit Prasetya Nugroho says

      March 18, 2020 at 2:46 am

      my suggestion is to use https://phpspreadsheet.readthedocs.io/en/latest/

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Welcome to my Home,

Avatar for Sigit Prasetya NugrohoThis site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.



Popular Articles

Checked checkbox AdminLTE Bootstrap in Jquery

November 4, 2014 By Sigit Prasetya Nugroho 7 Comments

Simple create date format validation with jqueryUI

December 21, 2014 By Sigit Prasetya Nugroho Leave a Comment

Create Simple Progress Bar for Fake Online Generator with Jquery

January 10, 2015 By Sigit Prasetya Nugroho Leave a Comment

22+ Coolest Free Jquery Plugin For Premium Theme

October 3, 2015 By Sigit Prasetya Nugroho Leave a Comment

Easy Build Your Anti Copy Paste Plugin

October 6, 2015 By Sigit Prasetya Nugroho Leave a Comment

Popular Tags

adminlte (15) adsense (13) adsense tips (4) affiliate amazon (13) amazon (12) Android (8) angular (16) angular 4 (12) angular 5 (4) asin grabber (3) Bootstrap (27) codeigniter (5) create wordpress theme (5) crud (8) css (6) free wordpress theme (7) google adsense (4) imacros (4) increase traffic (6) jquery (34) laravel (10) laravel 5 (5) learn android (5) lumen api (4) modal dialog (5) mysql (6) nodeJs (4) optimize seo (4) pdo (6) php (30) plugin (53) pos (8) Publisher Tips (5) react (6) Reactjs (9) SEO (37) theme (17) tutorial angular (5) tutorial angular 4 (6) tutorial javascript (10) tutorial javascript beginners (4) twitter (3) wordpress (18) wordpress plugin (13) XMLRPC (5)




  • About
  • Contact Us
  • Disclaimer
  • Privacy Policy
  • Terms and Conditions

©2022 Seegatesite.com