PHP generates EXCEL document example program_PHP tutorial-PHP Tutorial-php.cn

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

代码如下

复制代码

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');