정보실

웹학교

정보실

php PHP Export MySql Table to Xls

본문

  1. <?php
  2.  
  3. // DB TABLE Exporter
  4. //
  5. // How to use:
  6. //
  7. // Place this file in a safe place, edit the info just below here
  8. // browse to the file, enjoy!
  9.  
  10. // CHANGE THIS STUFF FOR WHAT YOU NEED TO DO
  11.  
  12. $dbhost = "127.0.0.1:3306";
  13. $dbuser = "XXXXX";
  14. $dbpass = "XXXXX";
  15. $dbname = "XXXXX";
  16. $dbtable = "XXXXX";
  17.  
  18. // END CHANGING STUFF
  19.  
  20.  
  21. // first thing that we are going to do is make some functions for writing out
  22. // and excel file. These functions do some hex writing and to be honest I got
  23. // them from some where else but hey it works so I am not going to question it
  24. // just reuse
  25.  
  26.  
  27. // This one makes the beginning of the xls file
  28. function xlsBOF() {
  29. echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
  30. return;
  31. }
  32.  
  33. // This one makes the end of the xls file
  34. function xlsEOF() {
  35. echo pack("ss", 0x0A, 0x00);
  36. return;
  37. }
  38.  
  39. // this will write text in the cell you specify
  40. function xlsWriteLabel($Row, $Col, $Value ) {
  41. $L = strlen($Value);
  42. echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
  43. echo $Value;
  44. return;
  45. }
  46.  
  47.  
  48.  
  49. // make the connection an DB query
  50. $dbc = mysql_connect( $dbhost , $dbuser , $dbpass ) or die( mysql_error() );
  51. mysql_select_db( $dbname );
  52. $q = "SELECT * FROM ".$dbtable."";
  53. $qr = mysql_query( $q ) or die( mysql_error() );
  54.  
  55.  
  56. // Ok now we are going to send some headers so that this
  57. // thing that we are going make comes out of browser
  58. // as an xls file.
  59. //
  60. header("Pragma: public");
  61. header("Expires: 0");
  62. header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  63. header("Content-Type: application/force-download");
  64. header("Content-Type: application/octet-stream");
  65. header("Content-Type: application/download");
  66.  
  67. //this line is important its makes the file name
  68. header("Content-Disposition: attachment;filename=export_".$dbtable.".xls ");
  69.  
  70. header("Content-Transfer-Encoding: binary ");
  71.  
  72. // start the file
  73. xlsBOF();
  74.  
  75. // these will be used for keeping things in order.
  76. $col = 0;
  77. $row = 0;
  78.  
  79. // This tells us that we are on the first row
  80. $first = true;
  81.  
  82. while( $qrow = mysql_fetch_assoc( $qr ) )
  83. {
  84. // Ok we are on the first row
  85. // lets make some headers of sorts
  86. if( $first )
  87. {
  88. foreach( $qrow as $k => $v )
  89. {
  90. // take the key and make label
  91. // make it uppper case and replace _ with ' '
  92. xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
  93. $col++;
  94. }
  95.  
  96. // prepare for the first real data row
  97. $col = 0;
  98. $row++;
  99. $first = false;
  100. }
  101.  
  102. // go through the data
  103. foreach( $qrow as $k => $v )
  104. {
  105. // write it out
  106. xlsWriteLabel( $row, $col, $v );
  107. $col++;
  108. }
  109. // reset col and goto next row
  110. $col = 0;
  111. $row++;
  112. }
  113.  
  114. xlsEOF();
  115. exit();
  116. ?>
  • 트위터로 보내기
  • 페이스북으로 보내기
  • 구글플러스로 보내기
  • 카카오톡으로 보내기

페이지 정보

조회 73회 ]  작성일18-08-30 13:31
php

웹학교