话不多说，请看代码：

<?php
date_default_timezone_set("PRC"); 
error_reporting(E_ALL);
error_reporting(0);
ini_set("display_errors", TRUE);
ini_set("display_startup_errors", TRUE);
define("EOL",(PHP_SAPI == "cli") ? PHP_EOL : "<br />");

require_once ("../Classes/PHPExcel.php");
require_once("config.php");
require_once("mysql.class.php");

//根据时间生成采购报表
$time = date("a");
$minute = date("i");
$apm = "";
if($time=="pm"){
 $apm = $time;
 $stime = mktime(12,00,00,date("m"),date("d")-1,date("Y"));
 $etime = mktime(11,59,59,date("m"),date("d"),date("Y"));
}else{
 $apm = $time;
 $stime = mktime(12,00,00,date("m"),date("d")-1,date("Y"));
 $etime = mktime(11,59,59,date("m"),date("d"),date("Y"));
}

//实例化excel类
$objPHPExcel = new PHPExcel();

////////获取文档信息
////////$objProps = $objPHPExcel->getProperties();
///////print_r($objProps);
///////echo "<br/>";
///////$objProps->setDescription("test_123456");
///////print_r($objProps);

$objPHPExcel->setActiveSheetIndex(0)
  ->setCellValue("A5","商品编码")
  ->setCellValue("B5","货号")
  ->setCellValue("C5","商品名称")
  ->setCellValue("D5","采购量");

//设置选定sheet表名
$objPHPExcel->getActiveSheet()->setTitle("祖名");
//设置字体样式
$objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setName("Arial")->setSize(25);//////->setUnderline(true);/////->getColor()->setARGB("FFFF0000");///->setBold(true);
//合并单元格 给单元格赋值(数值，字符串，公式)
$objPHPExcel->getActiveSheet()->mergeCells("A1:D3")->setCellValue("A1", "zhongyi清单");
///////$objPHPExcel->getActiveSheet()->mergeCells("A4:D4")->setCellValue("A4", "=SUM(E4:F4)");

$date_now = date("Y-m-d");
$objPHPExcel->getActiveSheet()->mergeCells("A4:D4")->setCellValue("A4", "采购日期：".$date_now." ".$apm."　");
//设置单列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(20);//$objPHPExcel->getActiveSheet()->getColumnDimension("G")->setRowHeight(50);/
$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(44);
$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(15);

//大边框样式 边框加粗
$lineBORDER = array(
 "borders" => array(
 "outline" => array(
  "style" => PHPExcel_Style_Border::BORDER_THICK,
  "color" => array("argb" => "000000"),
 ),
 ),
);
//表头样式
$head = array(
 "font" => array(
 "bold" => true
 ),
 "alignment" => array(
  "horizontal" => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  "vertical" => PHPExcel_Style_Alignment::VERTICAL_CENTER
  ),

);
//标题样式
$title = array(
 "font" => array(
 "bold" => true
 ),
);
//居中对齐
$CENTER = array(
 "alignment" => array(
  "horizontal" => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  "vertical" => PHPExcel_Style_Alignment::VERTICAL_CENTER
 ),
);
//靠右对齐
$RIGHT = array(
 "alignment" => array(
  "horizontal" => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
  "vertical" => PHPExcel_Style_Alignment::VERTICAL_CENTER
 ),
);
//细边框样式
$linestyle = array(
 "borders" => array(
 "outline" => array(
  "style" => PHPExcel_Style_Border::BORDER_THIN,
  "color" => array("argb" => "FF000000"),
 ),
 ),
);

$objPHPExcel->getActiveSheet()->getStyle("A1:D3")->applyFromArray($head);///->getAlignment()->getHorizontal("");///->getBorders()->getTop()->setBorderStyle("");
//->setWrapText(true);自动换行
$objPHPExcel->getActiveSheet()->getStyle("A4:D4")->applyFromArray($RIGHT); 
$objPHPExcel->getActiveSheet()->getStyle("A5:D5")->applyFromArray($title); 

//填充色
/////$objPHPExcel->getActiveSheet()->getStyle("A1")->getFill()->getStartColor()->setARGB("FFFF0000");/

//插入数据
$dsql->Execute("omebrand_list","select i.goods_id , sum( `nums` ) AS num, i.name,i.addon,i.price,g.bn as b,i.bn as h,
g.goods_id,i.goods_id,i.order_id
FROM `sdb_b2c_order_items` as i,sdb_b2c_goods as g
WHERE i.order_id in (select order_id from sdb_b2c_orders where status ="active" and createtime between $stime and $etime) and i.goods_id=g.goods_id and g.cat_id=173 GROUP BY h");
$m = 0;
 unset($re);
 while($row=$dsql->GetObject("omebrand_list"))
 {  $re[$m] = get_object_vars($row);
 $m++;
 }
$row_count = 5;
$objPHPExcel->setActiveSheetIndex(0)
  ->setCellValue("A6", 12325416541)
  ->setCellValue("B6", 4962132165262)
  ->setCellValue("C6", 121515212515241521)
  ->setCellValue("D6", 96215465415);
foreach($re as $r => $dataRow) {
 $baseRow = 6;
 $row = $baseRow + $r;
 $bn=$dataRow[h];
 $goods_id = $dataRow[goods_id];
  $spec_value = "";
  $aa = unserialize($dataRow[addon]);
  if ($aa["product_attr"]){
  foreach ($aa["product_attr"] as $arr_special_info) {
   $spec_value = $arr_special_info["value"];
  }
  }

  preg_match_all("/\-?\d+\.?\d*/i",$spec_value,$row1);
  $num = $row1[0][0];
  $all = $num*$dataRow[num];
 if($spec_value==""){
 $all=$dataRow["num"];
 //$prce=$dataRow[price];
 }
 $objPHPExcel->setActiveSheetIndex(0)
  ->setCellValue("A".$row, $dataRow["b"])
  ->setCellValue("B".$row, $bn)
  ->setCellValue("C".$row, $dataRow["name"])
  ->setCellValue("D".$row, $all);
 $objPHPExcel->getActiveSheet()->getStyle("A".$row_count)->applyFromArray($linestyle);  
 $objPHPExcel->getActiveSheet()->getStyle("B".$row_count)->applyFromArray($linestyle);
 $objPHPExcel->getActiveSheet()->getStyle("C".$row_count)->applyFromArray($linestyle);
 $objPHPExcel->getActiveSheet()->getStyle("D".$row_count)->applyFromArray($linestyle);  

 $baseRow++;
 $row_count++;
}
$objPHPExcel->getActiveSheet()->getStyle("A".$row_count)->applyFromArray($linestyle);  
$objPHPExcel->getActiveSheet()->getStyle("B".$row_count)->applyFromArray($linestyle);
$objPHPExcel->getActiveSheet()->getStyle("C".$row_count)->applyFromArray($linestyle);
$objPHPExcel->getActiveSheet()->getStyle("D".$row_count)->applyFromArray($linestyle); 
$objPHPExcel->getActiveSheet()->getStyle("A5:D".$row_count)->applyFromArray($CENTER); 
$objPHPExcel->getActiveSheet()->getStyle("A1:D".$row_count)->applyFromArray($lineBORDER);

//设置打印页边距
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0);
//设置纸张类型
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//设置自动筛选
$objPHPExcel->getActiveSheet()->setAutoFilter("A5:D".$row_count);
//设置自动换行
$objPHPExcel->getActiveSheet()->getStyle("B6:B".$row_count)->getAlignment()->setWrapText(true);
//设置格式化数字
$objPHPExcel->getActiveSheet()->getStyle("A6:A".$row_count)->getNumberFormat()->setFormatCode("0000000000");

//设置安全级别
$md=md5(time());
$md=substr($md,0,8);
$objPHPExcel->getActiveSheet()->getProtection()->setPassword("$md");
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

//添加图片 
/*
$obj=$objPHPExcel->getActiveSheet();
$objDrawing = new PHPExcel_Worksheet_Drawing(); 
$objDrawing->setName("wsyImg"); 
$objDrawing->setDescription("Image inserted by zhy"); 
$objDrawing->setPath("./wsy.jpg"); 
$objDrawing->setHeight(50); 
$objDrawing->setCoordinates("H23"); 
$objDrawing->setOffsetX(60); 
$objDrawing->setRotation(-10); /
$objDrawing->getShadow()->setVisible(true); 
$objDrawing->getShadow()->setDirection(-20); / 
$objDrawing->setWorksheet($obj);
*/

//页眉页脚
//$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader("zhy"); 
//$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter("end"); 

$objPHPExcel->setActiveSheetIndex(0);
$tname=date("Y-m-dH",time());
$tnam=iconv("UTF-8","GBK","祖名订单");
$tname=$tnam.$tname;

// Excel 2007保存
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
//$objWriter->save(str_replace(".php", ".xlsx", __FILE__)); 

// Excel 5保存 
//$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 
//$objWriter->save(str_replace(".php", ".xls", __FILE__)); 

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save(str_replace(".php", ".xls", __FILE__));

//$url = "/data/home/htdocs/ec/public/files/".date("Y")."/".date("Ym")."/";
createDir($url);
function createDir($dir) {
 if (!is_dir ($dir )) {
 mkdir($dir, 0777, true);
 chmod($dir, 0777); 
 chown( $dir, "daemon" );
 chgrp( $dir, "daemon" );  
 }
}
$name="forexmple_excel";
rename(str_replace(".php", ".xls", __FILE__), $name.".xls");
?>

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持网页设计!