PHPEXCEL usage and introduction

PHPEXCEL is a php plug-in used to generate excel files. it can easily operate excel files, such as generating excel files and modifying excel files. i. PHPEXCEL introduction PHPEXCEL provides A series of... PHPEXCEL is a php plug-in used to generate excel files. it can easily operate excel files, such as generating excel files and modifying excel files.

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. if you need to output a more complex format, it is a good choice. you can download it to the official source code.

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:

$ 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:

 GetProperties ()-> setCreator ("Maarten Balliauw"); last modifier $ 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. "); Keyword $ objPHPExcel-> getProperties ()-> setKeywords ("office 2007 openxml php"); type $ 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 cell value $ objPHPExcel-> getActiveSheet ()-> setCellValue ("A1", 'string'); $ objPHPExcel-> getActiveSheet ()-> se TCellValue ("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. to retrieve the corresponding data from the table body, the code is as follows:

 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 ()-> setKe Ywords ("office 2003 openxml php"); $ objExcel-> getProperties ()-> setCategory ("Test result file"); // start data processing (index starts from 0) $ objExcel-> setActiveSheetIndex (0); $ conn = mssql_connect ($ config ['SQL'] ['host'], $ config ['SQL'] ['user'], $ config ['SQL'] ['password']); mssql_select_db ($ config ['sqlsql'] ['dbname'], $ conn ); $ tm = $ _ REQUEST ['TM ']; $ SQL = "exec HNow05_getTTSpace '','". $ tm. "','', 1 "; $ SQL = mb_convert_encodin G ($ 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 the 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 ()-> setCellValu E ('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_row S ($ 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'); // horizontally centered $ objExcel-> getActiveSheet () -> getStyle ($ arr S [$ j]. $ a)-> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER);} // width of the height 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 ()-> get ColumnDimension ('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 scale $ objExcel-> getActiveSheet ()-> getPageSetup ()-> setOrientation (PHPExcel_Worksheet_PageSetup: ORIENTATION_PORTRAIT ); $ objExcel-> getActive Sheet ()-> getPageSetup ()-> setPaperSize (PHPExcel_Worksheet_PageSetup: PAPERSIZE_A4); // rename the table $ objExcel-> getActiveSheet ()-> setTitle ('Real-Time tidal situation '); // 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) save as excel2003 format // Set the Excel name $ excelName = 'Real-Time tide ('. $ tm. ')'; // $ excelName = 'Excel _'. date ("Y MdHis "); 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;?>

Tutorial address:

Reprinted! But please include the article address ^