CODEWITHSUNDEEP
phpsqlexcel
law
law

Reputation: 23

SQL queries results write to Excel

OK so I have no Idea how to do this except install PHPexcel

But say I have the results of a query in an array

$mysqli = new mysqli('server', 'user1', 'pass', 'users');
if ($mysqli->connect_error)
     die(connect error ('.$mysqli->connect_error.') '.$mysqli->connect_error);

$date = date("Y-m-d");
$query = "SELECT * from usr WHERE date = '$date'";
while($row = $query->fetch_array()) {
     //EXCEL STUFF???
}

And then how would I write this to an excel spreadsheet? Any help appreciated

Upvotes: 0

Views: 1596

Answers (2)

M I
M I

Reputation: 3682

you can use PHPExcel for writing to or reading from an Excel sheet.

Link: https://github.com/PHPOffice/PHPExcel

Here is example code for reading/writing to Excel

http://phpexcel.codeplex.com/wikipage?title=Examples

//Minimum sample code from site:

include 'PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();
// Add some data

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Hello');

// Save Excel 2007 file

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

Upvotes: 0

Peon
Peon

Reputation: 8020

1st: you have a syntax error:

$mysqli = new mysqli('server', 'user1', 'pass', 'users');
if ($mysqli->connect_error)
     die('Connect Error: ' . $mysqli->connect_error);

$date = date("Y-m-d");
$query = "SELECT * from usr WHERE date = '$date'";

while($row = $query->fetch_array()) {
     //EXCEL STUFF???
}

2nd, you can output it as a normal table and add this in the header:

header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=abc.xls");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

This script will automatically allow you to download a file named abc.xls with your content.

Upvotes: 2

Related Questions