PHPEXCEL usage and introduction

PHPEXCEL is a php plug-in used to generate excel files. It can easily operate excel data, such as generating excel files and modifying excel Data.

I. PHPEXCEL Introduction

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

PHPEXCEL is powerful, but it is relatively complicated to use. It is a good choice to output complicated formats. You can download the source code from the official website.

Ii. PHPEXCEL Functions

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

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

The Code is as follows:	Copy code
$ ExcelSheet-> mergeCells ('a1: A2 '); Set the cell value. Parameter: Cell name. value: $ ExcelSheet-> setCellValue ('a1', 'string content '); $ ExcelSheet-> setCellValue ('a2 ', 26); // Value $ ExcelSheet-> setCellValue ('a3 ', true); // Boolean Value $ ExcelSheet-> setCellValue ('a4 ',' = SUM (A2: A2) '); // Formula

Phpexcel usage

 

The Code is as follows:

Copy code

Include 'phpexcel. php '; Include 'phpexcel/Writer/excel2007.php '; // Or include 'phpexcel/Writer/excel5.php'; for output. xls Create an excel file $ ObjPHPExcel = new PHPExcel (); Save the 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

Created $ ObjPHPExcel-> getProperties ()-> setCreator ("Maarten Balliauw "); Last modified $ ObjPHPExcel-> getProperties ()-> setLastModifiedBy ("Maarten Balliauw "); Title $ ObjPHPExcel-> getProperties ()-> setTitle ("Office 2007 XLSX Test Document "); Question $ 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 "); Type $ ObjPHPExcel-> getProperties ()-> setCategory ("Test result file "); --------------------------------------- Set the current sheet $ ObjPHPExcel-> setActiveSheetIndex (0 ); Set sheet name $ ObjPHPExcel-> getActiveSheet ()-> setTitle ('simple '); Set the cell value $ 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 ′);

Iii. PHPEXCEL example application

The Code is as follows (it is worth noting that $ orderCellData is used in the header to record the order of merchant numbers, in order to retrieve the corresponding data in the table body ):

The Code is as follows:

Copy code

<? Php 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 processing object instance (this object is the same for 2003 2007) $ ObjExcel = new PHPExcel (); // Set attributes (this code does not matter, and 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 data processing (index starts from 0) $ ObjExcel-> setActiveSheetIndex (0 );   $ Conn = mssql_connect ($ config ['mssql'] ['host'], $ config ['mssql'] ['user'], $ config ['SQL'] ['Password']); Mssql_select_db ($ config ['mssql'] ['dbname'], $ conn ); $ Tm = $ _ REQUEST ['TT']; $ SQL = "exec HNow05_getTTSpace'', '". $ tm."', '', 1 "; $ SQL = mb_convert_encoding ($ SQL, 'gbk', 'utf-8 '); $ Res = mssql_query ($ SQL );   $ I = 0; $ K = array ('site Code', 'site name', 'river', 'reporting time', 'watermark', 'watermark '); $ Count = count ($ k ); $ Arrs = array ('A', 'B', 'C', 'D', 'E', 'F '); // Add a 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 = 'ping '; } Else if ($ tdptn = '5 '){ $ Tdptn = 'upgrade '; } 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 the 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 ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN ); $ ObjExcel-> getActiveSheet ()-> getStyle ($ arrs [$ j]. $ a)-> getBorders ()-> getAllBorders ()-> getColor ()-> setARGB ('ff00bbcc '); // Horizontal center $ ObjExcel-> getActiveSheet ()-> getStyle ($ arrs [$ j]. $ a)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER ); } }   // Height column width $ 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 the header and footer. If there are no different titles with odd numbers/even if the single-header assumption is used. $ ObjExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddHeader ('& L & BPersonal cash register & RPrinted on & D '); $ ObjExcel-> getActiveSheet ()-> getHeaderFooter ()-> setOddFooter ('& L & B '. $ objExcel-> getProperties ()-> getTitle (). '& RPage & P of & n '); // Set the page direction and size $ ObjExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup: ORIENTATION_PORTRAIT ); $ ObjExcel-> getActiveSheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: PAPERSIZE_A4 ); // Rename the table $ ObjExcel-> getActiveSheet ()-> setTitle ('real-time tidal status '); // 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) is saved as excel2003 // Set the Excel name $ ExcelName = 'real-time tidal condition ('. $ 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; ?>