php mysql报表开发_PHPExcel mysql php生成excel报表

/** PHPExcel */

require_once 'Classes/PHPExcel.php';

// Create new PHPExcel object

include 'config.php';

$objPHPExcel = new PHPExcel();

$a1 = '货品名称'; //这是两个标头 就是列名，最上面的那个

$a2 = '更新日期';

$a3 = '供应商';

$a4 = "仓库";

$a5 = "货品编码";

$a6 = "产品代码";

$a7 = "规格型号";

$a8 = "单价";

//$a1=iconv("utf-8","gb2312",$a1);

//如果是乱码的话，则需要转换下

//$a2=iconv("utf-8","gb2312",$a2);

$objPHPExcel->getActiveSheet()->setCellValue('a1', "$a1");

//设置列的值

$objPHPExcel->getActiveSheet()->setCellValue('b1', "$a2");

/* $objPHPExcel->getActiveSheet()->setCellValue('c1', "$a3");

$objPHPExcel->getActiveSheet()->setCellValue('d1', "$a4");*/

$objPHPExcel->getActiveSheet()->setCellValue('c1', "$a5");

$objPHPExcel->getActiveSheet()->setCellValue('d1', "$a6");

$objPHPExcel->getActiveSheet()->setCellValue('e1', "$a7");

$objPHPExcel->getActiveSheet()->setCellValue('f1', "$a8");

$con = mysql_connect($gDB['db_host'],$gDB['db_user'],$gDB['db_pass']) or die('数据库连接失败');

mysql_select_db($gDB['db_name']);

mysql_query('set NAMES utf8');

$result = mysql_query('select * from dc_goods');//连接数据库的就不用多解释了

$count=mysql_num_rows($result);

$i = 2;

//自增变量，用来控制行，因为标头占的第一行，所以这里从第二行开始

while($arr = mysql_fetch_array($result)){

$id = $arr['goods_id'];

$cname = $arr['goods_name'];

$last_update=date("Y-m-d",$arr['last_update']);

$sn=$arr['goods_sn'];

$cpdm=$arr['cpdm'];

$price=$arr['shop_price'];

$guige=$arr['guige'];

$objPHPExcel->getActiveSheet()->setCellValue('a'.$i, "$cname");

$objPHPExcel->getActiveSheet()->setCellValue('b'.$i, "$last_update");

/* $objPHPExcel->getActiveSheet()->setCellValue('c'.$i, "暂无");

$objPHPExcel->getActiveSheet()->setCellValue('d'.$i, "暂无");*/

$objPHPExcel->getActiveSheet()->setCellValue('c'.$i, "$sn");

$objPHPExcel->getActiveSheet()->setCellValue('d'.$i, "$cpdm");

$objPHPExcel->getActiveSheet()->setCellValue('e'.$i, "$guige");

$objPHPExcel->getActiveSheet()->setCellValue('f'.$i, "$price");

//这些跟上面的一样，开始一行一行的赋值。

$i++;

}

$count2=$count+3;

$count3=$count+2;

/* $objPHPExcel->getActiveSheet()->setCellValue('f'.$count3, "总价");

$objPHPExcel->getActiveSheet()->setCellValue('f'.$count2, "=SUM(F2:F$i)");*/

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(45);

//设置宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16);

$objPHPExcel->getActiveSheet()->getColumnDimension('d')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('c')->setWidth(20);

$objPHPExcel->getActiveSheet()->getColumnDimension('d')->setWidth(45);

$objPHPExcel->getActiveSheet()->getColumnDimension('e')->setWidth(45);

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

//创建表格类型，目前支持老版的excel5,和excel2007,也支持生成html,pdf,csv格式

//$download=$objWriter->save(str_replace('.php', '.xls', __FILE__));

//保存生成

$objPHPExcel->getActiveSheet()->setTitle(' 进价一览表');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)

$data=date("Ymdhis").'.xlsx';

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

header("Content-Disposition: attachment;filename=".$data."");

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

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

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

exit;