How to implement data export and import in PHP?

How to export and import data in PHP?

PHP is a multi-purpose scripting language commonly used to develop web applications. During the development process, the import and export of data is a very important function. This article will introduce how to use PHP to export and import data.

1. Data export

1. Export to CSV file
   CSV file is a plain text format commonly used to transfer data between different systems. Data can be easily exported to CSV files using PHP. The following are the steps to implement:

First, connect to the database and query the data that needs to be exported.

// 连接到数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// 查询需要导出的数据
$query = "SELECT * FROM table_name";
$result = mysqli_query($conn, $query);

Then, create a file and write the queried data to the file.

// 创建一个CSV文件
$file = fopen('export.csv', 'w');

// 写入表头
$header = array('字段1', '字段2', '字段3');
fputcsv($file, $header);

// 写入数据
while ($row = mysqli_fetch_assoc($result)) {
    fputcsv($file, $row);
}

// 关闭文件
fclose($file);

Finally, the exported CSV file will be saved in the same directory as the PHP file and contain the queried data.

2. Export to Excel file
   In addition to exporting to CSV files, you can also use PHP to export data to Excel files. You can use the PHPExcel library to achieve this functionality. First, you need to download and install the PHPExcel library from the PHPExcel official website.

// 导入PHPExcel类库
require_once 'PHPExcel/Classes/PHPExcel.php';

// 创建一个Excel对象
$objPHPExcel = new PHPExcel();

Then, connect to the database and query the data that needs to be exported.

// 连接到数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// 查询需要导出的数据
$query = "SELECT * FROM table_name";
$result = mysqli_query($conn, $query);

Next, write the queried data into an Excel file.

// 设置表头
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', '字段1')
    ->setCellValue('B1', '字段2')
    ->setCellValue('C1', '字段3');

// 逐行写入数据
$row = 2;
while ($data = mysqli_fetch_assoc($result)) {
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A'.$row, $data['字段1'])
        ->setCellValue('B'.$row, $data['字段2'])
        ->setCellValue('C'.$row, $data['字段3']);
    $row++;
}

// 导出Excel文件
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('export.xls');

Finally, the exported Excel file will be saved in the same directory as the PHP file and contain the queried data.

2. Data import

Data import is the opposite of data export, importing data from external files into the database. The following are the steps to implement:

1. Import CSV file
   First, connect to the database.

// 连接到数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

Then, read the data in the CSV file and insert it into the database.

// 打开CSV文件
$file = fopen('import.csv', 'r');

// 循环读取CSV文件中的每一行数据
while (($data = fgetcsv($file)) !== FALSE) {
    // 将数据插入到数据库中
    $query = "INSERT INTO table_name (字段1, 字段2, 字段3) VALUES ('$data[0]', '$data[1]', '$data[2]')";
    mysqli_query($conn, $query);
}

// 关闭文件
fclose($file);

Finally, the data in the CSV file will be inserted into the database.

2. Import Excel file
   You can use the PHPExcel library to import data in Excel files. First, connect to the database.

// 连接到数据库
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

Then, load the Excel file and read the data in it.

// 加载Excel文件
$objPHPExcel = PHPExcel_IOFactory::load('import.xls');

// 获取第一个工作表
$sheet = $objPHPExcel->getActiveSheet();

// 循环读取每一行数据
foreach ($sheet->getRowIterator() as $row) {
    $rowData = array();

    // 循环读取每一列数据
    foreach ($row->getCellIterator() as $cell) {
        $rowData[] = $cell->getValue();
    }

    // 将数据插入到数据库中
    $query = "INSERT INTO table_name (字段1, 字段2, 字段3) VALUES ('$rowData[0]', '$rowData[1]', '$rowData[2]')";
    mysqli_query($conn, $query);
}

Finally, the data in the Excel file will be inserted into the database.

Through the above steps, we can export and import data in PHP. Whether exporting to a CSV file or an Excel file, cross-platform and cross-system data transmission can be easily achieved. At the same time, through data import, we can quickly import data from external files into the database to facilitate subsequent data processing and analysis.

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