php导出excel表格
[php]代码库
/** Error reporting */ |
error_reporting(E_ALL & ~E_NOTICE); |
date_default_timezone_set('Europe/London'); |
/** PHPExcel */ |
require_once '../export/phpexcel/Classes/PHPExcel.php'; |
// Create new PHPExcel object |
$objPHPExcel = new PHPExcel(); |
$objPHPExcel->getProperties()->setCreator("meilang") |
->setLastModifiedBy("meilang") |
->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"); |
// Add some data |
$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', '快递公司') |
->SetCellValue('L1', '地址1') |
->SetCellValue('M1', '地址2') |
->SetCellValue('N1', '快递号') |
->SetCellValue('O1', '手机号码') |
->SetCellValue('P1', '邮编') |
->SetCellValue('Q1', '会员留言') |
->SetCellValue('R1', '地区/省'); |
$i = 2; |
foreach ($results as $r) { |
$objPHPExcel->setActiveSheetIndex(0) |
->SetCellValue('A' . $i, $r['order_id']) |
->SetCellValue('B' . $i, $r['customer_name']) |
->SetCellValue('C' . $i, $r['name']) |
->SetCellValue('D' . $i, $r['product_nums']) |
->SetCellValue('E' . $i, $r['pname']) |
->SetCellValue('F' . $i, $r['addresstype']) |
->SetCellValue('G' . $i, $r['total']) |
->SetCellValue('H' . $i, $r['date_added']) |
->SetCellValue('I' . $i, $r['firstname']) |
->SetCellValue('J' . $i, $r['lastname']) |
->SetCellValue('K' . $i, $r['shipping_method']) |
->SetCellValue('L' . $i, $r['payment_address_1']) |
->SetCellValue('M' . $i, $r['payment_address_2']) |
->SetCellValue('N' . $i, $r['shipping_num']) |
->SetCellValue('O' . $i, $r['payment_phone']) |
->SetCellValue('P' . $i, $r['payment_postcode']) |
->SetCellValue('Q' . $i, $r['liuyan']) |
->SetCellValue('R' . $i, $r['payment_zone']); |
$i++; |
} |
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13); |
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16); |
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14); |
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7.86); |
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(27.5); |
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14); |
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(13.14); |
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(18.29); |
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(14); |
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12); |
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13.67); |
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(14); |
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(46); |
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(23.67); |
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(14.83); |
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(12); |
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(26); |
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(12); |
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25); |
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16); |
// 水平方向上两端对齐 |
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment:: HORIZONTAL_CENTER); |
// 垂直方向上中间居中 |
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment:: VERTICAL_CENTER); |
// 固定第一行 |
$objPHPExcel->getActiveSheet()->freezePane('A2'); |
// 设置默认字体、大小 |
$objPHPExcel->getDefaultStyle()->getFont()->setName('Times New Roman'); |
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9); |
// 设置第一行填充颜色 |
$objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->setFillType(PHPExcel_Style_Fill:: FILL_SOLID); |
$objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFill()->getStartColor()->setARGB('888888'); |
$objPHPExcel->getActiveSheet()->getStyle('A1:R1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color:: COLOR_WHITE); |
//*********************** 画出单元格边框 ***************************** |
$styleArray = array( |
'borders' => array( |
'allborders' => array( |
//'style' => PHPExcel_Style_Border::BORDER_THICK,// 边框是粗的 |
'style' => PHPExcel_Style_Border:: BORDER_THIN,// 细边框 |
'color' => array('argb' => 'ADADAD'), |
), |
), |
); |
$objPHPExcel->getActiveSheet()->getStyle('A1:R' . ++$i)->applyFromArray($styleArray);// 这里就是画出从单元格 A5 到Si 的边框 |
//*********************** 画出单元格边框结束 ***************************** |
// Rename sheet |
$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 (Excel5) |
ob_end_clean(); |
header('Content-Type: application/vnd.ms-excel'); |
header('Content-Disposition: attachment;filename="订单报表.xls"'); |
header('Cache-Control: max-age=0'); |
header('Content-type:application/vnd.ms-excel'); |
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); |
$objWriter->save('php://output'); |
