How to export excel using PHPExcel

墨辰丷
Release: 2023-03-25 20:36:02
Original
2644 people have browsed it

This article mainly introduces how to export excel with PHPExcel. Interested friends can refer to it. I hope it will be helpful to everyone.

//Set the includepath of the PHPExcel class library

set_include_path('.'.PATH_SEPARATOR.'D:\workspace\biznaligy_eh\dev_src\includes\PHPExcel'.PATH_SEPARATOR.get_include_path());
Copy after login
require_once'PHPExcel.php';
require_once'PHPExcel/Writer/Excel5.php';//用于其他低版本xls
require_once'PHPExcel/Writer/Excel2007.php';//用于excel-2007格式
Copy after login

//Create a processing object instance<br/>

$objExcel=newPHPExcel();
Copy after login

<br/>//Create a file format writing object instance ,uncomment<br/>

$objWriter=newPHPExcel_Writer_Excel5($objExcel);//用于其他版本格式
//or
//$objWriter=newPHPExcel_Writer_Excel2007($objExcel);//用于2007格式
//$objWriter->setOffice2003Compatibility(true);
Copy after login

//Set the basic properties of the document<br/>

$objProps=$objExcel->getProperties();
$objProps->setCreator("ZealLi");
$objProps->setLastModifiedBy("ZealLi");
$objProps->setTitle("OfficeXLSTestDocument");
$objProps->setSubject("OfficeXLSTestDocument,Demo");
$objProps->setDescription("Testdocument,generatedbyPHPExcel.");
$objProps->setKeywords("officeexcelPHPExcel");
$objProps->setCategory("Test");
Copy after login

//Set the current sheet index for subsequent content operations. <br/>//Generally, explicit calls are only needed when using multiple sheets. <br/>//By default, PHPExcel will automatically create the first sheet and set SheetIndex=0<br/>

$objExcel->setActiveSheetIndex(0);
$objActSheet=$objExcel->getActiveSheet();
Copy after login

//Set the name of the current active sheet<br/>

$objActSheet->setTitle(&#39;测试Sheet&#39;);
Copy after login

<br/>//Set the cell content and PHPExcel automatically determines the cell content type based on the incoming content<br/>

$objActSheet->setCellValue(&#39;A1&#39;,&#39;字符串内容&#39;);//字符串内容
$objActSheet->setCellValue(&#39;A2&#39;,26);//数值
$objActSheet->setCellValue(&#39;A3&#39;,true);//布尔值
$objActSheet->setCellValue(&#39;A4&#39;,&#39;=SUM(A2:A2)&#39;);//公式
Copy after login

<br/>//Explicitly specify the content type<br/>

$objActSheet->setCellValueExplicit(&#39;A5&#39;,&#39;8757584&#39;,PHPExcel_Cell_DataType::TYPE_STRING);
Copy after login

//Merge cells<br/>

$objActSheet->mergeCells(&#39;B1:C22&#39;);
Copy after login

<br/>//Separate cells<br/>

$objActSheet->unmergeCells(&#39;B1:C22&#39;);
Copy after login

<br/>//Set width<br/>

$objActSheet->getColumnDimension(&#39;B&#39;)->setAutoSize(true); 
$objActSheet->getColumnDimension(&#39;A&#39;)->setWidth(30);
Copy after login

<br/>//Set the number format of the cell content. <br/>//If PHPExcel_Writer_Excel5 is used to generate content, <br/>//It should be noted here that in the const variable definition of the PHPExcel_Style_NumberFormat class <br/>//In various custom formatting methods, other types can be normal Used, but when setFormatCode<br/>// is FORMAT_NUMBER, the actual effect is that the format is not set to "0". Need<br/>//Modify the getXf($style) method in the source code of the PHPExcel_Writer_Excel5_Format class, <br/>//Add a ## in front of if($this->_BIFF_version==0x0500){(near line 363) #//Line of code: <br/>//if($ifmt==='0')$ifmt=1;<br/><br/>//Set the format to PHPExcel_Style_NumberFormat::FORMAT_NUMBER to avoid certain large numbers<br/>//It is displayed using scientific notation. With the setAutoSize method below, the content of each line can be displayed <br/>//all according to the original content. <br/><br/>

<br/>
Copy after login

//Set font<br/><br/>

$objFontA5=$objStyleA5->getFont();
$objFontA5->setName(&#39;CourierNew&#39;);
$objFontA5->setSize(10);
$objFontA5->setBold(true);
$objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objFontA5->getColor()->setARGB(&#39;FFFF0000&#39;);
$objFontA5->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//$objFontA5->getFont()->setColor(PHPExcel_Style_Color::COLOR_RED);
Copy after login

//Set alignment

<br/>

$objAlignA5=$objStyleA5->getAlignment();
$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objAlignA5->setWrapText(true);//自动换行,前提是单元格内的值超列宽,或者在值内写入个\n
Copy after login

//Set border

<br/>

$objBorderA5=$objStyleA5->getBorders();
$objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorderA5->getTop()->getColor()->setARGB(&#39;FFFF0000&#39;);//边框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);
Copy after login

//Set the CELL fill color

<br/>

$objFillA5=$objStyleA5->getFill();
$objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFillA5->getStartColor()->setARGB(&#39;FFEEEEEE&#39;);
Copy after login

//Copy the style information from the specified cell.<br/><br/>

$objActSheet->duplicateStyle($objStyleA5,&#39;B1:C22&#39;);
Copy after login

/ /Add picture<br/><br/>

$objDrawing = newPHPExcel_Worksheet_Drawing();
$objDrawing->setName(&#39;ZealImg&#39;);
$objDrawing->setDescription(&#39;ImageinsertedbyZeal&#39;);
$objDrawing->setPath(&#39;./zeali.net.logo.gif&#39;);
$objDrawing->setHeight(36);
$objDrawing->setCoordinates(&#39;C23&#39;);
$objDrawing->setOffsetX(10);
$objDrawing->setRotation(15);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($objActSheet);
Copy after login

//Add a new worksheet<br/><br/>

$objExcel->createSheet();
$objExcel->getSheet(1)->setTitle(&#39;测试2&#39;);
Copy after login

//Save and set password<br/><br/>

$objPHPExcel->getActiveSheet()->getProtection()->setPassword(&#39;PHPExcel&#39;);
Copy after login

//Protect cells

<br/>

$objExcel->getSheet(1)->getProtection()->setSheet(true);
$objExcel->getSheet(1)->protectCells(&#39;A1:C22&#39;,&#39;PHPExcel&#39;);
Copy after login

//Show grid lines:<br/><br/>

$objPHPExcel->getActiveSheet()->setShowGridlines(true);
Copy after login

//Show hidden columns

<br/>

$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setVisible(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setVisible(false);
Copy after login

//Show hidden rows

<br/>

$objPHPExcel->getActiveSheet()->getRowDimension(&#39;10&#39;)->setVisible(false);
Copy after login

//Default column width

<br/>

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
Copy after login

//Default row width

<br/>

$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
Copy after login

//worksheet Default style settings (different from the default need to be set separately)

<br/>

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName(&#39;Arial&#39;);
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8);
$alignment = $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment();
$alignment->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$alignment->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
Copy after login

//Output content

<br/>

$outputFileName="output.xls";
Copy after login

//Go to the file through the file path and then use Ajax to refresh the page

<br/>

////$objWriter->save($outputFileName);
Copy after login

//Go to the browser

<br/>

header("Content-Type:application/force-download");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header(&#39;Content-Disposition:inline;filename="&#39;.$outputFileName.&#39;"&#39;);
header("Content-Transfer-Encoding:binary");
header("Last-Modified:".gmdate("D,dMYH:i:s")."GMT");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Pragma:no-cache");
$objWriter->save(&#39;php://output&#39;);
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;PDF&#39;);
$objWriter->save(&#39;a.pdf&#39;)
 
public function getCellByColumnAndRow($pColumn = 0, $pRow = 0) {
    return $this->getCell(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
}
print_r(PHPExcel_Cell::columnIndexFromString(&#39;D&#39;));exit; //echo 4
echo PHPExcel_Cell::stringFromColumnIndex(4) //  echo E
for($c=PHPExcel_Cell::columnIndexFromString(&#39;A&#39;);$c<PHPExcel_Cell::columnIndexFromString(&#39;J&#39;);$c++){
    echo PHPExcel_Cell::stringFromColumnIndex($c);
}
Copy after login

//phpexcel is not frozen by default, and the following are frozen columns. If you set two, you will determine all

<br/>

$sheet->freezePane(&#39;A1&#39;);
$sheet->freezePane(&#39;B1&#39;);
Copy after login

Freeze rows<br/><br/>

$sheet->freezePane(&#39;D1&#39;);
$sheet->freezePane(&#39;D2&#39;);
Copy after login

Cannot define A, b, c again, otherwise the column freezing will be replaced

<br/>

public function freezePaneByColumnAndRow($pColumn = 0, $pRow = 0){
    $this->freezePane(PHPExcel_Cell::stringFromColumnIndex($pColumn) . $pRow);
}
public function unfreezePane() {
   $this->freezePane(&#39;&#39;);
 }
$worksheet->setInputEncoding("UTF-8");
//$freeze = $sheet->getFreezePane();
Copy after login

<br/>

Use phpexcel to batch format cells

<br/>

<br/>##Java code

$style_obj = new PHPExcel_Style();  
$style_array = array(  
    &#39;borders&#39; => array(  
        &#39;top&#39; => array(&#39;style&#39; => PHPExcel_Style_Border::BORDER_THIN),  
        &#39;left&#39; => array(&#39;style&#39; => PHPExcel_Style_Border::BORDER_THIN),  
        &#39;bottom&#39; => array(&#39;style&#39; => PHPExcel_Style_Border::BORDER_THIN),  
        &#39;right&#39; => array(&#39;style&#39; => PHPExcel_Style_Border::BORDER_THIN)  
    ),  
    &#39;alignment&#39; => array(  
        &#39;horizontal&#39; => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,  
        &#39;vertical&#39;   => PHPExcel_Style_Alignment::VERTICAL_CENTER,  
        &#39;wrap&#39;       => true  
    )  
);  
$style_obj->applyFromArray($style_array);  
$sheet->setSharedStyle($style_obj, "A1:O35");
Copy after login
How to export excel using PHPExcelphpexcel big data export, data append (190,000 rows, divided into 20 sheets, 10,000 rows is a sheet, the first time to 1 sheet data, other data is appended to excel in 19 times to cover the corresponding sheet )

先用 PhpExcel 建立reader,再load文件,这样打开已经存在的文档,然后再建立writer,将reader中的数据都复制过来,再用 PhpExcel 进行数据修改,再以load的文件名覆盖保存。

<br/>

Java代码 How to export excel using PHPExcel

<?php  
require_once &#39;PHPExcel/IOFactory.php&#39;;  
$reader = PHPExcel_IOFactory::createReader(&#39;Excel5&#39;); //读取旧版 excel 档案  
$PHPExcel = $reader->load("y.xls"); // 档案名称  
$sheet = $PHPExcel->getSheet(0); // 读取第一个工作表(编号从 0 开始)  
$highestRow = $sheet->getHighestRow(); // 取得总列数  
for ($row = 1; $row <= $highestRow; $row++) {  
    for ($column = 1; $column <= 6; $column++) {  
        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();  
    }  
}  
?>
Copy after login

 相关推荐:

Yii2框架中PHPExcel导出Excel文件方法

Yii2框架中实现PHPExcel导出Excel文件的方法分享

phpExcel导出excel 

The above is the detailed content of How to export excel using PHPExcel. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template