Php generates an EXCEL document instance Program

Source: Internet
Author: User
Tags getcolor

The following describes several php implementation programs used to generate EXCEL documents. For more information, see.

Original Style
Original method: Send the header, and send the header of the attachment to the user's browser to indicate that the object is to be downloaded. Then, read the data in the database, parse it one by one, and write it into an excel file.

The Code is as follows: Copy code

<? Php
$ DB_Server = "localhost ";
$ DB_Username = "root ";
$ DB_Password = "";
$ DB_DBName = "DBName ";
$ DB_TBLName = "DB_TBLName ";
$ Savename = date ("YmjHis ");
$ Connect = @ mysql_connect ($ DB_Server, $ DB_Username, $ DB_Password) or die ("Couldn't connect .");
Mysql_query ("Set Names 'gbk '");
$ File_type = "vnd. ms-excel ";
$ File_ending = "xls ";
Header ("Content-Type: application/$ file_type; charset = gbk ");
Header ("Content-Disposition: attachment; filename =". $ savename. ". $ file_ending ");
Header ("Pragma: no-cache ");
$ Now_date = date ("Y-m-j H: I: s ");
$ Title = "Database Name: $ DB_DBName, data table: $ DB_TBLName, backup date: $ now_date ";
$ SQL = "Select * from $ DB_TBLName ";
$ ALT_Db = @ mysql_select_db ($ DB_DBName, $ Connect) or die ("Couldn't select database ");
$ Result = @ mysql_query ($ SQL, $ Connect) or die (mysql_error ());
Echo ("$ titlen ");
$ Sep = "t ";
For ($ I = 0; $ I <mysql_num_fields ($ result); $ I ++)
{
Echo mysql_field_name ($ result, $ I). "t ";
}
Print ("n ");
$ I = 0;
While ($ row = mysql_fetch_row ($ result ))
{
$ Schema_insert = "";
For ($ j = 0; $ j <mysql_num_fields ($ result); $ j ++)
{
If (! Isset ($ row [$ j]) $ schema_insert. = "NULL". $ sep;
Elseif ($ row [$ j]! = "") $ Schema_insert. = "$ row [$ j]". $ sep;
Else $ schema_insert. = "". $ sep;
}
$ Schema_insert = str_replace ($ sep. "$", "", $ schema_insert );
$ Schema_insert. = "t ";
Print (trim ($ schema_insert ));
Print "n"; $ I ++;
}
Return (true );
?>

PHPExcel Library

The following is an excel method that uses PHPExcel to achieve the same functions as above. GetCol is a recursive function used to return the corresponding column number Encoding Based on numbers. This is because the row number and column number must be specified during the export process. The row number is a simple number, and the column number is a combination of A-Z. To facilitate the import of two-dimensional arrays, column number encoding is automatically obtained based on the number of columns.

Instructions for use:

1. Save the following code as excel. php and call it on the page.

2. Call xlsBOF (), write some content to xlswritenunber () or xlswritelabel (), and finally call xlsEOF.

You can also use the fwrite function to directly write data to the server, instead of simply displaying data in the browser with echo.

The following is the PHP code:

The Code is as follows: Copy code

<? Php
// ----- Begin of function library -----
// Excel begin of file header
Function xlsBOF (){
Echo pack ("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0 );
Return;
}
// Excel end of file footer
Function xlsEOF (){
Echo pack ("ss", 0x0A, 0x00 );
Return;
}
// Function to write a Number (double) into Row, Col
Function xlsWriteNumber ($ Row, $ Col, $ Value ){
Echo pack ("sssss", 0x203, 14, $ Row, $ Col, 0x0 );
Echo pack ("d", $ Value );
Return;
}
// Function to write a label (text) into Row, Col
Function xlsWriteLabel ($ Row, $ Col, $ Value ){
$ L = strlen ($ Value );
Echo pack ("ssssss", 0x204, 8 + $ L, $ Row, $ Col, 0x0, $ L );
Echo $ Value;
Return;
}
?>
 
The following is the call code:

<? Php
Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT ");
Header ("Last-Modified:". gmdate ("D, d m yh: I: s"). "GMT ");
Header ("Cache-Control: no-cache, must-revalidate ");
Header ("Pragma: no-cache ");
Header ('content-type: application/x-msexcel ');
Header ("Content-Disposition: attachment; filename=EmplList.xls ");
Header ("Content-Description: PHP/INTERBASE Generated Data ");
//
// The next lines demonstrate the generation of the Excel stream
//
Include ("excel. php ");
XlsBOF (); // begin Excel stream
XlsWriteLabel (0, 0, "This is a label"); // write a label in A1, use for dates too
XlsWriteNumber (0, 1, 9999); // write a number B1
XlsEOF (); // close the stream
?>


Complete instance

The Code is as follows: Copy code

<?
// Set the include path of the PHPExcel class library
Set_include_path ('.'. PATH_SEPARATOR.
'D: ZealPHP_LIBS '. PATH_SEPARATOR.
Get_include_path ());

/**
* The following is an example. The optional methods are different for rows starting //.
* Open the comment of the corresponding row.
* If Excel5 is used, the output content should be GBK encoded.
*/
Require_once 'phpexcel. php ';

// Uncomment
/// Require_once 'phpexcel/Writer/excel5.php'; // used for other earlier versions of xls
// Or
/// Require_once 'phpexcel/Writer/excel2007.php'; // used for excel-2007 format

// Create a processing object instance
$ ObjExcel = new PHPExcel ();

// Create a file format to write the object instance, uncomment
//// $ ObjWriter = new PHPExcel_Writer_Excel5 ($ objExcel); // used for other version formats
// Or
//// $ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objExcel); // used in 2007 format
// $ ObjWriter-> setOffice2003Compatibility (true );

//*************************************
// Set the basic attributes of the document
$ ObjProps = $ objExcel-> getProperties ();
$ ObjProps-> setCreator ("Zeal Li ");
$ ObjProps-> setLastModifiedBy ("Zeal Li ");
$ ObjProps-> setTitle ("Office XLS Test Document ");
$ ObjProps-> setSubject ("Office XLS Test Document, Demo ");
$ ObjProps-> setDescription ("Test document, generated by PHPExcel .");
$ ObjProps-> setKeywords ("office excel PHPExcel ");
$ ObjProps-> setCategory ("Test ");

//*************************************
// Set the current sheet index for subsequent content operations.
// Display the call only when multiple sheets are used.
// By default, PHPExcel will automatically create the first sheet and set SheetIndex = 0
$ ObjExcel-> setActiveSheetIndex (0 );


$ ObjActSheet = $ objExcel-> getActiveSheet ();

// Set the name of the current active sheet
$ ObjActSheet-> setTitle ('test Sheet ');

//*************************************
// Set the cell content
//
// PHPExcel automatically determines the cell content type based on the input content
$ ObjActSheet-> setCellValue ('a1', 'string content'); // string content
$ ObjActSheet-> setCellValue ('a2 ', 26); // Value
$ ObjActSheet-> setCellValue ('a3 ', true); // Boolean Value
$ ObjActSheet-> setCellValue ('a4 ',' = SUM (A2: A2) '); // Formula

// Explicitly specify the content type
$ ObjActSheet-> setCellValueExplicit ('a5 ', '123 ',
PHPExcel_Cell_DataType: TYPE_STRING );

// Merge Cells
$ ObjActSheet-> mergeCells ('b1: c22 ');

// Separate cells
$ ObjActSheet-> unmergeCells ('b1: c22 ');

//*************************************
// Set the cell style
//

// Set the width
$ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true );
$ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 );

$ ObjStyleA5 = $ objActSheet-> getStyle ('a5 ');

// Set the numeric format of the cell content.
//
// If PHPExcel_Writer_Excel5 is used to generate the content,
// Note that the const variable defined in the PHPExcel_Style_NumberFormat class
// Other types of custom formatting methods can be used normally, but when setFormatCode
// When FORMAT_NUMBER is used, the actual effect is not set to "0 ". Yes
// Modify the getXf ($ style) method in the source code of the PHPExcel_Writer_Excel5_Format class,
// Add one before if ($ this-> _ BIFF_version = 0x0500) {(near row 363rd)
// Line of code:
// If ($ ifmt = '0') $ ifmt = 1;
//
// Set the format to PHPExcel_Style_NumberFormat: FORMAT_NUMBER to avoid large numbers.
// The data is displayed in scientific notation. The following setAutoSize method can be used to display the content of each row.
// All are displayed based on the original content.
$ ObjStyleA5
-> GetNumberFormat ()
-> SetFormatCode (PHPExcel_Style_NumberFormat: FORMAT_NUMBER );

// Set the font
$ ObjFontA5 = $ objStyleA5-> getFont ();
$ ObjFontA5-> setName ('courier new ');
$ ObjFontA5-> setSize (10 );
$ ObjFontA5-> setBold (true );
$ ObjFontA5-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE );
$ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 ');

// Set alignment
$ ObjAlignA5 = $ objStyleA5-> getAlignment ();
$ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT );
$ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER );

// Set the border
$ ObjBorderA5 = $ objStyleA5-> getBorders ();
$ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffff000000'); // color
$ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );

// Set the fill color
$ ObjFillA5 = $ objStyleA5-> getFill ();
$ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID );
$ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee ');

// Copy the style information from the specified cell.
$ ObjActSheet-> duplicateStyle ($ objStyleA5, 'b1: C22 ');


//*************************************
// Add an image
$ ObjDrawing = new PHPExcel_Worksheet_Drawing ();
$ ObjDrawing-> setName ('zealimg ');
$ ObjDrawing-> setDescription ('image inserted by zeal ');
$ ObjDrawing-> setPath ('./zeali.net.logo.gif ');
$ ObjDrawing-> setHeight (36 );
$ ObjDrawing-> setCoordinates ('c23 ');
$ ObjDrawing-> setOffsetX (10 );
$ ObjDrawing-> setRotation (15 );
$ ObjDrawing-> getShadow ()-> setVisible (true );
$ ObjDrawing-> getShadow ()-> setDirection (36 );
$ ObjDrawing-> setWorksheet ($ objActSheet );


// Add a new worksheet
$ ObjExcel-> createSheet ();
$ ObjExcel-> getSheet (1)-> setTitle ('test 2 ');

// Protect cells
$ ObjExcel-> getSheet (1)-> getProtection ()-> setSheet (true );
$ ObjExcel-> getSheet (1)-> protectCells ('a1: c22', 'phpexcel ');


//*************************************
// Output content
//
$ OutputFileName = "output.xls ";
// To the file
/// $ ObjWriter-> save ($ outputFileName );
// Or
// Go to the browser
/// Header ("Content-Type: application/force-download ");
/// Header ("Content-Type: application/octet-stream ");
/// Header ("Content-Type: application/download ");
/// Header ('content-Disposition: inline; filename = "'. $ outputFileName .'"');
/// Header ("Content-Transfer-Encoding: binary ");
/// Header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT ");
/// Header ("Last-Modified:". gmdate ("D, d m y h: I: s"). "GMT ");
/// Header ("Cache-Control: must-revalidate, post-check = 0, pre-check = 0 ");
/// Header ("Pragma: no-cache ");
//// $ ObjWriter-> save ('php: // output ');

?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.