Usage and introduction of PHPEXCEL_PHP tutorial-PHP Tutorial-php.cn

PHPEXCEL is a php plug-in used to generate excel. It can easily operate on excel data, such as: generating excel, modifying excel data, etc.

1. Introduction to PHPEXCEL

PHPEXCEL provides a series of APIs that can parse and generate documents such as excel and pdf.

Although PHPEXCEL is powerful, it is relatively cumbersome to use. If you need to output a more complex format, it is a good choice. You can download the source code from the official website.

2. Some functions of PHPEXCEL

Set the current workbook and return the workbook object:
$excelSheet = $excel->setActiveSheetIndex(0);

Merge cells and return the cell object. The following example merges the cells in the first and second rows of column A:

The code is as follows

Copy code

代码如下

复制代码

$excelSheet->mergeCells('A1:A2');

设置单元格的值，参数：单元格名称，值：
$excelSheet->setCellValue('A1', '字符串内容');
$excelSheet->setCellValue('A2', 26); //数值
$excelSheet->setCellValue('A3', true); //布尔值
$excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //公式

$excelSheet->mergeCells('A1:A2');

Set the value of the cell, parameter: cell name, value:

$excelSheet->setCellValue('A1', 'String content');

$excelSheet->setCellValue('A2', 26); //Value

代码如下

复制代码

include ‘PHPExcel.php’;

　　include ‘PHPExcel/Writer/Excel2007.php’;

　　//或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的

　　创建一个excel

　　$objPHPExcel = new PHPExcel();

　　保存excel—2007格式

　　$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

　　//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式

　　$objWriter->save(”xxx.xlsx”);

　　直接输出到浏览器

　　$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

　　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-execl”);

　　header(”Content-Type:application/octet-stream”);

　　header(”Content-Type:application/download”);;

　　header(’Content-Disposition:attachment;filename=”resume.xls”‘);

　　header(”Content-Transfer-Encoding:binary”);

　　$objWriter->save(’php://output’);

$excelSheet->setCellValue('A3', true); //Boolean value $excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //Formula

Introduction to phpexcel usage

The code is as follows

Copy code

include ‘PHPExcel.php’; include ‘PHPExcel/Writer/Excel2007.php’; //Or include ‘PHPExcel/Writer/Excel5.php’; for outputting .xls Create an excel $objPHPExcel = new PHPExcel(); Save excel—2007 format $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 　//Or $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); non-2007 format $objWriter->save("xxx.xlsx"); Output directly to the browser $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 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-execl"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download");; header(’Content-Disposition:attachment;filename="resume.xls”‘); header("Content-Transfer-Encoding:binary"); $objWriter->save(’php://output’); 　

————————————————————————————————————————

Set excel attributes:

The code is as follows

Copy code

代码如下

复制代码

创建人

　　$objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);

　　最后修改人

　　$objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”);

　　标题

　　$objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”);

　　题目

　　$objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”);

　　描述

　　$objPHPExcel->getProperties()->setDescription(”Test document for Office 2007 XLSX, generated using PHP classes.”);

　　关键字

　　$objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”);

　　种类

　　$objPHPExcel->getProperties()->setCategory(”Test result file”);

　　——————————————————————————————————————–

　　设置当前的sheet

　　$objPHPExcel->setActiveSheetIndex(0);

　　设置sheet的name

　　$objPHPExcel->getActiveSheet()->setTitle(’Simple’);

　　设置单元格的值

　　$objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’);

　　$objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);

　　$objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);

　　$objPHPExcel->getActiveSheet()->setCellValue(’C5′, ‘=SUM(C2:C4)’);

　　$objPHPExcel->getActiveSheet()->setCellValue(’B8′, ‘=MIN(B2:C5)’);

　　合并单元格

　　$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);

　　分离单元格

　　$objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

Created by $objPHPExcel->getProperties()->setCreator("Maarten Balliauw"); Last modified by $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw"); Title $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document"); Title $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document"); Description 　$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes."); Keywords 　$objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”); Category $objPHPExcel->getProperties()->setCategory(”Test result file”); 　———————————————————————————————————————— Set the current sheet $objPHPExcel->setActiveSheetIndex(0); Set the name of the sheet $objPHPExcel->getActiveSheet()->setTitle(’Simple’); Set the value of the cell $objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’); $objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12); $objPHPExcel->getActiveSheet()->setCellValue(’A3′, true); $objPHPExcel->getActiveSheet()->setCellValue(’C5′, ‘=SUM(C2:C4)’); $objPHPExcel->getActiveSheet()->setCellValue(’B8′, ‘=MIN(B2:C5)’); Merge cells $objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′); Separate cells $objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

3. Example application of PHPEXCEL

The entire code is as follows (it is worth noting that the table header uses $orderCellData to record the order of each merchant number, in order to retrieve the corresponding data in the table body):

The code is as follows

Copy code

require_once '../../../libs/PHPExcel/Classes/PHPExcel.php';

require_once '../../../libs/PHPExcel/Classes/PHPExcel/Writer/Excel5.php';

include_once '../../../libs/PHPExcel/Classes/PHPExcel/IOFactory.php';

include '../common/config.php';

// Create a handler object instance (this object is the same for 2003 2007)

$objExcel = new PHPExcel();

//Set attributes (this code is irrelevant, the content can be replaced with what you need)

$objExcel->getProperties()->setCreator("office 2003 excel");

$objExcel->getProperties()->setLastModifiedBy("office 2003 excel");

$objExcel->getProperties()->setTitle("Office 2003 XLS Test Document");

$objExcel->getProperties()->setSubject("Office 2003 XLS Test Document");

$objExcel->getProperties()->setDescription("Test document for Office 2003 XLS, generated using PHP classes.");

$objExcel->getProperties()->setKeywords("office 2003 openxml php");

$objExcel->getProperties()->setCategory("Test result file");

//Start processing data (index starts from 0)

$objExcel->setActiveSheetIndex(0);

$conn = mssql_connect($config['mssql']['host'],$config['mssql']['user'],$config['mssql']['password']);

mssql_select_db($config['mssql']['dbname'],$conn);

$tm=$_REQUEST['tm'];

$sql = "exec HNow05_getTTSpace '','".$tm."','',1";

$sql=mb_convert_encoding($sql,'GBK','UTF-8');

$res=mssql_query($sql);

$i=0;

$k = array('station code', 'station name', 'river system', 'come report time', 'water level', 'water potential');

$count = count($k);

$arrs = array('A','B','C','D','E','F');

//Add header

for($i=0;$i<$count;$i++){

$objExcel->getActiveSheet()->setCellValue($arrs[$i]."1", "$k[$i]");

}

/*--------Read data from the database-------*/

$i=0;

while($arr=mssql_fetch_array($res))

{

$stcd = $arr["STCD"];

$stnm = $arr["STNM"];

$rvnm = $arr["RVNM"];

$tm= $arr["TM"];

$tdz= $arr["TDZ"];

$tdptn= $arr["TDPTN"];

if($tdptn=='6'){

$tdptn='flat';

}else if($tdptn=='5'){

$tdptn='rising';

}else if($tdptn=='4'){

$tdptn='drop';

}

$u1=$i+2;

$stnm=iconv("GBK","utf-8",$stnm);

$rvnm=iconv("GBK","utf-8",$rvnm);

$tm=iconv("GBK","utf-8",$tm);

/*----------Write content-------------*/

$objExcel->getActiveSheet()->setCellValue('a'.$u1, "$stcd");

$objExcel->getActiveSheet()->setCellValue('b'.$u1, "$stnm");

$objExcel->getActiveSheet()->setCellValue('c'.$u1, "$rvnm");

$objExcel->getActiveSheet()->setCellValue('d'.$u1, "$tm");

$objExcel->getActiveSheet()->setCellValue('e'.$u1, "$tdz");

$objExcel->getActiveSheet()->setCellValue('f'.$u1, "$tdptn");

$i++;

}

/*----------Set cell border and color-------------*/

$rows = mssql_num_rows($res);

for($i=0;$i<($rows+1);$i++){

for($j=0;$j<$count;$j++){

$a = $i+1;

> 🎜>

$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->getColor()->setARGB(' FF00BBcc');

//Centered horizontally

}

// Width of high column

$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);

$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);

$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);

$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);

// Set header and footer. If there are no different headers odd/even using a single header is assumed.

$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');

> 🎜>

// Set page orientation and size

$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Rename table

$objExcel->getActiveSheet()->setTitle('Real-time tide conditions');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel5) and save it in excel2003 format

//Set Excel name

$excelName = 'Real-time tide conditions ('.$tm.')';

//$excelName = 'Excel_'.date("YmdHis");

header('Content-Type: application/vnd.ms-excel');

header('Cache-Control: max-age=0');

header( 'Content-Disposition: attachment; filename='.iconv("utf-8", "GBK", $excelName).'.xls');

$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');

$objWriter->save('php://output');

exit;