从MySQL创建Excel文件 [英] Creating Excel file from MySQL

问题描述

我正在尝试从MySQL数据库中的表格生成一个XLS文件，但Excel文件格式不正确。当Excel文件生成您尝试打开的文件的格式不同于指定的文件时给出错误。文件打开时，数据格式不正确。

任何想法我失踪了？

 <？php 
 $ host ='XXXXXXX'; 
 $ dbname ='XXXXXXXX'; 
 $ username ='XXXXXXXX'; 
 $ password ='XXXXXXXX'; 
 
函数xlsBOF（）{$​​ b $ b echo pack（ssssss，0x809,0x8,0x0,0x10,0x0,0x0）; 
返回; 
} 
 
函数xlsEOF（）{$​​ b $ b echo pack（ss，0x0A，0x00）; 
返回; 
} 
 
函数xlsWriteLabel（$ Row，$ Col，$ Value）{
 $ L = strlen（$ Value）; 
 echo pack（ssssss，0x204,8 + $ L，$ Row，$ Col，0x0，$ L）; 
 echo $ Value; 
返回; 
} 
 
函数xlsWriteNumber（$ Row，$ Col，$ Value）{
 echo pack（sssss，0x203,14，$ Row，$ Col，0x0）; 
 echo pack（d，$ Value）; 
返回; 
} 
 
 try {
 $ conn = new PDO（mysql：host = $ host; dbname = $ dbname，$ username，$ password）; 
 echo在$ host成功连接到$ dbname; 
 $ conn = null; 
} catch（PDOException $ pe）{
 die（无法连接到数据库$ dbname：$ pe-> getMessage（））; 
} 
 
 $ q =SELECT * FROM tablename; 
 $ qr = mysql_query（$ q）or die（mysql_error（））; 
 
 header（Pragma：public）; 
 header（Expires：0）; 
 header（Cache-Control：must-revalidate，post-check = 0，pre-check = 0）; 
 header（Content-Type：application / force-download）; 
 header（Content-Type：application / octet-stream）; 
 header（Content-Type：application / download）; 
 
 header（Content-Disposition：attachment; filename = export _。$ dbtable。。xls）; 
 
 header（Content-Transfer-Encoding：binary）; 
 
 xlsBOF（）; 
 
 $ col = 0; 
 $ row = 0; 
 
 $ first = true; 
 
 while（$ qrow = mysql_fetch_assoc（$ qr））
 {
 if（$ first）
 {
 foreach（$ qrow as $ k = > $ v）
 {
 xlsWriteLabel（$ row，$ col，strtoupper（ereg_replace（_，，$ k）））; 
 $ col ++; 
} 
 
 $ col = 0; 
 $ row ++; 
 $ first = false; 
} 
 
 //通过数据
 foreach（$ qrow as $ k => $ v）
 {
 //写出来
 xlsWriteLabel（$ row，$ col，$ v）; 
 $ col ++; 
} 
 //重置col和goto下一行
 $ col = 0; 
 $ row ++; 
} 
 
 xlsEOF（）; 
 exit（）; 
  

解决方案

我不确定.xls但输出作为CSV表的MySQL结果，fputcsv函数没有太大的麻烦：

  //清除任何以前的输出
 ob_end_clean（）; 
 //我假设你已经有$ result 
 $ num_fields = mysql_num_fields（$ result）; 
 
 //获取MySQL结果标头
 $ headers = array（）; 
 $ headers [] =[Row]; （$ i = 0; $ i $ $ num_fields; $ i ++）{
 $ headers [] = strtoupper（mysql_field_name（$ result，$ i））; 
 
} 
 
 //当前日期的文件名
 $ current_date = date（y / m / d）; 
 $ filename =MyFileName。 $ current_date。名为 .csv; 
 
 //打开php输出流并写入头文件
 $ fp = fopen（'php：// output'，'w'）; 
 if（$ fp&& $ result）{
 header（'Content-Type：text / csv'）; 
 header（'Content-Disposition：attachment; filename ='。$ filename）; 
 header（'Pragma：no-cache'）; 
 header（'Expires：0'）; 
 echo您的CSV文件的标题\\\\
; 
 //将mysql头写入csv 
 fputcsv（$ fp，$ headers）; 
 $ row_tally = 0; 
 //将mysql行写入csv 
 while（$ row = mysql_fetch_array（$ result，MYSQL_NUM））{
 $ row_tally = $ row_tally + 1; 
 echo $ row_tally。，; 
 fputcsv（$ fp，array_values（$ row））; 
} 
 die; 
} 
  

I am trying to generate an XLS file from a table in a MySQL DB, but the Excel file is not properly formatted & given error when the Excel file generated "The file which you are trying to open is in different format than one specified". When the file is opened the data is not properly formatted.

Any ideas what I am missing?

<?php
$host = 'XXXXXXX';
$dbname = 'XXXXXXXX';
$username = 'XXXXXXXX';
$password = 'XXXXXXXX';

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    echo "Connected to $dbname at $host successfully.";
    $conn = null;
} catch (PDOException $pe) {
    die("Could not connect to the database $dbname :" . $pe->getMessage());
}

$q = "SELECT * FROM tablename";
$qr = mysql_query( $q ) or die( mysql_error() );

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");

header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");

header("Content-Transfer-Encoding: binary ");

xlsBOF();

$col = 0;
$row = 0;

$first = true;

while( $qrow = mysql_fetch_assoc( $qr ) )
{
    if( $first )
    {
      foreach( $qrow as $k => $v )
      {
        xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
        $col++;
      }

      $col = 0;
      $row++;
      $first = false;
    }

    // go through the data
    foreach( $qrow as $k => $v )
    {
      // write it out
      xlsWriteLabel( $row, $col, $v );
      $col++;
    }
    // reset col and goto next row
    $col = 0;
    $row++;
}

xlsEOF();
exit();

解决方案

I'm not sure about .xls but for outputting a MySQL result as a CSV table the fputcsv function does it without much fuss:

// Clear any previous output
ob_end_clean();
// I assume you already have your $result
$num_fields = mysql_num_fields($result);

// Fetch MySQL result headers
$headers = array();
$headers[] = "[Row]";
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = strtoupper(mysql_field_name($result , $i));
}

// Filename with current date
$current_date = date("y/m/d");
$filename = "MyFileName" . $current_date . ".csv";

// Open php output stream and write headers
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename='.$filename);
    header('Pragma: no-cache');
    header('Expires: 0');
    echo "Title of Your CSV File\n\n";
    // Write mysql headers to csv
    fputcsv($fp, $headers);
    $row_tally = 0;
    // Write mysql rows to csv
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $row_tally = $row_tally + 1;
    echo $row_tally.",";
        fputcsv($fp, array_values($row));
    }
    die;
}

这篇关于从MySQL创建Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！