1.下载PHPExcel组件http://pan.baidu.com/s/1i3vHGgt 这是我弄好的组件直接放进extensions里面就好。
2.在main.php主配置文件下,添加如下代码:
3.这是我的表结构:
CREATE TABLE `dpp_goods` (
`goods_id` mediumint(8) unsigned NOT NULL auto_increment,
`cat_id` smallint(5) unsigned NOT NULL,
`goods_combination` varchar(8) NOT NULL default '单品',
`goods_name` varchar(120) NOT NULL default '',
`approval_number` varchar(60) NOT NULL,
`goods_function` varchar(100) NOT NULL,
`adapt_people` varchar(60) NOT NULL default '',
`material` varchar(300) NOT NULL,
`goods_number` smallint(5) unsigned NOT NULL default '0',
`specifications` varchar(100) NOT NULL default '',
`market_price` decimal(10,2) unsigned NOT NULL default '0.00',
`shop_price` decimal(10,2) unsigned NOT NULL default '0.00',
`com_price` decimal(10,2) unsigned NOT NULL default '0.00',
`note` varchar(300) NOT NULL,
PRIMARY KEY (`goods_id`),
KEY `cat_id` (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=78 ;
CREATE TABLE `dpp_category` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`parent_id` smallint(5) unsigned default NULL,
`cat_name` varchar(90) NOT NULL default '',
PRIMARY KEY (`cat_id`),
KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;
4.在控制器下写如下代码:
/**
* 导出EXCEL
*/
public function actionExport(){
$objPHPExcel = new PHPExcel();
$sql = 'SELECT b.cat_name,a.* FROM {{goods}} a INNER JOIN {{category}} b ON a.cat_id = b.cat_id ORDER BY a.goods_id desc;';
$result = Yii::app()->db->createCommand($sql);
$query = $result -> queryAll();
//print_r($query);
//设置第1行的行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
//设置第2行的行高
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
//设置字体
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
$styleArray1 = array(
'font' => array(
'bold' => true,
'color'=>array(
'rgb' => '000000',
),
'size' => '24',
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
),
);
$styleArray2 = array(
'font' => array(
'color'=>array(
'rgb' => 'ff0000',
),
'size' => '10',
),
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
),
);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','产品定价表')
->setCellValue('A2','序号')
->setCellValue('B2','商品名称')
->setCellValue('C2','分类名称')
->setCellValue('D2','组合方式')
->setCellValue('E2','批准文号')
->setCellValue('F2','功能')
->setCellValue('G2','适应人群')
->setCellValue('H2','原料')
->setCellValue('I2','规格')
->setCellValue('J2','库存')
->setCellValue('K2','市场价格')
->setCellValue('L2','定价')
->setCellValue('M2','组合价')
->setCellValue('N2','备注');
$i=3;
foreach($query as $v){
//print_r($v);
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$i,$v[goods_id])
->setCellValue('B'.$i,$v[goods_name])
->setCellValue('C'.$i,$v[cat_name])
->setCellValue('D'.$i,$v[goods_combination])
->setCellValue('E'.$i,$v[approval_number])
->setCellValue('F'.$i,$v[goods_function])
->setCellValue('G'.$i,$v[adapt_people])
->setCellValue('H'.$i,$v[material])
->setCellValue('I'.$i,$v[specifications])
->setCellValue('J'.$i,$v[goods_number])
->setCellValue('K'.$i,$v[market_price])
->setCellValue('L'.$i,$v[shop_price])
->setCellValue('M'.$i,$v[com_price])
->setCellValue('N'.$i,$v[note]);
//单元格高度自适应
$objPHPExcel->getActiveSheet()->getDefaultRowDimension('A'.$i.':N'.$i)->setRowHeight(-1);
//单元格换行
// $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setWrapText(true);
// $objPHPExcel->getActiveSheet()->getStyle('H'.$i.':I'.$i)->getAlignment()->setWrapText(true);
//长度不够显示的时候换行
$objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getAlignment()->setShrinkToFit(true);
$objPHPExcel->getActiveSheet()->getStyle('H'.$i.':I'.$i)->getAlignment()->setWrapText(true);
//设置填充颜色
// $objPHPExcel->getActiveSheet()->getStyle('L'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
// $objPHPExcel->getActiveSheet()->getStyle('L'.$i)->getFill()->getStartColor()->setARGB('FFFFFF');
//使用数组定义L列的样式
$objPHPExcel->getActiveSheet()->getStyle('L'.$i)->applyFromArray($styleArray2);
//设置字体靠左
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':I'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('N'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
//设置字体靠右
$objPHPExcel->getActiveSheet()->getStyle('J'.$i.':M'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//设置字体垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A'.$i.':M'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置字体水平居中
$objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('J'.$i.':M'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i++;
}
// //加粗字体
// $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFont()->setBold(true);
// //设置字体垂直居中
// $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// //设置字体水平居中
// $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
//单元格加粗,居中:
// 将A1单元格设置为加粗,居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
//加粗字体
$objPHPExcel->getActiveSheet()->getStyle('A2:N2')->getFont()->setBold(true);
//设置字体垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A2:N2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置字体水平居中
$objPHPExcel->getActiveSheet()->getStyle('A2:N2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//字体靠左
//$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
//设置每列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(28);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(12);
//输出
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$filename="商品列表.xls";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
查询出来的字段名和excel里面的列名要一一对应。
这是生成的excel文件
简单明了,已经非常详细了,相信大家都能看得懂。(真的不能再详细了)