php CSV EXCEL 文件导入导出
最近在开发一个项目,需要用导出excel文件这个功能,于是结合网上搜索到的资料及自己的体会,用以下方法实现了。
1.谷歌代码
示例代码:
4 |
require 'php-excel.class.php' ; |
6 |
// create a simple 2-dimensional array |
8 |
1 => array ( 'Name' , 'Surname' ), |
9 |
array ( 'Schwarz' , 'Oliver' ), |
10 |
array ( 'Test' , 'Peter' ) |
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' ); |
下载链接:http://php-excel.googlecode.com/files/php-excel-v1.1-20090910.zip
说明:这个是导出excel文件用的。
2.自己写的函数:
示例代码:
导出csv:
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' ); |
14 |
header( 'Pragma:public' ); |
15 |
echo array_to_string(get_export_data()); |
21 |
function array_to_string( $result ) |
24 |
return i( "没有符合您要求的数据!^_^" ); |
27 |
$size_result = sizeof( $result ); |
28 |
for ( $i = 0 ; $i < $size_result ; $i ++) { |
29 |
$data .= i( $result [ $i ][ 'name' ]). ',' .i( $result [ $i ][ 'option' ]). "\n" ; |
37 |
function get_export_data() |
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 ); |
45 |
while ( $row = mysql_fetch_array( $result )){ |
46 |
$res [ $i ][name] = $row [name]; |
47 |
$res [ $i ][option] = $row [option]; |
54 |
* @param <type> $strInput |
59 |
return iconv( 'utf-8' , 'gb2312' , $strInput ); //页面编码为utf-8时使用,否则导出的中文为乱码 |
导入csv:
2 |
header( "Content-type:text/html;charset=utf-8" ); |
7 |
$handle = fopen ( $file , 'r' ); |
10 |
//利用php函数fgetcsv,第一个参数为文件句柄,第二个为读取行数,第三个函数为字段分界符,第四个参数为字段环绕符 |
11 |
function inputCsv( $handle ){ |
14 |
while ( $data = fgetcsv ( $handle ,10000)){ |
16 |
for ( $i =0; $i < $num ; $i ++){ |
17 |
$out [ $n ][ $i ] = $data [ $i ]; |
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 ]). ' ' ; //页面编码为utf-8时使用,否则中文会出现乱码 |
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的 |
5 |
$objPHPExcel = new PHPExcel(); |
8 |
$objWriter = new PHPExcel_Writer_Excel2007( $objPHPExcel ); |
9 |
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式 |
10 |
$objWriter ->save(”xxx.xlsx”); |
12 |
$objWriter = new PHPExcel_Writer_Excel5( $objPHPExcel ); |
13 |
header(”Pragma: public ”); |
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’); |
25 |
$objPHPExcel ->getProperties()->setCreator(”Maarten Balliauw”); |
27 |
$objPHPExcel ->getProperties()->setLastModifiedBy(”Maarten Balliauw”); |
29 |
$objPHPExcel ->getProperties()->setTitle(”Office 2007 XLSX Test Document”); |
31 |
$objPHPExcel ->getProperties()->setSubject(”Office 2007 XLSX Test Document”); |
33 |
$objPHPExcel ->getProperties()->setDescription(”Test document for Office 2007 XLSX, generated using PHP classes.”); |
35 |
$objPHPExcel ->getProperties()->setKeywords(”office 2007 openxml php”); |
37 |
$objPHPExcel ->getProperties()->setCategory(”Test result file”); |
40 |
$objPHPExcel ->setActiveSheetIndex(0); |
43 |
$objPHPExcel ->getActiveSheet()->setTitle(’Simple’); |
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)’); |
53 |
$objPHPExcel ->getActiveSheet()->mergeCells(’A18:E22′); |
56 |
$objPHPExcel ->getActiveSheet()->unmergeCells(’A28:B28′); |
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’); |
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′ ); |
70 |
$objPHPExcel ->getActiveSheet()->getColumnDimension(’B’)->setAutoSize(true); |
71 |
$objPHPExcel ->getActiveSheet()->getColumnDimension(’D’)->setWidth(12); |
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); |
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); |
89 |
$objPHPExcel ->getActiveSheet()->getStyle(’A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); |
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); |
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′); |
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′); |
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()); |
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()); |
131 |
//在默认sheet后,创建一个worksheet |
132 |
echo date (’H:i:s’) . ” Create new Worksheet object\n”; |
133 |
$objPHPExcel ->createSheet(); |
135 |
$objWriter = PHPExcel_IOFactory::createWriter( $objExcel , 'Excel5' ); |
136 |
$objWriter -save( 'php://output' ); |
php CSV EXCEL 文件导入导出 最近在开发一个项目,需要用导出excel文件这个功能,于是结合网上搜 […]