Generate and read excel files with php-PHP Tutorial-php.cn

Generate and read excel files with php

高洛峰

Release： 2023-03-03 16:06:02

Original

1390 people have browsed 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 to generate and read Excel. document.

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 The class library is based on Microsoft's OpenXML and PHP, and provides powerful support for Excel, such as setting workbooks, font styles, pictures, borders, etc. Let's take a look at how it reads and writes 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 and save it 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 don’t 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);

code 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

Next, let’s look at an example of reading the contents of an Excel file:

The function excelToArray in the following code 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

Simplified 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

I hope this article will be helpful to you. This is where I will introduce to you how to generate and read the contents of excel files in PHP. I hope everyone will continue to pay attention to our website! If you want to learn PHP, you can continue to pay attention to this site.