SD Shared

Integrasi PHPexcel dengan Codeigniter

Pertama yang anda butuhkan ialah :

1. PHPExcel, Download
2. Codeigniter

Langkahnya :

1. Extrak hasil downloadan PHPExcel, lalu ke direktori C:/download/PHPExcel/Classes.
2. Copy semuanya yaitu folder PHPExcel dan phpexcel.php ke folder codeigniter /application/third_party/ (pastekan disni)
3. Lalu membuat liblary pada direktori codeigniter /application/liblaries/, buat file excel.php dan copy code dibawah ini :

Contoh untuk membaca file excel dan konversi kedalam array

$file = './files/test.xlsx';
 
//load the excel library
$this->load->library('excel');
 
//read file from path
$objPHPExcel = PHPExcel_IOFactory::load($file);
 
//get only the Cell Collection
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
 
//extract to a PHP readable array format
foreach ($cell_collection as $cell) {
    $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
    $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
    $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
 
    //header will/should be in row 1 only. of course this can be modified to suit your need.
    if ($row == 1) {
        $header[$row][$columns] = $data_value;
    } else {
        $arr_data[$row][$col] = $data_value;
    }
}
 
//send the data in an array format
$data['header'] = $header;
$data['values'] = $arr_data;

Contoh untuk membuat file excel

//load PHPExcel library
$this->load->library('Excel');
 
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
 
// Set document properties
$objPHPExcel->getProperties()->setCreator("mohamadikhwan.com")
        ->setLastModifiedBy("mohamadikhwan.com")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated by PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
 
 
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'bonjour')
            ->setCellValue('C2', 'monde')
            ->setCellValue('A4', 'tutorial from: mohamadikhwan.com');
 
// Rename worksheet (worksheet, not filename)
$objPHPExcel->getActiveSheet()->setTitle('createdUsingPHPExcel');
 
 
// 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 (Excel2007)
//clean the output buffer
ob_end_clean();
 
//this is the header given from PHPExcel examples. 
//but the output seems somewhat corrupted in some cases.
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//so, we use this header instead.
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="mohamadikhwan_dot_com_phpexcel_tut.xlsx"');
header('Cache-Control: max-age=0');
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

Terimakasih dan tinggalkan komentarnya jika ada yang kurang dimengerti atau merasa terbantu

Artikel ini referensi dan ditulis ulang kebahasa sendiri dari http://blog.mohamadikhwan.com/