export data to xls file (not csv file) from mysql table

[stevebateman]

Hello everyone!

Hope all r fine. I need your help.

I want to write code in php to export the data from db table to xls file (not csv file). Let me explain:

Suppose, Table1 has 5 columns (c1, c2, c3, c4, c5) and Table 2 has 3 columns (d1, d2, d3). Now, I want to do two things:

1. Create a xls file (not csv file) and save data of each column of Table1. That is, I want to save the data of entire table in an excel file (.xls format, not .csv format)

2. Create a xls file (not csv file) and save data of c2, c3, c4 (from Table1) and d2, d3 (from Table2).

3. insert data into Table3 from a xls file (not csv file). That is, I want to browse a xls file (not csv file), and after clicking submit button the data of that xls file should get inserted into Table3 of my db.

Note: please help me with the code. please do not refer to any other link.

Thank you.

[NigelRen]

I'm assuming with your comments that you don't want to make any effort with the code, not even using Google to search for some code to start from - in which case I doubt if I can be bothered to give you any help.

Good luck.

[stevebateman]

Well, I have gone through different codes and modified them. but, didn't work at all. i searched google and some other forums. some of them mentioned some link to follow. but, none of them worked. i guess they posted that link without testing themselves. as i have already spent much time on it i asked for the codes, not the links (as most of them were useless)

[stevebateman]

Here is the solution:

<?php
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
}
function xlsWriteLabel($Row, $Col, $Value) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
}

header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=\"export_steve_".date("Y-m-d").".xls\"");
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
header("Expires: 0");

// start exporting
xlsBOF();

// first row
xlsWriteLabel(0, 0, "id");
xlsWriteLabel(0, 1, "name");
xlsWriteLabel(0, 2, "email");

// second row
xlsWriteNumber(1, 0, 230);
xlsWriteLabel(1, 1, "Steve");
xlsWriteLabel(1, 2, "steve@bateman.com");

// third row
xlsWriteNumber(2, 0, 350);
xlsWriteLabel(2, 1, "Bateman");
xlsWriteLabel(2, 2, "bateman@steve.com");

// end exporting
xlsEOF();

// Note: you can use mysql query to fetch row from the table and export the value of the columns "id", "name", "email" from Table.
?>