当前位置:Gxlcms > PHP教程 > php导出csv文件:指定编码导出与csv文件导入与导出类

php导出csv文件:指定编码导出与csv文件导入与导出类

时间:2021-07-01 10:21:17 帮助过:28人阅读

  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. */ 导出csv文件
  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. ?>

二、php导入与导出csv文件的类

php实现csv文件的导入和导出类。 代码:

  1. /**
  2. * CSV 文件处理类
  3. */
  4. class Csv{
  5. public $csv_array; //csv数组数据
  6. public $csv_str; //csv文件数据
  7. public function __construct($param_arr, $column){
  8. $this->csv_array = $param_arr;
  9. $this->path = $path;
  10. $this->column = $column;
  11. }
  12. /**
  13. * 导出
  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. //组装数据
  24. foreach($param_arr as $k=>$v){
  25. foreach($v as $k1=>$v1){
  26. $export_str .= implode(',',$v1)."n";
  27. }
  28. }
  29. //将$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. * 导入
  42. * */
  43. public function import($path,$column = 3){
  44. $flag = flase;
  45. $code = 0;
  46. $msg = '未处理';
  47. $filesize = 1; //1MB
  48. $maxsize = $filesize * 1024 * 1024;
  49. $max_column = 1000;
  50. //检测文件是否存在
  51. if($flag === flase){
  52. if(!file_exists($path)){
  53. $msg = '文件不存在';
  54. $flag = true;
  55. }
  56. }
  57. //检测文件格式
  58. if($flag === flase){
  59. $ext = preg_replace("/.*.([^.]+)/","$1",$path);
  60. if($ext != 'csv'){
  61. $msg = '只能导入CSV格式文件';
  62. $flag = true;
  63. }
  64. }
  65. //检测文件大小
  66. if($flag === flase){
  67. if(filesize($path)>$maxsize){
  68. $msg = '导入的文件不得超过'.$maxsize.'B文件';
  69. $flag = true;
  70. }
  71. }
  72. //读取文件
  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 = '文件不符合规格真实有:'.$num.'列数据';
  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. //组建数据
  90. $dataArray[$row][$i] = $data[$i];
  91. }
  92. }
  93. $row++;
  94. }
  95. }
  96. return $dataArray;
  97. }
  98. }
  99. $param_arr = array(
  100. 'nav'=>array('用户名','密码','邮箱'),
  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. ?>

人气教程排行