用PHP从mysql数据库中生成图表和数据的Excel文件

<?php
require 'vendor/autoload.php'; // 引入PhpSpreadsheet库

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// 连接MySQL数据库
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 查询数据库
$sql = "SELECT * FROM your_table";
$result = $conn->query($sql);

// 创建Excel对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 设置表头
$sheet->setCellValue('A1', 'Column 1');
$sheet->setCellValue('B1', 'Column 2');

// 填充数据
$row = 2;
if ($result->num_rows > 0) {
    while ($row_data = $result->fetch_assoc()) {
        $sheet->setCellValue('A' . $row, $row_data['column1']);
        $sheet->setCellValue('B' . $row, $row_data['column2']);
        $row++;
    }
}

// 生成图表
$chart = new \PhpOffice\PhpSpreadsheet\Chart\Chart(
    'chart1', // 图表ID
    null, // 图表标题
    null, // X轴标签
    null  // Y轴标签
);
$dataSeriesLabels = [
    new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('String', 'Sheet1!$A$1', null, 1), // 列1作为标签
];
$xAxisTickValues = [
    new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('String', 'Sheet1!$B$2:$B$' . ($row - 1), null, $row - 1), // 列2作为X轴刻度
];
$dataSeriesValues = [
    new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('Number', 'Sheet1!$B$2:$B$' . ($row - 1), null, $row - 1), // 列2作为数据
];
$series = new \PhpOffice\PhpSpreadsheet\Chart\Series(
    $dataSeriesValues,
    $dataSeriesLabels,
    null,
    $xAxisTickValues
);
$chart->setPlotArea(new \PhpOffice\PhpSpreadsheet\Chart\PlotArea(null, [$series]));
$chart->setLegend(null);

// 将图表插入到工作表
$sheet->addChart($chart);

// 保存Excel文件
$writer = new Xlsx($spreadsheet);
$filename = 'data.xlsx';
$writer->save($filename);

// 下载Excel文件
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$writer->save('php://output');

// 关闭数据库连接
$conn->close();
?>