PHP如何使用XlsWriter实现百万级数据导入导出
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、环境准备</a></li><li><a href="#_label1">二、百万级数据导出（Excel）</a></li><li><a href="#_label2">三、百万级数据导入（Excel到数据库）</a></li><li><a href="#_label3">四、性能优化技巧</a></li><li><a href="#_label4">五、注意事项</a></li></ul></div><p>在PHP中使用 XlsWriter（如 xlswriter 扩展）处理百万级数据的导入导出，需重点解决内存占用和性能问题。</p><p>以下是分步骤的实现方案：</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、环境准备</h2>
<p>1 安装 xlswriter 扩展</p>
<p>从PECL安装：</p>
<div class="jb51code"><pre class="brush:bash;">pecl install xlswriter
</pre></div>
<p>在 php.ini 中启用扩展：</p>
<div class="jb51code"><pre class="brush:php;">extension=xlswriter.so
</pre></div>
<p>2 调整PHP配置</p>
<p>处理大数据时需增加内存和执行时间限制：</p>
<div class="jb51code"><pre class="brush:php;">memory_limit = 1024M
max_execution_time = 3600
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>二、百万级数据导出（Excel）</h2>
<p><strong>核心思路</strong></p>
<p>流式写入：避免一次性加载所有数据到内存。</p>
<p>分页查询：从数据库分批读取数据。</p>
<p>直接输出到浏览器：减少临时文件占用。</p>
<p><strong>代码实现</strong></p>
<div class="jb51code"><pre class="brush:php;"><?php
// 1. 初始化Excel对象
$config = ['path' => '/tmp']; // 临时目录（可选）
$excel = new \Vtiful\Kernel\Excel($config);
$file = $excel->fileName('export.xlsx')->header(['ID', 'Name', 'Email']);
// 2. 设置HTTP头直接下载
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$file->output();
// 3. 连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
// 4. 分页查询并写入数据
$pageSize = 10000; // 每页数据量
$page = 1;
do {
$offset = ($page - 1) * $pageSize;
$stmt = $pdo->prepare("SELECT id, name, email FROM users LIMIT :offset, :limit");
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($data)) {
break;
}
// 写入当前页数据
foreach ($data as $row) {
$file->data([$row['id'], $row['name'], $row['email']]);
}
$page++;
ob_flush(); // 刷新输出缓冲区
flush();
} while (true);
// 5. 结束写入
$file->output();
</pre></div>
<p><strong>关键点</strong></p>
<p>分页查询：通过 LIMIT 分批拉取数据，避免一次性加载百万数据。</p>
<p>流式输出：直接输出到浏览器，减少内存占用。</p>
<p>缓冲区刷新：使用 ob_flush() 和 flush() 实时推送数据到客户端。</p>
<p class="maodian"><a name="_label2"></a></p><h2>三、百万级数据导入（Excel到数据库）</h2>
<p><strong>核心思路</strong></p>
<p>分块读取Excel：避免一次性加载整个文件。</p>
<p>批量插入：使用事务和批量SQL减少数据库操作次数。</p>
<p>错误处理：记录错误数据，避免单条失败导致全部回滚。</p>
<p><strong>代码实现</strong></p>
<div class="jb51code"><pre class="brush:php;"><?php
// 1. 上传文件处理
$uploadFile = $_FILES['file']['tmp_name'];
if (!is_uploaded_file($uploadFile)) {
die('非法文件');
}
// 2. 初始化Excel读取器
$excel = new \Vtiful\Kernel\Excel();
$excel->openFile($uploadFile);
$sheet = $excel->getSheet();
// 3. 连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$pdo->beginTransaction();
// 4. 分块读取并插入
$batchSize = 5000; // 每批插入量
$batchData = [];
$currentRow = 0;
try {
while ($row = $sheet->nextRow()) {
$currentRow++;
if ($currentRow === 1) {
continue; // 跳过标题行
}
// 数据校验（示例）
if (empty($row) || !filter_var($row, FILTER_VALIDATE_EMAIL)) {
error_log("Invalid data at row $currentRow: " . json_encode($row));
continue;
}
// 构建批量插入数据
$batchData[] = [
'id' => $row,
'name' => $row,
'email' => $row
];
// 批量插入
if (count($batchData) >= $batchSize) {
insertBatch($pdo, $batchData);
$batchData = [];
}
}
// 插入剩余数据
if (!empty($batchData)) {
insertBatch($pdo, $batchData);
}
$pdo->commit();
echo "导入成功！";
} catch (Exception $e) {
$pdo->rollBack();
echo "导入失败: " . $e->getMessage();
}
// 批量插入函数
function insertBatch($pdo, $data) {
$sql = "INSERT INTO users (id, name, email) VALUES ";
$values = [];
$placeholders = [];
foreach ($data as $item) {
$values[] = $item['id'];
$values[] = $item['name'];
$values[] = $item['email'];
$placeholders[] = '(?, ?, ?)';
}
$sql .= implode(', ', $placeholders);
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
}</pre></div>
<p><strong>关键点</strong></p>
<p>分块读取：逐行读取Excel，避免内存爆炸。</p>
<p>事务提交：批量插入后提交事务，减少数据库压力。</p>
<p>错误跳过：记录错误行，避免单条数据错误导致整体失败。</p>
<p class="maodian"><a name="_label3"></a></p><h2>四、性能优化技巧</h2>
<p>1 索引优化：</p>
<p>在导入前移除索引，导入完成后重新创建。</p>
<p>使用 ALTER TABLE ... DISABLE KEYS 和 ALTER TABLE ... ENABLE KEYS（MyISAM引擎）。</p>
<p>2 调整MySQL配置：</p>
<div class="jb51code"><pre class="brush:php;">innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 0
</pre></div>
<p>3 压缩Excel文件：</p>
<div class="jb51code"><pre class="brush:php;">$file = $excel->fileName('export.xlsx')->setCompressionLevel(6);
</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>五、注意事项</h2>
<p>内存监控：使用 memory_get_usage() 实时监控内存。</p>
<p>超时处理：通过 set_time_limit(0) 禁用脚本超时。</p>
<p>日志记录：记录导入导出的进度和错误。</p>
頁:
[1]