20,359
社区成员
发帖
与我相关
我的任务
分享
include 'Plugin/PHPExcel/Classes/PHPExcel/IOFactory.php';
//print_r(get_class_methods('PHPExcel_IOFactory'));
class fill_template {
var $startrow = 0;
function __construct($fn) {
$this->tpl = PHPExcel_IOFactory::load($fn);
$this->target = clone $this->tpl;
}
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');
$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汉字'));
$p->add_data(array(1,2,3,4,5,6,7,8,9));
$p->output('xxx.xls');
PHPExcel_Worksheet->insertNewRowBefore()
/**
* Insert a new row, updating all possible related data
*
* @param int $pBefore Insert before this one
* @param int $pNumRows Number of rows to insert
* @throws PHPExcel_Exception
* @return PHPExcel_Worksheet
*/
public function insertNewRowBefore($pBefore = 1, $pNumRows = 1) {
if ($pBefore >= 1) {
$objReferenceHelper = PHPExcel_ReferenceHelper::getInstance();
$objReferenceHelper->insertNewBefore('A' . $pBefore, 0, $pNumRows, $this);
} else {
throw new PHPExcel_Exception("Rows can only be inserted before at least row 1.");
}
return $this;
}