PHP 导出到 XLS

Question

以下代码存在一些问题。

第一个问题是今天它开始打印从服务器收到的重复标头。

第二个问题稍微小一些，它只从数据库中打印最多 39 行，当我请求更多时，它会超时并被中断

    <?
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=iNcard-Export-{$_GET['ids']}.xls ");
header("Content-Transfer-Encoding: binary ");


// Connect database.
mysql_connect("localhost","USERNAME","PASS");
mysql_select_db("DATABASE");

$leaddate = $_GET['date'];
$arr = $_GET['leadstatus'];


// Get data records from table.
$result=mysql_query("select customer.*, admin.firstname as LeadFirstName, admin.lastname as LeadLastName FROM customer_detail as customer LEFT JOIN admin_userlogin as admin ON customer.LeadOwnerId=admin.id WHERE customer_id IN ({$_GET['ids']}) ORDER BY CreatedTime DESC ");


// Functions for export to excel.
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 xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}


xlsBOF();

/*
Make a top line on your excel sheet at line 1 (starting at 0).
The first number is the row number and the second number is the column, both are start at '0'
*/

// Make column labels. (at line 3)
xlsWriteLabel(2,0,"Created.");
xlsWriteLabel(2,1,"Id.");
xlsWriteLabel(2,2,"Lead Owner.");
xlsWriteLabel(2,3,"Membership Status");
xlsWriteLabel(2,4,"Company");
xlsWriteLabel(2,5,"ABN/ACN Number");
xlsWriteLabel(2,6,"Trading Name");
xlsWriteLabel(2,7,"First Name");
xlsWriteLabel(2,8,"Last Name");
xlsWriteLabel(2,9,"Position");
xlsWriteLabel(2,10,"Phone");
xlsWriteLabel(2,11,"Email");
xlsWriteLabel(2,12,"Fax");
xlsWriteLabel(2,13,"Mobile");
xlsWriteLabel(2,14,"Street Address");
xlsWriteLabel(2,15,"City");
xlsWriteLabel(2,16,"State");
xlsWriteLabel(2,17,"Postcode");
xlsWriteLabel(2,18,"Industry");
xlsWriteLabel(2,19,"SubCategories");
xlsWriteLabel(2,20,"Keywords");
xlsWriteLabel(2,21,"MS1");
xlsWriteLabel(2,22,"MS2");
xlsWriteLabel(2,23,"MS3");
xlsWriteLabel(2,24,"MS4");
xlsWriteLabel(2,25,"RCB1");
xlsWriteLabel(2,26,"RCB2");
xlsWriteLabel(2,27,"RCB3");
xlsWriteLabel(2,28,"RCB4");
xlsWriteLabel(2,29,"Hot Offer");
xlsWriteLabel(2,30,"Hot Offer RCB");
xlsWriteLabel(2,31,"Hot Offer Start Date");
xlsWriteLabel(2,32,"Hot Offer Finish Date");
xlsWriteLabel(2,33,"Primary Suburb");
xlsWriteLabel(2,34,"Suburb 2");
xlsWriteLabel(2,35,"Suburb 3");
xlsWriteLabel(2,36,"Suburb 4");
xlsWriteLabel(2,37,"Suburb 5");
xlsWriteLabel(2,38,"Suburb 6");
xlsWriteLabel(2,39,"Terminal ID");
xlsWriteLabel(2,40,"Merchant ID");
xlsWriteLabel(2,41,"Bank Name");
xlsWriteLabel(2,42,"BankAccountName");
xlsWriteLabel(2,43,"BSB Number");
xlsWriteLabel(2,44,"Bank Account Number");
xlsWriteLabel(2,45,"Card Type");
xlsWriteLabel(2,46,"Card Number");
xlsWriteLabel(2,47,"Card Exp");
xlsWriteLabel(2,48,"CVV Number");
xlsWriteLabel(2,49,"Terminal Verified");
xlsWriteLabel(2,50,"iNcard Allocated");
xlsWriteLabel(2,51,"Postal Address");
xlsWriteLabel(2,52,"Postal Suburb");
xlsWriteLabel(2,53,"Postal State");
xlsWriteLabel(2,54,"Postal Postcode");
xlsWriteLabel(2,55,"UserName");
xlsWriteLabel(2,56,"Password");
xlsWriteLabel(2,57,"Notes");

$xlsRow = 3;

// Put data records from mysql by while loop.
while($row=mysql_fetch_array($result)){

if($row['State'])
{ 
$state = $row['State'];
}
else if($row['MailingState'])
{
$states = $row['MailingState'];
}

if($state == 1)
{
$state = "VIC";
}
else if($state == 2)
{
$state = "NSW";
}
else if($state == 3)
{
$state = "QLD";
}
else if($state == 4)
{
$state = "WA";
}
else if($state == 5)
{
$state = "TAS";
}
else if($state == 6)
{
$state = "SA";
}
else if($state == 7)
{
$state = "ACT";
}
else if($state == 8)
{
$state = "NT";
} 

$membershiptype = $row['Membership'];
if($membershiptype == 1)
{
$membershiptype = "Silver";
}
else if($membershiptype == 2)
{
$membershiptype = "Gold";
}
else if($membershiptype == 3)
{
$membershiptype = "Platinum";
}

$Industry = $row['Industry'];
if($Industry == 1)
{
$Industry = "Restaurant & Cafes";
}
else if($Industry == 2)
{
$Industry = "Travel & Leisure";
}
else if($Industry == 3)
{
$Industry = "Health & Beauty";
}
else if($Industry == 4)
{
$Industry = "Home Maker";
}
else if($Industry == 5)
{
$Industry = "Automotive";
}
else if($Industry == 6)
{
$Industry = "Daily Needs";
}
else if($Industry == 7)
{
$Industry = "For The Loved Ones";
}
else if($Industry == 8)
{
$Industry = "Trade Services";
}
else if($Industry == 9)
{
$Industry = "Fashion";
}
else if($Industry == 10)
{
$Industry = "Jewellery";
}
else if($Industry == 11)
{
$Industry = "Sports & Entertainment";
} 

$words = array (
    1 => 'Restaurnant',
    2 => 'Cafe',
    3 => 'Wines',
    4 => 'Bar',
    5 => 'Lounge',
    6 => 'Function',
    7 => 'Catering',
    8 => 'Events',
    9 => 'Air Tickets',
    10 => 'Adventure',
    11 => 'Luggage',
    12 => 'Car Hire',
    13 => 'Accommodation',
    14 => 'Massage',    
    15 => 'Spas',
    16 => 'Hair',
    17 => 'Body',
    18 => 'Wellbeing',
    19 => 'Nutrition',
    20 => 'Therapy',
    21 => 'Cosmetics',
    22 => 'Perfumery',
    23 => 'Audio',
    24 => 'Video Appliances',
    25 => 'Computers',
    26 => 'Furniture',
    27 => 'Buying',
    28 => 'Servicing',
    29 => 'Repair',
    30 => 'Parking',
    31 => 'Tinting',
    32 => 'Tyres',
    33 => 'Performance',
    34 => 'Groceries',
    35 => 'Wines',
    36 => 'Pet Shop',
    37 => 'Baby Products',
    38 => 'Gifts',
    39 => 'Homeware',
    40 => 'Florist',
    41 => 'Cleaning',
    42 => 'Plumbing',
    43 => 'Electricians',
    44 => 'Carpeting',
    45 => 'Landscaping',
    46 => 'Men/Woman -- Formal',
    47 => 'Casual',
    48 => 'Alternative',
    49 => 'Accessories',
    50 => 'Gold',
    51 => 'SIlver',
    52 => 'Diamond',
    53 => 'Costume',
    54 => 'Repair',
    55 => 'Watches',
    56 => 'Outdoor',
    57 => 'Cycling',
    58 => 'Adventure',
    59 => 'Movies',
    60 => 'Bowling',
    61 => 'Camping'
);








$parts = explode(',', $row['SubCategories']);

for ( $i = 0; $i < count($parts); $i++ )
{
    $parts[$i] = $words[ $parts[$i] ];
}

xlsWriteLabel($xlsRow,0,$row['CreatedTime']);
xlsWriteNumber($xlsRow,1,$row['customer_id']);
xlsWriteLabel($xlsRow,2, $row['LeadFirstName']." ".$row['LeadLastName']);
xlsWriteLabel($xlsRow,3,$membershiptype);
xlsWriteLabel($xlsRow,4,$row['Company']);
xlsWriteLabel($xlsRow,5,$row['ABN_ACNNumber']);
xlsWriteLabel($xlsRow,6,$row['TradingName']);
xlsWriteLabel($xlsRow,7,$row['FirstName']);
xlsWriteLabel($xlsRow,8,$row['LastName']);
xlsWriteLabel($xlsRow,9,$row['Position']);
xlsWriteLabel($xlsRow,10,$row['Phone']);
xlsWriteLabel($xlsRow,11,$row['Email']);
xlsWriteLabel($xlsRow,12,$row['Fax']);
xlsWriteLabel($xlsRow,13,$row['Mobile']);
xlsWriteLabel($xlsRow,14,$row['Street']);
xlsWriteLabel($xlsRow,15,$row['City']);
xlsWriteLabel($xlsRow,16,$state);
xlsWriteLabel($xlsRow,17,$row['PostCode']);
xlsWriteLabel($xlsRow,18,$Industry);
xlsWriteLabel($xlsRow,19,$parts[0].", ".$parts[1].", ".$parts[2].", ".$parts[3]);
xlsWriteLabel($xlsRow,20,$row['Keywords']);
xlsWriteLabel($xlsRow,21,$row['MS1']);
xlsWriteLabel($xlsRow,22,$row['MS2']);
xlsWriteLabel($xlsRow,23,$row['MS3']);
xlsWriteLabel($xlsRow,24,$row['MS4']);
xlsWriteLabel($xlsRow,25,$row['RCB1']);
xlsWriteLabel($xlsRow,26,$row['RCB2']);
xlsWriteLabel($xlsRow,27,$row['RCB3']);
xlsWriteLabel($xlsRow,28,$row['RCB4']);
xlsWriteLabel($xlsRow,29,$row['HotOffer1']);
xlsWriteLabel($xlsRow,30,$row['HotOfferRCB']);
xlsWriteLabel($xlsRow,31,$row['StartDate']);
xlsWriteLabel($xlsRow,32,$row['FinishDate']);
xlsWriteLabel($xlsRow,33,$row['PrimarySuburb']);
xlsWriteLabel($xlsRow,34,$row['Suburb2']);
xlsWriteLabel($xlsRow,35,$row['Suburb3']);
xlsWriteLabel($xlsRow,36,$row['Suburb4']);
xlsWriteLabel($xlsRow,37,$row['Suburb5']);
xlsWriteLabel($xlsRow,38,$row['Suburb6']);
xlsWriteLabel($xlsRow,39,$row['TerminalId']);
xlsWriteLabel($xlsRow,40,$row['MerchantId']);
xlsWriteLabel($xlsRow,41,$row['BankName']);
xlsWriteLabel($xlsRow,42,$row['BankAccountName']);
xlsWriteLabel($xlsRow,43,$row['BSBNumber']);
xlsWriteLabel($xlsRow,44,$row['BankACNumber']);
xlsWriteLabel($xlsRow,45,$row['CardType']);
xlsWriteLabel($xlsRow,46,$row['CrCardNumber']);
xlsWriteLabel($xlsRow,47,$row['CardExp']);
xlsWriteLabel($xlsRow,48,$row['CVVNumber']);
xlsWriteLabel($xlsRow,49,$row['TerminalVerified']);
xlsWriteLabel($xlsRow,50,$row['iNcardAllocated']);
xlsWriteLabel($xlsRow,51,$row['PostalPONumber']);
xlsWriteLabel($xlsRow,52,$row['MailingSuburb']);
xlsWriteLabel($xlsRow,53,$row['MailingState']);
xlsWriteLabel($xlsRow,54,$row['MailingPostcode']);
xlsWriteLabel($xlsRow,55,$row['ChosenUserID']);
xlsWriteLabel($xlsRow,56,$row['ChosenPassword']);
xlsWriteLabel($xlsRow,57,$row['Notes']);




$xlsRow++;
}
xlsEOF();



exit();
?>