How to generate an excel file from an API?

Question

Considering this method

public function exportExcel(Request $request){
    //dd($request);
    $spreadsheet = new \App\Reports\FormReport();
    $spreadsheet->exportReport($request);
    //return response()->json(['message'=>'Success']);
}

where my method exportReport($request) body is something like this:

    $dateTimeNow = Carbon::now();

    $spreadsheet->getActiveSheet()
    ->setCellValue('A1', "This is a test")
    ->setCellValue('A2', "Hello World")
    ->setCellValue('A3', Date::PHPToExcel($dateTimeNow));

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    header('Content-Disposition: attachment;filename="Employee List.xlsx"');

    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    exit;

and my javascript function:

function postRequest(){
    const url = window.location;
    const urlReq = url.origin+"/api/export_excel/exportexcel";
    const token = document.querySelector('meta[name="csrf-token"]').getAttribute('content');
    const list = document.getElementById('lt_employees').value;
    if(list){
      let obj = {
        employees : list,
      };

      console.log(obj);
      const request = new Request(urlReq, {
        method: 'POST',
        body: JSON.stringify(obj),
        headers: new Headers({
            'Content-Type': 'application/json',
            "X-CSRF-TOKEN": token
        })
      });

      //fetch(request).then(res => res.json()).then(res => console.log(res));
    }
  }

My problem is upon clicking the button that triggered my javascript method postRequest() is not generating an excel. What could be the problem here?

https://stackoverflow.com/questions/16086162/handle-file-download-from-ajax-post or https://stackoverflow.com/questions/3499597/javascript-jquery-to-download-file-via-post-with-json-data — Tim Williams, Jan 26 '20 at 07:45

score -1 · Answer 1 · answered Jan 26 '20 at 14:36

I don't know the language you use, but I understand that you initialize an Excel.Application object and then try to use a sheet. Excel is initially empty, there are no Workbooks and therefore there are no sheets. A new workbook must be opened or explicitly added. I am attaching an example in VBA, I hope it is useful

    Public Sub test()

        Dim App As Excel.Application
        Dim Wk As Workbook
        Dim Sh as WorkSheet

        Set App = New Excel.Application 'empty Excel Application
        Set Wk = App.Workbooks.Add ' add a new Workbook
        Set Sh = Wk.Sheets(1)
        ''something else
        Wk.Close 'close the Workbook
        App.Quit 'close Excel
        Set Sh = Nothing
        Set Wk = Nothing
        Set App = Nothing

    End Sub

How to generate an excel file from an API?

1 Answers1