TP5 references PHPExcel to implement import and export functions
Download PHPExcel from php Chinese website
Place the downloaded PHPExcel folder and PHPExcel in the Vendor directory in the framework, pay attention to the structure of the directory , according to my code, you can basically directly quote it using
Front-end code
<html>
<head></head>
<body>
<div class="panel-heading">
Advanced Tables
<a href="/daochu" class="btn-succes">导出</a>
<form action="/daoru" method="post" enctype="multipart/form-data">
<input name="upload[]" type="file" />
<input type="submit" />
</form>
</div>
</body>
</html>Export the code of the function controller
$list = Db::table('product')->select();
vendor("PHPExcel176.PHPExcel");
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
//set font size bold
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:E2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//合并cell
$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
// set table header content
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '订单数据汇总 时间:'.date('Y-m-d H:i:s'))
->setCellValue('A2', '订单ID')
->setCellValue('B2', '商品名称')
->setCellValue('C2', '价格')
->setCellValue('D2', '库存')
->setCellValue('E2', '图片');
// Miscellaneous glyphs, UTF-8
for($i=0;$i<count($list)-1;$i++){
$objPHPExcel->getActiveSheet(0)->setCellValue('A'.($i+3), $list[$i]['id']);
$objPHPExcel->getActiveSheet(0)->setCellValue('B'.($i+3), $list[$i]['name']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C'.($i+3), $list[$i]['price']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D'.($i+3), $list[$i]['stock']);
$objPHPExcel->getActiveSheet(0)->setCellValue('E'.($i+3), $list[$i]['main_img_url']);
//$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$objPHPExcel->getActiveSheet()->getStyle('A'.($i+3).':J'.($i+3))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getRowDimension($i+3)->setRowHeight(16);
}
// sheet命名
$objPHPExcel->getActiveSheet()->setTitle('订单汇总表');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// excel头参数
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="商品表('.date('Ymd-His').').xls"'); //日期为文件名后缀
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式
$objWriter->save('php://output'); 
Import the code of the function controller
public function daoru(){
$file = $_FILES['upload']['tmp_name'][0];
$data = $this->import_excel($file);
var_dump($data);
}
private function import_excel($file){
// 判断文件是什么格式
$type = pathinfo($file);
$type = strtolower($type["extension"]);
$type=$type==='csv' ? $type : 'Excel5';
ini_set('max_execution_time', '0');
Vendor('PHPExcel176.PHPExcel');
// 判断使用哪种格式
$objReader = \PHPExcel_IOFactory::createReader($type);
$objPHPExcel = $objReader->load($file);
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
//循环读取excel文件,读取一条,插入一条
$data=array();
//从第一行开始读取数据
for($j=3;$j<=$highestRow;$j++){
//从A列读取数据
for($k='A';$k<=$highestColumn;$k++){
// 读取单元格
$data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
}
}
return $data;
}The data content of the excel file is obtained here, which can be inserted into the database in a loop
The above is the detailed content of TP5 references PHPExcel to implement import and export functions. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1662
14
1419
52
1311
25
1261
29
1234
24
Complete Guide: How to process Excel files using php extension PHPExcel
Jul 28, 2023 pm 10:01 PM
Complete Guide: How to Process Excel Files Using PHP Extension PHPExcel Introduction: Excel files are often used as a common format for data storage and exchange when processing large amounts of data and statistical analysis. Using the PHP extension PHPExcel, we can easily read, write and modify Excel files to effectively process Excel data. This article will introduce how to use the PHP extension PHPExcel to process Excel files and provide code examples. 1. Install PHPExc
PHP development: Use PHPExcel to process Excel files
Jun 15, 2023 pm 03:45 PM
With the advent of the digital age, data has become the most important part of our daily lives and work, and Excel files have become one of the important tools for data processing. I believe that many PHP developers will often encounter the use of Excel files for data processing and operations at work. This article will introduce you to the methods and precautions for using the PHPExcel library to process Excel files. What is PHPExcel? PHPExcel is a PHP class
How to use phpexcel to convert Excel files to CSV files and open them
Mar 27, 2023 pm 04:16 PM
PHPEXCEL is an excellent PHP class library for reading and writing Excel files. It provides a very sufficient API that allows us to use PHP to read and write Excel files. Sometimes, we need to convert Excel files into CSV files for use on some occasions. So, this article mainly describes how to use the PHPEXCEL class library to convert Excel files into CSV files and open them.
Why phpexcel has become the focus of PHP developers
Mar 27, 2023 pm 06:15 PM
PHPExcel is an open source PHP library for processing Microsoft Excel files. It can read, create, modify and save Excel files. It is a powerful and highly customizable tool that can be used to handle tasks such as data analysis, report generation, data import and export, etc. In this article, we will introduce why PHPExcel has become the focus of PHP developers.
How to use PHPExcel to process Excel files?
Jun 01, 2023 pm 02:01 PM
PHPExcel is an open source PHP library for processing Microsoft Excel (.xls and .xlsx) files. It can read, write and operate Excel files, and provides a wealth of functions and methods. Using the PHPExcel library in PHP projects, you can quickly and easily process Excel files and implement functions such as data import, export and data processing. This article will introduce how to use PHPExcel to process Excel files. 1. To install PHPExcel, use
Create Excel files using PHP and PHPExcel
May 11, 2023 am 08:40 AM
In today's era of rapid information transfer, data processing and storage have become increasingly important. The use of Excel tables is the first choice for many people because Excel tables can integrate various data and can be easily analyzed and processed. In order to complete the creation of Excel tables more efficiently, we can use two powerful tools, PHP and PHPExcel. In this article, we will introduce how to create Excel files using PHP and PHPExcel. 1. Install PHPExcel first
PHP development tips: How to use PHPExcel and PHPExcel_IOFactory to operate MySQL database
Jul 02, 2023 pm 02:28 PM
PHP development tips: How to use PHPExcel and PHPExcel_IOFactory to operate MySQL database Overview: In web development, processing Excel files is a common and important task. PHPExcel is a powerful and easy-to-use PHP library that can help us read and write Excel files. This article will introduce how to use PHPExcel and PHPExcel_IOFactory libraries to operate MySQL database. step 1
PHP development skills: How to use PHPExcel to operate MySQL database
Jul 02, 2023 pm 12:21 PM
PHP development skills: How to use PHPExcel to operate MySQL database. With the booming development of the Internet, a large amount of data is stored in the database, and operations such as import, export, and processing are required. In PHP development, PHPExcel is a powerful library that can simplify the interaction with Excel files and realize the import and export of data. This article will introduce how to use PHPExcel to operate the MySQL database and implement data import and export functions. Installation and configuration of PHPExcel
