首页 >
未分类 > PhpExcel中文帮助手册|PhpExcel使用方法二
2015
04-15
PhpExcel中文帮助手册|PhpExcel使用方法二
首先到phpexcel官网上下载最新的phpexcel类,下周解压缩一个classes文件夹,里面包含了PHPExcel.php和PHPExcel的文件夹,这个类文件和文件夹是我们需要的,把classes解压到你项目的一个目录中,重名名为phpexcel,开始喽,(代码都摘自自带实例)
程序部分
001 | require_once './phpexcel/PHPExcel.php' ; |
004 | $objPHPExcel = new PHPExcel(); |
009 | ->setCreator( "Maarten Balliauw" ) |
010 | ->setLastModifiedBy( "Maarten Balliauw" ) |
011 | ->setTitle( "Office 2007 XLSX Test Document" ) |
012 | ->setSubject( "Office 2007 XLSX Test Document" ) |
013 | ->setDescription( "Test document for Office 2007 XLSX, generated using PHP classes." ) |
014 | ->setKeywords( "office 2007 openxml php" ) |
015 | ->setCategory( "Test result file" ); |
018 | $objPHPExcel ->setActiveSheetIndex(0) |
019 | ->setCellValue( 'A1' , 'Hello' ) |
020 | ->setCellValue( 'B2' , 'world!' ) |
021 | ->setCellValue( 'C1' , 12) |
022 | ->setCellValue( 'D2' , 12) |
023 | ->setCellValue( 'D3' , true ) |
024 | ->setCellValue( 'D4' , '=SUM(C1:D2)' ); |
027 | $objActSheet = $objPHPExcel ->getActiveSheet(); |
030 | $objActSheet ->setTitle( 'Simple2222' ); |
031 | 代码还没有结束,可以复制下面的代码来决定我们将要做什么 |
035 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'Excel2007' ); |
036 | $objWriter ->save( 'myexchel.xlsx' ); |
041 | header( 'Content-Type: application/vnd.ms-excel' ); |
042 | header( 'Content-Disposition: attachment;filename="01simple.xls"' ); |
043 | header( 'Cache-Control: max-age=0' ); |
045 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'Excel5' ); |
051 | header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ); |
052 | header( 'Content-Disposition: attachment;filename="01simple.xlsx"' ); |
053 | header( 'Cache-Control: max-age=0' ); |
055 | $objWriter = PHPExcel_IOFactory:: createWriter( $objPHPExcel , 'Excel2007' ); |
061 | header( 'Content-Type: application/pdf' ); |
062 | header( 'Content-Disposition: attachment;filename="01simple.pdf"' ); |
063 | header( 'Cache-Control: max-age=0' ); |
065 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'PDF' ); |
069 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'PDF' ); |
070 | $objWriter ->save( 'a.pdf' ); |
074 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'CSV' )->setDelimiter( ',' ) |
076 | ->setLineEnding( "\r\n" ) |
078 | ->save( str_replace ( '.php' , '.csv' , __FILE__ )); |
081 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'HTML' ); |
082 | $objWriter ->setSheetIndex(0); |
084 | $objWriter ->save( str_replace ( '.php' , '.htm' , __FILE__ )); |
091 | $objPHPExcel ->getDefaultStyle()->getFont()->setName( 'Arial' ); |
092 | $objPHPExcel ->getDefaultStyle()->getFont()->setSize(20); |
096 | $dateTimeNow = time(); |
099 | $objActSheet ->setCellValue( 'C9' , PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow )); |
100 | $objActSheet ->getStyle( 'C9' )->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); |
101 | $objActSheet ->setCellValue( 'C10' , PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow )); |
102 | $objActSheet ->getStyle( 'C10' )->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); |
103 | $objActSheet ->setCellValue( 'C10' , PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow )); |
104 | $objActSheet ->getStyle( 'C10' )->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4); |
106 | $objPHPExcel ->getActiveSheet()->getStyle( 'E4:E13' )->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); |
109 | $objActSheet ->getColumnDimension( 'B' )->setAutoSize(true); |
110 | $objActSheet ->getColumnDimension( 'A' )->setWidth(30); |
114 | $objActSheet ->getHeaderFooter()->setOddHeader( '&L&G&C&HPlease treat this document as confidential!' ); |
115 | $objActSheet ->getHeaderFooter()->setOddFooter( '&L&B' . $objPHPExcel ->getProperties()->getTitle() . '&RPage &P of &N' ); |
118 | $objPHPExcel ->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE); |
119 | $objPHPExcel ->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup:: PAPERSIZE_A4); |
121 | 为页眉添加图片 office中有效 wps中无效 锚:bbb |
122 | $objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing(); |
123 | $objDrawing ->setName( 'PHPExcel logo' ); |
124 | $objDrawing ->setPath( './images/phpexcel_logo.gif' ); |
125 | $objDrawing ->setHeight(36); |
126 | $objPHPExcel ->getActiveSheet()->getHeaderFooter()->addImage( $objDrawing , PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT ); |
130 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )->setAuthor( 'PHPExcel' ); |
131 | $objCommentRichText = $objPHPExcel ->getActiveSheet()->getComment( 'E13' )-> getText ()->createTextRun( 'PHPExcel:' ); |
132 | $objCommentRichText ->getFont()->setBold( true); |
133 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )-> getText ()->createTextRun( "\r\n" ); |
134 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )-> getText ()->createTextRun( 'Total amount on the current invoice, including VAT.' ); |
135 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )->setWidth( '100pt' ); |
136 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )->setHeight( '100pt' ); |
137 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )->setMarginLeft( '150pt' ); |
138 | $objPHPExcel ->getActiveSheet()->getComment( 'E13' )->getFillColor()->setRGB( 'EEEEEE' ); |
140 | 添加文字块 看效果图 office中有效 wps中无效 锚:bbb |
142 | $objRichText = new PHPExcel_RichText(); |
143 | $objRichText ->createText( 'This invoice is ' ); |
145 | $objPayable = $objRichText ->createTextRun( 'payable within thirty days after the end of the month' ); |
146 | $objPayable ->getFont()->setBold( true); |
147 | $objPayable ->getFont()->setItalic( true); |
148 | $objPayable ->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) ); |
149 | $objRichText ->createText( ', unless specified otherwise on the invoice.' ); |
151 | $objPHPExcel ->getActiveSheet()->getCell( 'A18' )->setValue( $objRichText ); |
152 | PHPExcel <wbr><wbr>学习笔记 |
155 | $objPHPExcel ->getActiveSheet()->mergeCells( 'A28:B28' ); |
156 | $objPHPExcel ->getActiveSheet()->unmergeCells( 'A28:B28' ); |
160 | $objPHPExcel ->getActiveSheet()->getProtection()->setSheet( true); |
161 | $objPHPExcel ->getActiveSheet()->protectCells( 'A3:E13' , 'PHPExcel' ); |
162 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); |
166 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->setName( 'Candara' ); |
167 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->setSize(20); |
168 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->setBold(true); |
169 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); |
170 | $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); |
173 | $objPHPExcel ->getActiveSheet()->getStyle( 'D11' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); |
174 | $objPHPExcel ->getActiveSheet()->getStyle( 'A18' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); |
175 | $objPHPExcel ->getActiveSheet()->getStyle( 'A18' )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); |
178 | $styleThinBlackBorderOutline = array ( |
181 | 'style' => PHPExcel_Style_Border::BORDER_THIN, |
183 | 'color' => array ( 'argb' => 'FF000000' ), |
187 | $objPHPExcel ->getActiveSheet()->getStyle( 'A4:E10' )->applyFromArray( $styleThinBlackBorderOutline ); |
191 | $objPHPExcel ->getActiveSheet()->getStyle( 'A1:E1' )->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); |
192 | $objPHPExcel ->getActiveSheet()->getStyle( 'A1:E1' )->getFill()->getStartColor()->setARGB( 'FF808080' ); |
195 | $objPHPExcel ->getActiveSheet()->getStyle( 'A3:E3' )->applyFromArray( |
200 | 'alignment' => array ( |
201 | 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT , |
205 | 'style' => PHPExcel_Style_Border::BORDER_THIN |
209 | 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR , |
211 | 'startcolor' => array ( |
214 | 'endcolor' => array ( |
220 | PHPExcel <wbr><wbr>学习笔记 |
225 | $objActSheet ->getCell( 'E26' )->getHyperlink()->setTooltip( 'Navigate to website' ); |
228 | $objDrawing = new PHPExcel_Worksheet_Drawing(); |
229 | $objDrawing ->setName( 'Paid' ); |
230 | $objDrawing ->setDescription( 'Paid' ); |
231 | $objDrawing ->setPath( './images/paid.png' ); |
232 | $objDrawing ->setCoordinates( 'B15' ); |
233 | $objDrawing ->setOffsetX(210); |
234 | $objDrawing ->setRotation(25); |
235 | $objDrawing ->setHeight(36); |
236 | $objDrawing ->getShadow()->setVisible (true ); |
237 | $objDrawing ->getShadow()->setDirection(45); |
238 | $objDrawing ->setWorksheet( $objPHPExcel ->getActiveSheet()); |
241 | 创建一个新工作表和设置工作表标签颜色 锚:bbb |
242 | $objExcel ->createSheet(); |
243 | $objPHPExcel ->setActiveSheetIndex(1); |
244 | $objExcel ->getSheet(1)->setTitle( '测试2' ); |
245 | $objPHPExcel ->getActiveSheet()->getTabColor()->setARGB( 'FF0094FF' ); |
248 | $objPHPExcel ->getActiveSheet()->insertNewRowBefore(6, 10); |
249 | $objPHPExcel ->getActiveSheet()->removeRow(6, 10); |
250 | $objPHPExcel ->getActiveSheet()->insertNewColumnBefore( 'E' , 5); |
251 | $objPHPExcel ->getActiveSheet()->removeColumn( 'E' , 5); |
254 | $objPHPExcel ->getActiveSheet()->getColumnDimension( 'C' )->setVisible(false); |
255 | $objPHPExcel ->getActiveSheet()->getColumnDimension( 'D' )->setVisible(true); |
258 | $objPHPExcel ->getActiveSheet()->setTitle( 'Invoice' ); |
261 | $objPHPExcel ->getActiveSheet()->getProtection()->setPassword( 'PHPExcel' ); |
262 | $objPHPExcel ->getActiveSheet()->getProtection()->setSheet( true); |
263 | $objPHPExcel ->getActiveSheet()->getProtection()->setSort( true); |
264 | $objPHPExcel ->getActiveSheet()->getProtection()->setInsertRows( true); |
265 | $objPHPExcel ->getActiveSheet()->getProtection()->setFormatCells( true); |
268 | $objPHPExcel ->getSecurity()->setLockWindows( true); |
269 | $objPHPExcel ->getSecurity()->setLockStructure( true); |
270 | $objPHPExcel ->getSecurity()->setWorkbookPassword( "PHPExcel" ); |
274 | $objPHPExcel ->getActiveSheet()->duplicateConditionalStyle( |
275 | $objPHPExcel ->getActiveSheet()->getStyle( 'B2' )->getConditionalStyles(), |
279 | Add conditional formatting 锚:bbb |
280 | echo date ( 'H:i:s' ) , " Add conditional formatting" , PHP_EOL; |
281 | $objConditional1 = new PHPExcel_Style_Conditional (); |
282 | $objConditional1 ->setConditionType(PHPExcel_Style_Conditional ::CONDITION_CELLIS ); |
283 | $objConditional1 ->setOperatorType(PHPExcel_Style_Conditional ::OPERATOR_BETWEEN ); |
284 | $objConditional1 ->addCondition( '200' ); |
285 | $objConditional1 ->addCondition( '400' ); |
289 | $objPHPExcel ->getActiveSheet()->setBreak( 'A' . $i , PHPExcel_Worksheet::BREAK_ROW ); |
294 | $dataArray = array ( array ( "2010" , "Q1" , "United States" , 790), |
295 | array ( "2010" , "Q2" , "United States" , 730), |
297 | $objPHPExcel ->getActiveSheet()->fromArray( $dataArray , NULL, 'A2' ); |
300 | $objPHPExcel ->getActiveSheet()->setAutoFilter( $objPHPExcel ->getActiveSheet()->calculateWorksheetDimension()); |
304 | $objCalc = PHPExcel_Calculation::getInstance(); |
305 | print_r( $objCalc ->listFunctionNames()) |
308 | $objValidation = $objPHPExcel ->getActiveSheet()->getCell( 'B3' )->getDataValidation(); |
309 | $objValidation ->setType( PHPExcel_Cell_DataValidation:: TYPE_WHOLE ); |
310 | $objValidation ->setErrorStyle( PHPExcel_Cell_DataValidation:: STYLE_STOP ); |
311 | $objValidation ->setAllowBlank(true); |
312 | $objValidation ->setShowInputMessage( true); |
313 | $objValidation ->setShowErrorMessage( true); |
314 | $objValidation ->setErrorTitle( 'Input error' ); |
316 | $objValidation ->setError( 'Only numbers between 10 and 20 are allowed!' ); |
317 | $objValidation ->setPromptTitle( 'Allowed input' ); |
318 | $objValidation ->setPrompt( 'Only numbers between 10 and 20 are allowed.' ); |
319 | $objValidation ->setFormula1(10); |
320 | $objValidation ->setFormula2(120); |
324 | $objPHPExcel ->getActiveSheet()->getStyle( 'B5' )->getAlignment()->setShrinkToFit(true); |
325 | $objPHPExcel ->getActiveSheet()->getStyle( 'B5' )->getAlignment()->setShrinkToFit(true); |
326 | $objPHPExcel ->getActiveSheet()->getCell(B14)->getValue(); |
327 | $objPHPExcel ->getActiveSheet()->getCell(B14)->getCalculatedValue(); |
332 | require_once '../Classes/PHPExcel/IOFactory.php' ; |
333 | $objPHPExcel = PHPExcel_IOFactory::load( |
335 | $dataArray = $objPHPExcel ->getActiveSheet()->toArray(); |
337 | $objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel , 'Excel2007' ); |
338 | $objWriter ->save( str_replace ( '.php' , '.xlsx' , __FILE__ )); |
341 | $objReader = PHPExcel_IOFactory:: createReader( 'Excel2003XML' ); |
342 | $objPHPExcel = $objReader ->load( "Excel2003XMLTest.xml" ); |
344 | $objReader = PHPExcel_IOFactory:: createReader( 'OOCalc' ); |
345 | $objPHPExcel = $objReader ->load( "OOCalcTest.ods" ); |
347 | $objReader = PHPExcel_IOFactory:: createReader( 'Gnumeric' ); |
348 | $objPHPExcel = $objReader ->load( "GnumericTest.gnumeric" ); |
350 | $objPHPExcel = PHPExcel_IOFactory:: load( "SylkTest.slk" ); |
354 | $objReader = PHPExcel_IOFactory::createReader( 'Excel2007' ); |
355 | $objPHPExcel = $objReader ->load ( "05featuredemo.xlsx" ); |
356 | foreach ( $objPHPExcel ->getWorksheetIterator() as $worksheet ) { |
357 | echo 'Worksheet - ' , $worksheet ->getTitle() , PHP_EOL; |
358 | foreach ( $worksheet ->getRowIterator() as $row ) { |
359 | echo ' Row number - ' , $row ->getRowIndex() , PHP_EOL; |
360 | $cellIterator = $row ->getCellIterator(); |
361 | $cellIterator ->setIterateOnlyExistingCells( false); |
362 | foreach ( $cellIterator as $cell ) { |
363 | if (! is_null ( $cell )) { |
364 | echo ' Cell - ' , $cell ->getCoordinate() , ' - ' , $cell ->getCalculatedValue() , PHP_EOL; |
372 | $data = array ( array ( 'title' => 'Excel for dummies' , |
376 | array ( 'title' => 'PHP for dummies' , |
380 | array ( 'title' => 'Inside OOP' , |
386 | foreach ( $data as $r => $dataRow ) { |
387 | $row = $baseRow + $r ; |
388 | $objPHPExcel ->getActiveSheet()->insertNewRowBefore( $row ,1); |
390 | $objPHPExcel ->getActiveSheet()->setCellValue( 'A' . $row , $r +1); |
391 | $objPHPExcel ->getActiveSheet()->setCellValue( 'B' . $row , $dataRow [ 'title' ]); |
392 | $objPHPExcel ->getActiveSheet()->setCellValue( 'C' . $row , $dataRow [ 'price' ]); |
393 | $objPHPExcel ->getActiveSheet()->setCellValue( 'D' . $row , $dataRow [ 'quantity' ]); |
394 | $objPHPExcel ->getActiveSheet()->setCellValue( 'E' . $row , '=C' . $row . '*D' . $row ); |
396 | $objPHPExcel ->getActiveSheet()->removeRow( $baseRow -1,1); |