How to import and export Excel in ThinkPHP6?

With the increasing popularity of Internet applications and the growing demand for interaction between various types of data and data transfer, file format conversion, import and export have become one of the common needs. In web applications, importing and exporting Excel files are common ways of data exchange in daily work. This article will introduce how to implement Excel import and export functions in the ThinkPHP6 framework.

1. Introduction to ThinkPHP6 framework

ThinkPHP6 is a free, open source, fast and simple object-oriented lightweight PHP framework. It has the characteristics of high performance, rich functions, flexible configuration, strong scalability, etc., and has become a popular framework in the field of PHP application development.

2. Excel export

1. Sample code

<?php
namespace appindexcontroller;

use PHPExcel;
use PHPExcel_IOFactory;

class ExcelExport
{
    public function export()
    {
        $objPHPExcel = new PHPExcel();

        // 设置当前活动sheet
        $objPHPExcel->setActiveSheetIndex(0);

        // 设置标题
        $objPHPExcel->getActiveSheet()->setTitle('学生成绩');

        // 设置表头
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '学号');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '语文');
        $objPHPExcel->getActiveSheet()->setCellValue('D1', '数学');
        $objPHPExcel->getActiveSheet()->setCellValue('E1', '英语');
        $objPHPExcel->getActiveSheet()->setCellValue('F1', '总分');

        // 设置数据
        $data = [
            ['1001', '张三', '85', '90', '88', '263'],
            ['1002', '李四', '90', '88', '90', '268'],
            ['1003', '王五', '92', '87', '91', '270']
        ];
        $row = 2;
        foreach ($data as $val) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $val[0]);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $val[1]);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $val[2]);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $val[3]);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $val[4]);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $val[5]);
            $row++;
        }

        // 设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);

        // 导出Excel
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="学生成绩.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }
}

2. Explanation

The above sample code demonstrates How to use PHPExcel library to implement Excel export. The PHPExcel library is an open source library for PHP to read and write Excel files. It is powerful and easy to operate.

First, we created a PHPExcel instance, then set the current active sheet, and set the title and header. After that, we set the data and set the column widths. Finally, we use the header function to set the content type, file name and cache control for exporting Excel, and then use the createWriter method of the PHPExcel_IOFactory class to create a writer in Excel2007 format and output the data stream to the browser.

3. Excel import

1. Sample code

<?php
namespace appindexcontroller;

use PHPExcel;
use PHPExcel_IOFactory;

class ExcelImport
{
    public function import()
    {
        if (empty($_FILES['file']['tmp_name'])) {
            echo '请选择文件！';
            exit;
        }

        $reader = PHPExcel_IOFactory::createReader('Excel2007');
        $PHPExcel = $reader->load($_FILES['file']['tmp_name']);

        $sheet = $PHPExcel->getSheet(0); // 获得第1个工作表

        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数

        $data = [];
        for ($row = 2; $row <= $highestRow; $row++) {
            $rowData = [];
            for ($col = 'A'; $col <= $highestColumn; $col++) {
                $rowData[$col] = (string)$sheet->getCell($col . $row)->getValue();
            }
            $data[] = $rowData;
        }

        var_dump($data);
    }
}

2. Explanation

The above sample code demonstrates How to use PHPExcel library to implement Excel import. First, we determine whether the client has uploaded the file and use the createReader method of the PHPExcel_IOFactory class to create a reader in Excel2007 format. Then, we use the load method of $reader to read the Excel file into memory and obtain the first worksheet in it. Next, we loop through the Excel table through the index value of the highest row and highest column, store each row of data in the form of an associative array into the $data variable, and finally output the variable.

4. Summary

Through the introduction of this article, we have learned how to use the ThinkPHP6 framework combined with the PHPExcel library to implement the import and export functions of Excel files, which provides convenience and convenience for our Web application development. support. We can flexibly use various methods and properties of the PHPExcel library according to actual needs to achieve file format exchange and better serve our applications.

The above is the detailed content of How to import and export Excel in ThinkPHP6?. For more information, please follow other related articles on the PHP Chinese website!