PHP导出到Excel会导致格式错误和不必要的迹象 [英] PHP export to Excel results in wrong format and unwanted signs
问题描述
我尝试将数据从Microsoft SQL Server 2008数据库导出到Excel电子表格.
I try to export data out of my Microsoft SQL Server 2008 Database to an Excel Spreadsheet.
我正在使用本教程: http://www.appservnetwork.com/modules.php ?name = News& file = article& sid = 8
I am using this tutorial: http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8
我的标题如下:
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=$filename.xls ");
header("Content-Transfer-Encoding: binary ");
XLS数据单元格如下所示:
The XLS Data Cell looks like this:
xlsBOF();
xlsWriteString(0,0,"$lng_report_age_title");
xlsWriteString(0,4,"$lng_report_sex_title");
xlsWriteString(0,6,"$lng_report_referredfrom_title");
xlsWriteString(0,8,"$lng_report_selfreferred_title");
xlsWriteString(1,0,"$lng_report_age04");
xlsWriteString(1,1,"$lng_report_age514");
xlsWriteString(1,2,"$lng_report_age1549");
xlsWriteString(1,3,"$lng_report_age50");
xlsWriteString(1,4,"$lng_report_sexm");
xlsWriteString(1,5,"$lng_report_sexf");
xlsWriteString(1,6,"$lng_report_referredfrom_indistrict");
xlsWriteString(1,7,"$lng_report_referredfrom_outdistrict");
xlsWriteString(1,8,"$lng_report_selfreferred_indistrict");
xlsWriteString(1,10,"$lng_report_selfreferred_outdistrict");
xlsWriteString(2,6,"$lng_report_referredfrom_new");
xlsWriteString(2,7,"$lng_report_referredfrom_new");
xlsWriteString(2,8,"$lng_report_selfreferred_new");
xlsWriteString(2,9,"$lng_report_selfreferred_old");
xlsWriteString(2,10,"$lng_report_selfreferred_new");
xlsWriteString(2,11,"$lng_report_selfreferred_old");
xlsWriteNumber(3,0,"$searchresultsage04");
xlsWriteNumber(3,1,"$searchresultsage514");
xlsWriteNumber(3,2,"$searchresultsage1549");
xlsWriteNumber(3,3,"$searchresultsage50");
xlsWriteNumber(3,4,"$searchresultssexm");
xlsWriteNumber(3,5,"$searchresultssexf");
xlsWriteNumber(3,6,"$searchresultsreferredfrom01");
xlsWriteNumber(3,7,"$searchresultsreferredfrom02");
xlsWriteNumber(3,8,"$searchresultsselfreferred03");
xlsWriteNumber(3,9,"$searchresultsselfreferred04");
xlsWriteNumber(3,10,"$searchresultsselfreferred05");
xlsWriteNumber(3,11,"$searchresultsselfreferred06");
xlsEOF();
exit();
创建XLS函数如下所示:
And the create XLS functions look like this:
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteString( $Row , $Col , $Value ) {
$L = strlen( $Value );
echo pack( "sssss" , 0x204 , 8 + $L , $Row , $Col , 0x0 , $L );
echo $Value;
return;
}
问题编号. 1: 在Excel工作表中，我要显示的文本周围会有几个不需要的特殊字符(也许是因为我使用的是UTF-8?(试图先对utf8_decode字符串；没有帮助))也许是因为我使用的是Excel 2007年?)
Problem Nr. 1: In the Excel Sheet there will be several unwanted special characters around the text that I want to display (Maybe because I'm using UTF-8? (tried to utf8_decode the string first; didn't help)Maybe because I'm using Excel 2007?)
问题编号. 2: 值未写入我希望它们包含的行/列中.
Problem Nr. 2: The Values are not getting written into the row/column I want them to be in.
结果看起来像这样(特殊字符由stackoverflow编辑器自动删除，因此我将它们替换为Xs):
The result looks like this (the special chars get automatically removed by the stackoverflow editor, so I replace them with Xs):
XXXXXXXAgeXXXXXSexXXXX
引用自XXXX
自我推荐的XXX 0-4年XXX
5-14年XXXX 15-49年XXX X
= 50年XXX XX男性XXX XX女性XXXX XX在区域XXX XX外出区域XXXX XX在区域XXX
XOut DistrictXXXXXXNewXXXXXXNewXXXXXXNewXXXXXOldXXXX
XNewXXXXXXOldXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXX
XXXXXXXAgeXXXXXSexXXXX
Referred FromXXXX
Self ReferredXXX 0-4 YearsXXX
5-14 YearsXXXX 15-49 YearsXXX X
=50 YearsXXX XXMaleXXX XXFemaleXXXX XXIn DistrictXXX XXOut DistrictXXXX XXIn DistrictXXX
XOut DistrictXXXXXXNewXXXXXXNewXXXXXXNewXXXXXOldXXXX
XNewXXXXXXOldXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXX
所有这些数据都在A列和1-8行中.
All this data is in column A and in rows 1-8.
我想知道的另一点是: 如何格式化单元格(边框，字体等)?
One more point I would like to know, once it works, is: How can I format the cells (Borders, Fonts, etc)?
谢谢.
推荐答案
作为对您的问题的间接解决方案，我建议在 http://phpexcel.codeplex.com/.
As an indirect solution to your question, I'd recommend the classes at http://phpexcel.codeplex.com/.
我过去使用它的效果很好.学习曲线还不错，文档还可以，它是一个小包装，在论坛中提供了很好的支持，而且我有把握确保它可以回答您提出的所有问题.
I've used it in the past with good results. The learning curve isn't too bad, the documentation is OK, it's a small package, it has good support in the forums, and I'm reasonably sure it can answer everything you asked.
这篇关于PHP导出到Excel会导致格式错误和不必要的迹象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
