我即将使用 Codeigniter 3.1.11 将 PhpExcel 迁移到 PhpSpreadsheet 以前我使用一个名为Excel的PHP类文件.php它有助于使用PhpExcel生成Excel，但现在我想使用PhpSpreadsheet连接它。
代码如下
class Excel extends PHPExcel{
public $alphabet;
/**
* Constructor
*
* Responsible for initializing library class
*
* @access public
* @return void
*/
public function __construct() {
parent::__construct();
$this->alphabet = range('A', 'Z');
$this->alphabet[26] = 'AA';
$this->alphabet[27] = 'AB';
$this->alphabet[28] = 'AC';
$this->alphabet[29] = 'AD';
$this->alphabet[30] = 'AE';
$this->alphabet[31] = 'AF';
$this->alphabet[32] = 'AG';
$this->alphabet[33] = 'AH';
$this->alphabet[34] = 'AI';
$this->alphabet[35] = 'AJ';
$this->alphabet[36] = 'AK';
$this->alphabet[37] = 'AL';
$this->alphabet[38] = 'AM';
}
/**
* Responsible for generating excel
*
* Column_format array is use as a column name having
* number => '#,##0.00'
* number => '0.00'
* number => '0'
*
* @access public
* @param array, array, array, array, Boolean
* @return void
*/
private function generate_excel_sheet($header_data, $content_data, $column_width = NULL, $column_format = NULL, $border = FALSE){
//make 2st row become bold
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getFont()->setBold(true);
//Wraping text
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->setWrapText(true);
//Setting column width
if(!empty($column_width)){
foreach($column_width as $column => $size){
$this->getActiveSheet()->getColumnDimension($column)->setWidth($size);
}
}
//Merger table header and content
$excel_data[0] = $header_data;
if(!empty($content_data))
$excel_data = array_merge($excel_data,$content_data);
$this->getActiveSheet()->fromArray($excel_data, null, 'A2'); //Adding table data from A2 cell
//Setting column formate as Number
if(!empty($column_format)){
$last_row = $this->getActiveSheet()->getHighestRow();
foreach($column_format as $column => $format){
if(isset($format[0]) && $format[0] == 'Number')
$this->getActiveSheet()->getStyle($column)->getNumberFormat()->setFormatCode($format[1]);
if(isset($format[0]) && $format[0] == 'Date')
$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode($format[1]);
}
}
//Creating Border if $border = TRUE
if($border){
$styleArray = array( 'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].(count($excel_data)+1))->applyFromArray($styleArray);
}
}//end function generate_excel(...)
/**
* This is the main class to create excel we pass the Excel name, Table data, Sheet Name and Sheet Heading
* Where Table data is a multidimensional associative array detials are as follow
* Sheet
* $table_data[0][table_heading]= array();
* $table_data[0][contant_data] = array();
* $table_data[0][column_width] = array() (by default NULL);
* $table_data[0][column_format]= array() (by default NULL);
* $table_data[0][table_border] = Boolean (by default FALSE);
*
* $sheet_name[0] = 'sheet1';
* $sheet_heading[0] = 'This is a testing heading'
*
* @access public
* @param string, array, array, array
* @return void
*/
public function create_excel($excel_name = 'new_excel', $table_data = NULL, $sheet_name = NULL, $sheet_heading = NULL){
foreach($table_data as $key => $row){
//Create a new worksheet, after the default sheet
if($key > 0) $this->createSheet();
//Set Active Sheet
$this->setActiveSheetIndex($key);
//name the worksheet
$sheet = $this->getActiveSheet();
$sheet->setTitle($sheet_name[$key]);
//Getting last column name
$last_column = $this->alphabet[count($table_data[$key]['table_heading'])-1];
//heading worksheet
$sheet->setCellValue('A1', $sheet_heading[$key]);
$sheet->mergeCells('A1:'.$last_column.'1'); //Need to make it dynamic
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setSize(20);
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setBold(true);
$sheet->getStyle('A1:'.$last_column.'1')->getAlignment()->applyFromArray(array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$header_data = NULL;
$content_data = NULL;
$column_width = NULL;
$column_format = NULL;
$border = FALSE;
if(!empty($table_data[$key]['table_heading']))
$header_data = $table_data[$key]['table_heading'];
if(!empty($table_data[$key]['content_data']))
$content_data = $table_data[$key]['content_data'];
if(!empty($table_data[$key]['column_width']))
$column_width = $table_data[$key]['column_width'];
if(!empty($table_data[$key]['column_format']))
$column_format = $table_data[$key]['column_format'];
$table_border = $table_data[$key]['table_border'];
$this->generate_excel_sheet($header_data, $content_data, $column_width, $column_format, $table_border);
}//end foreach($table_data as $key => $value)
//Print excel here;
$this->print_excel($excel_name);
}//end function create_excel(...)
/**
*
*/
public function print_excel($excel_name){
//Creating header
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='.$excel_name.'.xlsx');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this, 'Excel2007');
$objWriter->save('php://output');
}
}// END Excel CLASS
/* End of file Excel.php */
/* Location: ./application/libraries/Excel.php */
我使用以下函数调用了上面的代码
/**
* Download Excel file
*
* @access private
* @param array
* @return void
*/
private function download_excel($excel_data){
$excel_name = $this->controller_name.'_'.date('Y-m-d');
$sheet_name = array('Report Salary');
$sheet_heading = array('Report Salary');
$header_data = array( 'S. No.', 'Title', 'First Name', 'Last Name', 'salary');
$content_data = array();
//Setting content data
$i =0;
foreach($excel_data as $row){
$i++;
$row_content = array();
$row_content[] = $i;
$row_content[] = $row->title;
$row_content[] = html_entity_decode($row->first_name, ENT_QUOTES, "UTF-8");
$row_content[] = html_entity_decode($row->last_name, ENT_QUOTES, "UTF-8");
$row_content[] = html_entity_decode($row->salary, ENT_QUOTES, "UTF-8");
$content_data[] = $row_content;
}
//Set number format
//$column_format = NULL;
$column_format = array('E' => array('Number','#,##0'));
//Set excel column width
$column_width = array( 'A' => 10, 'B' => 6, 'C' => 30, 'D' => 30, 'E' => 8);
$table_data[0]['table_heading']= $header_data;
$table_data[0]['content_data'] = $content_data;
$table_data[0]['column_width'] = $column_width;
$table_data[0]['column_format']= $column_format;
$table_data[0]['table_border'] = TRUE;
//Generate excel
$this->excel->create_excel($excel_name, $table_data, $sheet_name, $sheet_heading);
}//end function download()
有人可以帮助转换它吗？
谢谢
最后，我自己解决。我希望它也能帮助其他人。 只是为了在这里更新Excel.php文件是由我编写的，这有助于使使用PhpSpreadsheet库更容易。我曾经将 Excel.php 文件放在 Codeigniter 表中的库目录中结构化。
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
* An open source application development framework for PHP 7.3 or newer
*
* @author Sameer Naqvi
* @since Version 2.0
* @filesource
*/
// ------------------------------------------------------------------------
/**
* Excel Class
*
* This library holds the functions which helps to generate the Excel in (xlsx formate)
*
* @package Libraries
* @subpackage -
* @category Library
* @author Sameer Naqvi
*/
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
use PhpOfficePhpSpreadsheetStyleBorder;
use PhpOfficePhpSpreadsheetStyleAlignment;
//use PhpOfficePhpSpreadsheetStyleNumberFormat;
class Excel extends Spreadsheet{
public $alphabet;
/**
* Constructor
*
* Responsible for initializing library class
*
* @access public
* @return void
*/
public function __construct() {
parent::__construct();
$this->alphabet = range('A', 'Z');
$this->alphabet[26] = 'AA';
$this->alphabet[27] = 'AB';
$this->alphabet[28] = 'AC';
$this->alphabet[29] = 'AD';
$this->alphabet[30] = 'AE';
$this->alphabet[31] = 'AF';
$this->alphabet[32] = 'AG';
$this->alphabet[33] = 'AH';
$this->alphabet[34] = 'AI';
$this->alphabet[35] = 'AJ';
$this->alphabet[36] = 'AK';
$this->alphabet[37] = 'AL';
$this->alphabet[38] = 'AM';
}
/**
* Responsible for generating excel sheet
*
* Column_format array is use as a column name having
* Number => '#,##0.00'
* Number => '0.00'
* Number => '0'
* Date => 'yyyy-mm-dd'
*
* @access public
* @param array, array, array, array, Boolean
* @return void
*/
private function generate_excel_sheet($header_data, $content_data, $column_width = NULL, $column_format = NULL, $border = FALSE){
//make 2st row become bold
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getFont()->setBold(true);
//Wraping text
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->setWrapText(true);
//Alignment = Vertical_center
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].'2')->getAlignment()->applyFromArray(array('vertical' => Alignment::VERTICAL_CENTER));
//Setting column width
if(!empty($column_width)){
foreach($column_width as $column => $size){
$this->getActiveSheet()->getColumnDimension($column)->setWidth($size);
}
}
//Merger table header and content
$excel_data[0] = $header_data;
if(!empty($content_data))
$excel_data = array_merge($excel_data,$content_data);
$this->getActiveSheet()->fromArray($excel_data, null, 'A2'); //Adding table data from A2 cell
//Setting column formate as Number or Date
if(!empty($column_format)){
$last_row = $this->getActiveSheet()->getHighestRow();
foreach($column_format as $column => $format){
if(isset($format[0]) && $format[0] == 'Number')
$this->getActiveSheet()->getStyle($column)->getNumberFormat()->setFormatCode($format[1]);
if(isset($format[0]) && $format[0] == 'Date')
$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode($format[1]);
//$this->getActiveSheet()->getStyle($column.'3:'.$column.$last_row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);
}
}
//Creating Border if $border = TRUE
if($border){
$styleArray = array(
'borders' => array(
'allBorders' => array(
'borderStyle' => Border::BORDER_THIN,
'color' => array('argb' => '000000'),
),
),
);
$this->getActiveSheet()->getStyle('A2:'.$this->alphabet[count($header_data)-1].(count($excel_data)+1))->applyFromArray($styleArray);
}
}//end function generate_excel(...)
/**
* This is the main class to create excel we pass the Excel name, Table data, Sheet Name and Sheet Heading
* Where Table data is a multidimensional associative array detials are as follow
* Sheet
* $table_data[0][table_heading]= array();
* $table_data[0][contant_data] = array();
* $table_data[0][column_width] = array() (by default NULL);
* $table_data[0][column_format]= array() (by default NULL);
* $table_data[0][table_border] = Boolean (by default FALSE);
*
* $sheet_name[0] = 'sheet1';
* $sheet_heading[0] = 'This is a testing heading'
*
* @access public
* @param string, array, array, array
* @return void
*/
public function create_excel($excel_name = 'new_excel', $table_data = NULL, $sheet_name = NULL, $sheet_heading = NULL){
foreach($table_data as $key => $row){
//Create a new worksheet, after the default sheet
if($key > 0) $this->getActiveSheet();
//Set Active Sheet
$this->setActiveSheetIndex($key);
//name the worksheet
$sheet = $this->getActiveSheet();
$sheet->setTitle($sheet_name[$key]);
//Getting last column name
$last_column = $this->alphabet[count($table_data[$key]['table_heading'])-1];
//heading worksheet
$sheet->setCellValue('A1', $sheet_heading[$key]);
$sheet->mergeCells('A1:'.$last_column.'1'); //Need to make it dynamic
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setSize(20);
$sheet->getStyle('A1:'.$last_column.'1')->getFont()->setBold(true);
$sheet->getStyle('A1:'.$last_column.'1')->getAlignment()->applyFromArray(array('horizontal' => Alignment::HORIZONTAL_CENTER));
$header_data = NULL;
$content_data = NULL;
$column_width = NULL;
$column_format = NULL;
$border = FALSE;
if(!empty($table_data[$key]['table_heading']))
$header_data = $table_data[$key]['table_heading'];
if(!empty($table_data[$key]['content_data']))
$content_data = $table_data[$key]['content_data'];
if(!empty($table_data[$key]['column_width']))
$column_width = $table_data[$key]['column_width'];
if(!empty($table_data[$key]['column_format']))
$column_format = $table_data[$key]['column_format'];
$table_border = $table_data[$key]['table_border'];
$this->generate_excel_sheet($header_data, $content_data, $column_width, $column_format, $table_border);
}//end foreach($table_data as $key => $value)
//Print excel here;
$this->print_excel($excel_name);
}//end function create_excel(...)
/**
* This function helps to print or downlaod excel
*/
public function print_excel($excel_name){
//Creating header
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$excel_name.'.xlsx');
header('Cache-Control: max-age=0');
$writer = new Xlsx($this);
ob_end_clean();
$writer->save('php://output');
exit;
}
/**
* This function is responsible to convert php date into excel date
* Then we can apply excel formating on date field
*
* @access public
* @param data (yyyy-mm-dd)
* @return int (excel number e.g. 2008-12-31 to 39813)
*/
public function convert_date_php_2_excel($php_date){
if(isset($php_date) && trim($php_date)!= "")
return intval(25569 + strtotime($php_date) / 86400);
return NULL;
}
}// END Excel CLASS
/* End of file Excel.php */
/* Location: ./application/libraries/Excel.php */