search
Home类库下载PHP类库PHPExcel related operations
PHPExcel related operationsOct 19, 2016 am 10:01 AM
phpexcel

The following are summarized several usage methods
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 properties:
Creator
$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');

保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
设置格式
// Set cell number formats
echo date('H:i:s') . " Set cell number formatsn";
$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
设置宽width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
设置font
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
设置align
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
设置column的border
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
设置border的color
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel-> getActiveSheet());
//Handling Chinese output issues
The string needs to be converted to UTF-8 encoding for normal output, otherwise Chinese characters will be output as blank, as follows:
$str = iconv('gb2312', ' utf-8', $str);
Or you can write a function specifically to handle Chinese strings:
function convertUTF8($str)
{
if(empty($str)) return '';
return iconv('gb2312 ', 'utf-8', $str);
}
//Output data processing method from the database
Read data from the database such as:
$db = new Mysql($dbconfig);
$sql = "SELECT * FROM Table name";
$row = $db->GetAll($sql); // $row is a two-dimensional array
$count = count($row);
for ($i = 2; $i $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));
$objPHPExcel->getActiveSheet() ->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row [$i-2][3]));
$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));
$objPHPExcel ->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));
$objPHPExcel->getActiveSheet()->setCellValue ('F' . $i, convertUTF8($row[$i-2][6]));
$objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i -2][7]));
$objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));
}

In default After sheet, create a worksheet
echo date('H:i:s') . " Create new Worksheet objectn";
$objPHPExcel->createSheet();
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5' );
$objWriter-save('php://output');


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
PHP开发:使用 PHPExcel 处理 Excel 文件PHP开发:使用 PHPExcel 处理 Excel 文件Jun 15, 2023 pm 03:45 PM

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

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

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

怎么用phpexcel将Excel文件转成CSV文件并打开怎么用phpexcel将Excel文件转成CSV文件并打开Mar 27, 2023 pm 04:16 PM

​PHPEXCEL是一个优秀的PHP读写Excel文件的类库,它提供了非常充分的API,能够让我们使用PHP来读写Excel文件。而有些时候,我们需要将Excel文件转换成CSV文件,在一些场合下使用。那么,本文主要讲述如何使用PHPEXCEL类库将Excel文件转换成CSV文件,并进行打开。

php如何使用PHPExcel处理Excel文件?php如何使用PHPExcel处理Excel文件?Jun 01, 2023 pm 02:01 PM

PHPExcel是一款开源的PHP库,用于处理MicrosoftExcel(.xls和.xlsx)文件。它可以读取、写入和操作Excel文件,提供了丰富的函数和方法。在PHP项目中使用PHPExcel库,可以快速方便地处理Excel文件,实现数据的导入、导出和数据处理等功能。本文将介绍如何使用PHPExcel处理Excel文件。一、安装PHPExcel要使

PHP开发技巧:如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库PHP开发技巧:如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库Jul 02, 2023 pm 02:28 PM

PHP开发技巧:如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库概述:在Web开发中,处理Excel文件是一个常见且重要的任务。PHPExcel是一个功能强大而且易于使用的PHP库,它可以帮助我们读取和写入Excel文件。本文将介绍如何使用PHPExcel和PHPExcel_IOFactory库来操作MySQL数据库。步骤1

使用PHP和PHPExcel创建Excel文件使用PHP和PHPExcel创建Excel文件May 11, 2023 am 08:40 AM

在如今信息快速传递的时代,数据的处理和存储变得越来越重要。而Excel表格的使用是很多人的首选,这是因为Excel表格可以整合各种数据并且可以轻松地进行分析和处理。为了更加高效地完成Excel表格的创建,我们可以使用PHP和PHPExcel这两个强大的工具。在本篇文章中,我们将介绍如何使用PHP和PHPExcel创建Excel文件。1.安装PHPExcel首

phpexcel为什么成为PHP开发者关注的对象phpexcel为什么成为PHP开发者关注的对象Mar 27, 2023 pm 06:15 PM

PHPExcel是一种处理微软 Excel 文件的开源 PHP 库,可以读取、创建、修改和保存 Excel 文件。它是一个强大且高度可定制的工具,可用于处理数据分析、报告生成、数据导入和导出等任务。在本文中,将介绍PHPExcel为什么成为PHP开发者关注的对象。

PHP开发技巧:如何使用PHPExcel操作MySQL数据库PHP开发技巧:如何使用PHPExcel操作MySQL数据库Jul 02, 2023 pm 12:21 PM

PHP开发技巧:如何使用PHPExcel操作MySQL数据库随着互联网的蓬勃发展,大量的数据被存储于数据库中,并且需要进行导入、导出、处理等操作。在PHP开发中,PHPExcel是一个强大的库,可以简化与Excel文件的交互,实现数据的导入与导出。本文将介绍如何使用PHPExcel来操作MySQL数据库,实现数据的导入和导出功能。PHPExcel的安装和配置

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version