CODEWITHSUNDEEP
phpmysqlexcelexport-to-excel
Pr0no
Pr0no

Reputation: 4099

Export MySQL data into Excel matrix

I have a MySQL table named "relations", as follows:

from to count
-------------
A    B  456
A    C  233
A    D  463
B    A  766
B    C  215
B    D  142

(In reality, "from" and "to" contain names of people and the table contains 150 rows). I now need to export this table to an excel file (not CSV) in (150x150) matrix form, like this:

  |  A    B    C   D
--+------------------
A |  0   456  233 463
B | 766   0   215 142

I need to do this from within PHP, but don't know how to go about this. Do I first create an empty Excel file, naming all the rows and columns? (so like this:)

  |  A    B    C   D
--+------------------
A |
B |

If I do this, then how can I connect to the right cell to insert the correct counts? Or do I write the Excel file from within PHP alltogether?

Also, in reality the table is not structured, so it looks something like:

from to
-------
A    C
F    K
F    L
B    Z
M    P
P    A

So I don't think I'm able to write cell-by-cell, row-for-row. Or I should first query the database to sort by column "from" and then by column "to", probably.

Any help to get me started would be greatly appreciated.

Upvotes: 2

Views: 2064

Answers (3)

JuanXarg
JuanXarg

Reputation: 438

Excel will also understand html tables. So you could:

  1. Fetch data
  2. Iterate and build a table just as you would want that to come out in Excel
  3. Set headers to download and MIME types.
  4. Open in Excel (you will get a warning message).

Headers should look something like this:

<?php
[..]
header('Content-Type: application/force-download');
header('Content-Disposition: attachment; filename="'.$filename.'"');
header('Expires: 0');
[..]
?>

Also consider these so that browsers don't cache the file:

if (isset($_SERVER['HTTP_USER_AGENT']) && (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false)) {
  header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  header('Pragma: public');
} else {
  header('Pragma: no-cache');
}

Upvotes: 1

Vyktor
Vyktor

Reputation: 20997

You probably want to use Pear::Spreadsheet_Excel_Writer, they provide good examples here such as:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

// sending HTTP headers
$workbook->send('test.xls');

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('My first worksheet');

// The actual data
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');

Very simple and easy access.

But my personal experience: DON'T USE IT if you need to write lot of data, I was saving 8 columns and 60 000 rows, first 10 000 rows took about 2 minutes, next 10 000 about 45 minutes and after 3 hours I stopped the script before reaching 30 000. I end up using perl variant which took about 3 minutes to complete 60 000 rows.

The irony is that I'm now using CSV import in Excel :)

Upvotes: 1

Alex Chorry
Alex Chorry

Reputation: 334

  • Fetch data from mysql
  • Build an array with fetched data (your db data's order doesnt matter, you can always sort array by key/value whenever you like)
  • Use PHPExcel (or ExcelWriter class, or whatever) to generate your excel file
  • Generate your file iterating your data array col by col, row by row.

Upvotes: 0

Related Questions