Guide to Common Functions of phpExcel Chinese Help Manual
- 2021-07-13 04:33:00
- OfStack
PHPExcel Basic Operations:
Defining EXCEL Entities
That is to say, define an PHPEXCEL object and set the display content in the EXCEL object
// Excel Begin
// Prepare EXCEL Include files of
// Error reporting
error_reporting(0);
// PHPExcel
require_once dirname(__FILE__) . 'PHPExcel.php';
// Generate a new excel Object
$objPHPExcel = new PHPExcel();
// Settings excel Attributes of a document
$objPHPExcel->getProperties()->setCreator("Sam.c")
->setLastModifiedBy("Sam.c Test")
->setTitle("Microsoft Office Excel Document")
->setSubject("Test")
->setDescription("Test")
->setKeywords("Test")
->setCategory("Test result file");
// Start operation excel Table
// Operation number 1 Sheets
$objPHPExcel->setActiveSheetIndex(0);
// Set the workbook name
$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'phpexcel Test '));
// Set the default font and size
$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', ' Song Style '));
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
3. Output file
// If output is required EXCEL Format
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Output directly from browser $filename
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
// If output is required PDF Format
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
Set the width of 1 column:
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
Set the height of 1 row:
$objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);
Merge cells:
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
Set A1 cells to be bold and centered:
$styleArray1 = array(
'font' => array(
'bold' => true,
'size'=>12,
'color'=>array(
'argb' => '00000000',
),
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
);
// Will A1 Cells are set to bold and centered
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
Write content to a specific cell:
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Baby');
Set cell style (horizontal/vertical center):
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
Set the cell style (black font):
$objPHPExcel->getActiveSheet()->getStyle('H5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); // Black
Format cells (background):
$objPHPExcel->getActiveSheet()->getStyle('H5')->getFill()->getStartColor()->setARGB('00ff99cc'); // Set the background to light pink
Format cells (numeric format):
// If output is required EXCEL Format
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Output directly from browser $filename
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
// If output is required PDF Format
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
0
Put a picture in a cell:
// If output is required EXCEL Format
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Output directly from browser $filename
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
// If output is required PDF Format
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
1
Set hyperlinks in cells:
// If output is required EXCEL Format
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// Output directly from browser $filename
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/vnd.ms-excel;");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
// If output is required PDF Format
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type: application/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
2
Set the cell border
$styleThinBlackBorderOutline = array(
'borders' => array (
'outline' => array (
'style' => PHPExcel_Style_Border::BORDER_THIN, // Settings border Style
//'style' => PHPExcel_Style_Border::BORDER_THICK, Another 1 Styles
'color' => array ('argb' => 'FF000000'), // Settings border Color
),
),
);
$objPHPExcel->getActiveSheet()->getStyle( 'A4:E10')->applyFromArray($styleThinBlackBorderOutline);
// Add 1 A new one worksheet
$objExcel->createSheet();
$objActSheet = $objExcel->getSheet($s);
$objActSheet->setTitle(' Table '.$GSheet);