PHPEXCEL usage and introduction _ PHP Tutorial

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 PHPEXCEL as a php plug-in used to generate excel. it can easily perform operations on excel data, such as generating excel data 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:
$ 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:

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:

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:

< 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; ?>

Bytes. I. PHPEXCEL introduction PHPEXCEL provides...