首页 > php开发 > php CSV EXCEL 文件导入导出

php CSV EXCEL 文件导入导出

2011年8月12日
跳到评论

php CSV EXCEL 文件导入导出

最近在开发一个项目，需要用导出excel文件这个功能，于是结合网上搜索到的资料及自己的体会，用以下方法实现了。

1.谷歌代码

示例代码：

1 <?php
2   
3 // load library
4 require 'php-excel.class.php';
5   
6 // create a simple 2-dimensional array
7 $data = array(
8         1 => array ('Name', 'Surname'),
9         array('Schwarz', 'Oliver'),
10         array('Test', 'Peter')
11         );
12   
13 // generate file (constructor parameters are optional)
14 $xls = new Excel_XML('UTF-8', false, 'My Test Sheet');
15 $xls->addArray($data);
16 $xls->generateXML('my-test');
17   
18 ?>

下载链接：http://php-excel.googlecode.com/files/php-excel-v1.1-20090910.zip

说明：这个是导出excel文件用的。

2.自己写的函数：

示例代码：

导出csv:

查看源代码

打印帮助

1 <?php
2 export_csv();
3   
4 /**
5  *导出到CSV文件
6  */
7 function export_csv()
8 {
9     $filename = date('YmdHis').".csv";
10  header("Content-type:text/csv");
11     header("Content-Disposition:attachment;filename=".$filename);
12     header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
13     header('Expires:0');
14     header('Pragma:public');
15     echo array_to_string(get_export_data());
16 }
17 /**
18  *导出数据转换
19  * @param $result
20  */
21 function array_to_string($result)
22 {
23     if(empty($result)){
24         return i("没有符合您要求的数据！^_^");
25     }
26     $data;
27     $size_result = sizeof($result);
28     for($i = 0 ; $i < $size_result ;  $i++) {
29         $data .= i($result[$i]['name']).','.i($result[$i]['option'])."\n";
30     }
31     return $data;
32 }
33 /**
34  *获取导出报表的数据
35  * @return
36  */
37 function get_export_data()
38 {
39  $link = mysql_connect('localhost','root','root') or die(mysql_error());
40  mysql_select_db('joomla');
41  $sql = 'select a.name,a.option from jos_components a limit 10';
42  $result = mysql_query($sql);
43  $res = array();
44  $i = 0;
45  while($row = mysql_fetch_array($result)){
46   $res[$i][name] = $row[name];
47   $res[$i][option] = $row[option];
48   $i++; 
49  } 
50     return $res;
51 }
52 /**
53  *编码转换
54  * @param <type> $strInput
55  * @return <type>
56  */
57 function i($strInput)
58 {
59     return iconv('utf-8','gb2312',$strInput);//页面编码为utf-8时使用，否则导出的中文为乱码
60 }
61 ?>

导入csv:

查看源代码

打印帮助

1 <?php
2 header("Content-type:text/html;charset=utf-8");
3 //定义文件路径、文件名
4 $file = 'test.csv';
5   
6 //打开csv文件
7 $handle = fopen($file,'r');
8   
9 //循环读取CSV文件内容，并返回
10 //利用php函数fgetcsv,第一个参数为文件句柄，第二个为读取行数，第三个函数为字段分界符，第四个参数为字段环绕符
11 function inputCsv($handle){
12  $out = array();
13  $n = 0;
14  while($data = fgetcsv($handle,10000)){
15   $num = count($data);
16   for($i=0;$i<$num;$i++){
17    $out[$n][$i] = $data[$i];
18   }
19   $n++;
20  }
21  return $out;
22 }
23 $result = inputCsv($handle);
24 for($i=0;$i<count($result);$i++){
25  for($j=0;$j<count($result[$i]);$j++){
26   echo iconv('gb2312','utf-8',$result[$i][$j]).'&nbsp;&nbsp;'; //页面编码为utf-8时使用，否则中文会出现乱码
27  }
28  echo '<br />';
29 }
30 ?>

3.用高手写的excel类：

 

phpExcel，操作excel很方便，尤其是可以方便的加入图片，支持jpg gif png格式。

下载地址：http://www.codeplex.com/PHPExcel

以下是从网上找的使用方法，我自己还没有具体使用，感觉相当的强大。如果有什么心得，以后补上

查看源代码

打印帮助

1 include ‘PHPExcel.php’;
2 include ‘PHPExcel/Writer/Excel2007.php’;
3 //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
4 //创建一个excel
5 $objPHPExcel = new PHPExcel();
6   
7 //保存excel—2007格式
8 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
9 //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
10 $objWriter->save(”xxx.xlsx”);
11 //直接输出到浏览器
12 $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
13 header(”Pragma: public”);
14 header(”Expires: 0″);
15 header(”Cache-Control:must-revalidate, post-check=0, pre-check=0″);
16 header(”Content-Type:application/force-download”);
17 header(”Content-Type:application/vnd.ms-execl”);
18 header(”Content-Type:application/octet-stream”);
19 header(”Content-Type:application/download”);;
20 header(’Content-Disposition:attachment;filename=”resume.xls”‘);
21 header(”Content-Transfer-Encoding:binary”);
22 $objWriter->save(’php://output’);
23 //设置excel的属性：
24 //创建人
25 $objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);
26 //最后修改人
27 $objPHPExcel->getProperties()->setLastModifiedBy(”Maarten Balliauw”);
28 //标题
29 $objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test Document”);
30 //题目
31 $objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX Test Document”);
32 //描述
33 $objPHPExcel->getProperties()->setDescription(”Test document for Office 2007 XLSX, generated using PHP classes.”);
34 //关键字
35 $objPHPExcel->getProperties()->setKeywords(”office 2007 openxml php”);
36 //种类
37 $objPHPExcel->getProperties()->setCategory(”Test result file”);
38   
39 //设置当前的sheet
40 $objPHPExcel->setActiveSheetIndex(0);
41   
42 //设置sheet的name
43 $objPHPExcel->getActiveSheet()->setTitle(’Simple’);
44   
45 //设置单元格的值
46 $objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’);
47 $objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);
48 $objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);
49 $objPHPExcel->getActiveSheet()->setCellValue(’C5′, ‘=SUM(C2:C4)’);
50 $objPHPExcel->getActiveSheet()->setCellValue(’B8′, ‘=MIN(B2:C5)’);
51   
52 //合并单元格
53 $objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);
54   
55 //分离单元格
56 $objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);
57   
58 //保护cell
59 $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
60 $objPHPExcel->getActiveSheet()->protectCells(’A3:E13′, ‘PHPExcel’);
61   
62 //设置格式
63 // Set cell number formats
64 echo date(’H:i:s’) . ” Set cell number formats\n”;
65 $objPHPExcel->getActiveSheet()->getStyle(’E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
66 $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(’E4′), ‘E5:E13′ );
67   
68 //设置宽width
69 // Set column widths
70 $objPHPExcel->getActiveSheet()->getColumnDimension(’B’)->setAutoSize(true);
71 $objPHPExcel->getActiveSheet()->getColumnDimension(’D’)->setWidth(12);
72   
73 //设置font
74 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFont()->setName(’Candara’);
75 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFont()->setSize(20);
76 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFont()->setBold(true);
77 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
78 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
79 $objPHPExcel->getActiveSheet()->getStyle(’E1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
80 $objPHPExcel->getActiveSheet()->getStyle(’D13′)->getFont()->setBold(true);
81 $objPHPExcel->getActiveSheet()->getStyle(’E13′)->getFont()->setBold(true);
82   
83 //设置align
84 $objPHPExcel->getActiveSheet()->getStyle(’D11′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
85 $objPHPExcel->getActiveSheet()->getStyle(’D12′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
86 $objPHPExcel->getActiveSheet()->getStyle(’D13′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
87 $objPHPExcel->getActiveSheet()->getStyle(’A18′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
88 //垂直居中
89 $objPHPExcel->getActiveSheet()->getStyle(’A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
90   
91 //设置column的border
92 $objPHPExcel->getActiveSheet()->getStyle(’A4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
93 $objPHPExcel->getActiveSheet()->getStyle(’B4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
94 $objPHPExcel->getActiveSheet()->getStyle(’C4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
95 $objPHPExcel->getActiveSheet()->getStyle(’D4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
96 $objPHPExcel->getActiveSheet()->getStyle(’E4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
97   
98 //设置border的color
99 $objPHPExcel->getActiveSheet()->getStyle(’D13′)->getBorders()->getLeft()->getColor()->setARGB(’FF993300′);
100 $objPHPExcel->getActiveSheet()->getStyle(’D13′)->getBorders()->getTop()->getColor()->setARGB(’FF993300′);
101 $objPHPExcel->getActiveSheet()->getStyle(’D13′)->getBorders()->getBottom()->getColor()->setARGB(’FF993300′);
102 $objPHPExcel->getActiveSheet()->getStyle(’E13′)->getBorders()->getTop()->getColor()->setARGB(’FF993300′);
103 $objPHPExcel->getActiveSheet()->getStyle(’E13′)->getBorders()->getBottom()->getColor()->setARGB(’FF993300′);
104 $objPHPExcel->getActiveSheet()->getStyle(’E13′)->getBorders()->getRight()->getColor()->setARGB(’FF993300′);
105   
106 //设置填充颜色
107 $objPHPExcel->getActiveSheet()->getStyle(’A1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
108 $objPHPExcel->getActiveSheet()->getStyle(’A1′)->getFill()->getStartColor()->setARGB(’FF808080′);
109 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
110 $objPHPExcel->getActiveSheet()->getStyle(’B1′)->getFill()->getStartColor()->setARGB(’FF808080′);
111   
112 //加图片
113 $objDrawing = new PHPExcel_Worksheet_Drawing();
114 $objDrawing->setName(’Logo’);
115 $objDrawing->setDescription(’Logo’);
116 $objDrawing->setPath(’./images/officelogo.jpg’);
117 $objDrawing->setHeight(36);
118 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
119   
120 $objDrawing = new PHPExcel_Worksheet_Drawing();
121 $objDrawing->setName(’Paid’);
122 $objDrawing->setDescription(’Paid’);
123 $objDrawing->setPath(’./images/paid.png’);
124 $objDrawing->setCoordinates(’B15′);
125 $objDrawing->setOffsetX(110);
126 $objDrawing->setRotation(25);
127 $objDrawing->getShadow()->setVisible(true);
128 $objDrawing->getShadow()->setDirection(45);
129 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
130   
131 //在默认sheet后，创建一个worksheet
132 echo date(’H:i:s’) . ” Create new Worksheet object\n”;
133 $objPHPExcel->createSheet();
134   
135 $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
136 $objWriter-save('php://output');

php CSV EXCEL 文件导入导出 最近在开发一个项目，需要用导出excel文件这个功能，于是结合网上搜 […]

php开发
«
»
评论 ( 0 )
订阅这里的评论 Trackback 留言
  1. 还没有评论
评论提交中, 请稍候...

留言

(Spamcheck Enabled)

Trackbacks & Pingbacks ( 0 )
显示/隐藏 列表
  1. 还没有 trackbacks