动态查询SQL时使用PHP将标头硬编码为excel文件

问题描述

我有这个 PHPExcel 代码:

/** Error reporting */
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');

/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';
    $objPHPExcel = new PHPExcel();
    //$objPHPExcel->setActiveSheetIndex(0)
               // ->setCellValue('A1', 'Hello')
               // ->setCellValue('A2', 'world!')

$row = 2;
while($row_data = mysql_fetch_assoc($result)) {
    $col = 1;
    foreach($row_data as $key=>$value) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
$col++;
    }
        $row++;
    }

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Results');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Output.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

基本上，我是在动态查询数据库并将其放入Excel文件中.

Basically I am dynamically querying the database and placing it into an excel file.

我只想对几个标头进行硬编码.因此，对于A1，我想在"Hello"和A2:"World！"中进行硬编码.查询数据库并将数据从B列开始

I just want to hardcode in a few headers. So for A1 I'd like to hardcode in "Hello" and A2: "World!" while querying the database and putting the data starting in Column B

推荐答案

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('A2', 'world!');

$col = 1;
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    foreach($row_data as $value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $row++;
    }
    $col++;
}

请注意，Excel5的上限为256列，因此，如果保存的数据记录超过255个，则另存的列将从保存的工作簿中删除.

Note that Excel5 has a limit of 256 columns, so if you have more than 255 data records the additional columns will de dropped from the saved workbook if you're saving to that format.

这篇关于动态查询SQL时使用PHP将标头硬编码为excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！