Usage and introduction of PHPEXCEL_PHP tutorial-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

Usage and introduction of PHPEXCEL_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 13, 2016 pm 05:06 PM

excelphpphpexcelandCanplug-inyesgenerateusageofIntroduction

PHPEXCEL is a php plug-in used to generate excel. It can easily operate on excel data, such as: generating excel, modifying excel data, etc.

1. Introduction to PHPEXCEL

PHPEXCEL provides a series of APIs that can parse and generate documents such as excel and pdf.

Although PHPEXCEL is powerful, it is relatively cumbersome to use. If you need to output a more complex format, it is a good choice. You can download the source code from the official website.

2. Some functions of PHPEXCEL

Set the current workbook and return the workbook object:
$excelSheet = $excel->setActiveSheetIndex(0);

Merge cells and return the cell object. The following example merges the cells in the first and second rows of column A:

The code is as follows

Copy code

代码如下

复制代码

$excelSheet->mergeCells('A1:A2');

设置单元格的值，参数：单元格名称，值：
$excelSheet->setCellValue('A1', '字符串内容');
$excelSheet->setCellValue('A2', 26); //数值
$excelSheet->setCellValue('A3', true); //布尔值
$excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //公式

$excelSheet->mergeCells('A1:A2');

Set the value of the cell, parameter: cell name, value:

$excelSheet->setCellValue('A1', 'String content');

$excelSheet->setCellValue('A2', 26); //Value

代码如下

复制代码

include ‘PHPExcel.php’;

　　include ‘PHPExcel/Writer/Excel2007.php’;

　　//或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的

　　创建一个excel

　　$objPHPExcel = new PHPExcel();

　　保存excel—2007格式

　　$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

　　//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式

　　$objWriter->save(”xxx.xlsx”);

　　直接输出到浏览器

　　$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’);

$excelSheet->setCellValue('A3', true); //Boolean value $excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //Formula

Introduction to phpexcel usage

The code is as follows

Copy code

include ‘PHPExcel.php’; include ‘PHPExcel/Writer/Excel2007.php’; //Or include ‘PHPExcel/Writer/Excel5.php’; for outputting .xls Create an excel $objPHPExcel = new PHPExcel(); Save 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

Copy code

代码如下

复制代码

创建人

　　$objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);

　　最后修改人

　　$objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”);

　　标题

　　$objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”);

　　题目

　　$objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”);

　　描述

　　$objPHPExcel->getProperties()->setDescription(”Test document for Office 2007 XLSX, generated using PHP classes.”);

　　关键字

　　$objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”);

　　种类

　　$objPHPExcel->getProperties()->setCategory(”Test result file”);

　　——————————————————————————————————————–

　　设置当前的sheet

　　$objPHPExcel->setActiveSheetIndex(0);

　　设置sheet的name

　　$objPHPExcel->getActiveSheet()->setTitle(’Simple’);

　　设置单元格的值

　　$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)’);

　　合并单元格

　　$objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);

　　分离单元格

　　$objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

Created by $objPHPExcel->getProperties()->setCreator("Maarten Balliauw"); Last modified by $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw"); Title $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document"); Title $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”); Category $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 value of the cell $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′);

3. Example application of PHPEXCEL

The entire code is as follows (it is worth noting that the table header uses $orderCellData to record the order of each merchant number, in order to retrieve the corresponding data in the table body):

The code is as follows

Copy code

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 handler object instance (this object is the same for 2003 2007)

$objExcel = new PHPExcel();

//Set attributes (this code is irrelevant, 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 processing data (index starts from 0)

$objExcel->setActiveSheetIndex(0);

$conn = mssql_connect($config['mssql']['host'],$config['mssql']['user'],$config['mssql']['password']);

mssql_select_db($config['mssql']['dbname'],$conn);

$tm=$_REQUEST['tm'];

$sql = "exec HNow05_getTTSpace '','".$tm."','',1";

$sql=mb_convert_encoding($sql,'GBK','UTF-8');

$res=mssql_query($sql);

$i=0;

$k = array('station code', 'station name', 'river system', 'come report time', 'water level', 'water potential');

$count = count($k);

$arrs = array('A','B','C','D','E','F');

//Add header

for($i=0;$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='flat';

}else if($tdptn=='5'){

$tdptn='rising';

}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 cell border and color-------------*/

$rows = mssql_num_rows($res);

for($i=0;$i

for($j=0;$j

$a = $i+1;

> 🎜>

$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->getColor()->setARGB(' FF00BBcc');

//Centered horizontally

}

// Width of high 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()->getColumnDimension('E')->setWidth(10);

$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);

// Set header and footer. If there are no different headers odd/even using a single header is assumed.

$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash register&RPrinted on &D');

> 🎜>

// Set page orientation and size

$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Rename table

$objExcel->getActiveSheet()->setTitle('Real-time tide conditions');

// 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) and save it in excel2003 format

//Set Excel name

$excelName = 'Real-time tide conditions ('.$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;

Statement

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

如何在技嘉主板上设置键盘启动功能 (技嘉主板启用键盘开机方式)Dec 31, 2023 pm 05:15 PM

技嘉的主板怎么设置键盘开机首先，要支持键盘开机，一定是PS2键盘！！设置步骤如下：第一步：开机按Del或者F2进入bios，到bios的Advanced(高级)模式普通主板默认进入主板的EZ（简易）模式，需要按F7切换到高级模式，ROG系列主板默认进入bios的高级模式（我们用简体中文来示范）第二步：选择到——【高级】——【高级电源管理（APM）】第三步：找到选项【由PS2键盘唤醒】第四步：这个选项默认是Disabled（关闭）的，下拉之后可以看到三种不同的设置选择，分别是按【空格键】开机、按组

CS玩家的首选：推荐的电脑配置Jan 02, 2024 pm 04:26 PM

1.处理器在选择电脑配置时，处理器是至关重要的组件之一。对于玩CS这样的游戏来说，处理器的性能直接影响游戏的流畅度和反应速度。推荐选择IntelCorei5或i7系列的处理器，因为它们具有强大的多核处理能力和高频率，可以轻松应对CS的高要求。2.显卡显卡是游戏性能的重要因素之一。对于射击游戏如CS而言，显卡的性能直接影响游戏画面的清晰度和流畅度。建议选择NVIDIAGeForceGTX系列或AMDRadeonRX系列的显卡，它们具备出色的图形处理能力和高帧率输出，能够提供更好的游戏体验3.内存电

主板上的数字音频输出接口-SPDIF OUTJan 14, 2024 pm 04:42 PM

主板上SPDIFOUT连接线序最近我遇到了一个问题，就是关于电线的接线顺序。我上网查了一下，有些资料说1、2、4对应的是out、+5V、接地；而另一些资料则说1、2、4对应的是out、接地、+5V。最好的办法是查看你的主板说明书，如果找不到说明书，你可以使用万用表进行测量。首先找到接地，然后就可以确定其他的接线顺序了。主板vdg怎么接线连接主板的VDG接线时，您需要将VGA连接线的一端插入显示器的VGA接口，另一端插入电脑的显卡VGA接口。请注意，不要将其插入主板的VGA接口。完成连接后，您可以

广联达软件是一家专注于建筑信息化领域的软件公司，其产品被广泛应用于建筑设计、施工、运营等各个环节。由于广联达软件功能复杂、数据量大，对电脑的配置要求较高。本文将从多个方面详细阐述广联达软件的电脑配置推荐，以帮助读者选择适合的电脑配置处理器广联达软件在进行建筑设计、模拟等操作时，需要进行大量的数据计算和处理，因此对处理器的要求较高。推荐选择多核心、高主频的处理器，如英特尔i7系列或AMDRyzen系列。这些处理器具有较强的计算能力和多线程处理能力，能够更好地满足广联达软件的需求。内存内存是影响计算

Python函数介绍：isinstance函数的用法和示例Nov 04, 2023 pm 03:15 PM

Python函数介绍：isinstance函数的用法和示例Python是一门功能强大的编程语言，提供了许多内置函数，使得编程变得更加方便和高效。其中一个非常有用的内置函数是isinstance()函数。本文将介绍isinstance函数的用法和示例，并提供具体的代码示例。isinstance()函数用于判断一个对象是否是指定的类或类型的实例。该函数的语法如下

PHP开发：使用 PHPExcel 处理 Excel 文件Jun 15, 2023 pm 03:45 PM

随着数字化时代的到来，数据已经成为了我们日常生活和工作中最重要的一部分，而Excel文件则成为数据处理的重要工具之一。相信很多PHP开发者也会在工作中经常遇到使用Excel文件进行数据处理和操作的情况。本文将为大家介绍使用PHPExcel库来处理Excel文件的方法和注意事项。什么是PHPExcel？PHPExcel是一个PHP类

Python函数介绍：abs函数的用法和示例Nov 03, 2023 pm 12:05 PM

Python函数介绍：abs函数的用法和示例一、abs函数的用法介绍在Python中，abs函数是一个内置函数，用于计算给定数值的绝对值。它可以接受一个数字参数，并返回该数字的绝对值。abs函数的基本语法如下：abs(x)其中，x是要计算绝对值的数值参数，可以是整数或浮点数。二、abs函数的示例下面我们将通过一些具体的示例来展示abs函数的用法：示例1：计算

完全指南：如何使用php扩展PHPExcel处理Excel文件Jul 28, 2023 pm 10:01 PM

完全指南：如何使用PHP扩展PHPExcel处理Excel文件引言:在处理大量数据和统计分析时，Excel文件经常被用作数据存储和交换的一种常见格式。使用PHP扩展PHPExcel，我们可以轻松地读取、写入和修改Excel文件，从而有效地处理Excel数据。本文将介绍如何使用PHP扩展PHPExcel来处理Excel文件，并提供代码示例。一、安装PHPExc

See all articles