PHP Export CSV file: Specify encoding export with CSV file import and Export class

Source: Internet
Author: User
Tags export class mysql query
  1. /*
  2. * PHP code to export MySQL data to CSV
  3. *
  4. * Sends the result of a MySQL query as a CSV file for download
  5. * Easy-to-convert to UTF-8.
  6. */
  7. /*
  8. * Establish database connection
  9. */
  10. $conn = mysql_connect (' localhost ', ' login ', ' pass ') or Die (Mysql_error ());
  11. mysql_select_db (' database_name ', $conn) or Die (Mysql_error ($conn));
  12. mysql_query ("SET NAMES CP1252");
  13. /*
  14. * Execute SQL query
  15. */
  16. $query = sprintf (' SELECT field1,field2 from table_name ');
  17. $result = mysql_query ($query, $conn) or Die (Mysql_error ($conn));
  18. /*
  19. * Send response headers to the browser
  20. * Following headers instruct the browser to treat the data as a CSV file called Export.csv
  21. */
  22. Header (' content-type:text/csv; charset=cp1252 ');
  23. Header (' content-disposition:attachment;filename=output.csv ');
  24. /*
  25. * Output header row (if atleast one row exists)
  26. */
  27. $row = Mysql_fetch_assoc ($result);
  28. if ($row) {
  29. Echocsv (Array_keys ($row));
  30. }
  31. /*
  32. * Output data rows (if atleast one row exists)
  33. * * Export CSV file
  34. while ($row) {
  35. Echocsv ($row);
  36. $row = Mysql_fetch_assoc ($result);
  37. }
  38. /*
  39. * echo the input array as CSV data maintaining consistency with most CSV implementations
  40. *-Uses double-quotes as enclosure when necessary
  41. *-Uses double double-quotes to escape double-quotes
  42. *-Uses CRLF as a line separator
  43. */
  44. function Echocsv ($fields)
  45. {
  46. $separator = ";
  47. foreach ($fields as $field) {
  48. if (Preg_match ('/\\r|\\n|,| ' /', $field)) {
  49. $field = ' "'. Str_replace (' "', '" ' "', $field) ';
  50. }
  51. Echo $separator. $field;
  52. $separator = ', ';
  53. }
  54. echo "\ r \ n";
  55. }
  56. ?>
Copy Code

Second, PHP Import and export the CSV file class

PHP implements the import and export classes of CSV files. Code:

  1. /**
  2. * CSV file Processing class
  3. */
  4. Class csv{
  5. Public $csv _array; CSV array data
  6. Public $csv _str; CSV file data
  7. Public function __construct ($param _arr, $column) {
  8. $this->csv_array = $param _arr;
  9. $this->path = $path;
  10. $this->column = $column;
  11. }
  12. /**
  13. * Export
  14. * */
  15. Public Function export () {
  16. if (Empty ($this->csv_array) | | empty ($this->column)) {
  17. return false;
  18. }
  19. $param _arr = $this->csv_array;
  20. unset ($this->csv_array);
  21. $export _str = Implode (', ', $param _arr[' nav '). " n ";
  22. unset ($param _arr[' nav ');
  23. Assembly data
  24. foreach ($param _arr as $k = + $v) {
  25. foreach ($v as $k 1=> $v 1) {
  26. $export _str. = Implode (', ', $v 1). " n ";
  27. }
  28. }
  29. Export the $export_str
  30. Header ("Cache-control:public");
  31. Header ("Pragma:public");
  32. Header ("Content-type:application/vnd.ms-excel");
  33. Header ("Content-disposition:attachment;filename=txxx.csv");
  34. Header (' Content-type:application/octet-stream ');
  35. Ob_start ();
  36. $file _str= iconv ("Utf-8", ' GBK ', $export _str);
  37. Ob_end_clean ();
  38. echo $export _str;
  39. }
  40. /**
  41. * Import
  42. * */
  43. Public Function Import ($path, $column = 3) {
  44. $flag = flase;
  45. $code = 0;
  46. $msg = ' not processed ';
  47. $filesize = 1; 1MB
  48. $maxsize = $filesize * 1024 * 1024;
  49. $max _column = 1000;
  50. Detects if a file exists
  51. if ($flag = = = Flase) {
  52. if (!file_exists ($path)) {
  53. $msg = ' file does not exist ';
  54. $flag = true;
  55. }
  56. }
  57. Detecting file formats
  58. if ($flag = = = Flase) {
  59. $ext = Preg_replace ("/.*." ( [^.] +)/"," $ ", $path);
  60. if ($ext! = ' csv ') {
  61. $msg = ' Import only csv format file ';
  62. $flag = true;
  63. }
  64. }
  65. Detecting File size
  66. if ($flag = = = Flase) {
  67. if (filesize ($path) > $maxsize) {
  68. $msg = ' The imported file must not exceed '. $maxsize. ' b file ';
  69. $flag = true;
  70. }
  71. }
  72. Read file
  73. if ($flag = = flase) {
  74. $row = 0;
  75. $handle = fopen ($path, ' R ');
  76. $dataArray = Array ();
  77. while ($data = Fgetcsv ($handle, $max _column, ",")) {
  78. $num = count ($data);
  79. if ($num < $column) {
  80. $msg = ' file does not conform to specifications true to have: '. $num. ' Column data ';
  81. $flag = true;
  82. Break
  83. }
  84. if ($flag = = = Flase) {
  85. for ($i =0; $i <3; $i + +) {
  86. if ($row = = 0) {
  87. Break
  88. }
  89. Build data
  90. $dataArray [$row] [$i] = $data [$i];
  91. }
  92. }
  93. $row + +;
  94. }
  95. }
  96. return $dataArray;
  97. }
  98. }
  99. $param _arr = Array (
  100. ' Nav ' =>array (' username ', ' password ', ' email '),
  101. Array (0=>array (' xiaohai1 ', ' 123456 ', ' xiaohai1@jbxue.com '),
  102. 1=>array (' Xiaohai2 ', ' 213456 ', ' xiaohai2@jbxue.com '),
  103. 2=>array (' Xiaohai3 ', ' 123456 ', ' xiaohai3@jbxue.com ')
  104. ));
  105. $column = 3;
  106. $csv = new CSV ($param _arr, $column);
  107. $csv->export ();
  108. $path = ' C:\Documents and Settings\administrator\temp\txxx.csv ';
  109. $import _arr = $csv->import ($path, 3);
  110. Var_dump ($import _arr);
  111. ?>
Copy Code
  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.