如何使用php更改excel单元格或列格式?
问题描述:
<?php
if($_POST) {
//here i give test $result datas
$result = array(array("Date"=>"2016-01-01","Time"=>"01:08:06","num"=>"","amount"=>"0.12"), array("Date"=>"2016-01-02","Time"=>"02:08:06","num"=>"1234567890","amount"=>"1.12"),array("Date"=>"2016-01-03","Time"=>"03:08:06","num"=>"1234567899","amount"=>"3.12")
);
require_once "phpexcel/class.writeexcel_workbook.inc.php";
require_once "phpexcel/class.writeexcel_worksheet.inc.php";
$fname = tempnam("/tmp", "simple.xls");
$workbook = &new writeexcel_workbook($fname);
$f_volume =& $workbook->addformat();
//$f_volume->set_align('left');
$f_volume->set_num_format('@');
$gen =& $workbook->addformat();
$gen->set_align('left');
$gen->set_num_format('General');
$dat =& $workbook->addformat();
//$dat->set_align('left');
$dat->set_num_format('DD/MM/YYYY');
$inv_ref =& $workbook->addformat();
$inv_ref->set_num_format('YYYY-MM-DD HH:MM:SS');
$setup =& $workbook->addformat();
$setup->set_align('left');
$setup->set_num_format('#,###.00#');
$tax =& $workbook->addformat();
$tax->set_align('left');
$tax->set_num_format('0.###%');
$worksheet = &$workbook->addworksheet("My Report");
$worksheet->write_string(0,0,'Date');
$worksheet->write_string(0,1,'Time');
$worksheet->write_string(0,2,'Dialled');
$worksheet->write_string(0,3,'Rate');
$worksheet->set_column(0,0,10,$dat);
$worksheet->set_column(0,1,10,$f_volume);
$worksheet->set_column(0,2,20,$f_volume);
$worksheet->set_column(0,3,10,$f_volume);
$j=1;
$unitTotal = 0;
$totalTotal = 0;
if(count($result) > 0) {
foreach($result as $outKey=>$rr){
$worksheet->write($j,0,date("d/m/Y",strtotime($rr['Date'])));
$worksheet->write($j,1,$rr['Time']);
$worksheet->write($j,2,$rr['Dialled']);
$worksheet->write($j,3,$rr['Rate']);
}
}
$workbook->close();
//header("Content-Type: application/x-msexcel; charset=UTF-8");
header("Content-Type: application/x-msexcel; name=Calls Report.xls");
header("Content-Disposition: inline; filename=Calls Report.xls");
$fh=fopen($fname, "rb");
fpassthru($fh);
unlink($fname);
}
?>
我上面提到的代码都用来生成“我的报告”创先争优和它运作良好,问题是以后打开Excel右键单击在细胞>设置单元格格式 那么它显示的文本格式,但我需要证明,如果我选择日期然后格式是日期,如果时间然后格式是时间,如果它的数量然后格式是货币,我已经使用“$ dat”在这里设置日期格式,但它不工作。如何使用php更改excel单元格或列格式?
答
从库文件,下载从GitHub库...
$objPHPExcel->getActiveSheet()->getStyle('A6')
->getNumberFormat()
->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME
);
这是否achive你想要什么?我现在无法自己测试它。
虽然,它看起来像你可能会使用不同/旧版本的库。你确定你使用的libarary支持你想要做的吗? – Relequestual
我会检查并让你知道 – Prime
看到你正在使用的图书馆的文档通常会有几个步骤,然后才来到SO,说有些东西不符合你的期望。 – Relequestual