phpexcel中文教程-設置表格字體顏色背景樣式、數據格式、對齊方式、添加圖片、批注、文字塊、合并拆分單元格、單元格密碼保護

首先到phpexcel官網上下載最新的phpexcel類，下周解壓縮一個classes文件夾，里面包含了PHPExcel.php和PHPExcel的文件夾，這個類文件和文件夾是我們需要的，把classes解壓到你項目的一個目錄中，重名名為phpexcel，開始嘍，（代碼都摘自自帶實例）

程序部分

require_once?'./phpexcel/PHPExcel.php';

?

// 首先創建一個新的對象??PHPExcel object

$objPHPExcel =?new?PHPExcel();

?

// 設置文件的一些屬性，在xls文件——>屬性——>詳細信息里可以看到這些值，xml表格里是沒有這些值的

$objPHPExcel

??????->getProperties()??//獲得文件屬性對象，給下文提供設置資源

??????->setCreator(?"Maarten Balliauw") ????????????????//設置文件的創建者

??????->setLastModifiedBy(?"Maarten Balliauw") ?????????//設置最后修改者

??????->setTitle(?"Office 2007 XLSX Test Document"?)????//設置標題

??????->setSubject(?"Office 2007 XLSX Test Document"?)??//設置主題

??????->setDescription(?"Test document for Office 2007 XLSX, generated using PHP classes.")?//設置備注

??????->setKeywords(?"office 2007 openxml php")????????//設置標記

??????->setCategory(?"Test result file");????????????????//設置類別

// 位置aaa ?*為下文代碼位置提供錨

// 給表格添加數據

$objPHPExcel->setActiveSheetIndex(0)?????????????//設置第一個內置表（一個xls文件里可以有多個表）為活動的

????????????->setCellValue(?'A1',?'Hello'?)?????????//給表的單元格設置數據

????????????->setCellValue(?'B2',?'world!'?)??????//數據格式可以為字符串

????????????->setCellValue(?'C1', 12)????????????//數字型

????????????->setCellValue(?'D2', 12)????????????//

????????????->setCellValue(?'D3',?true?)???????????//布爾型

????????????->setCellValue(?'D4',?'=SUM(C1:D2)'?);//公式

?

//得到當前活動的表,注意下文教程中會經常用到$objActSheet

$objActSheet = $objPHPExcel->getActiveSheet();

// 位置bbb ?*為下文代碼位置提供錨

// 給當前活動的表設置名稱

$objActSheet->setTitle('Simple2222');

代碼還沒有結束，可以復制下面的代碼來決定我們將要做什么

?

我們將要做的是

1,直接生成一個文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'Excel2007');

$objWriter->save('myexchel.xlsx');

?

2、提示下載文件

excel 2003 .xls

// 生成2003excel格式的xls文件

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename="01simple.xls"');

header('Cache-Control: max-age=0');

?

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'Excel5');

$objWriter->save('php://output');

exit;

?

excel 2007 .xlsx

// 生成2007excel格式的xlsx文件

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="01simple.xlsx"');

header('Cache-Control: max-age=0');

?

$objWriter?= PHPExcel_IOFactory::?createWriter($objPHPExcel,?'Excel2007');

$objWriter->save(?'php://output');

exit;

?

pdf 文件

// 下載一個pdf文件

header('Content-Type: application/pdf');

header('Content-Disposition: attachment;filename="01simple.pdf"');

header('Cache-Control: max-age=0');

?

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'PDF');

$objWriter->save('php://output');

exit;

// 生成一個pdf文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'PDF');

$objWriter->save('a.pdf');

?

?

CSV 文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'CSV')->setDelimiter(','?) ?//設置分隔符

??????????????????????????????????????????????????????????????????->setEnclosure('"'?) ???//設置包圍符

??????????????????????????????????????????????????????????????????->setLineEnding("\r\n"?)//設置行分隔符

??????????????????????????????????????????????????????????????????->setSheetIndex(0)??????//設置活動表

??????????????????????????????????????????????????????????????????->save(str_replace('.php'?,?'.csv'?, __FILE__));

?

HTML 文件

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'HTML');???????//將$objPHPEcel對象轉換成html格式的

$objWriter->setSheetIndex(0);??//設置活動表

//$objWriter->setImagesRoot('http://www.example.com');

$objWriter->save(str_replace('.php',?'.htm', __FILE__));?????//保存文件

?

?

?

?

設置表格樣式和數據格式

設置默認的字體和文字大小 ????錨：aaa

$objPHPExcel->getDefaultStyle()->getFont()->setName(?'Arial');

$objPHPExcel->getDefaultStyle()->getFont()->setSize(20);

?

日期格式 ?????錨：bbb

//獲得秒值變量

$dateTimeNow = time();

//三個表格分別設置為當前實際的 日期格式、時間格式、日期和時間格式

//首先將單元格的值設置為由PHPExcel_Shared_Date::PHPToExcel方法轉換后的excel格式的值，然后用過得到該單元格的樣式里面數字樣式再設置顯示格式

$objActSheet->setCellValue(?'C9', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));?

$objActSheet->getStyle(?'C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

$objActSheet->setCellValue(?'C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));

$objActSheet->getStyle(?'C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

$objActSheet->setCellValue(?'C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));

$objActSheet->getStyle(?'C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

//將E4到E13的數字格式設置為EUR

$objPHPExcel->getActiveSheet()->getStyle(?'E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);

?

設置列的寬度??????錨：bbb

$objActSheet->getColumnDimension(?'B')->setAutoSize(true);???//內容自適應

$objActSheet->getColumnDimension(?'A')->setWidth(30);?????????//30寬

?

設置文件打印的頁眉和頁腳??????錨：bbb

//設置打印時候的頁眉頁腳（設置完了以后可以通過打印預覽來看效果）字符串中的&*好像是一些變量

$objActSheet->getHeaderFooter()->setOddHeader(?'&L&G&C&HPlease treat this document as confidential!');

$objActSheet->getHeaderFooter()->setOddFooter(?'&L&B'?. $objPHPExcel->getProperties()->getTitle() .?'&RPage &P of &N'?);

設置頁面文字的方向和頁面大小????錨：bbb

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::?ORIENTATION_LANDSCAPE);

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::?PAPERSIZE_A4); ????//A4紙大小

?

為頁眉添加圖片?????office中有效 wps中無效 ?錨：bbb

$objDrawing =?new?PHPExcel_Worksheet_HeaderFooterDrawing();

$objDrawing->setName('PHPExcel logo');

$objDrawing->setPath('./images/phpexcel_logo.gif');

$objDrawing->setHeight(36);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT?);

?

設置單元格的批注????錨：bbb

//給單元格添加批注

$objPHPExcel->getActiveSheet()->getComment(?'E13')->setAuthor('PHPExcel'?);?????//設置作者

$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13'?)->getText()->createTextRun('PHPExcel:');??//添加批注

$objCommentRichText->getFont()->setBold(?true);??//將現有批注加粗

$objPHPExcel->getActiveSheet()->getComment(?'E13')->getText()->createTextRun("\r\n"?);??????//添加更多批注

$objPHPExcel->getActiveSheet()->getComment(?'E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.'?);

$objPHPExcel->getActiveSheet()->getComment(?'E13')->setWidth('100pt'?);??????//設置批注顯示的寬高 ，在office中有效在wps中無效

$objPHPExcel->getActiveSheet()->getComment(?'E13')->setHeight('100pt'?);

$objPHPExcel->getActiveSheet()->getComment(?'E13')->setMarginLeft('150pt'?);

$objPHPExcel->getActiveSheet()->getComment(?'E13')->getFillColor()->setRGB('EEEEEE'?);??????//設置背景色 ，在office中有效在wps中無效

?

添加文字塊????看效果圖?office中有效 wps中無效 ?錨：bbb

//大概翻譯??創建一個富文本框??office有效??wps無效

$objRichText =?new?PHPExcel_RichText();

$objRichText->createText('This invoice is ');????//寫文字

//添加文字并設置這段文字粗體斜體和文字顏色

$objPayable = $objRichText->createTextRun(?'payable within thirty days after the end of the month');

$objPayable->getFont()->setBold(?true);

$objPayable->getFont()->setItalic(?true);

$objPayable->getFont()->setColor(?new?PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN?) );

$objRichText->createText(', unless specified otherwise on the invoice.');

//將文字寫到A18單元格中

$objPHPExcel->getActiveSheet()->getCell(?'A18')->setValue($objRichText);

?

合并拆分單元格????錨：bbb

$objPHPExcel->getActiveSheet()->mergeCells(?'A28:B28');??????// A28:B28合并

$objPHPExcel->getActiveSheet()->unmergeCells(?'A28:B28');????// A28:B28再拆分

?

單元格密碼保護????錨：bbb

// 單元格密碼保護不讓修改

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(?true);??// 為了使任何表保護，需設置為真

$objPHPExcel->getActiveSheet()->protectCells(?'A3:E13',?'PHPExcel'?);?// 將A3到E13保護?加密密碼是 PHPExcel

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);?//去掉保護

?

設置單元格字體???錨：bbb

//將B1的文字字體設置為Candara，20號的粗體下劃線有背景色

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getFont()->setName('Candara'?);

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getFont()->setSize(20);

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

$objPHPExcel->getActiveSheet()->getStyle(?'B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

?

文字對齊方式??錨：bbb

$objPHPExcel->getActiveSheet()->getStyle(?'D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);????//水平方向上對齊

$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);//水平方向上兩端對齊

$objPHPExcel->getActiveSheet()->getStyle(?'A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);???????//垂直方向上中間居中

?

設置單元格邊框??錨：bbb

$styleThinBlackBorderOutline =?array(

???????'borders'?=>?array?(

?????????????'outline'?=>?array?(

???????????????????'style'?=> PHPExcel_Style_Border::BORDER_THIN,???//設置border樣式

???????????????????//'style'?=> PHPExcel_Style_Border::BORDER_THICK, ?另一種樣式

???????????????????'color'?=>?array?('argb'?=>?'FF000000'), ?????????//設置border顏色

????????????),

??????),

);

$objPHPExcel->getActiveSheet()->getStyle(?'A4:E10')->applyFromArray($styleThinBlackBorderOutline);

?

背景填充顏色 ????錨：bbb

//設置填充的樣式和背景色

$objPHPExcel->getActiveSheet()->getStyle(?'A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle(?'A1:E1')->getFill()->getStartColor()->setARGB('FF808080');

?

綜合設置樣例

$objPHPExcel->getActiveSheet()->getStyle(?'A3:E3')->applyFromArray(

?????????????array(

???????????????????'font'????=>?array?(

?????????????????????????'bold'??????=>?true

???????????????????),

???????????????????'alignment'?=>?array?(

?????????????????????????'horizontal'?=> PHPExcel_Style_Alignment::HORIZONTAL_RIGHT?,

??????????????????),

???????????????????'borders'?=>?array?(

?????????????????????????'top'?????=>?array?(

???????????????????????????????'style'?=> PHPExcel_Style_Border::BORDER_THIN

????????????????????????)

??????????????????),

???????????????????'fill'?=>?array?(

?????????????????????????'type'???????=> PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR?,

?????????????????????????'rotation'???=> 90,

?????????????????????????'startcolor'?=>?array?(

???????????????????????????????'argb'?=>?'FFA0A0A0'

?????????????????????????),

?????????????????????????'endcolor'???=>?array?(

???????????????????????????????'argb'?=>?'FFFFFFFF'

?????????????????????????)

??????????????????)

????????????)

);

?

?

給單元格內容設置url超鏈接??????錨：bbb

$objActSheet->getCell('E26')->getHyperlink()->setUrl(?'http://www.phpexcel.net');????//超鏈接url地址

$objActSheet->getCell('E26')->getHyperlink()->setTooltip(?'Navigate to website');??//鼠標移上去連接提示信息

?

給表中添加圖片?????錨：bbb

$objDrawing =?new?PHPExcel_Worksheet_Drawing();

$objDrawing->setName('Paid');

$objDrawing->setDescription('Paid');

$objDrawing->setPath('./images/paid.png');?//圖片引入位置

$objDrawing->setCoordinates('B15');?//圖片添加位置

$objDrawing->setOffsetX(210);

$objDrawing->setRotation(25);?

$objDrawing->setHeight(36);

$objDrawing->getShadow()->setVisible?(true?);

$objDrawing->getShadow()->setDirection(45);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

//還可以添加有gd庫生產的圖片，詳細見自帶實例25

?

創建一個新工作表和設置工作表標簽顏色?????錨：bbb

$objExcel->createSheet();?

$objPHPExcel->setActiveSheetIndex(1); ??//設置第2個表為活動表，提供操作句柄

$objExcel->getSheet(1)->setTitle(?'測試2'); ??//直接得到第二個表進行設置,將工作表重新命名為測試2

$objPHPExcel->getActiveSheet()->getTabColor()->setARGB(?'FF0094FF'); ????//設置標簽顏色

?

添加或刪除行和列?????錨：bbb

$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10); ??//在行6前添加10行

$objPHPExcel->getActiveSheet()->removeRow(6, 10); ?????????????????//從第6行往后刪去10行

$objPHPExcel->getActiveSheet()->insertNewColumnBefore(?'E', 5); ???//從第E列前添加5類

$objPHPExcel->getActiveSheet()->removeColumn(?'E', 5);?????????????//從E列開始往后刪去5列

?

隱藏和顯示某列?????錨：bbb

$objPHPExcel->getActiveSheet()->getColumnDimension(?'C')->setVisible(false);??????????//隱藏

$objPHPExcel->getActiveSheet()->getColumnDimension(?'D')->setVisible(true);???????????//顯示

?

重新命名活動的表的標簽名稱 ????錨：bbb

$objPHPExcel->getActiveSheet()->setTitle(?'Invoice');

?

設置工作表的安全

$objPHPExcel->getActiveSheet()->getProtection()->setPassword(?'PHPExcel');

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(?true);?// This should be enabled in order to enable any of the following!

$objPHPExcel->getActiveSheet()->getProtection()->setSort(?true);

$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(?true);

$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(?true);

?

設置文檔安全 ??錨：bbb

$objPHPExcel->getSecurity()->setLockWindows(?true);

$objPHPExcel->getSecurity()->setLockStructure(?true);

$objPHPExcel->getSecurity()->setWorkbookPassword(?"PHPExcel");?????//設置密碼

?

樣式復制??????錨：bbb

//將B2的樣式復制到B3至B7

$objPHPExcel->getActiveSheet()->duplicateConditionalStyle(

????????????????????????$objPHPExcel->getActiveSheet()->getStyle(?'B2')->getConditionalStyles(),

?????????????????????????'B3:B7'

???????????????????);

?

Add conditional formatting????錨：bbb

echo?date('H:i:s'?) ,?" Add conditional formatting"?, PHP_EOL;

$objConditional1 =?new?PHPExcel_Style_Conditional?();

$objConditional1->setConditionType(PHPExcel_Style_Conditional?::CONDITION_CELLIS?);

$objConditional1->setOperatorType(PHPExcel_Style_Conditional?::OPERATOR_BETWEEN?);

$objConditional1->addCondition('200');

$objConditional1->addCondition('400');

?

設置分頁（主要用于打印）????錨：bbb

//設置某單元格為頁尾

$objPHPExcel->getActiveSheet()->setBreak(?'A'?. $i, PHPExcel_Worksheet::BREAK_ROW?);

?

?

用數組填充表????錨：bbb

//吧數組的內容從A2開始填充

$dataArray =?array(?array("2010"?,????"Q1",??"United States",??790),

???????????????????array("2010"?,????"Q2",??"United States",??730),

??????????????????);

$objPHPExcel->getActiveSheet()->fromArray($dataArray,?NULL,?'A2');

?

設置自動篩選?????錨：bbb

$objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());

//$objPHPExcel->getActiveSheet()->calculateWorksheetDimension()....得到A1行的所有內容個

?

打印出的到所有的公式

$objCalc = PHPExcel_Calculation::getInstance();

print_r($objCalc->listFunctionNames())

?

設置單元格值的范圍?????錨：bbb

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B3'?)->getDataValidation();

$objValidation->setType( PHPExcel_Cell_DataValidation::?TYPE_WHOLE?);

$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::?STYLE_STOP?);

$objValidation->setAllowBlank(true);

$objValidation->setShowInputMessage(?true);????????????//設置顯示提示信息

$objValidation->setShowErrorMessage(?true);????????????//設置顯示錯誤信息

$objValidation->setErrorTitle('Input error');????//錯誤標題

//$objValidation->setShowDropDown(true);

$objValidation->setError('Only numbers between 10 and 20 are allowed!');???????//錯誤內容

$objValidation->setPromptTitle('Allowed input');???????//設置提示標題

$objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');?//提示內容

$objValidation->setFormula1(10);?????//設置最大值

$objValidation->setFormula2(120);????//設置最小值

//或者這樣設置??$objValidation->setFormula2(1,5,6,7);??設置值是1，5，6，7中的一個數

?

其他

$objPHPExcel->getActiveSheet()->getStyle(?'B5')->getAlignment()->setShrinkToFit(true);?//長度不夠顯示的時候 是否自動換行

$objPHPExcel->getActiveSheet()->getStyle(?'B5')->getAlignment()->setShrinkToFit(true);?//自動轉換顯示字體大小,使內容能夠顯示

$objPHPExcel->getActiveSheet()->getCell(B14)->getValue();???????????//獲得值，有可能得到的是公式

$objPHPExcel->getActiveSheet()->getCell(B14)->getCalculatedValue();//獲得算出的值

?

?

導入或讀取文件

//通過PHPExcel_IOFactory::load方法來載入一個文件，load會自動判斷文件的后綴名來導入相應的處理類，讀取格式保含xlsx/xls/xlsm/ods/slk/csv/xml/gnumeric

require_once?'../Classes/PHPExcel/IOFactory.php';

$objPHPExcel = PHPExcel_IOFactory::load(

//吧載入的文件默認表（一般都是第一個）通過toArray方法來返回一個多維數組

$dataArray = $objPHPExcel->getActiveSheet()->toArray();

//讀完直接寫到一個xlsx文件里

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,?'Excel2007');?//$objPHPExcel是上文中讀的資源

$objWriter->save(str_replace('.php',?'.xlsx', __FILE__));

?

讀取xml文件

$objReader = PHPExcel_IOFactory::?createReader('Excel2003XML'?);

$objPHPExcel = $objReader->load(?"Excel2003XMLTest.xml"?);

讀取ods文件

$objReader = PHPExcel_IOFactory::?createReader('OOCalc'?);

$objPHPExcel =?$objReader->load("OOCalcTest.ods"?);

讀取numeric文件

$objReader = PHPExcel_IOFactory::?createReader('Gnumeric'?);

$objPHPExcel = $objReader->load(?"GnumericTest.gnumeric"?);

讀取slk文件

$objPHPExcel = PHPExcel_IOFactory::?load("SylkTest.slk"?);

?

?

循環遍歷數據

$objReader = PHPExcel_IOFactory::createReader('Excel2007'?);?//創建一個2007的讀取對象

$objPHPExcel = $objReader->load?("05featuredemo.xlsx"?);?????????????//讀取一個xlsx文件

foreach?($objPHPExcel->getWorksheetIterator()?as?$worksheet) {?????//遍歷工作表

???????echo?'Worksheet - '?, $worksheet->getTitle() , PHP_EOL;

???????foreach?($worksheet->getRowIterator()?as?$row) {???????//遍歷行

?????????????echo?'????Row number - '?, $row->getRowIndex() , PHP_EOL;

????????????$cellIterator = $row->getCellIterator();???//得到所有列

????????????$cellIterator->setIterateOnlyExistingCells(?false);?// Loop all cells, even if it is not set

?????????????foreach?($cellIterator?as?$cell) {??//遍歷列

???????????????????if?(!is_null($cell)) {??//如果列不給空就得到它的坐標和計算的值

?????????????????????????echo?'????????Cell - '?, $cell->getCoordinate() ,?' - '?, $cell->getCalculatedValue() , PHP_EOL;

??????????????????}

????????????}

??????}

}

?

數組插入的表中

//插入的數據 3行數據

$data =?array(?array('title'??????=>?'Excel for dummies',

?????????????????????'price'??????=> 17.99,

?????????????????????'quantity' ??=> 2

???????????????????????????),

????????????????????array('title'???????=>?'PHP for dummies',

???????????????????????????'price' ?????=> 15.99,

???????????????????????????'quantity'??=> 1

???????????????????????????),

????????????????????array('title'??????=>?'Inside OOP',

???????????????????????????'price' ?????=> 12.95,

???????????????????????????'quantity'??=> 1

???????????????????????????)

???????????????????);

$baseRow = 5;??????//指定插入到第5行后

foreach($data?as?$r => $dataRow) {

??????$row = $baseRow + $r;????//$row是循環操作行的行號

??????$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);??//在操作行的號前加一空行，這空行的行號就變成了當前的行號

???????//對應的咧都附上數據和編號

??????$objPHPExcel->getActiveSheet()->setCellValue(?'A'.$row, $r+1);?????

??????$objPHPExcel->getActiveSheet()->setCellValue(?'B'.$row, $dataRow['title']);

??????$objPHPExcel->getActiveSheet()->setCellValue(?'C'.$row, $dataRow['price']);

??????$objPHPExcel->getActiveSheet()->setCellValue(?'D'.$row, $dataRow['quantity']);

??????$objPHPExcel->getActiveSheet()->setCellValue(?'E'.$row,?'=C'.$row.'*D'?.$row);

}

$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);?????//最后刪去第4行，這是示例需要，在此處為大家提供刪除實例