0

I am having problems in sending data from view to my phpexcel function via ajax. I tried the following the solutions "PHPExcel download using ajax call" but seems like I am still missing something. I add a console log to see if the data are transferred and yes it has but it only prints the title and not the contents from the database. Please help... Here is the code of my ajax:

$('#btnExport').click(function(){
var fdate = $('#dateFrom').val();
var tdate = $('#dateTo').val();
var dept = $('#txtdept').val();
var item1 = $('#txtsearch3').val();
var formData;
var page = "<?php echo site_url('tms/exportToExcel');?>";

//formData = $('#formData1').serialize();

if(fdate == "" || tdate == "")
{
    $('#no-dates').modal('show');
}
else if(fdate == "" && tdate == "")
{
    $('#no-dates').modal('show');
}
else
{

    if(dept != "")
    {
        $.ajax({
            type: 'POST',
            url: page,
            data: {dept:dept,fdate:fdate,tdate:tdate},
            success: function(response){
                console.log(response);
                window.open(page,'_blank');
            },
        });
    }

    else if(item1 != "")
    {
        $.ajax({
            type: 'POST',
            url: "<?php echo site_url('tms/exportsolo')?>",
            data: {item1:item1,fdate:fdate,tdate:tdate},
            success: function(){
                window.open(page,'_blank');
            },
        });
    }
    //console.log(dept+" "+fdate+" "+tdate);
}
//window.open(page);

});

this is one of the functions that create excel file:

public function exportToExcel()
{
    //set_time_limit(0);
    $dept = $this->input->post('dept');
    $from = @date('Y-m-d',@strtotime($this->input->post('fdate')));
    $to = @date('Y-m-d',@strtotime($this->input->post('tdate')));

    //echo $dept." ".$from." ".$to;

    $result['excelData'] = $this->DBmodel->get_excelData($dept);

    $this->excel->setActiveSheetIndex(0); // active sheet
    $this->excel->getActiveSheet()->setTitle('Attendance Logs'); // sheet title
    $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setName('Verdana'); // default font
    $this->excel->getActiveSheet()->SetCellValue('B1','EMPLOYEE ATTENDANCE LOGS'); // title
    $this->excel->getActiveSheet()->getPageSetUp()->setRowstoRepeatAtTopByStartAndEnd(1,3); // rows to repeat
    $this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(29.25);
    $this->excel->getActiveSheet()->mergeCells('B1:T1');
    $this->excel->getActiveSheet()->mergeCells('C4:D4');
    $this->excel->getActiveSheet()->mergeCells('E8:F8');

    $this->excel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
    $this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(18);
    $this->excel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('#333');

    $this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(3.14);
    $this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(0.67);
    $this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(10.86);
    $this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(1.43);
    $this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(9.71);
    $this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(1.43);
    $this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(10.7);
    $this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(1.57);
    $this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(2);
    $this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(9);
    $this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(0.58);
    $this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(0.92);
    $this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(9);
    $this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(1.71);
    $this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(2.43);
    $this->excel->getActiveSheet()->getColumnDimension('P')->setWidth(6.14);
    $this->excel->getActiveSheet()->getColumnDimension('Q')->setWidth(3);
    $this->excel->getActiveSheet()->getColumnDimension('R')->setWidth(9.29);
    $this->excel->getActiveSheet()->getColumnDimension('S')->setWidth(4);
    $this->excel->getActiveSheet()->getColumnDimension('T')->setWidth(0.67);
    $this->excel->getActiveSheet()->getColumnDimension('U')->setWidth(0.67);

    $rowCount = 4;
    foreach($result['excelData'] as $val)
    {
        $rowTitle = $rowCount + 2; // column
        $rowTitle1 = $rowCount + 4; // row
       //data label
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('C'.($rowCount).':D'.($rowCount));
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('C'.$rowCount,'ID No:');

        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('C'.($rowTitle).':D'.($rowTitle));
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('C'.$rowTitle,'Name:');

        $this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('I'.($rowCount).':J'.($rowCount));
        $this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('I'.$rowCount,'Dept:');

        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('C'.($rowTitle1).':D'.($rowTitle1));
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('C'.$rowTitle1,'Section:');

        $this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('I'.($rowTitle1).':J'.($rowTitle1));
        $this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('I'.$rowTitle1,'Line:');

        //data contents
        $this->excel->getActiveSheet()->mergeCells('E'.($rowCount).':G'.($rowCount));
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->SetCellValue('E'.$rowCount,$val['EmpID']);
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setName('Arial');

        $this->excel->getActiveSheet()->mergeCells('E'.($rowTitle).':S'.($rowTitle));
        $this->excel->getActiveSheet()->SetCellValue('E'.$rowTitle,$val['Lastname'] . ', ' . $val['Firstname']);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setName('Arial');

        $this->excel->getActiveSheet()->mergeCells('L'.($rowCount).':S'.($rowCount));
        $this->excel->getActiveSheet()->SetCellValue('L'.$rowCount,$val['Department']);
        $this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setName('Arial');

        $this->excel->getActiveSheet()->SetCellValue('E'.$rowTitle1,$val['Section']);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setName('Arial');

        $this->excel->getActiveSheet()->SetCellValue('L'.$rowTitle1,$val['Line']);
        $this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setSize(12);
        $this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setName('Arial');
        $rowCount++;

        $result['exceltime'] = $this->DBmodel->get_excelTime($val['EmpID'],$from,$to);

        $titleRow = $rowCount + 5;
        $rowCount += 6;

        foreach($result['exceltime'] as $val)
        {
            if($val['TimeIn'] == "00:00:00")
        {
            $TimeIn = "";
        }
        else
        {
            $TimeIn = $val['TimeIn'];
        }

        if($val['LunchOut']=="00:00:00")
        {
            $LunchOut = "";
        }
        else
        {
            $LunchOut = $val['LunchOut'];
        }

        if($val['LunchIn']=="00:00:00")
        {
            $LunchIn = "";
        }
        else
        {
            $LunchIn = $val['LunchIn'];
        }

        if($val['TimeOut']=="00:00:00")
        {
            $TimeOut = "";
        }
        else
        {
            $TimeOut = $val['TimeOut'];
        }

        if($val['RNDOUT']=="00:00:00")
        {
            $Adjusted = "";
        }
        else
        {
            $Adjusted = $val['RNDOUT'];
        }
        $this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setSize(11);
        $this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('C'.$titleRow,'Date');

        $this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setSize(11);
        $this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('E'.$titleRow,'TimeIn');

        $this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->mergeCells('G'.($titleRow).':H'.($titleRow));
        $this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getAlignment()->setWrapText(true);
        $this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setSize(11);
        $this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('G'.$titleRow,'LunchOut');

        $this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setSize(11);
        $this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('J'.$titleRow,'LunchIn');

        $this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setBold(true);
        $this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setSize(11);
        $this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('M'.$titleRow,'TimeOut');          

        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setSize(10);
        $this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('C'.$rowCount,$val['dt']);

        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setSize(10);
        $this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('E'.$rowCount,$TimeIn);

        $this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getFont()->setSize(10);
        $this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('G'.$rowCount,$LunchOut);

        $this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getFont()->setSize(10);
        $this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('J'.$rowCount,$LunchIn);

        $this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getFont()->setSize(10);
        $this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getFont()->setName('Arial');
        $this->excel->getActiveSheet()->SetCellValue('M'.$rowCount,$TimeOut);

        $rowCount++;
        }
        $rowCount+=1;
        $newRow = $this->excel->getActiveSheet()->getHighestRow();
        $this->excel->getActiveSheet()->setBreak('A'.$newRow,PHPExcel_WorkSheet::BREAK_ROW);
    }
    $excelWriter = PHPExcel_IOFactory::createWriter($this->excel,'Excel2007');
    header('Content-Type:application/vnd.ms-excel');
    header('Content-Disposition:attachment;filename="'.$dept.'.xlsx"');
    header('Cache-Control:max-age=0');
    ob_end_clean();
    $excelWriter->save('php://output');
}

I don't know if there is need to revise this because it's working 100% when I don't use ajax......

Community
  • 1
  • 1
SilverRay
  • 331
  • 2
  • 7
  • 23
  • What you return after ajax call? – Ali Jun 01 '16 at 08:14
  • If you're using Ajax, then it's up to the ajax success handler to handle the data stream that it gets back from the server, the browser cannot do that automatically, because using ajax you're overriding the browsers native handling of responses..... it's far easier to have a link to the export – Mark Baker Jun 01 '16 at 08:14
  • To answer Ali's question: It returns nothing, no excel file is being downloaded so I added a console log to monitor the process and it does create the excel file but the ajax only opens the url that I put and not downloading the file...... – SilverRay Jun 01 '16 at 08:16
  • To answer Mark Baker comments: At first I used direct link as you said but it conflicts in my search box function and so I am pushed to pass the data using ajax..... – SilverRay Jun 01 '16 at 08:17
  • Then your ajax needs to handle the response, you should be getting a stream of binary data back from the request – Mark Baker Jun 01 '16 at 08:28
  • Yes that I received in console log but no document is downloaded.... – SilverRay Jun 01 '16 at 08:29
  • Ok I changed the page variable to be equal to the url and it now starts to download the document but the problem now is that it only prints the title and not the contents..... – SilverRay Jun 01 '16 at 08:40
  • That stream of data ___is___ the document being downloaded to the browser; but if yu're using ajax, then ___your success handler___ then needs to handle receiving that stream of data and writing it to file on the client PC.... using ajax is telling the browser that you'll accept responsibility for doing that because you're overriding the browsers normal behaviour.... that's why it's so much easier providing a link, because then you're telling the browser to do that for you – Mark Baker Jun 01 '16 at 10:35
  • I am just using ajax to pass the data from the client to the server not to write a data to the client side..... please check my code... – SilverRay Jun 02 '16 at 00:21

2 Answers2

1

Not using ajax but your could try changing the "action" attribute of your form and using form submit. The user won't be redirected but asked to download the file based on the headers received back from php. 

$('#btnExport').click(function(){
var fdate = $('#dateFrom').val();
var tdate = $('#dateTo').val();
var dept = $('#txtdept').val();
var item = $('#txtsearch3').val();
var form = $('#yourFromId');

if(fdate == "" || tdate == "")
{
    $('#no-dates').modal('show');
}
else if(fdate == "" && tdate == "")
{
    $('#no-dates').modal('show');
}
else
{
    if(dept != "" && item == "")
    {
        // update form fields if needed
        form.attr('action', "<?php echo site_url('tms/exportToExcel');?>");
        form.submit();
    }
    else if(dept == "" && item != "")
    {
        // update form fields if needed
        form.attr('action', "<?php echo site_url('tms/exportsolo');?>");
        form.submit();
    }
}
});
StaticBeagle
  • 5,070
  • 2
  • 23
  • 34
  • I am sorry but I cannot use your code first because it will conflict in my search function second is I tried already and that really conflicts to my other buttons because you have to put almost data fields inside a form and it destroys also my layout that is why I want to ajax because I can only pick data from specific element and not the whole page.... – SilverRay Jun 01 '16 at 08:45
  • Ok cool sorry I couldn't help. I'm looking forward to the answer of this question. – StaticBeagle Jun 01 '16 at 08:49
  • You could help me though because I already solved the problem regarding the ajax don't download the document but now the problem is that it only prints the title page and not the contents of the database... – SilverRay Jun 01 '16 at 08:52
  • If you try submitting your form url manually in a new tab, do the contents of the downloaded file look ok? – StaticBeagle Jun 01 '16 at 08:57
  • I remove all of my forms and getting all the data from ajax request via the elements id..... – SilverRay Jun 01 '16 at 08:58
  • I see . In that case you could try creating a dummy excel file and populating a couple of cells with dummy values and see if the contents look ok. If the problem is not on the excel file you could try sending back the results of your queries back and either log them in the console or use fiddler to see what's coming back. I'm tempted to presume that issue is now in the backend. – StaticBeagle Jun 01 '16 at 09:08
  • Yes it seems that the data that I pass to controller for database query criteria does not reach but when I tried to echo the data that has been passed, it was there only it does not proceed to process the database... – SilverRay Jun 02 '16 at 00:59
  • I tried your code and this the console log shows jquery.js:5039 Resource interpreted as Document but transferred with MIME type application/vnd.ms-excel: "http://localhost/tms/index.php/tms/exportToExcel?txtdept=HR&txtfdate1=05%2F24%2F2016&txtTdate1=05%2F27%2F2016". – SilverRay Jun 02 '16 at 01:36
0

I solved my problem by tweaking a little bit of RatHat code answer and this is what saves me:

$('#btnExport').click(function(){
var fdate = $('#dateFrom').val();
var tdate = $('#dateTo').val();
var dept = $('#txtdept').val();
var item = $('#txtsearch3').val();
var form1 = $('#formData1');
var form2 = $('#formData2');

$('#txtdept').val(dept);
$('#txtfdate1').val(fdate);
$('#txtTdate1').val(tdate);

$('#txtItem').val(item);
$('#txtfdate2').val(fdate);
$('#txtTdate2').val(tdate);   



if(fdate == "" || tdate == "")
{
    $('#no-dates').modal('show');
}
else if(fdate == "" && tdate == "")
{
    $('#no-dates').modal('show');
}
else
{
    if(dept != "")
    {
        form1.attr('action',"<?php echo site_url('tms/exportToExcel');?>");
        form1.attr('method','POST');
        form1.submit();
    }
    else if(item != "")
    {
        form2.attr('action',"<?php echo site_url('tms/exportsolo');?>");
        form2.attr('method','POST');
        form2.submit();
    }
}
});

Thanks to all who leave comments to my post....

SilverRay
  • 331
  • 2
  • 7
  • 23