基于PHPExcel 导出Excel表格

下载PHPExcel源码:http://phpexcel.codeplex.com/       只要 PHPExcel_1.8.0_doc\Classes 目录下所有文件即可

<?php
	require_once("../db_config.php");
	require_once("Classes/PHPExcel.php");
	include("Classes/PHPExcel/IOFactory.php"); 
	
	
	//$id=$_GET["id"];
	$id="SA00000008";
	$sql_saleinfo="select * from OrderInfoTable where SalesID='$id' ";
	$rs_saleinfo=mysql_query($sql_saleinfo);
	
	//创建一个excel对象
	$objPHPExcel = new PHPExcel();

	// Set properties  设置文件属性
	$objPHPExcel->getProperties()->setCreator("ctos")
        ->setLastModifiedBy("ctos")
        ->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");

	//set width  设置表格宽度
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(45);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);

	//设置水平居中  
	$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
	// set table header content  设置表头名称 
	$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '序号')
        ->setCellValue('B1', '商品编号')
        ->setCellValue('C1', '商品名称')
        ->setCellValue('D1', '商品品牌')
        ->setCellValue('E1', '商品类型')
        ->setCellValue('F1', '条形码')
        ->setCellValue('G1', '单位')
        ->setCellValue('H1', '单价')
        ->setCellValue('I1', '数量')
        ->setCellValue('J1', '小计')
        ->setCellValue('K1', '备注');
		
	$rownum=1;	
  	while ($rows_saleinfo=mysql_fetch_assoc($rs_saleinfo))
	{ 
		$rownum++;		
		
		$mid=$rows_saleinfo["MerchID"];
		$price=$rows_saleinfo["MerchSalesPrice"];
		$num=$rows_saleinfo["MerchSalesQuantity"];
		$each_sum=$rows_saleinfo["MerchSalesPrice"]*($rows_saleinfo["MerchSalesQuantity"]-$rows_saleinfo["ReturnsNum"]);
		$remark=$rows_saleinfo["PoorQualityRecords"];
		
		$sql4="select mit.MerchNameCh,mit.MerchCode,mit.MerchBrand,mit.MerchTypeID,ut.UnitName,mtype.MerchTypeName from MerchInfoTable mit 
					LEFT JOIN UnitTable ut ON ut.UnitID=mit.UnitID 
					LEFT JOIN MerchTypeTable mtype ON mtype.MerchTypeID=mit.MerchTypeID 			
					WHERE mit.MerchID='$mid'";
		$rs4=mysql_query($sql4);
		$row4=mysql_fetch_assoc($rs4); 
		$mename=$row4["MerchNameCh"];
		$code=$row4["MerchCode"];
		$brand=$row4["MerchBrand"];
		$unitname=$row4["UnitName"];
		$typename=$row4["MerchTypeName"];
			
		$objPHPExcel->getActiveSheet()->setCellValue('A' . $rownum, $rownum-1);  
		$objPHPExcel->getActiveSheet()->setCellValue('B' . $rownum, $mid);
		$objPHPExcel->getActiveSheet()->setCellValue('C' . $rownum, $mename);	
		$objPHPExcel->getActiveSheet()->setCellValue('D' . $rownum, $typename);	
		$objPHPExcel->getActiveSheet()->setCellValue('E' . $rownum, $brand);	
		$objPHPExcel->getActiveSheet()->setCellValue('F' . $rownum, ' '.$code);	
		$objPHPExcel->getActiveSheet()->setCellValue('G' . $rownum, $unitname);	
		$objPHPExcel->getActiveSheet()->setCellValue('H' . $rownum, $price);
		$objPHPExcel->getActiveSheet()->setCellValue('I' . $rownum, $num);
		$objPHPExcel->getActiveSheet()->setCellValue('J' . $rownum, $each_sum);
		$objPHPExcel->getActiveSheet()->setCellValue('K' . $rownum, $remark);

	}
		
		

	$objPHPExcel->getActiveSheet()->setTitle('Simple');


	// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);

	//	$filename="销售订单".date('Y-m-d');
	// Redirect output to a client’s web browser (Excel5)
//	ob_end_clean();//清除缓冲区,避免乱码
	header('Content-Type: application/vnd.ms-excel');
	//	header('Content-Disposition: attachment;filename='.$filename);
	header('Content-Disposition: attachment;filename="01simple.xls"');

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	exit;
?>


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。