How to export a dataset to excel

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 895 Views - Last Post: 06 June 2019 - 10:16 AM Rate Topic: -----

#1 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

How to export a dataset to excel

Posted 29 May 2019 - 11:01 AM

If I have a simple SELECT * query that outputs all of the records from a database table, how could I add functionality to export those records to an excel worksheet?
Is This A Good Question/Topic? 0
  • +

Replies To: How to export a dataset to excel

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15114
  • View blog
  • Posts: 60,481
  • Joined: 12-June 08

Re: How to export a dataset to excel

Posted 29 May 2019 - 11:15 AM

You could use the php write file options, and write each line out into a .CSV (comma separate values).

https://www.php.net/...ut-contents.php

https://www.php.net/...ion.fputcsv.php

https://www.w3school...tem_fputcsv.asp
Was This Post Helpful? 1
  • +
  • -

#3 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 29 May 2019 - 11:30 AM

Thanks!

As of now, I am using the php file intangent with ajax. When I define the file I would use (the php file). Am I able to use the variable from the file?

IE:

$project_rows

Not knowing much about this, it seems the third link from w3 fits the best. So based on my original code (under the following) would this be correct?

<?php

$file = fopen("list.csv","w");

foreach ($project_rows as $project_row)
  {
  fputcsv($file,explode(',',$project_row));
  }

fclose($file); ?>


try {
	$con = getConfig('pdo');
	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$projects_sql = "
		SELECT *
		FROM project_gallery
		WHERE category = '".$category."'
	";

	if ($projects_stmt = $con->prepare($projects_sql)) {
		$projects_stmt->execute();
		$project_rows = $projects_stmt->fetchAll(PDO::FETCH_ASSOC);
		$proj_arr = array();
		foreach ($project_rows as $project_row) {
			$project_img = $project_row['p_img'];
			$project_alt = $project_row['p_alt'];
			$project_display_img = '<img src="https://mbkit.com/img/'.$project_img.'" alt="'. $project_alt .'">';
			$project_title = $project_row['p_name'];
			$html = '';
			$html .= '<div class="projectCont" data-current="'.$category.'">';
			$html .= $project_display_img;
			/*$html .= sprintf(
				'<img class="home-comment-profile-pic" src="%s">',
				empty($row['img']) ? 'profile_images/default.jpg' : $row['img']
			);*/
			$html .= '<div class="projectInfo">';
			$html .= '<span class="projectTitle">' . $project_title . '</span>';
			$html .= '</div>';
			$html .= '</div>';
			$data = array('id' => $project_row['id'], 'date' => $project_row['date_added'], 'html' => $html);
			$proj_arr[] = $data;
		}
	}
    echo json_encode($proj_arr);
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15114
  • View blog
  • Posts: 60,481
  • Joined: 12-June 08

Re: How to export a dataset to excel

Posted 29 May 2019 - 11:32 AM

What happens when you try it? ;)
Was This Post Helpful? 0
  • +
  • -

#5 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 29 May 2019 - 11:40 AM

Touche.

So, I want to keep the base functionality of showing the list of items. I was planning on adding a separate button that would execute the csv function. I would be using the same php file that contains my select query.

What would I need to do with the button via ajax to intern get this csv function to run? I've pretty much only used ajax to send info or to receive it via json.
Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,114
  • Joined: 15-January 14

Re: How to export a dataset to excel

Posted 29 May 2019 - 02:44 PM

There's nothing special with ajax. It's like any other browser request as far as PHP is concerned. The browser sends a request, either POST or GET (or others), for a specific URL, with whatever HTTP headers, and an optional POST body with data. That's how every browser request is whether it was sent using Javascript or not. PHP can't tell the difference between an ajax request and any other request. It just gets the request and the data, and sends a response.

If you're trying to get people to download the file, you won't use ajax for that. I use a hidden iframe on my page that I either set the src URL for, or create a hidden post form with the iframe as the target and then submit the form. The response from PHP should send an appropriate content-type header that will cause the browser to show the open/save dialog box.
Was This Post Helpful? 0
  • +
  • -

#7 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 30 May 2019 - 07:18 AM

View PostArtificialSoldier, on 29 May 2019 - 02:44 PM, said:

If you're trying to get people to download the file, you won't use ajax for that. I use a hidden iframe on my page that I either set the src URL for, or create a hidden post form with the iframe as the target and then submit the form. The response from PHP should send an appropriate content-type header that will cause the browser to show the open/save dialog box.


You lost me with this part. Why would I use an iframe and not just the ajax request to get the csv to download? Do you have any kind of source or example you could share?
Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,114
  • Joined: 15-January 14

Re: How to export a dataset to excel

Posted 30 May 2019 - 09:48 AM

Quote

Why would I use an iframe and not just the ajax request to get the csv to download?

Well, what do you want to do with the CSV data? Do you want to just display the raw CSV data on the page for someone to copy and paste, or do you want the browser to show the open/save file dialog box? If it's the latter, then you don't use ajax for that. You could get the CSV data and then put a link on the page that they click on to save it, or there are some ways to try to force a download box which may or may not work in various browsers. If you want it to work in all of them, then once you generate the CSV data on the server you send a content-type header to the browser telling it to download the file. Since ajax requests run in the background, trying to send that header with an ajax request isn't going to show anything to the user. That's why I submit a form to a hidden iframe, so that it is a regular browser request that the browser will handle like any other request to download a file.
Was This Post Helpful? 1
  • +
  • -

#9 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 30 May 2019 - 11:34 AM

Gotcha. Ideally once the "Export" button is clicked, I would want a Excel download box to be generated in the browser.
Was This Post Helpful? 0
  • +
  • -

#10 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,114
  • Joined: 15-January 14

Re: How to export a dataset to excel

Posted 30 May 2019 - 12:47 PM

Right, when I do that I use the hidden iframe as a destination for either a post form, or just set the URL src.
Was This Post Helpful? 0
  • +
  • -

#11 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3824
  • View blog
  • Posts: 13,916
  • Joined: 08-August 08

Re: How to export a dataset to excel

Posted 30 May 2019 - 08:09 PM

If you want to create files in the Excel format you might try this:

https://phpspreadshe...s.io/en/latest/
https://github.com/P.../PhpSpreadsheet
Was This Post Helpful? 0
  • +
  • -

#12 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 03 June 2019 - 12:26 PM

Alright, I have tried this out except for the Excel format part. It wasn't a good first attempt.. at least not with the 5 errors. Most of this I have never done before, so please bear with the ignorance.

Code: (Errors below code)

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$file_name = "subscriber_list.csv";
$file = fopen("subscriber_list.csv","w");
	
try {
	$servername = 'localhost';
	$usernameCon = '';
	$passwordCon = '';
	$con = new PDO('mysql:host='.$servername.';dbname=', $usernameCon, $passwordCon);

	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql_subscribers = "
		SELECT *
		FROM test_notif
		ORDER BY date_subscribed
	";
	$subscriber_stmt = $con->prepare($sql_subscribers);
	$subscriber_stmt->execute();
	$subscriber_rows = $subscriber_stmt->fetchAll(PDO::FETCH_ASSOC);

	foreach ($subscriber_rows as $subscriber_row) {
		fputcsv($file,explode(',',$subscriber_row));
	}

	header("Content-Type: application/pdf");
	header("Content-Disposition: attachment; filename='.$file_name.'");

	fclose($file);
}	
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

?>
<body>
	<form action="" name="csvForm" target="csvIframe">
		<input type="submit" value="Download File">
		<iframe name="csvIframe" src="">
		</iframe>
	</form>
</body>
</html>


Errors:

Quote

Warning: explode() expects parameter 2 to be string, array given in /home4/public_html/csvTest.php on line 25

Warning: fputcsv() expects parameter 2 to be array, null given in /home4/public_html/csvTest.php on line 25

Warning: Cannot modify header information - headers already sent by (output started at /home4/public_html/csvTest.php:25) in /home4/pfarley1/public_html/csvTest.php on line 28

Warning: Cannot modify header information - headers already sent by (output started at /home4/public_html/csvTest.php:25) in /home4/pfarley1/public_html/csvTest.php on line 29


Console Error after I hit the submit button:

Quote

Refused to display 'https://csvTest?' in a frame because it set 'X-Frame-Options' to 'deny'.

Was This Post Helpful? 0
  • +
  • -

#13 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,114
  • Joined: 15-January 14

Re: How to export a dataset to excel

Posted 03 June 2019 - 02:33 PM

You pass the array straight to fputcsv, you're trying to use explode on it first but it's already an array. Also, don't tell the browser it's a PDF with the content-type header, either use octet-stream to force a download box or send an actual CSV or Excel mime type.

The iframe option probably has to do with the default headers that your server sends. You can probably overwrite that by sending your own X-Frame-Options header set to "sameorigin". Either way, I would move the iframe outside of the form.

Also, after you send the headers you need to send the actual file data. You can close the file and then use file_get_contents or readfile to get and/or send the data to the browser.
Was This Post Helpful? 0
  • +
  • -

#14 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 538
  • Joined: 30-April 15

Re: How to export a dataset to excel

Posted 05 June 2019 - 07:05 AM

For the array/explode point, I was trying to follow this model that modi123_1 gave:
https://www.w3school...tem_fputcsv.asp


For the octet-stream are you referring to something like this?

<a href="./file.pdf" id='example' type="application/octet-stream">Example</a>


If so, how would I incorporate a link into this?

Quote

You can probably overwrite that by sending your own X-Frame-Options header set to "sameorigin".

This may sound like a newb question, but how do I sent X-Frame-Option headers?

Quote

Either way, I would move the iframe outside of the form.

Done. No change in functionality.

Quote

Also, after you send the headers you need to send the actual file data. You can close the file and then use file_get_contents or readfile to get and/or send the data to the browser.

I will try to figure out the other things before getting to this part just now.

I just found that when I ran this test last time that it actually did create a subscriber_list.csv file and when I opened that file through file explorer, it forced a download. However, the file didn't contain anything.
Was This Post Helpful? 0
  • +
  • -

#15 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,114
  • Joined: 15-January 14

Re: How to export a dataset to excel

Posted 05 June 2019 - 08:39 AM

Quote

For the octet-stream are you referring to something like this?

I'm talking about the content-type header you're sending. You're claiming the file is a PDF file, I'm saying change it to a generic octet-stream type.

Quote

This may sound like a newb question, but how do I sent X-Frame-Option headers?

You send any header using the header function.

Quote

However, the file didn't contain anything.

Your issues with explode and fputcsv mean nothing is being written to the file.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2