通过post将数据发送到url，使用javascript导出到excel

Question

I have a reporting website that I use DataTables Server Side Processing on. Everything works great except that I need to be able to export the whole data set and not just the part that is showing on the screen. I have reports that have 10,000+ rows and 65+ columns so showing the whole report on the page is out of the question (would take more than 5 minutes and then time out). I've gotten really close to an answer, I think, but need help getting the rest of the way. Here's what I've got:

I'm collecting the data that I need sending it to a file that uses PHPExcel libraries to export an Excel file.

When I navigate to the file (ExportAllToExcel.php) it works fine, but when I use a button to send the data to the file there is no download. Here's what I've got going right now:

$.fn.dataTable.ext.buttons.export =
{
    className: 'buttons-alert',
    id: 'ExportButton',
    text: "Export All Test III",
    action: function (e, dt, node, config)
    {
        var SearchData = dt.rows({ filter: 'applied' }).data();
        var OrderData = dt.order();
        var NumRow = SearchData.length;
        var SearchData2 = [];
        for (j = 0; j < NumRow; j++)
        {
            var NewSearchData = SearchData[j];
            for (i = 0; i < NewSearchData.length; i++)
            {
                NewSearchData[i] = NewSearchData[i].replace("<div class='Scrollable'>", "");
                NewSearchData[i] = NewSearchData[i].replace("</div>", "");
            }
            SearchData2.push([NewSearchData]);
        }
        for (i = 0; i < SearchData2.length; i++)
        {
            for (j = 0; j < SearchData2[i].length; j++ )
            {
                SearchData2[i][j] = SearchData2[i][j].join('::');
            }
        }
        SearchData2 = SearchData2.join("%%");

        //var SendPageData = new XMLHttpRequest();
        //SendPageData.open("POST", "./ExportAllToExcel.php", true);
        //SendPageData.send('{NumRow=' + NumRow + '},{SearchData=' + SearchData2 + '}');
        $.post('./ExportAllToExcel.php',{SearchData: SearchData2,NumRow: NumRow});
        window.location.href = './ExportAllToExcel.php';
    }
};

This doesn't work. The $.POST sends the data and gets a response, but does not export the file.

The Window.location goes to the file and exports to Excel but doesn't have the data from $_POST so the file only has headers.

And the SendPageData does the same as the $.POST sends the data and gets a response, but doesn't create the file.

And here's the ExportAllToExcel.php:

<?php
require $_SERVER['DOCUMENT_ROOT'].'/dev/Location/Helper/PageName.php';              //Pulls the Page name and Table name and returns the $SQLTableName, $TableName, $Title, $Page and $HeadingDesc
include $_SERVER['DOCUMENT_ROOT'].'/dev/Location/DBConn.php';                       //DB connection info

$headings = array();                        //Create the empty array for use later and so that it won't throw an error if not assinged later
$hsql = "select Headings from TableHeadings where TableName = '$TableName' order by Id";    //Get all the column headers from the TableHeadings table in SQL

$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);         //Count how many columns that there will be
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
    $headings[$row] = $rHeadings[$row]["Headings"];     //fill the array of column headings for use in creating the DataTable
}
print_r($headings);

// Error reporting
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

// Add some data
$ColumnArray = 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','AY','AZ');
//$HeadingArray = array('Year','Quater','Country','Sales');
$HeadingArray = $headings;

$primaryKey = 'id';
$table = $SQLTableName;                  
$request = $_POST;
$dataArray  = array();
$dataArraystr = explode('%%',$_POST['SearchData']);

foreach($dataArraystr as $ArrayStr)
{
    $dataArray[] = explode('::',$ArrayStr);
}

// Include PHPExcel 
require_once dirname(__FILE__) . './Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Michael McNair")
                             ->setLastModifiedBy("Michael McNair")
                             ->setTitle($TableName)
                             ->setSubject($TableName)
                             ->setDescription("Report for " .$TableName. " using PHPExcel, generated using PHP classes.")
                             ->setKeywords("office PHPExcel php " . $TableName)
                             ->setCategory("Report Export File");

$objPHPExcel->getActiveSheet()->fromArray($HeadingArray, NULL, 'A1');
$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2');

$CountOfArray = count($HeadingArray);
// Set title row bold
$objPHPExcel->getActiveSheet()->getStyle('A1:' .$ColumnArray[$CountOfArray-1]. '1')->getFont()->setBold(true);

// Set autofilter
// Always include the complete filter range!
// Excel does support setting only the caption
// row, but that's not a best practise...
$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

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

// Add a second sheet, but infront of the existing sheet
//$myWorkSheet = new PHPExcel_Worksheet($objPHPExcel,'New Worksheet');
//$objPHPExcel->addSheet($myWorkSheet,0);


// 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 (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="ExportAllToExcelTest.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
///$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
ob_clean();
$objWriter->save('php://output');
?>

Answer 1

I have fixed the problem. This is the button now:

$.fn.dataTable.ext.buttons.export =
{
    className: 'buttons-alert',
    id: 'ExportButton',
    text: "Export All To Excel",
    action: function (e, dt, node, config)
    {
        window.location.href = './ServerSide.php?ExportToExcel=Yes';
    }
};

I use just the one $_GET and send that to my ServerSide.php file, the same file that gets the data for the browser, to begin with. There I now check for that and use my KeepPost.php file to keep the filtering and sorting that the user has placed on the report:

<?php

    if( isset($_POST['draw']))
        {
            include 'DBConn.php';
            //echo "Here";
            //print_r($_POST);
            $KeepPost = $_POST;    
            $KeepPost['length'] = -1;
            $PostKept = serialize($KeepPost);
            $TSQL = "UPDATE PostKept set Value = '" .$PostKept. "'";
            $sth = $conn->prepare($TSQL);
            //print_r($sth);
            $sth->execute();
        }
?>

Then in ServerSide.php I check for the $_GET['ExportToExcel']:

if (isset($_GET['ExportToExcel']) && $_GET['ExportToExcel'] == 'Yes')
{
    $GetSQL = "Select Value from PostKept";
    $KeepResult = $conn->query($GetSQL);
    $KeepResults = $KeepResult->fetchALL(PDO::FETCH_ASSOC);

    //print_r($KeepResults);
    error_log(date("Y/m/d h:i:sa")." KeepResults:  " .$KeepResults[0]['Value']. "
",3,"C:\Temp\LogPHP.txt");
    //findSerializeError($_COOKIE['KeepPost']);
    //print_r($_COOKIE);
    $request = unserialize($KeepResults[0]['Value']);
    //echo "<br>Request: "; print_r($request);

    $DataReturn = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader,1));
    //echo "DataReturn:<br>"; print_r($DataReturn);
    require './ExportAllToExcel.php';
}

This then sends the correct data to the ExportAllToExcel.php file and exports the data the users wants.