Skip to content

Instantly share code, notes, and snippets.

@nguyenthanhxuan
Forked from IamSmith/FullExample.php
Created May 2, 2013 01:21
Show Gist options
  • Save nguyenthanhxuan/5499595 to your computer and use it in GitHub Desktop.
Save nguyenthanhxuan/5499595 to your computer and use it in GitHub Desktop.
<?php
require_once("PHPExcel/PHPExcel.php");
$phpExcel = new PHPExcel();
$styleArray = array(
'font' => array(
'bold' => true,
)
);
//Get the active sheet and assign to a variable
$foo = $phpExcel->getActiveSheet();
//add column headers, set the title and make the text bold
$foo->setCellValue("A1", "Foo1")
->setCellValue("B1", "Foo2")
->setCellValue("C1", "Foo3")
->setCellValue("D1", "Foo3")
->setTitle("Foo")
->getStyle("A1:D1")->applyFromArray($styleArray);
//Create a new sheet
$bar = $phpExcel->createSheet();
$bar->setCellValue("A1", "Bar1")
->setCellValue("B1", "Bar2")
->setCellValue("C1", "Bar3")
->setCellValue("D1", "Bar3")
->setTitle("Bar")
->getStyle("A1:D1")->applyFromArray($styleArray);
//When in loops you always need to use a counter to ensure data goes into the next row.
for ($rowCounter = 2; $rowCounter < 20; $rowCounter++) {
$foo->setCellValue("A$rowCounter", "Row" . ($rowCounter - 2))
->setCellValue("B$rowCounter", $rowCounter * 2)
->setCellValue("C$rowCounter", $rowCounter / 2)
->setCellValue("D$rowCounter", "=B$rowCounter+C$rowCounter");
$bar->setCellValue("A$rowCounter", "Row" . ($rowCounter - 2))
->setCellValue("B$rowCounter", ($rowCounter % 2) ? "Type 1" : "Type2")
->setCellValue("C$rowCounter", str_repeat("foo ", rand(5, 10)))
->setCellValue("D$rowCounter", str_repeat("% ", rand(20, 50)));
}
//Merge the first two columns of the next row and sum columns C & D.
$foo->mergeCells("A$rowCounter:B$rowCounter");
$foo->setCellValue("A$rowCounter", "Total")
->setCellValue("C$rowCounter", "=SUM(C2:C" . ($rowCounter -1) . ")")
->setCellValue("D$rowCounter", "=SUM(D2:D" . ($rowCounter -1) . ")");
//Set the text alignment to right for the total cell.
$foo->getStyle("A$rowCounter")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//Set the column widths
$foo->getColumnDimension("A")->setWidth(40);
$foo->getColumnDimension("B")->setWidth(20);
$foo->getColumnDimension("C")->setWidth(20);
$foo->getColumnDimension("D")->setWidth(20);
$bar->getColumnDimension("A")->setAutoSize(true);
$bar->getColumnDimension("B")->setAutoSize(true);
$bar->getColumnDimension("C")->setAutoSize(true);
$bar->getColumnDimension("D")->setWidth(40);
//Wrap long fields
$bar->getStyle("D1:D20")->getAlignment()->setWrapText(true);
//Set the active sheet to the first sheet before outputting. This is only needed if you want to ensure the file is opened on the first sheet.
$phpExcel->setActiveSheetIndex(0);
//Output the generated excel file so that the user can save or open the file.
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"example-excel-file.xls\"");
header("Cache-Control: max-age=0");
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, "Excel5");
$objWriter->save("php://output");
exit;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment