Export MySQL data into Excel matrix

Question

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.

Have you tried exporting excel to txt. format if not you should try it, you can see the the structure of the text then you can simply put it to your DB. — tomexsans
– tomexsans, Commented Feb 7, 2012 at 21:59
When you export to CSV, you can load it in Excel and do a save-as and convert it to an Excel-specific format. — Mr. Llama
– Mr. Llama, Commented Feb 8, 2012 at 16:02

Vyktor · Accepted Answer · 2012-02-07 22:08:03Z

1

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 :)

answered Feb 7, 2012 at 22:08

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pr0no Over a year ago

Thanks, I eventually built a HTML table (as indicated below) to import into Excel. But writing CSV and importing into Excel was also an easy option :) Thanks!

JuanXarg · Accepted Answer · 2012-02-07 22:12:08Z

Excel will also understand html tables. So you could:

Fetch data
Iterate and build a table just as you would want that to come out in Excel
Set headers to download and MIME types.
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');
}

Alex Chorry · Accepted Answer · 2012-02-07 21:57:57Z

0

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.

answered Feb 7, 2012 at 21:57

Alex Chorry

3344 silver badges9 bronze badges

Collectives™ on Stack Overflow

Export MySQL data into Excel matrix

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related