How to generate and read excel files in php-PHP Tutorial-php.cn

How to generate and read excel files in php

墨辰丷

Release： 2023-03-28 19:56:01

Original

1807 people have browsed it

The generation and reading of reports are often used in the management background of the website. CSV and Excel are both commonly used report formats. This article introduces the generation and reading of excel files in PHP. Friends who need it can come and learn about it.

Tables are often generated on websites. CSV and Excel are both commonly used report formats. CSV is relatively simple. If you have any questions, I will publish some CSV examples one after another. Here I mainly introduce how to use PHP. Generate and read Excel files.

To execute the following function, you must first introduce a class library: PHPExcel. PHPExcel is a powerful PHP class library used to read and write different file formats, such as Excel 2007, PDF format, HTML format, etc. This class library is based on Microsoft's OpenXML and PHP. It provides powerful support for Excel, such as setting workbooks, font styles, pictures, borders, etc. Let's take a look at how to read it. Writing Excel files:

First let’s look at how to generate an Excel file:

The function of the function arrayToExcel in the code below is to generate an Excel file from a two-dimensional array of data. file and saved on the server.

require_once &#39;Classes/PHPExcel/Reader/Excel2007.php&#39;;
require_once &#39;Classes/PHPExcel/Reader/Excel5.php&#39;;
include &#39;Classes/PHPExcel/IOFactory.php&#39;;
function arrayToExcel($data){
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle(&#39;firstsheet&#39;);
$objPHPExcel->getDefaultStyle()->getFont()->setName(&#39;Arial&#39;);
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
//add data
$i = 2;
foreach ($data as $line){
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A&#39;.$i, $line[&#39;From&#39;]);
$objPHPExcel->getActiveSheet()->getCell(&#39;A&#39;.$i)->setDataType(&#39;n&#39;);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;B&#39;.$i, $line[&#39;To&#39;]);
$objPHPExcel->getActiveSheet()->getCell(&#39;B&#39;.$i)->setDataType(&#39;n&#39;);
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
$file = &#39;excel.xls&#39;;
$objWriter->save($file);
}

Copy after login

If you do not want to save it on the server and want to download it directly to the client after generation, you can add the following code when outputting the file , instead of using $objWriter->save($file);

The code is as follows:

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(&#39;Content-Disposition:attachment;filename="excel.xls"&#39;);
header("Content-Transfer-Encoding:binary");
$objWriter->save(&#39;php://output&#39;);

Copy after login

Connect Let’s look at an example of reading the contents of an Excel file:

The function excelToArray in the code below is to rearrange the contents of an excel into an array.

The code is as follows:

require_once &#39;Classes/PHPExcel.php&#39;;
require_once &#39;Classes/PHPExcel/IOFactory.php&#39;;
function excelToArray($file){
$objReader = PHPExcel_IOFactory::createReader(&#39;Excel5&#39;);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$excelData = array();
for ($row = 2; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex; ++$col) { $excelData[$row][] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $excelData;
}

Copy after login

##Streamlining method

The code is as follows:

<?php
/**
 *
 * @copyright 2007-2012 Xiaoqiang.
 * @author Xiaoqiang.Wu <jamblues@gmail.com>
 * @version 1.01
 */
error_reporting(E_ALL);
date_default_timezone_set(&#39;Asia/ShangHai&#39;);
 
/** PHPExcel_IOFactory */
require_once &#39;../Classes/PHPExcel/IOFactory.php&#39;; 
// Check prerequisites
if (!file_exists("31excel5.xls")) {
  exit("not found 31excel5.xls.n");
}
$reader = PHPExcel_IOFactory::createReader(&#39;Excel5&#39;); //设置以Excel5格式(Excel97-2003工作簿)
$PHPExcel = $reader->load("31excel5.xls"); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一??工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
 
/** 循环读取每个单元格的数据 */
for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始
  for ($column = &#39;A&#39;; $column <= $highestColumm; $column++) {//列数是以A列开始
    $dataset[] = $sheet->getCell($column.$row)->getValue();
    echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />";
  }
} 
?>

Copy after login

Summary: The above is the entire content of this article, I hope it can be helpful to everyone’s learning helped.

Related recommendations:

php Implement Alipay’s face-to-face payment (scan QR code payment) function

PHPStrom's new FTP project and detailed online operation graphic explanation

phpRealize WeChat native payment (scan QR code payment) function

The above is the detailed content of How to generate and read excel files in php. For more information, please follow other related articles on the PHP Chinese website!