怎么用php把查询出来的文件导出成excel表格的形式呢？

自己组excel xml
http://blog.csdn.net/mengxiangbaidu/article/details/6718521

将内容设置为一个标准的<table>,文件头设置为application/vnd.ms-excel就行需要下载的话，设置成这样：
Header( "Content-type:application/octet-stream");
Header( "Accept-Ranges:bytes");
Header( "Content-type:application/vnd.ms-excel");
Header( "Content-Disposition:attachment;filename=文档.xls");

你好 excelcreator这个类导入进去ie浏览器可以下载成excel 火狐枫叶什么的就只能下载这个php页面了下载不成excel文件

我用的是这段代码：require_once("../lib/excelcreator.class.php");
$myxls = new ExcelCreator ("中文Excel");
$aTableHead = '
    <Row ss:AutoFitHeight="0">
     <Cell><Data ss:Type="String">Name3</Data></Cell>     <Cell><Data ss:Type="String">Surname3</Data></Cell>    </Row>';
$aTableBody = '
    <Row ss:AutoFitHeight="0">
     <Cell><Data ss:Type="String">Schwarz3</Data></Cell>     <Cell><Data ss:Type="String">Oliver3</Data></Cell>    </Row>';
$aTableBottom = '
    <Row ss:AutoFitHeight="0">
     <Cell><Data ss:Type="String">123</Data></Cell>     <Cell><Data ss:Type="String">Peter3</Data></Cell>    </Row>';
$workSheet = $myxls->createWorkSheet ( "中文sheet1", $aTableHead, $aTableBody, $aTableBottom );
echo $myxls->createExcel ( $workSheet );火狐就只能打印出php的 ie就可以

你好，我试用了一下：
我的windows sp3 ，wamp环境
PHP Version 5.2.5
System Windows NT MICROSOF-EBE0D1 5.1 build 2600
Build Date Nov 8 2007 23:18:08
Configure Command cscript /nologo configure.js "--enable-snapshot-build" "--with-gd=shared"
Server API Apache 2.0 Handler
Virtual Directory Support enabled
Configuration File (php.ini) Path C:\WINDOWS
Loaded Configuration File D:\wamp\Apache2\bin\php.ini
PHP API 20041225
PHP Extension 20060613
Zend Extension 220060519
Debug Build no
Thread Safety enabled
Zend Memory Manager enabled
IPv6 Support enabled
Registered PHP Streams php, file, data, http, ftp, compress.zlib, https, ftps
Registered Stream Socket Transports tcp, udp, ssl, sslv3, sslv2, tls
Registered Stream Filters convert.iconv.*, string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed, zlib.*枫叶浏览器2.0.1.40
chrome19
火狐10
ie8
都没问题。

我感觉那个 $aTableBottom这个东西没什么用吧

有的时候可能需要excel报表有表尾，主要是预留的，可以不加东西。
类代码应该都能看懂，可以根据需要自己改。

是编码问题，要求是文件必须是utf-8，无dom的。其它的编码只能ie行

//$ary 数据结果集
//GetKey 你要导出的字段类似每个字段对应一个checkbox function ExecutionExportPrint($ary,&$GetKeys)
    {
     //定义此数组存储excel列标
$_total  = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX');
//定义表格title
$_totaltitle = array(
'WorkID'=>'稿件编号',
'DesignTime'=>'设计日期',
'ProdName'=>'期刊名称',
'IssueTime'=>'刊期',
'DeptID'=>'部门名称',
'CorpName'=>'客户名称',
'Pusn'=>'客户助理',
'Designer'=>'设计师',
'P'=>'p数',
'Color'=>'颜色',
'EditionName'=>'版位',
'WorkType'=>'类型',
'WorkStatus'=>'稿件状态',
'Urgency'=>'紧急程度'
);

//截取与字段数目等值的一段列标数组
for ($i = 0; $i < count($GetKeys); $i++)
{
$column[$i] = $_total[$i];
}
//组装成列标=>字段名的数据结构。
$fieldmap    = array_combine($column,$GetKeys);
$objExcel    = new PHPExcel();
$objActSheet = $objExcel -> getActiveSheet(0);
//error_reporting(E_ALL);
//使用cache_towincache方式，单元格对象会保存在Wincache中，只在内存中保存索引
        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_wincache;
        $cacheSettings = array( 'cacheTime'        => 600
                      );
       PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

//第一行插入title，设置居中
for ($i = 1;$i <= count($ary);$i ++)
{
foreach ($fieldmap as $key=>$value)
{
$objActSheet -> setCellValue($key.'1', transformUtf8($_totaltitle[$value]));//列值
        //$objActSheet -> getColumnDimension($key) -> setWidth();//宽度
        $objActSheet -> getStyle($key.'1')       -> getAlignment() -> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet -> setCellValue($key.($i+1), transformUtf8($ary[$i-1] -> $value));
$objActSheet -> getStyle($key.($i+1))    -> getAlignment() -> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
}
}
if(self::ex == '2007')
{ //导出excel2007文档
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="'.date('Ymdhis').'.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter =  PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
     $objWriter -> save('php://output');
     exit;

} else
{  //导出excel2003文档
     header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
     header('Content-Disposition: attachment;filename="'.date('Ymdhis').'_Excel.xls"');
     header('Cache-Control: max-age=0');
     $objWriter =  PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
     $objWriter -> save('php://output');
     exit;
}
}

这是用 phpexcel生成，更我的用法一样，还可以使用生成xml的方式//默认为xls格式，还可以生成 csv、txt
function save_detail_excel($sql,$file_name,$file_type="xls"){

global $db_conn;
if($sql<>""){
set_time_limit(0);
ini_set('memory_limit','600M');
$rows=mysql_query($sql,$db_conn);
$row_counts_list=mysql_num_rows($rows);
$field_count=mysql_num_fields($rows);

if ($row_counts_list!=0) {
//文档类型
if($file_type=="xls"){

//记录集个数小于3500，使用phpexcel生成xls
if ($row_counts_list<3500) {
require("../../inc/excel.php");
//当前活动表
$objPHPExcel->setActiveSheetIndex(0);
//sheet表名
$objPHPExcel->getActiveSheet()->setTitle("".$file_name."");
$field_count2=$field_count-1;
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_tit_left, "A1:$tit_arr[$field_count2]1");
$objPHPExcel->getActiveSheet()->setSharedStyle($excel_list_left, "A2:$tit_arr[$field_count2]".($row_counts_list+1));
for ($i=0;$i<$field_count;$i++){
$objPHPExcel->getActiveSheet()->setCellValue("$tit_arr[$i]1","".mysql_field_name($rows,$i)."");
}
$i=1;
while($rs= mysql_fetch_array($rows)){
$i+=1;
for ($k=0;$k<$field_count;$k++){

$objPHPExcel->getActiveSheet()->getCell($tit_arr[$k].$i)->setValueExplicit($rs[$k], PHPExcel_Cell_DataType::TYPE_STRING);

}
/*if($i%100==0){
ob_flush();
flush();
}*/
}
$objPHPExcel->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file_path=excel_file("".$file_name."_","xls");
$objWriter->save(str_replace('.php', '.xls',$file_path[2]));

}else{
//记录集个数大于3500，使用XML生成xls
$file_path=excel_file("".$file_name."_",$file_type);
$fp = fopen($file_path[2],"w");

$sheet_rows=40000;//单Sheet记录集个数

$xml_head="<?xml version=\"1.0\"?>\n<?mso-application progid=\"Excel.Sheet\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\"><DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"><Title>详单数据导出</Title><Author>睿拓科技</Author><Category>详单、汇总</Category>\n<Company>睿拓科技</Company></DocumentProperties>\n<OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\"><AllowPNG/> <RemovePersonalInformation/></OfficeDocumentSettings><ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"> <WindowHeight>8010</WindowHeight><WindowWidth>14805</WindowWidth><WindowTopX>240</WindowTopX><WindowTopY>105</WindowTopY>\n<ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook>\n<Styles>\n<Style ss:ID=\"Default\" ss:Name=\"Normal\"><Alignment ss:Vertical=\"Bottom\"/><Borders/><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"10\" ss:Color=\"#000000\"/><Interior/><NumberFormat/><Protection/></Style><Style ss:ID=\"s71\"><Alignment ss:Vertical=\"Center\"/><Borders><Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\"/><NumberFormat ss:Format=\"@\"/></Style><Style ss:ID=\"s72\"><Alignment ss:Vertical=\"Center\"/><Borders>\n<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\n <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/><Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/></Borders><Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Color=\"#000000\" ss:Bold=\"1\"/> <Interior ss:Color=\"#92D050\" ss:Pattern=\"Solid\"/><NumberFormat ss:Format=\"@\"/></Style></Styles>";

fwrite($fp,$xml_head);

for ($i=0;$i<$field_count;$i++){

$filed_list.="<Cell ss:StyleID=\"s72\"><Data ss:Type=\"String\">".mysql_field_name($rows,$i)."</Data></Cell>";
}
$xml_sheet_head_row="<Row>".$filed_list.="</Row>";

$i=0;
$sheet_id=0;

while($rs= mysql_fetch_array($rows)){
$i++;
$style="";
$f_list="";
$row_=$i%$sheet_rows;

if($row_==1){
$sheet_id++;

fwrite($fp,"\n<Worksheet ss:Name=\"".$file_name.$sheet_id."\">\n<Table x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">".$xml_sheet_head_row);
ob_flush();
flush();
}

for ($k=0;$k<$field_count;$k++){
$f_list.="<Cell ss:StyleID=\"s71\"><Data ss:Type=\"String\">".$rs[$k]."</Data></Cell>";
}

fwrite($fp,"<Row>".$f_list."</Row>");

if($row_==0){

fwrite($fp,"</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");

}

}

if($row_<$sheet_rows){
fwrite($fp,"</Table>\n<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Unsynced/><Print><ValidPrinterInfo/>\n<PaperSizeIndex>9</PaperSizeIndex>\n<HorizontalResolution>-3</HorizontalResolution><VerticalResolution>0</VerticalResolution></Print><Selected/><Panes><Pane><Number>3</Number><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet>\n\n");
}

fwrite($fp,"</Workbook>");
fclose($fp);

}
unset($xml_head);
unset($filed_list);
unset($f_list);

}elseif($file_type=="csv"){
//生成Csv文档
$file_path=excel_file("".$file_name."_",$file_type);
$fp=fopen($file_path[2],"w");

for ($i=0;$i<$field_count;$i++){
$filed_list.=mysql_field_name($rows,$i).",";
}
fwrite($fp,utf82gb($filed_list)."\n");

$i=1;
while($rs= mysql_fetch_array($rows)){
$f_list="";
$i++;
for ($k=0;$k<$field_count;$k++){
$f_list.=$rs[$k].",";
}
$row_list=$f_list."\n";

fwrite($fp,utf82gb($row_list));

if($i%100==0){
ob_flush();
flush();
}
}
fclose($fp);

unset($f_list);
unset($row_list);

}else{
//生成TXT文档
$file_path=excel_file("".$file_name."_",$file_type);
$fp=fopen($file_path[2],"w");

for ($i=0;$i<$field_count;$i++){
$filed_list.=mysql_field_name($rows,$i)."\t";
}
fwrite($fp,utf82gb($filed_list)."\n");

$i=1;
while($rs= mysql_fetch_array($rows)){
$f_list="";
$i++;
for ($k=0;$k<$field_count;$k++){
$f_list.=$rs[$k]."\t";
}
$row_list=$f_list."\n";

fwrite($fp,utf82gb($row_list));

if($i%100==0){
ob_flush();
flush();
}

}
fclose($fp);

unset($f_list);
unset($row_list);
}

$do_res=array("counts"=>"1","file_path"=>gb2utf8(str_replace("../../data/","/data/",$file_path[1])),"file_name"=>gb2utf8($file_path[0]),"des"=>"文件导出完成，请点击下载！");

}else {
$do_res=array("counts"=>"0","file_path"=>"","file_name"=>"","des"=>"未找到符合条件的数据...");
}
}else{
$do_res=array("counts"=>"-1","file_path"=>"","file_name"=>"","des"=>"数据查询条件有误，请检查后重试...");
}
return $do_res;
mysql_free_result($rows);
}

调试易

怎么用php把查询出来的文件导出成excel表格的形式呢？

解决方案 »