for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){ //从第A列开始输出 $val = trim($currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue());//ord()将字符转为十进制数 //如果输出汉字有乱码,则需将输出内容用iconv函数进行编码转换,如下将gb2312编码转为utf-8编码输出 if($num[0]['type'] == 3){ foreach ($dataHouse as $k=>$vals){ $amount += $vals['recharge']; switch ($val){ case '#制表日期': $val=date("Y-m-d",time()); break; case '#房产编号': $val=$vals['house_no']; break; case '#占用者名称': $val=$vals['owner_name']; break; case '#管理费费用说明': $val=$vals['fee_name']; break; case '#租金费用说明': $val=$vals['fee_name']; break; case '#电梯分摊费用说明': $val=$vals['fee_name']; break; case '#推广费费用说明': $val=$vals['fee_name']; break; case '#管理费充值时间': $val=date("Y-m-d",$vals['uptime']); break; case '#租金费充值时间': $val=date("Y-m-d",$vals['uptime']); break; case '#推广费充值时间': $val=date("Y-m-d",$vals['uptime']); break; case '#电梯分摊费充值时间': $val=date("Y-m-d",$vals['uptime']); break; case '#管理费开始时间': $val=date("Y-m-d",$vals['start_time']); break; case '#租金费开始时间': $val=date("Y-m-d",$vals['start_time']); break; case '#电梯分摊费开始时间': $val=date("Y-m-d",$vals['start_time']); break; case '#推广费开始时间': $val=date("Y-m-d",$vals['start_time']); break; case '#管理费': $val=$vals['recharge']; break; case '#租金': $val=$vals['recharge']; break; case '#电梯分摊费': $val=$vals['recharge']; break; case '#推广费': $val=$vals['recharge']; break; case '#费用合计': $val=$amount; break; } } }else{ foreach ($dataHouse as $k=>$vals){ switch ($val){ case '#抄表日期': $val=''; break; case '#房产编号': $val=$vals['resource_no']; break; case '#应收日期': $val=date("Y-m-d",$vals['should_date']); break; case '#占用者名称': $val=$vals['owner_name']; break; case '#制表日期': $val=date("Y-m-d",time()); break; case '#概要说明': $val=$vals['schema_number']; break; case '#电费费用说明': $val=$vals['fee_desc']; break; case '#水费费用说明': $val=$vals['fee_desc']; break; case '#管理费费用说明': $val=$vals['fee_desc']; break; case '#租金费用说明': $val=$vals['fee_name']; break; case '#电梯分摊费费用说明': $val=$vals['fee_name']; break; case '#推广费费用说明': $val=''; break; case '#电表上次行度': $val=''; break; case '#电表本次行度': $val=''; break; case '#电表用量': $val=''; break; case '#电表单价': $val=''; break; case '#电表金额': $val=''; break; case '#水表金额': $val=''; break; case '#管理费': $val=$vals['amount']; break; case '#租金': $val=$vals['amount']; break; case '#电梯分摊': $val=$vals['amount']; break; case '#推广费': $val=$vals['amount']; break; case '#本次应收': $val=$vals['amount']; break; case '#未收总计': $val=$vals['amount']; break; case '#水表上次行度': $val=''; break; case '#水表本次行度': $val=''; break; case '#水表用量': $val=''; break; case '#水表单价': $val=''; break; case '#管理费标准': $val=''; break; case '#建筑面积': $val=$vals['build_area']; break; case '#累计欠款': $val=''; break; case '#滞纳金计算日期': $val=$vals['late_calc_time']; break; } } } // $sheet->getColumnDimension($currentColumn) ->setAutoSize(true); // 自动适应列宽 $PHPExcels->getActiveSheet()->setCellValue($currentColumn."$i", "$val"); //$PHPExcels->getColumnDimension($currentColumn)->setAutoSize(true); // $objActsheet->duplicateStyle(); } } }
$excel = new Spreadsheet_Excel_Reader();
$excel->read("a.xls");//读取的xls文件
$key=array();
for ($i = 1; $i <= $excel->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $excel->sheets[0]['numCols']; $j++) {
//显示每个单元格内容
if(isset($excel->sheets[0]['cells'][$i][$j])) {
$key[]=$excel->sheets[0]['cells'][$i][$j]."\r\n";
}
}
}
$b='b.xls';//写入到xls文件
$h=fopen($b,'a');
foreach($key as $val){
$str.=$val;
}
for($i=0;$i<10;$i++){//复制的次数
fwrite($h,$str);
}
fclose($h);
将一个只有格式的 Excel 作为模板
然后从数据库里读取数据填写进去
每条数据填一个,最后保存为一个 Excel 以便打印如果是的话,存在一个问题:如何知道哪个单元格填什么?
有什么约定吗?
require_once APPPATH.'libraries/PHPExcel/Iofactory'.EXT;
require_once APPPATH.'libraries/Phpexcel'.EXT;
$filePath = iconv("utf-8","gb2312",$num[0]['path']);
$PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($filePath)){
$PHPReader = new PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($filePath)){
echo 'no Excel';
return ;
}
}
$PHPExcel = $PHPReader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0); //读取excel文件中的第一个工作表
//$sheet = $PHPExcels->getActiveSheet(); // 获取当前激活的工作表
$i = 1;
foreach ($data as $k=>$v){
if($num[0]['type'] == 3)
$dataHouse = mod_fee::view_pre_month_man(self::$corpId,$v['house_no']);
else
$dataHouse = mod_fee::view_fee_payment_select($value,self::$corpId,$v['house_id']);
$allColumn = $currentSheet->getHighestColumn(); //取得最大的列号 $amount = 0;
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
for($currentRow = 2;$currentRow <= $allRow;$currentRow++){ //从第二行开始输出,因为excel表中第一行为列名
$i++;
for($currentColumn= 'A';$currentColumn<= $allColumn; $currentColumn++){ //从第A列开始输出
$val = trim($currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue());//ord()将字符转为十进制数 //如果输出汉字有乱码,则需将输出内容用iconv函数进行编码转换,如下将gb2312编码转为utf-8编码输出
if($num[0]['type'] == 3){ foreach ($dataHouse as $k=>$vals){
$amount += $vals['recharge']; switch ($val){
case '#制表日期':
$val=date("Y-m-d",time());
break;
case '#房产编号':
$val=$vals['house_no'];
break;
case '#占用者名称':
$val=$vals['owner_name'];
break;
case '#管理费费用说明':
$val=$vals['fee_name'];
break;
case '#租金费用说明':
$val=$vals['fee_name'];
break;
case '#电梯分摊费用说明':
$val=$vals['fee_name'];
break;
case '#推广费费用说明':
$val=$vals['fee_name'];
break;
case '#管理费充值时间':
$val=date("Y-m-d",$vals['uptime']);
break;
case '#租金费充值时间':
$val=date("Y-m-d",$vals['uptime']);
break;
case '#推广费充值时间':
$val=date("Y-m-d",$vals['uptime']);
break;
case '#电梯分摊费充值时间':
$val=date("Y-m-d",$vals['uptime']);
break;
case '#管理费开始时间':
$val=date("Y-m-d",$vals['start_time']);
break;
case '#租金费开始时间':
$val=date("Y-m-d",$vals['start_time']);
break;
case '#电梯分摊费开始时间':
$val=date("Y-m-d",$vals['start_time']);
break;
case '#推广费开始时间':
$val=date("Y-m-d",$vals['start_time']);
break;
case '#管理费':
$val=$vals['recharge'];
break;
case '#租金':
$val=$vals['recharge'];
break;
case '#电梯分摊费':
$val=$vals['recharge'];
break;
case '#推广费':
$val=$vals['recharge'];
break;
case '#费用合计':
$val=$amount;
break;
}
}
}else{
foreach ($dataHouse as $k=>$vals){ switch ($val){
case '#抄表日期':
$val='';
break;
case '#房产编号':
$val=$vals['resource_no'];
break;
case '#应收日期':
$val=date("Y-m-d",$vals['should_date']);
break;
case '#占用者名称':
$val=$vals['owner_name'];
break;
case '#制表日期':
$val=date("Y-m-d",time());
break;
case '#概要说明':
$val=$vals['schema_number'];
break;
case '#电费费用说明':
$val=$vals['fee_desc'];
break;
case '#水费费用说明':
$val=$vals['fee_desc'];
break;
case '#管理费费用说明':
$val=$vals['fee_desc'];
break;
case '#租金费用说明':
$val=$vals['fee_name'];
break;
case '#电梯分摊费费用说明':
$val=$vals['fee_name'];
break;
case '#推广费费用说明':
$val='';
break;
case '#电表上次行度':
$val='';
break;
case '#电表本次行度':
$val='';
break;
case '#电表用量':
$val='';
break;
case '#电表单价':
$val='';
break;
case '#电表金额':
$val='';
break;
case '#水表金额':
$val='';
break;
case '#管理费':
$val=$vals['amount'];
break;
case '#租金':
$val=$vals['amount'];
break;
case '#电梯分摊':
$val=$vals['amount'];
break;
case '#推广费':
$val=$vals['amount'];
break;
case '#本次应收':
$val=$vals['amount'];
break;
case '#未收总计':
$val=$vals['amount'];
break;
case '#水表上次行度':
$val='';
break;
case '#水表本次行度':
$val='';
break;
case '#水表用量':
$val='';
break;
case '#水表单价':
$val='';
break;
case '#管理费标准':
$val='';
break;
case '#建筑面积':
$val=$vals['build_area'];
break;
case '#累计欠款':
$val='';
break;
case '#滞纳金计算日期':
$val=$vals['late_calc_time'];
break; }
}
}
// $sheet->getColumnDimension($currentColumn) ->setAutoSize(true); // 自动适应列宽
$PHPExcels->getActiveSheet()->setCellValue($currentColumn."$i", "$val");
//$PHPExcels->getColumnDimension($currentColumn)->setAutoSize(true);
// $objActsheet->duplicateStyle(); }
}
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$num[0]['title'].'"');
header('Cache-Control: max-age=0'); $PHPExcels->setActiveSheetIndex(0);
$objWriter = Iofactory::createWriter($PHPExcels, 'Excel5');
$objWriter->save('php://output');
var $startrow = 0;
function __construct($fn) {
$this->tpl = PHPExcel_IOFactory::load($fn);
}
function add_data($ar) {
$ar = array_values($ar);
if(!isset($this->target)) $this->target = clone $this->tpl;
$sheet = $this->tpl->getActiveSheet();
$i = 0;
foreach($sheet->getRowDimensions() as $y=>$row) {
foreach($sheet->getColumnDimensions($row) as $x=>$col) {
$txt = trim($sheet->getCell($x.$y)->getValue());
if($txt && $txt{0} == '#') $txt = isset($ar[$i]) ? $ar[$i++] : '';
$h = $y + $this->startrow;
$this->target->getActiveSheet()->getCell("$x$h")->setValue($txt);
$this->target->getActiveSheet()->duplicateStyle($sheet->getStyle("$x$y"), "$x$h");
}
}
foreach($sheet->getMergeCells() as $merge) {
$merge = preg_replace('/\d+/e',"$0+$this->startrow", $merge);
$this->target->getActiveSheet()->mergeCells($merge);
}
$this->startrow += $sheet->getHighestRow() + 3;//多加3行便于裁剪
}
function output($fn) {
$t = PHPExcel_IOFactory::createWriter($this->target, 'Excel5');
$t->save($fn);
}
}
用法$p = new fill_template('tpl.xls');//实例化并加载模板xls
$p->add_data(array(1,2,3,4,5,6,7,8,9));//数据要按模板中“#”出现的次序排列
$p->add_data(array(1,2,3,4,5,6,7,8,9, 'a汉字'));//汉字要用utf-8的
$p->add_data(array(1,2,3,4,5,6,7,8,9));
$p->output('xxx.xls');//输出到文件