[PHP] PHPExcel 讀取/寫入Excel
PHPExcel 官方團隊已經停止維護了，現在官方團隊開發維護的是它的升級版 PHPExcel 擴充套件包，叫做 PhpSpreadsheet，功能更加強大。☞ PhpSpreadsheet升級版 PHPExcel laravel 框架下使用 PhpSpreadsheet：
1)切換到你想安裝 PhpSpreadsheet 的 laravel 專案目錄下
cd /Library/WebServer/Documents/laravel專案2)執行指令
composer require phpoffice/phpspreadsheet3)安裝成功的PhpSpreadsheet，會存放在
verdor\phpoffice\phpspreadsheet4)教學文件
☞ laravel 框架下 新手如何使用安裝 PhpSpreadsheet
☞ phpspreadsheet初次使用指引
☞ Laravel 的 PhpSpreadsheet 包入門
01／使用 PhpSpreadsheet 匯出 excel
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class ExcelController extends Controller
{
public function index()
{
// 新增文件
$spreadsheet = new Spreadsheet();
$spreadsheet->createSheet();
$objActSheet= $spreadsheet->setActiveSheetIndex(0);
// 文件內容
$objActSheet->setCellValue('A1', '標題一')
->setCellValue('B1', '標題二');
// 輸出文件
header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment;filename="' . 'test' . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
// 刪除清空
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
}
官網
☞ https://phpexcel.codeplex.com/☞ phpexcel範例包
☞ HTTP Content Streaming
| 用途 | 語法 |
|---|---|
| 宣告 | $objPHPExcel = new PHPExcel(); |
| 建立新的工作表 | $objPHPExcel->createSheet() |
| 寫入資料(取陣列) | $objPHPExcel->getActiveSheet()->fromArray(陣列, null, 'A1'); |
| 輸出Excel 2007 | header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename=檔名'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit; [另法] 儲存 Excel 檔，存成 .xlsx 格式 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); |
| 其他設定 | |
| 設定預設顯示的工作表 | $objPHPExcel->setActiveSheetIndex(0) |
| 設定標題 | $objPHPExcel->getActiveSheet()->setTitle(); |
| 調整儲存格欄寬:指定高度 | $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); |
| 調整儲存格欄寬:指定寬度 | $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth(8); |
| 調整儲存格欄寬:自動高度 | $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(-1); |
| 文字對齊方式(水平) | $objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); |
| 文字對齊方式(垂直) | $objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); |
| 在單格裡面換行 | $objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()->setWrapText(true); |
| 用途 | 語法 |
|---|---|
| 設定要被讀取的檔案 | $objPHPExcel = PHPExcel_IOFactory::load(檔名); |
| 寫入陣列(取資料) | $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); |
01／建立 Excel2007 多個工作表
<?php
// [1]init
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
for ($j = 0; $j <= 2; $j++) {
// [2]create
if ($j > 0)
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($j);
// [3]format
$objPHPExcel->getActiveSheet()->setTitle();
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
for($col = 'A'; $col !== 'D'; $col++) {
// 指定寬度
$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth(15);
// 水平置中
$objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 垂直置中
$objPHPExcel->getActiveSheet()->getStyle($col)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
// [4]data
$arr_data = array();
$arr_data[0][0] = '欄位一';
$arr_data[0][1] = '欄位二';
$arr_data[0][2] = '欄位三';
$arr_data[1][0] = '資料一';
$arr_data[1][1] = '資料二';
$arr_data[1][2] = '資料三';
$arr_data[2][0] = '資料一';
$arr_data[2][1] = '資料二';
$arr_data[2][2] = '資料三';
$objPHPExcel->getActiveSheet()->fromArray($arr_data, null, 'A1');
}
// [5]open from the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// [6]output
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="download.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>
02／讀取 Excel2007 一個工作表( 方法一 )
<?php
header("Content-Type:text/html; charset=utf-8");
// 引入函式庫
require_once 'PHPExcel.php';
// 設定要被讀取的檔案，不使用中文檔名
$file = 'import.xlsx';
try {
$objPHPExcel = PHPExcel_IOFactory::load($file);
} catch(Exception $e) {
die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage());
}
$arr_data = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
// echo "<pre>";
// print_r($arr_data);
// echo "</pre>";
////////////////////////////////////////
// 開始列印
// 欄與列的index
$colindex = 0;
$rowindex = 0;
$rownull = true;
// 資料對應的欄位標題
$arr_title = array();
$arr_content = array();
foreach( $arr_data as $key => $col ){
// 讀取標題
if($rowindex == 0){
foreach ($col as $colkey => $colvalue){
array_push($arr_title, $colvalue);
}
}
// 讀取內容
if( $rowindex >= 1 ) {
foreach ( $col as $colkey => $colvalue ){
if( $colindex >= 0 ){
if( $colvalue != "" )
$rownull = false;
$arr_content[$rowindex][$colindex] = $colvalue;
}
$colindex++;
}
if($rownull && $rowindex > 0)
break;
$rownull = true;
$colindex = 0;
}
$rowindex++;
} //next $arr_data
echo "<h2>列印標題</h2>";
echo "<pre>";
print_r($arr_title);
echo "</pre>";
echo "<hr/>";
echo "<h2>列印內容</h2>";
echo "<pre>";
print_r($arr_content);
echo "</pre>";
?>
03／讀取 Excel2007 一個工作表( 方法二 )
<?php
header("Content-Type:text/html; charset=utf-8");
// 引入函式庫
require_once 'PHPExcel.php';
// 設定要被讀取的檔案，不使用中文檔名
$inputFileName = 'import.xlsx';
// Read your Excel workbook
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
// Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
// Loop through each row of the worksheet in turn
$rowData = array();
for ($row = 1; $row <= $highestRow; $row++){
// Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
NULL,
TRUE,
FALSE);
// Insert row data array into your database of choice here
if ($row == 1)
echo "<h2>列印標題</h2>";
if ($row == 2)
echo "<hr/><h2>列印內容</h2>";
echo "<pre>";
print_r($rowData);
echo "</pre>";
}
?>
LARAVEL EXCEL Source
☞ Laravel Excel實現Excel/CSV檔案匯入匯出的功能詳解（合併單元格，設定單元格樣式）☞ Laravel Excel
☞ Laravel 外掛 PhpSpreadSheet 使用總結
☞ 如何在LARAVEL實作EXCEL匯入匯出功能
☞ Laravel之Excel匯入、匯出
☞ PhpSpreadsheet 使用教學 101 – 基本的安裝與使用
☞ 【Laravel】使用phpoffice/phpspreadsheet匯出資料
Source
☞ PHPExcel API☞ 憑空生出Excel檔
☞ Yii的框架使用PHPExcel擴展從數據庫導出Excel中
☞ [PHP]phpExcel常用功能備忘
☞ phpexcel 寫入到excel文檔
☞ Setting autosize column phpExcel
☞ PHP 輸出 Excel ，使用PHPExcel
☞ PHPExcel
☞ PHPExcel
☞ Using PHPExcel to make automatic generated excel files
☞ Create a simple 2007 XLSX Excel file [PHP]
☞ PHP export to xlsx? (**我使用這個方法輸出)
☞ phpExcel example
☞ PHPExcel – 讀取Excel資料 ( 範例從 MEGA 下載 )
☞ how to use phpexcel to read data and insert into database?
☞ PHPExcel outputting zeros as blank cells
沒有留言: