I am trying to add a logo image on a generated excel worksheet using the code listing below but for some reason it does nothing
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("workbooks/" . $labref . "/" . $labref . ".xlsx");
$objPHPExcel->getActiveSheet(2);
$objWorkSheet = $objPHPExcel->createSheet();
$objWorkSheet->setTitle("Sample Summary");
$dir1 = "exclusive_image";
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName("name");
$objDrawing->setDescription("Description");
$objDrawing->setPath(base_url().'exclusive_image/nqcl.png');
$objDrawing->setCoordinates('A1');
$objDrawing->setWorksheet($objWorkSheet->getActiveSheet(2));
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save("workbooks/" . $labref . "/" . $labref . ".xlsx");
" Add a drawing to the worksheet\n"; $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); $objDrawing->setName('Sample image');$objDrawing->setDescription('Sample image'); $objDrawing->setImageResource($gdImage); $objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG); $objDrawing-> ...
You may use the following code: header('Content-Type: application/vnd. ms-excel'); header('Content-Disposition: attachment;filename="file. xlsx"'); header('Cache-Control: max-age=0'); $writer->save('php://output');
Open the connection to Excel by calling the odbc_connect or odbc_pconnect methods. To close connections, use odbc_close or odbc_close_all. $conn = odbc_connect("CData ODBC Excel Source","user","password"); Connections opened with odbc_connect are closed when the script ends.
Adding logo is so simple, try below code.
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->setSheetIndexAndTitle(1, "YOUR_SHEET_TITLE"); // first sheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$logo = base_path() . '/images/logo.png'; // Provide path to your logo file
$objDrawing->setPath($logo);
$objDrawing->setOffsetX(8); // setOffsetX works properly
$objDrawing->setOffsetY(300); //setOffsetY has no effect
$objDrawing->setCoordinates('B1');
$objDrawing->setHeight(75); // logo height
$objDrawing->setWorksheet($sheet);
Use:
<?php
include 'DB.php';
error_reporting(E_ALL);
ini_set('include_path', ini_get('include_path').';../Classes/');
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
$excel = new PHPExcel();
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="your_name.xls"');
header('Cache-Control: max-age=0');
$excel->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B2', 'world!')
->setCellValue('C1', 'Hello')
->setCellValue('D2', 'world!');
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$logo = 'images/logo.png'; // Provide path to your logo file
$objDrawing->setPath($logo); //setOffsetY has no effect
$objDrawing->setCoordinates('E1');
$objDrawing->setHeight(200); // logo height
$objDrawing->setWorksheet($excel->getActiveSheet());
// Do your stuff here
writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
// This line will force the file to download
$writer->save('php://output');
try,
public function rep()
{
//$this->load->library('phpexcel');
$this->load->library('PHPExcel/IOFactory');
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("dev@Eri")
->setTitle("Laporan Barang")
->setSubject("Laporan Barang")
->setDescription("report using PHPEXCEL.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
// Create the worksheet
$objPHPExcel->setActiveSheetIndex(0);
//DATA
$data = array(array('kode'=>'B001',
'Barang'=>'Dunhill',
'Kategori'=>'Rokok'),
array('kode'=>'B002',
'Barang'=>'Marlboro',
'Kategori'=>'Rokok'),
array('kode'=>'B003',
'Barang'=>'Pepsodent',
'Kategori'=>'Pasta Gigi'),
array('kode'=>'B004',
'Barang'=>'Gillete Goal',
'Kategori'=>'Pisau Cukur'));
################################## HEADER TABLE #################################
$objPHPExcel->getActiveSheet()->setCellValue('A6', "Kode Barang")
->setCellValue('B6', "Barang")
->setCellValue('C6', "Kategori");
################################## /HEADER TABLE ################################
$nox=count($data)+6;
############################### ROW VALUE TABLE ##############################
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A7');
############################### /ROW VALUE TABLE #############################
// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL);
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.75);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
// Set title row bold;
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFont()->setBold(true);
// Set fills
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Set autofilter
// Always include the complete filter range!
// Excel does support setting only the caption
// row, but that's not a best practise...
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM),
'left' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM) ),
)
);
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A6:C$nox");
// Set style for header row using alternative method
$objPHPExcel->getActiveSheet()->getStyle('A6:C6')->applyFromArray(
array( 'font' => array('bold' => true),
'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
'fill' => array('type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startcolor' => array('argb' => 'FFA0A0A0'),
'endcolor' => array('argb' => 'FFFFFFFF') )
) );
// Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./assets/images/photo.jpg');
$objDrawing->setCoordinates('A2');
$objDrawing->setHeight(50);
$objDrawing->setWidth(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
############################# HEADER TABLE #######################################
$objPHPExcel->getActiveSheet()->getStyle('A6:C1000')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A6:C1000')->getFont()->setSize(12);
############################# /HEADER TABLE ######################################
// Merge cells
############################# PAGE TITLE #########################################
$objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', "Compeni");
$objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(22);
$objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
############################# /PAGE TITLE ########################################
############################# SUB PAGE TITLE #####################################
$objPHPExcel->getActiveSheet()->mergeCells('A3:C3');
$objPHPExcel->getActiveSheet()->setCellValue('A3',"Laporan Barang");
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setName('Verdana');
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
############################# /SUB PAGE TITLE ####################################
############################# DETAIL PAGE REPORT #####################################
$objPHPExcel->getActiveSheet()->mergeCells('A4:C4');
$objPHPExcel->getActiveSheet()->setCellValue('A4',"Per ".date("d-F-Y")."");
$objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setName('Verdana');
$objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
############################# /DETAIL PAGE REPORT ####################################
// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test"'.date("d-F-Y").'".xlsx"');
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
$this->load->view('test');
exit;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With