Membuat & mengedit file excel menggunakan PHPExcel

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = new PHPExcel(); //memanggil atau mengimpor class PHPExcel
$objPHPExcel->getProperties()->setCreator("Lamongandev")
                             ->setLastModifiedBy("Lamongandev")
                             ->setTitle("Isi Contoh Title PHPExcel")
                             ->setSubject("Isi Subject Contoh PHPExcel")
                             ->setDescription("Isi Deskripsi Contoh PHPExcel")
                             ->setKeywords("Isi Keyword contoh php Excel");
$objPHPExcel->setActiveSheetIndex(0); //Excel Sheet 0/1

/* Kita coba mengisi kolom */
$objPHPExcel->getActiveSheet()->SetCellValue('A1', "Tesisi A1"); //A1, A=Kolom & 1=Row/baris nomor 1
$objPHPExcel->getActiveSheet()->SetCellValue('A2', "Tesisi A2"); //A1, A=Kolom & 2=Row/baris nomor 2
$objPHPExcel->getActiveSheet()->SetCellValue('B1', "Tesisi B1"); //B1, B=Kolom & 1=Row/baris no1

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); //membuat file
try {
    $objWriter->save('hasil.xlsx'); //menyimpan file "hasil.xlsx" Terletak di folder yg sama dgn file.
    //$objWriter->save('php://output'); //jika otomatis download tanpa menyimpan
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel
    echo "File Sukses Dibuat..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('folder/file.xlsx'); // Mengambil file excel yang akan dimodif
$objPHPExcel->setActiveSheetIndex(0); //Menuju ke sheet yang akan dimodif
//$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(TRUE); //auto resize width kolom

$objPHPExcel->getActiveSheet()->SetCellValue('D2', "IsiModif D2"); //Mengedit, menambahkan text ke kolom D

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
try {
    $objWriter->save('hasil_modif.xlsx'); //menyimpan file hasil modif "hasil.xlsx" (Terletak di folder yg sama dgn file).
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel

    echo "File Sukses Dimodif..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>

<?php
/* Style font */
$styleArray = array(
   'font'  => array(
        'bold'  => true, //mengaktifkan bold text, jika tidak "FALSE"
        'color' => array('rgb' => '008000'), //Mengganti warna pada text, 008000 adalah kode warna RGB
        'size'  => 15, //Mengubah ukuran text
        'name'  => 'Verdana' //font family
    )
    'alignment' => array(
        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, //setting alignment (HORIZONTAL_LEFT , HORIZONTAL_CENTER , HORIZONTAL_RIGHT)
    ));      
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray($styleArray); //berarti mengubah dari A1 sampai ke C1


/* Style Border */
$styleArray = array(
    'borders' => array(
        'outline' => array( //Style border type = (outline, allborders, inside, vertical, horizontal)
            'style' => PHPExcel_Style_Border::BORDER_THICK, //type border type = (BORDER_THIN, BORDER_THICK)
            'color' => array('argb' => '008000'), //warna border Code RGB
        ),
    ),
);
$objPHPExcel->getActiveSheet()->getStyle('B2:D2')->applyFromArray($styleArray); //Berarti membuat border kolom dari B2 sampai D2


/* -----Atau bisa juga dengan bentuk kode sprti ini----- */

/* Mengubah warna text */
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB("008000"); //Kode warna RGB, Atau sprti ini,
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);

/* Text alignment */
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //HORIZONTAL_LEFT , HORIZONTAL_CENTER , HORIZONTAL_RIGHT

/* mengubah Fontsize */
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(15);
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel
$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('hasil.xlsx'); // Mengambil file excel yang akan dimodif
$objPHPExcel->setActiveSheetIndex(0); //Menuju ke sheet yang akan dimodif

        $gambar = imagecreatefromstring(file_get_contents('https://files.ariefendi.me/d/old-programmer.jpg')); //url gambar
        $objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
        $objDrawing->setName('Nama Gambar');
        $objDrawing->setDescription('Deskripsi Gambar');
        $objDrawing->setImageResource($gambar);
        $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
        $objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
        $objDrawing->setCoordinates("C2"); //lokasi / Koordinat
        $objDrawing->setResizeProportional(false);
        $objDrawing->setWidth(150); //lebar gambar
        $objDrawing->setHeight(150); //tinggi gamber
        $objDrawing->setOffsetY(4.1);
        $objDrawing->setOffsetX(4.1);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
try {
    $objWriter->save('hasil_modif.xlsx'); //menyimpan file "hasil.xlsx" Terletak di folder yg sama dgn file.
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel

    echo "File Sukses Dimodif..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('folder/file-excel.xlsx'); // Mengambil/membaca file excel
$objPHPExcel->setActiveSheetIndex(0); //Menuju ke 1

    print $objPHPExcel->getActiveSheet()->getCell('A1')->getValue(); //mengambil value kolom,row A1
    print '<hr/>';
    print $objPHPExcel->getActiveSheet()->getTitle(); //mengambil nama sheet

$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel); //menutup file excel
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('hasil.xlsx'); // Mengambil file excel yang akan dimodif

$objPHPExcel->createSheet($objPHPExcel->getSheetCount())->setTitle("NamaSheet Baru"); //membuat sheet baru

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
try {
    $objWriter->save('hasil_modif.xlsx'); //menyimpan file hasil modif "hasil.xlsx" (Terletak di folder yg sama dgn file).
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel

    echo "Berhasil membuat sheet baru..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('foder/file_excel.xlsx'); // Mengambil file excel yang akan dimodif
$objPHPExcel->setActiveSheetIndex(0); //ke sheet 1

$objClonedWorksheet = clone $objPHPExcel->getSheetByName('NamaSheet 1'); //nama Sheet Yang akan dicopy
$objClonedWorksheet->setTitle('Sheet hasil Copyan'); //nama Sheet baru hasil copy
$objPHPExcel->addSheet($objClonedWorksheet);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
try {
    $objWriter->save('hasil_modif.xlsx'); //menyimpan file hasil modif "hasil.xlsx" (Terletak di folder yg sama dgn file).
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel

    echo "File Sukses Dimodif..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>

<?php
include('Classes/PHPExcel.php'); //memanggil atau mengimpor libaray PHPExcel

$objPHPExcel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('hasil_modif.xlsx'); // Mengambil file excel yang akan dimodif
$objPHPExcel->setActiveSheetIndex(0); //Menuju ke sheet yang akan dimodif

$sheetIndex = $objPHPExcel->getIndex($objPHPExcel->getSheetByName('Nama SHeet')); //nama sheet yg akan dihapus
$objPHPExcel->removeSheetByIndex($sheetIndex); //menghapus

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
try {
    $objWriter->save('hasil_modif2.xlsx'); //menyimpan file hasil modif "hasil.xlsx" (Terletak di folder yg sama dgn file).
    $objPHPExcel->disconnectWorksheets();
    unset($objWriter, $objPHPExcel); //menutup file excel
    echo "File Sukses Dimodif..";
} catch (Exception $e) { //menangkap jika error
    echo 'ERROR : ', $e->getMessage(); //menampilkan error
    die();
}
?>