Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > PHP generates EXCEL document example program_PHP tutorial

PHP generates EXCEL document example program_PHP tutorial

WBOY
Release： 2016-07-13 16:57:02
Original
868 people have browsed it

The following introduces several PHP implementation programs for generating EXCEL documents. Friends in need can refer to them.

Original writing style
Original method: send the header, use the header of the attachment to send it to the user's browser to indicate that it is to be downloaded, then read the data in the database, parse it one by one, and write it into a file in excel format

The code is as follows Copy code
 代码如下 复制代码

$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 = "数据库名:$DB_DBName,数据表:$DB_TBLName,备份日期:$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 {
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);
?>
$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                                                                         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); <🎜> ?>

Use PHPExcel library

The following is a method of using PHPExcel to implement excel with the same functions as above. getCol is a recursively implemented function used to return the corresponding column number code based on the number. Because the row number and column number need to be pointed out during the export process. Row numbers are simple numbers, while column numbers are a combination of "A-Z". In order to facilitate the import of two-dimensional arrays, the column number code is automatically obtained based on the number of columns

Instructions for use:

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

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

You can also use the fwrite function to write directly to the server instead of using echo to only display it on the browser.

The following is the PHP code:

The code is as follows Copy code
 代码如下 复制代码

// ----- 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;
}
?>
 
下面是调用代码：

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
?>
// ----- 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 calling code: 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 example

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 are usage examples. There are different optional methods for lines starting with ////. Please base on actual needs
* Open the comment for the corresponding line.
* If you use Excel5, the output content should be GBK encoded.
​*/
require_once 'PHPExcel.php';

// uncomment
////require_once 'PHPExcel/Writer/Excel5.php'; // Used for other lower versions of xls
// or
////require_once 'PHPExcel/Writer/Excel2007.php'; // For excel-2007 format

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

//Create file format writing object instance, uncomment
////$objWriter = new PHPExcel_Writer_Excel5($objExcel); // Used for other version formats
// or
////$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // For 2007 format
//$objWriter->setOffice2003Compatibility(true);

//******************************************
//Set basic document properties
$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.
//Generally, display calls are only needed when using multiple sheets.
//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('TestSheet');

//******************************************
//Set cell content
//
//PHPExcel automatically determines the cell content type based on the incoming 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', '847475847857487584',
PHPExcel_Cell_DataType::TYPE_STRING);

//Merge cells
$objActSheet->mergeCells('B1:C22');

//Separate cells
$objActSheet->unmergeCells('B1:C22');

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

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

$objStyleA5 = $objActSheet->getStyle('A5');

//Set the number format of the cell content.
//
//If PHPExcel_Writer_Excel5 is used to generate content,
//It should be noted here that the const variable defined in the PHPExcel_Style_NumberFormat class
//In various custom formatting methods, other types can be used normally, but when setFormatCode
//When it is FORMAT_NUMBER, the actual effect is not set to "0". Need
//Modify the getXf($style) method in the source code of the PHPExcel_Writer_Excel5_Format class,
//Add one
in front of if ($this->_BIFF_version == 0x0500) { (near line 363) //Line of code:
//if($ifmt === '0') $ifmt = 1;
//
//Set the format to PHPExcel_Style_NumberFormat::FORMAT_NUMBER to avoid certain large numbers
//Displayed using scientific notation, with the following setAutoSize method, the content of each line can be made
//All are displayed according to the original content.
$objStyleA5
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

//Set font
$objFontA5 = $objStyleA5->getFont();
$objFontA5->setName('Courier New');
$objFontA5->setSize(10);
$objFontA5->setBold(true);
$objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objFontA5->getColor()->setARGB('FF999999');

//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('FFFF0000'); // 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 fill color
$objFillA5 = $objStyleA5->getFill();
$objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFillA5->getStartColor()->setARGB('FFEEEEEE');

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


//************************************
//Add picture
$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 cell
$objExcel->getSheet(1)->getProtection()->setSheet(true);
$objExcel->getSheet(1)->protectCells('A1:C22', 'PHPExcel');


//************************************
//Output content
//
$outputFileName = "output.xls";
//To file
////$objWriter->save($outputFileName);
//or
//Go to 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');

?>

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631566.htmlTechArticleThe following introduces several PHP implementation programs for generating EXCEL documents. Friends in need can refer to them. Original writing method Original method: send header, use the header of the attachment to send to the user's browser table...
Related labels：
excel php under several kinds Example accomplish document have generate of program
source：php.cn
Previous article：PHP Taobao IP data to obtain user IP and geographical location_PHP tutorial Next article：PHP A complete paging class (with source code)_PHP tutorial
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
PHP arrays obtained from URL parameters do not behave as expected I have a URL parameter that contains the category ID and I want to treat it as an array li...
From 2024-04-06 22:09:02
0
1
1428
Where should I place CustomLog directive in apache I'm using php:7.2-apachedocker. I need to disable health check url login access log. Based...
From 2024-04-06 22:03:59
0
1
990
What is the format of the variables in the return value? I am a new learner of php. I found a piece of code: if($x<time()){return[false,'error']...
From 2024-04-06 21:55:20
0
1
778
Problems encountered when using opentbs to generate odt files: values ​​of the same key are displayed in the same row instead of separate columns. I'm using a library called OpenTbs to create odt using PHP, I'm using it because columns a...
From 2024-04-06 20:18:18
0
1
483
Group MySQL results by ID for looping over I have a table with flight data in mysql. I'm writing a php code that will group and displ...
From 2024-04-06 17:27:56
0
1
406
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template