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

2. /*
3. * PHP code to export MySQL data to CSV
4. *
5. * Sends the result of a MySQL query as a CSV file for download
6. * Easy to convert to UTF-8.
7. */
9. /*
10. * establish database connection
11. */
13. $conn = mysql_connect('localhost', 'login', 'pass') or die(mysql_error());
14. mysql_select_db('database_name', $conn) or die(mysql_error($conn));
15. mysql_query("SET NAMES CP1252");
16. /*
17. * execute sql query
18. */
19. $query = sprintf('SELECT field1,field2 FROM table_name');
20. $result = mysql_query($query, $conn) or die(mysql_error($conn));
21. /*
22. * send response headers to the browser
23. * following headers instruct the browser to treat the data as a csv file called export.csv
24. */
25. header('Content-Type: text/csv; charset=cp1252');
26. header('Content-Disposition: attachment;filename=output.csv');
27. /*
28. * output header row (if atleast one row exists)
29. */
31. $row = mysql_fetch_assoc($result);
32. if ($row) {
33. echocsv(array_keys($row));
34. }
36. /*
37. * output data rows (if atleast one row exists)
38. */ 导出csv文件
39. while ($row) {
40. echocsv($row);
41. $row = mysql_fetch_assoc($result);
42. }
44. /*
45. * echo the input array as csv data maintaining consistency with most CSV implementations
46. * - uses double-quotes as enclosure when necessary
47. * - uses double double-quotes to escape double-quotes
48. * - uses CRLF as a line separator
49. */
51. function echocsv($fields)
52. {
53. $separator = '';
54. foreach ($fields as $field) {
55. if (preg_match('/\\r|\\n|,|"/', $field)) {
56. $field = '"' . str_replace('"', '""', $field) . '"';
57. }
58. echo $separator . $field;
59. $separator = ',';
60. }
61. echo "\r\n";
62. }
63. ?>

复制代码

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

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

2. /**
3. * CSV 文件处理类
4. */
5. class Csv{
6. public $csv_array; //csv数组数据
7. public $csv_str; //csv文件数据
8. public function __construct($param_arr, $column){
9. $this->csv_array = $param_arr;
10. $this->path = $path;
11. $this->column = $column;
12. }
13. /**
14. * 导出
15. * */
16. public function export(){
17. if(empty($this->csv_array) || empty($this->column)){
18. return false;
19. }
20. $param_arr = $this->csv_array;
21. unset($this->csv_array);
22. $export_str = implode(',',$param_arr['nav'])."n";
23. unset($param_arr['nav']);
24. //组装数据
25. foreach($param_arr as $k=>$v){
26. foreach($v as $k1=>$v1){
27. $export_str .= implode(',',$v1)."n";
28. }
29. }
30. //将$export_str导出
31. header( "Cache-Control: public" );
32. header( "Pragma: public" );
33. header("Content-type:application/vnd.ms-excel");
34. header("Content-Disposition:attachment;filename=txxx.csv");
35. header('Content-Type:APPLICATION/OCTET-STREAM');
36. ob_start();
37. // $file_str= iconv("utf-8",'gbk',$export_str);
38. ob_end_clean();
39. echo $export_str;
40. }
41. /**
42. * 导入
43. * */
44. public function import($path,$column = 3){
45. $flag = flase;
46. $code = 0;
47. $msg = '未处理';
48. $filesize = 1; //1MB
49. $maxsize = $filesize * 1024 * 1024;
50. $max_column = 1000;
52. //检测文件是否存在
53. if($flag === flase){
54. if(!file_exists($path)){
55. $msg = '文件不存在';
56. $flag = true;
57. }
58. }
59. //检测文件格式
60. if($flag === flase){
61. $ext = preg_replace("/.*.([^.]+)/","$1",$path);
62. if($ext != 'csv'){
63. $msg = '只能导入CSV格式文件';
64. $flag = true;
65. }
66. }
67. //检测文件大小
68. if($flag === flase){
69. if(filesize($path)>$maxsize){
70. $msg = '导入的文件不得超过'.$maxsize.'B文件';
71. $flag = true;
72. }
73. }
74. //读取文件
75. if($flag == flase){
76. $row = 0;
77. $handle = fopen($path,'r');
78. $dataArray = array();
79. while($data = fgetcsv($handle,$max_column,",")){
80. $num = count($data);
81. if($num $msg = '文件不符合规格真实有：'.$num.'列数据';
82. $flag = true;
83. break;
84. }
85. if($flag === flase){
86. for($i=0;$i 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','[e-mail]'),
102. 1=>array('xiaohai2','213456','[e-mail]'),
103. 2=>array('xiaohai3','123456','[e-mail]')
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. ?>

复制代码