How to use PHPExcel with CodeIgniter?

PHPExcel is a pure PHP library for reading and writing spreadsheet files and CodeIgniter is one of the well known PHP MVC Framework. In this tutorial, I am gonna show you how to Integrate PHPEXcel library in CodeIgniter with simple steps.

Here is my another post, you might be interested in – How to generate Excel using PhpSpreadsheet in CodeIgniter?

Setp 1: Download and setup CodeIgniter.(download it here: https://ellislab.com/codeigniter)

Setp 2: Download PHPExcel.(download it here: http://phpexcel.codeplex.com/)

Setp 3: Unzip or extract the downloaded PHPExcel lib files and copy Class directory inside files to application/third-party directory(folder).

setp 4: Now create one file called EXcel.php in application/library folder [application/library/Excel.php]. Then include PHPExcel Class to it. see how I did it below

That’s it now you can use PHPEXcel methods inside your CodeIgniter application.But you should load it before using it methods(Ex: $this->load->library(‘excel’);)

How to read excel file

How to Create excel file on the file

How to Generate Excel file with Database table data

That’s it.

I hope you like this Post, Please feel free to comment below, your suggestion and problems if you face - we are here to solve your problems.

0 0 vote
Article Rating
Subscribe
Notify of
guest
79 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Nilesh Daldra
Nilesh Daldra
6 years ago

This example gives garbage characters in excel file…by google i found issue in this code is…
it should

$objWriter->save(‘test.xls’);
instead of
$objWriter->save(‘php://output’);

it i also need some clean code..

Mhmoud
Mhmoud
5 years ago

if you please can you add an image to header page in your example?

arjun
5 years ago
Reply to  Mhmoud

Header page means? Could you please explain this in detail

SHAH FAISAL
SHAH FAISAL
5 years ago
Reply to  arjun

It means He/She wants to add a Image in excel sheet on top cells like header.

kabeza
kabeza
5 years ago

Thanks for the little tutorial and snippet. Worked great

Bongle Pappu
Bongle Pappu
5 years ago

Ever heard of plagiarism? How do you implement it in PHP? Can you cook curry with plagiarism?

Nick Naja
Nick Naja
5 years ago

Awesome Dude!!

SHAH FAISAL
SHAH FAISAL
5 years ago

That greate Dude!!

Mufaddal Saifee
Mufaddal Saifee
5 years ago

Thanks Arjun, Great Tutorial Really helpful… 🙂

Sandy
Sandy
5 years ago

Hi dude , i have a problem when i accessed the page to test php excel it return webpage not available

could you describe it to me ?

Baman SB
Baman SB
5 years ago

Clean and simple. TNX Arjun.

Trevor Albrecht
Trevor Albrecht
5 years ago

Can you have a pre-formatted spreadsheet and use this to populate it with data?

raknjak
raknjak
5 years ago

Ah a neit little trick that I can use for everything, great!

aya fers ∞
aya fers ∞
5 years ago

i’m having an error:

A PHP Error was encountered

Severity: Notice

Message: Undefined property: CI_Loader::$excel

Filename: pages/excel.php

Line Number: 5

helpp

Ridhazis Faranto Najid

Thanks. It works perfectly for me

mahesh
mahesh
5 years ago

Hey its not working for me. But when I run your examples, they works perfectecly. I just copied your export code still getting garbage data when I open downloaded xls file. Thanks

Abhijith S
Abhijith S
5 years ago

Cool, Its working perfectly. Thank you.

Dileep Kumar
Dileep Kumar
5 years ago

export excel is not working please help me out.

Deepak Prasad
Deepak Prasad
5 years ago

How to config google map api with multiple location dynamically in codeigniter

chabilita
chabilita
5 years ago

do you know what serialize and unserialized is ? can you please give me some example. thank you 🙂

arjun
5 years ago
Reply to  chabilita

Serialize() converts an array into a normal string that you can pass in a URL,save as in database(wordpress uses) etc.
Unserialize() is the opposite of serialize() – it takes a serialized string and converts it back to an array.

chabilita
chabilita
5 years ago
Reply to  arjun

Can you please give example sir . thank you 🙂

arjun
5 years ago
Reply to  chabilita

In couple days ,will write a post

chabilita
chabilita
5 years ago
Reply to  arjun

I’ll Wait, Thank you 🙂

amandat
amandat
5 years ago

I had to add

ob_end_clean();

before the last line for it to work. Otherwise, great, quick how-to. Thanks.

Marco Antonio Aguilar Gomez
Marco Antonio Aguilar Gomez
4 years ago
Reply to  amandat

You saved my life. Thx!

leeyoo
leeyoo
4 years ago

Hello, Whenever I call this controller I get the following error:A PHP Error was encountered

Severity: Error

Message: Class ‘PHPExcel_IOFactory’ not found

Filename: controllers/Result.php

Line Number: 229
line229: $objReader = PHPExcel_IOFactory::createReader(‘Excel2007’); please help me

Pathy
Pathy
4 years ago
Reply to  leeyoo

you are configured not correct pls follow as arjun declared

Muhammad Uzair Khan
Muhammad Uzair Khan
4 years ago

Nice trick. (Y)

Pathy
Pathy
4 years ago

good job ajjun ..its work on create excel only..i cant read excel how
thanks in advance ..

arjun
4 years ago
Reply to  Pathy

are you able to open your base excel sheet.

Pathy
Pathy
4 years ago
Reply to  arjun

yes i can open base excel sheet .
arjun..i need to retrieve data from mysql table to excel with all rows and colums as seen in tables..i can receive single row of data only …column name as we proceeded in phpexcel code.
i need multiple rows with column names to assiciate excel sheet ..
how it is.
and i have to split rows as number sets (2000/20)…and once again i will to upload to mysql ..

this may have any chance pls help me do it..
thanks

arjun
4 years ago
Reply to  Pathy

Hi , Have updated the post. It will lead you to achieve your requirement.

Pathy
Pathy
4 years ago
Reply to  arjun

thanks arjun
we need table colums in first row in excel
we have 5000 questions and options and answers in the same table
we have to spilit them all in sets cinsists of 500 per set and inner set should have 20 each..
how to split them in excel ..
can have a trick pls help me do

arjun
4 years ago
Reply to  Pathy

you have to push table columns name into result array

Pathy
Pathy
4 years ago
Reply to  arjun

yes thank for your reply arjun ..try to understand the picture .i m waitning for your response

Pathy
Pathy
4 years ago
Reply to  Pathy

now im trying to push column in to result array
thank you again arjun

arjun
4 years ago
Reply to  Pathy

my question is how you want to display the result, any sample excel

Pathy
Pathy
4 years ago
Reply to  arjun

table having 13 columns and multiple rows per test ok ..
we need to save 20 per excel sheet up to 500 *20 per test if test question having 5000 qn. for example ..
i cannot create xls column with rows perfectly ..i m used array_merge its wrking on print_r() function only ..but its too print rows and columns as consequent index ..
rows are printed as rows and columns are printed as rows when using array_merge function ..
any possiblities pls i m in hurry ..

Pathy
Pathy
4 years ago
Reply to  Pathy

how to send files to you

arjun
4 years ago
Reply to  Pathy

can you share visual representation of your requirement

arjun
4 years ago
Reply to  Pathy

you just need to call fromArray() method on active sheet.

$this->excel->getActiveSheet()->fromArray($users);

Saket Sharma
Saket Sharma
4 years ago

thanku bro it helped a lot

Bakhtawar Mubeen
Bakhtawar Mubeen
4 years ago

arjun I am doing in HMVC and confused where to save my excel file and where I can save my php file.

arjun
4 years ago

Let me know which HMVC module are you using, so that I can give proper solution

Bakhtawar Mubeen
Bakhtawar Mubeen
4 years ago
Reply to  arjun

I am doing in seller module.and don’t know where to put the excel file and wher to put

reedo88
reedo88
4 years ago

Hi Arjun, this is great, thankyou. But do you know what mime type to use for Excel2007?

arjun
4 years ago
Reply to  reedo88

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

amandat
amandat
4 years ago
Reply to  arjun

I’ve tried every configuration I can find for the Excel2007 and not having any luck. Have you? I need a chart and charts don’t work in the Excel5…

amandat
amandat
4 years ago

This works beautifully for Excel5 but when I try to update for Excel2007, I get nothing or a file that won’t open. I have tried every configuration of this (with and without ob_end_clean()). Is CodeIgniter doing something to get in the way?

Excel5 works great:
header(‘Content-type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”testfile.xls”‘);
header(‘Cache-Control: max-age=0’);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);
$objWriter->save(‘php://output’);

Excel2007 not so much:
header(‘Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”testfile.xlsx”‘);
header(‘Cache-Control: max-age=0’);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
$objWriter->save(‘php://output’);

Radha
Radha
4 years ago

I try to generate excel with database,but it show error as Non-existent class: CI_Excel,where i did mistake?

Kewal Kanojia
Kewal Kanojia
4 years ago

Hello Arjun,

I using this but facing problem while export data from database into excel. Please see attach file.

Pankaj More
Pankaj More
4 years ago
Reply to  Kewal Kanojia

used ob_end_clean(); before header method

ROBERTO RAMIREZ CRUZ
ROBERTO RAMIREZ CRUZ
4 years ago
Reply to  Pankaj More

put this line under success action if you are using ajax:
like this.
window.location = site_url + ‘your controller/you function’;

ROBERTO RAMIREZ CRUZ
ROBERTO RAMIREZ CRUZ
4 years ago
Reply to  Kewal Kanojia

you can put this line under success action if you are using ajax:
like this.
window.location = site_url + ‘your controller/you function’;

Sathish Siva
Sathish Siva
4 years ago

hai arjun can u tell me which portion cover under model and view and controller . i am new for framework

Sathish Siva
Sathish Siva
4 years ago

how to get the database table data using laravel4, can u send source code here which parts belongs to model and view and controller

Ahmad
Ahmad
4 years ago

Hello Arjun, how can i add heading in excel?
Thanks

igor
igor
4 years ago

Arjun, I was using your code with writing to Excel5 but the result file is like the one that Kewal Kanojia’s has. Any hints to solve it?

ROBERTO RAMIREZ CRUZ
ROBERTO RAMIREZ CRUZ
4 years ago
Reply to  igor

put this line under success action if you are using ajax:
like this.
window.location = site_url + ‘your controller/you function’;

igor
igor
4 years ago

By the way, “How to Create excel file on the file” should probably be “How to Create excel file on the fly”, shouldn’t it?

igor
igor
4 years ago

Arjun, you’ve posted: “we are here to solve your problems.” Why then you do not reply requests for help? If you do not know how to solve it – write it in comment.

irene baluis
irene baluis
4 years ago

do you have a code for looping data in the cells ?

Shrikant Gupta
Shrikant Gupta
4 years ago

Hi, Arjun
thanks for this amazing tutorial, i follow all steps and its work fine on my local server but when i was upload this code live server i am getting (file not found) page. then i used ob_end_clean and some other code like change headers but nothing is going well, when i comment the last line of code $objWriter->save(‘php://output’); now its work fine and but blank excel-file download can you help me please. thanks in advance.

$objWriter->save(‘php://output’);
$objWriter->save(‘php://output’);

Sai
Sai
4 years ago

Thank, but if export more than 100 row it not work
And No error Just print the row
Can u explain me why it like that

Ajit Gurung
Ajit Gurung
4 years ago

Hi Arjun,
How can I export the array in a column instead of a row?
and How can I add the column header?

Vandolph Reyes
Vandolph Reyes
4 years ago

Thanks mate, works great.

dhaval
dhaval
4 years ago

ÐÏࡱá;þÿ
þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿÿÿ

þÿÿÿþÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿà…ŸòùOh«‘+’³Ù0¸@Hh€˜

¤°äUntitled
SpreadsheetUnknown CreatorUnknown
[email protected]€×¢wR€Ò@€×¢wR€Ò
»

ÌÑB°=¼%r8X”1ܐCalibri1¼Calibriàõÿ
À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
àõÿ À
à À
à”È
“€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…$“test
worksheetÁÁgæ®üCThis
is just some text value

»

ÌÑ*+‚€Áƒ„&ffffffæ?’ffffffæ?(è?)è?¡”dXX333333Ó?333333Ó?U}$
}$
}$
}$
ý

>¶@d‹‹då

ggÿÿÿÿÿ

þÿÕÍÕœ.“—+,ù®0¼HP

X`hp

xŽä

WorksheetFeuilles
de calculRoot Entryÿÿÿÿÿÿÿÿ
ÀF€×¢wR€Ò€×¢wR€Ò@SummaryInformation(ÿÿÿÿ
ÀFèWorkbookÿÿÿÿÿÿÿÿÿÿÿÿ
ÀF)DocumentSummaryInformation8ÿÿÿÿÿÿÿÿÿÿÿÿ
ÀFìþÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

dhaval
dhaval
4 years ago
Reply to  dhaval

when i generate excel. its display in excel.php

Abdurrahman Ukasyah
Abdurrahman Ukasyah
3 years ago

thx, i was helped.

José Antonio Vivas Acosta
José Antonio Vivas Acosta
3 years ago

Muchisimas gracias desde Venezuela, me sirvio mucho tu aporte… Thank you very much!

saroj sahu
saroj sahu
3 years ago

Hello,
Very help full post I have a query what if we want to download the same excel file in our directory , I am not asking about browser download option.

Thanks.

ikramullah mohmand
ikramullah mohmand
3 years ago

Salam Arjun,
This was damn easy and helpful, sharing is caring, Saved my day.
Love you.

peace

Ashiq Muhammed
Ashiq Muhammed
3 years ago

when i created excel sheet xlsx file , that can be currepted , what i can do for that error ?

Sumit Sharma
Sumit Sharma
2 years ago

Hello Arjun,
Now phpExcel is replaced by phpspreadsheet , can you create a tutorial for phpspreadsheet also.
Thanks

arjun
2 years ago
Reply to  Sumit Sharma

Sure.. Thank you for the suggestion.

Muhammad Luthfi Fachreza
Muhammad Luthfi Fachreza
2 years ago

Hello Arjun,

Thank You for your article, this is so helpful

Abd Rahman
Abd Rahman
2 years ago

not open in linux

function excelHarian(){
// Load plugin PHPExcel nya
include APPPATH.’third_party/PHPExcel/PHPExcel.php’;

// Panggil class PHPExcel nya
$excel = new PHPExcel();
// Settingan awal fil excel
$excel->getProperties()->setCreator(‘My Notes Code’)
->setLastModifiedBy(‘My Notes Code’)
->setTitle(“Laporan Pendapatan Harian”)
->setSubject(“Siswa”)
->setDescription(“Laporan Semua Data Siswa”)
->setKeywords(“Laporan Pendapatan Harian”);
// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$style_col = array(
‘font’ => array(‘bold’ => true), // Set font nya jadi bold
‘alignment’ => array(
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
),
‘borders’ => array(
‘top’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
‘right’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border right dengan garis tipis
‘bottom’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
‘left’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
)
);
// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = array(
‘alignment’ => array(
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
),
‘borders’ => array(
‘top’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
‘right’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border right dengan garis tipis
‘bottom’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
‘left’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
)
);
$excel->setActiveSheetIndex(0)->setCellValue(‘A1’, “Laporan Pendapatan Harian”); // Set kolom A1 dengan tulisan “DATA SISWA”
$excel->getActiveSheet()->mergeCells(‘A1:H1’); // Set Merge Cell pada kolom A1 sampai E1
$excel->getActiveSheet()->getStyle(‘A1’)->getFont()->setBold(TRUE); // Set bold kolom A1
$excel->getActiveSheet()->getStyle(‘A1’)->getFont()->setSize(15); // Set font size 15 untuk kolom A1
$excel->getActiveSheet()->getStyle(‘A1’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1

// Buat header tabel nya pada baris ke 3
$excel->setActiveSheetIndex(0)->setCellValue(‘A3’, “NO”); // Set kolom A3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘B3’, “TANGGAL”); // Set kolom B3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘C3’, “MOTOR”); // Set kolom C3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘D3’, “MOBIL”); // Set kolom D3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘E3’, “BUS/TRUCK”); // Set kolom E3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘F3’, “BOX”); // Set kolom D3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘G3’, “TAKSI”); // Set kolom E3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘H3’, “LOKASI PARKIR”); // Set kolom D3 dengan tulisan
$excel->setActiveSheetIndex(0)->setCellValue(‘I3’, “PENDAPATAN”); // Set kolom E3 dengan tulisan

// Apply style header yang telah kita buat tadi ke masing-masing kolom header
$excel->getActiveSheet()->getStyle(‘A3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘B3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘C3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘D3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘E3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘F3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘G3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘H3’)->applyFromArray($style_col);
$excel->getActiveSheet()->getStyle(‘I3’)->applyFromArray($style_col);

// Panggil function view yang ada di SiswaModel untuk menampilkan semua data siswanya
$range_date = !empty($this->input->post(‘reservation’)) ? $this->input->post(‘reservation’) : ”;
if (!empty($range_date)) {
$start_date = substr($range_date, 0, -13);
$end_date = substr($range_date, 13);
$datetime1 = new DateTime($start_date);
$datetime2 = new DateTime($end_date);
$tanggal1 = $datetime1->format(‘Y-m-d’);
$tanggal2 = $datetime2->format(‘Y-m-d’);

$where = ‘date(tiket.waktuMasuktiket) BETWEEN”‘.$tanggal1.'” and “‘.$tanggal2.'”‘;
}else {
$where = null;
}
$siswa = $this->Mpendapatanharian->getAllTiket($where);

$no = 1; // Untuk penomoran tabel, di awal set dengan 1
$numrow = 4; // Set baris pertama untuk isi tabel adalah baris ke 4
foreach($siswa as $data){ // Lakukan looping pada variabel siswa
$excel->setActiveSheetIndex(0)->setCellValue(‘A’.$numrow, $no);
$excel->setActiveSheetIndex(0)->setCellValue(‘B’.$numrow, $data[‘tanggal’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘C’.$numrow, $data[‘Motor’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘D’.$numrow, $data[‘Mobil’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘E’.$numrow, $data[‘BusTruck’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘F’.$numrow, $data[‘Box’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘G’.$numrow, $data[‘Taksi’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘H’.$numrow, $data[‘jumlahLokasi’]);
$excel->setActiveSheetIndex(0)->setCellValue(‘I’.$numrow, $data[‘totalPendapatan’]);

// Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
$excel->getActiveSheet()->getStyle(‘A’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘B’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘C’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘D’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘E’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘F’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘G’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘H’.$numrow)->applyFromArray($style_row);
$excel->getActiveSheet()->getStyle(‘I’.$numrow)->applyFromArray($style_row);

$no++; // Tambah 1 setiap kali looping
$numrow++; // Tambah 1 setiap kali looping
}
// Set width kolom
$excel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(5); // Set width kolom A
$excel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(15); // Set width kolom B
$excel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(10); // Set width kolom C
$excel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(10); // Set width kolom D
$excel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(15); // Set width kolom E
$excel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(10); // Set width kolom F
$excel->getActiveSheet()->getColumnDimension(‘G’)->setWidth(10); // Set width kolom G
$excel->getActiveSheet()->getColumnDimension(‘H’)->setWidth(20); // Set width kolom H
$excel->getActiveSheet()->getColumnDimension(‘I’)->setWidth(20); // Set width kolom I

// Set height semua kolom menjadi auto (mengikuti height isi dari kolommnya, jadi otomatis)
$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(-1);
// Set orientasi kertas jadi LANDSCAPE
$excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
// Set judul file excel nya
$excel->getActiveSheet(0)->setTitle(“Laporan Pendapatan Harian”);
$excel->setActiveSheetIndex(0);
// Proses file excel
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment; filename=”Laporan Pendapatan Harian.xlsx”‘); // Set nama file excel nya
header(‘Cache-Control: max-age=0’);
$write = PHPExcel_IOFactory::createWriter($excel, ‘Excel2007’);
ob_end_clean();
$write->save(‘php://output’);
exit;
}

trackback

[…] Here is my another post, you might be interested in – How to use PHPExcel with CodeIgniter? […]

DMCA.com Protection Status
79
0
Would love your thoughts, please comment.x
()
x