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..

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

Thanks for the little tutorial and snippet. Worked great

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

Awesome Dude!!

That greate Dude!!

Thanks Arjun, Great Tutorial Really helpful… 🙂

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 ?

Clean and simple. TNX Arjun.

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

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

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

Thanks. It works perfectly for me

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

Cool, Its working perfectly. Thank you.

export excel is not working please help me out.

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

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

I had to add

ob_end_clean();

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

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

Nice trick. (Y)

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

thanku bro it helped a lot

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

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

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’);

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

Hello Arjun,

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

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

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

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

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?

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?

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.

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

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’);

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

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

Thanks mate, works great.

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

þÿÿÿþÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþÿà…Ÿòù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ìþÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

thx, i was helped.

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

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.

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

peace

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

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

Hello Arjun,

Thank You for your article, this is so helpful

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;
}

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

[…] How to use PHPExcel with CodeIgniter? […]