phpexcel rapid development guide (good)-PHP Tutorial-php.cn

phpexcel rapid development guide (good)

WBOY

Release： 2016-07-25 09:04:20

Original

952 people have browsed it

include("./class/class.php"); // Contains the basic header file of class
include("./class/phpexcel/PHPExcel.php" ); // Generate the basic class definition of excel (note the case of the file name)
// If you directly output the excel file, you must include this file
include("./class/phpexcel /PHPExcel/IOFactory.php");
// Create a phpexcel object, which contains the output content and format
$m_objPHPExcel = new PHPExcel();
< p>// Template file, in order to separate the format and content, the specific content of the output file is implemented in the template file
// The template file operates the object $m_objPHPExcel
include("./include/excel.php");< ;/p>
//Output file type, excel or pdf
$m_exportType = "excel";
$m_strOutputExcelFileName = date('Y-m-j_H_i_s')." .xls"; // Output EXCEL file name
$m_strOutputPdfFileName = date('Y-m-j_H_i_s').".pdf"; // Output PDF file name
// PHPExcel_IOFactory, output excel
//require_once dirname(__FILE__).'/Classes/PHPExcel/IOFactory.php';
// If you need to output EXCEL format
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($m_objPHPExcel, 'Excel5');
// Output $m_strOutputExcelFileName directly from the browser
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=".$m_strOutputExcelFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
//If you need to output PDF format
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($m_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");
}
?>

Copy code

2. Template file content (with common operations attached)

global $m_objPHPExcel; // Defined by external file
// Set basic properties
$m_objPHPExcel->getProperties() ->setCreator("Sun Star Data Center")
->setLastModifiedBy("Sun Star Data Center")
->setTitle("Microsoft Office Excel Document")
->setSubject("Test Data Report -- From Sunstar Data Center")
->setDescription("LD Test Data Report, Generate by Sunstar Data Center")
->setKeywords("sunstar ld report")
->setCategory("Test result file");
//Create multiple workbooks
$sheet1 = $m_objPHPExcel->createSheet();
$sheet2 = $m_objPHPExcel->createSheet();
< ;p>// You can operate the corresponding workbook by operating the index
// Just set the workbook index to be operated as the current active workbook, such as
// $m_objPHPExcel->setActiveSheetIndex(0);
//Set the first workbook as the active workbook
$m_objPHPExcel->setActiveSheetIndex(0);
//Set the name of the active workbook
// If it is Chinese, you must use the iconv function to convert the encoding
$m_objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'Test Workbook'));
//Set the default font and size
$m_objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));
$m_objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
// Set the width of a column
$m_objPHPExcel->getActiveSheet()-> getColumnDimension('A')->setWidth(15);
// Set the height of a row
$m_objPHPExcel->getActiveSheet()->getRowDimension('6')-> ;setRowHeight(30);
// Merge cells
$m_objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
< p>// Define a style, bold, centered
$styleArray1 = array(
'font' => array(
'bold' => true,
'color'=>array(
'argb' = > '00000000',
),
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
);< /p>
//Apply style to cell A1
$m_objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
//Set cell style (black font)
$m_objPHPExcel->getActiveSheet()->getStyle('H5')->getFont()->getColor()->setARGB(PHPExcel_Style_Color ::COLOR_BLACK); // Black
// Set cell format (background)
$m_objPHPExcel->getActiveSheet()->getStyle('H5')->getFill( )->getStartColor()->setARGB('00ff99cc'); // Set the background to light pink
// Set cell format (number format)
$m_objPHPExcel-> ;getActiveSheet()->getStyle('F1')->getNumberFormat()->setFormatCode('0.000');
// Write content to a specific cell
$m_objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello Baby');
// Set cell style (centered)
$m_objPHPExcel->getActiveSheet( )->getStyle('H5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Put a picture into the cell and add the data picture Place it in cell J1
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath(" ../logo.jpg"); // Image path can only be a relative path
$objDrawing->setWidth(400); // Image width
$objDrawing->setHeight(123); // Image height
$objDrawing->setCoordinates('J1');//Cell
$objDrawing->setWorksheet($m_objPHPExcel->getActiveSheet());
// Set A5 cell content and add hyperlinks
$m_objPHPExcel->getActiveSheet()->setCellValue('A5', iconv('gbk', 'utf-8', 'Hyperlink jbxue.com'));
$m_objPHPExcel-> ;getActiveSheet()->getCell('A5')->getHyperlink()->setUrl('http://bbs.it-home.org/');
?>

Copy code

3. Generate static files on the server side Compared with direct generation, the main difference between these two methods is the generation format. The template files are exactly the same. The following is a modified version based on the above example. Pay attention to the difference from the above example.

// Contains the basic header file of class
include("./class/class.php");
// Generate the basic class definition of excel (note the case of the file name)
include("./class/phpexcel/PHPExcel.php");
// Contains files written in Excel5 format. If you need to generate excel2007 files, include the corresponding Writer can
include("./class/phpexcel/PHPExcel/Writer/Excel5.php");
// Includes writing PDF format files
include("./class/phpexcel/PHPExcel/Writer/PDF.php") ;
// Create a phpexcel object, which contains the output content and format
$m_objPHPExcel = new PHPExcel();
// Template file, in order To achieve separation of format and content, the specific content of the output file is implemented in the template file
// The template file operates the object $m_objPHPExcel
include("./include/excel.php");
$m_exportType = "pdf";
$m_strOutputExcelFileName = date('Y-m-j_H_i_s').".xls"; // Output EXCEL File name
$m_strOutputPdfFileName = date('Y-m-j_H_i_s').".pdf"; // Output PDF file name
// Output file saving path, this path must be writable
$ m_strOutputPath = "./output/";
// If you need to output EXCEL format
if($m_exportType=="excel"){
$objWriter = new PHPExcel_Writer_Excel5($m_objPHPExcel);
$objWriter->save($m_strOutputPath.$m_strOutputExcelFileName);
}
// If you need to output PDF format
if($m_exportType=="pdf"){
$objWriter = new PHPExcel_Writer_PDF($m_objPHPExcel);
$objWriter->save($m_strOutputPath.$m_strOutputPdfFileName);
}
?>