How to export to excel?

Question

How to export database to excel ? I have table like this :

+---------------------------------------------------------------------------------------+
id |title
----------------------------------------------------------------------------------------
 52 |   Book A  
 54 |   <div editor_id="mce_editor_0" align="justify">Pembayaran Dividen PT. Telkom Tbk. untuk Tahun Buku 2007 Tahap I akan dilaksanakan mulai tanggal&nbsp; 18 Desember 2007 dengan mempergunakan Surat Pemberitahuan Pembayaran Dividen (SPPD) sebagai bukti pembayaran.</div>
+---------------------------------------------------------------------------------------+

And I have script for export like this:

<?php
// nama file

$namaFile = "report.xls";

// Function penanda awal file (Begin Of File) Excel

function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}

// Function penanda akhir file (End Of File) Excel

function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
// Function untuk menulis data (angka) ke cell excel

function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}

// Function untuk menulis data (text) ke cell excel

function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}

// header file excel

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,
    pre-check=0");
//header("Content-Type: application/force-download");
//header("Content-Type: application/octet-stream");
  header("Content-Type: application/x-msdownload");

  // header untuk nama file
  header("Content-Disposition: attachment;
    filename=".$namaFile."");

  //header("Content-Transfer-Encoding: binary ");




  // memanggil function penanda awal file excel
  xlsBOF();

 // ------ membuat kolom pada excel --- //

 // mengisi pada cell A1 (baris ke-0, kolom ke-0)
 xlsWriteLabel(0,0,"ID");               
 xlsWriteLabel(0,1,"TITLE");     

 // koneksi ke mysql

 mysql_connect("localhost", "root", "");
 mysql_select_db("db_lama");

 // query menampilkan semua data

 $query = "SELECT * FROM bf_articles ";
 $hasil = mysql_query($query);

 // nilai awal untuk baris cell
 $noBarisCell = 1;

 // nilai awal untuk nomor urut data
 $noData = 1;

 while ($data = mysql_fetch_array($hasil))
 {
   // menampilkan no. urut data
   xlsWriteNumber($noBarisCell,0,$data['id']);
   xlsWriteLabel($noBarisCell,1,$data['title']);



  // increment untuk no. baris cell dan no. urut data
  $noBarisCell++;
  $noData++;
 }

 // memanggil function penanda akhir file excel
 xlsEOF();
 exit();

 ?>

This is not working, there's an error

file error: data may have been lost

What can i do ? please help me :(

Use `CSV`. I don't think binary handling xls files is such a lightweight work to be done as you are trying. — LS_ᴅᴇᴠ, Sep 17 '13 at 07:50

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

May be your runtime give problem. Otherwise use this link. It's having,

At first:  Try to track where exactly it arrears(row) and check the length of string in cell(should be only < 255)
This module has some nasty moments. By default the limit had been set in constructor of Spreadsheet_Excel_Writer_BIFFwriter($this->_limit = 2080;) ~20kb.
Change it to size what you need.

Other ways: 1) export like,

<?php
$file="demo.xls";
$test="<table  ><tr><td>Cell 1</td><td>Cell 2</td></tr></table>";
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file");
echo $test;
?>

2) get help from this link.

edited May 23 '17 at 12:28

Community

1
1

answered Sep 17 '13 at 04:57

KarSho

5,699
13
45
78

how to set the limit on my script ? – N4ta nata Sep 17 '13 at 06:20
I had try put `set_time_limit(2080)` under `while`, but didnt work :(( – N4ta nata Sep 17 '13 at 06:50

score 0 · Answer 2 · answered Sep 17 '13 at 04:54

Well I normally do this with PHPExcel (http://phpexcel.codeplex.com/), but you can also do this manually by setting headers. If you need formatting, font styles, etc., then use PHPExcel, otherwise make a CSV string and set the content headers to spit out a .xls file and it should read just fine.

You can easily do this as CSV as previously mentioned (although this wasn't your question). If you want to do this as CSV you can use the previous example or here's one I wrote a long time ago:

http://pastebin.ws/8fbfco

How to export to excel?

2 Answers2