Seegatesite.com

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

🏠 » 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 ∙ ∙ PHP ∙ 4 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 :

 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 :

Reader Interactions

Comments

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

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