PHPExcel是一个强大的PHP处理Excel工具。如果你需要将数据输出成Excel格式，PHPExcel将是您不二的选择。
上面这句话是我今天使用PHPExcel的最大感触。
前面一段时间接手一个网站，需要对网站的一些数据处理成Excel格式后反馈给销售部门。
我接手之前同事都是手工sql查询后手工制作Excel，虽然每个月只需要处理一次，但是还是很繁琐，而且容易出错。
我接手之后，首先是将数据输出成Table，然后粘贴到Excel（因为公司没有购买office，所以我使用的是openoffice）里面
粘贴进去之后，格式变化很大。还需要手工调整，也是费时费力。
开始就有直接输出成Excel的打算，按前面方法做了两次之后，很崩溃，现在又要生成上个月的报表了，所以今天索性直接程序生成Excel
youdao一下之后，选用PHPExcel
PHPExcel网站：http://phpexcel.codeplex.com/
基于
LGPL的开源组件
按照里面的例子，很快我就写出了这个报表。以后只需要输入一个url，报表就生成了。
效果图如下：
因为不想在服务器上放太多东西，所以数据由服务器上脚本返回一个xml的数据。拿到数据后用simplexml解析数据
似乎用json更方便些，忘记服务器上支持不支持json了，因为以前是table的格式，所以改成xml很方便
下面就不扯了，直接上源代码
<?php
error_reporting(E_ALL);
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
$objxls = new PHPExcel();
$year = isset($_GET['y']) ? intval($_GET['y']) : date('Y', time());
$last_month = isset($_GET['m']) ? intval($_GET['m']) : date('m', time());
$excel_file = "$year-$last_month.xls";
for($_m = $last_month; $_m > 0; $_m--)
{
$month = $_m;
$month_short_string = date('M', strtotime("$year-$month-01"));
$month_long_string = date('F', strtotime("$year-$month-01"));
$data_file = "$year-$month.xml";
$remote_url = "http://wap***********.org/sales.php?y=$year&m=$month";
if(!file_exists($data_file))
{
file_put_contents($data_file, file_get_contents($remote_url));
}
if(!file_exists($data_file))
{
die('Data file not exist!');
}
$xml = simplexml_load_file($data_file);
if($_m != $last_month)
{
$objxls->createSheet();
}
$objxls->setActiveSheetIndex($last_month - $_m);
$active = $objxls->getActiveSheet();
$active->setCellValue('A1', "WAP Sales – $month_long_string $year");
$active->mergeCells('A1:H1');
$active->mergeCells('A2:H2');
$active->setCellValue('A3', '');
$active->setCellValue('B3', 'No of new sub as of the month');
$active->setCellValue('C3', 'No of total sub as of the month');
$active->setCellValue('D3', 'No of free download from subs');
$active->setCellValue('E3', 'No of $8888 download from subs');
$active->setCellValue('F3', 'No of download from subs');
$active->setCellValue('G3', ' 5HKD');
$active->setCellValue('H3', '4 HKD');
$count = count($xml->data->day);
$offst = 4;
for($i = 0; $i < $count; $i++)
{
$line = $i + $offst;
$active->setCellValue("A$line", $xml->data->day[$i]);
$active->setCellValue("B$line", $xml->data->xxxxxxxx[$i]);
$active->setCellValue("C$line", '');
$active->setCellValue("D$line", $xml->data->xxxxxxxx[$i]);
$active->setCellValue("E$line", $xml->data->xxxxxxxx[$i]);
$active->setCellValue("F$line", $xml->data->xxxxxxxx[$i]);
$active->setCellValue("G$line", $xml->data->xxxxxxxx[$i]);
$active->setCellValue("H$line", $xml->data->xxxxxxxx[$i]);
}
$total_line = $count + $offst;
$active->setCellValue("A$total_line", 'Total');
$active->setCellValue("B$total_line", $xml->sub->xxxxxxxx);
$active->setCellValue("C$total_line", '');
$active->setCellValue("D$total_line", $xml->sub->xxxxxxxx);
$active->setCellValue("E$total_line", $xml->sub->xxxxxxxx);
$active->setCellValue("F$total_line", $xml->sub->xxxxxxxx);
$active->setCellValue("G$total_line", $xml->sub->xxxxxxxx);
$active->setCellValue("H$total_line", $xml->sub->xxxxxxxx);
//SetBorder
$boder_obj = new PHPExcel_Style();
$boder_array = array(
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
);
$boder_obj->applyFromArray($boder_array);
$active->setSharedStyle($boder_obj, "A3:H$total_line");
//Set Background
$black_background = new PHPExcel_Style();
$black_background->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('argb' => 'FF000000')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
)
);
$active->setSharedStyle($black_background, "A4:A$total_line");
$active->setSharedStyle($black_background, "B3:H3");
$light_yellow_background = new PHPExcel_Style();
$light_yellow_background->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color'=> array('argb' => 'FFFFFF99')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
)
);
$active->setSharedStyle($light_yellow_background, "B4:B$total_line");
$gray_background = new PHPExcel_Style();
$gray_background->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color'=> array('argb' => 'FF999999')
),
'borders' => array(
'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
)
)
);
$active->setSharedStyle($gray_background, "B$total_line:H$total_line");
//Set Font Bold & color
$active->getStyle('A1')->getFont()->setBold(true);
$active->getStyle('B3:F3')->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$active->getStyle('G3:H3')->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE));
$active->getStyle("H4:H$total_line")->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED));
$active->getStyle("A4:A$total_line")->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE));
$active->getColumnDimension('A')->setAutoSize(true);
$active->getColumnDimension('B')->setAutoSize(true);
$active->getColumnDimension('C')->setAutoSize(true);
$active->getColumnDimension('D')->setAutoSize(true);
$active->getColumnDimension('E')->setAutoSize(true);
$active->getColumnDimension('F')->setAutoSize(true);
$active->getColumnDimension('G')->setAutoSize(true);
$active->getColumnDimension('H')->setAutoSize(true);
//Set All center
$active->getStyle("A1:H$total_line")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$active->setTitle($month_short_string);
}
$objxls->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$excel_file.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objxls, 'Excel5');
$objWriter->save('php://output');
die();
?>
屏蔽了一些敏感信息
代码都很简单，没有加太多注释。