Php+sqlsrv exports the excel of a complex header
printed headers have been processed
cannot export smoothly in the format
$sql = "select row_number() over (order by id desc ) as rownum
,[companyname],isnull(dbo.get_IdToValue(unitprop),"") as [unitprop], isnull(dbo.get_IdToValue(unitspec),"") as [unitspec], isnull(dbo.get_IdToValue(fundspychannels),"") [fundspychannels],[officepostnum] ,[staffnum],[retirednum],[availcar],[scrapcar], [quotacar],[dbnum], isnull(dbo.get_IdToValue(brand),"") [brand], isnull(dbo.get_IdToValue(cartype),"") as [cartype],isnull(dbo.get_IdToValue(output),"") as [output],isnull(dbo.get_IdToValue(seats),"") as [seats],[oneprice],[num],[price],[appurchrate],[totalprice],isnull(dbo.get_IdToValue(afundfrom),"") as [afundfrom]
,isnull(dbo.get_IdToValue(abuytype),"") as [abuytype],[acaruse],[areason],[auditopn],[auditorname],CONVERT(varchar(100), auditime, 23) as [auditime]
, (case when auditflag = 0 then "" when auditflag =1 then "" when auditflag=2 then "" end ) as auditflag
,[approveopn],[approver]
,(case when approveflag = 0 then "" when approveflag =1 then "" when approveflag=2 then "" end ) as approveflag,CONVERT(varchar(100), approvetime, 23) as [approvetime],[acfzrname],[managername],[magtel]
,(select manname from t_account where acid=V_applypurchasecar.acid) as operator,[remark],CONVERT(varchar(100), applydate, 23) as [applydate],CONVERT(varchar(100), created, 23) as created from V_applypurchasecar";
if ($where != "") {
$sql = $sql . " where " . $where;
}
$result = $db->query($sql);
//$title = "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t()\t()\t()\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
$title = "<table id="tb" name="tb" class="ptbiankuang" width="100%" border="0" cellspacing="0" cellpadding="0" style="margin-top:-1px;">
<tr style="height:36pt;">
</tr>
<tr style="height:36pt;">
<th rowspan=2></th>
<th rowspan=2></th>
<th colspan=6></th>
<th colspan=3></th>
<th colspan=7></th>
<th rowspan=2></th>
</tr>
<tr style="height:36pt;">
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr></table>";
$fn = saveexcelfile($title, $result);
echo $fn;
freedbquerystmt($result);
exit;
The simple header before
can be exported in format, but now after changing the title, it is not good to make
function saveexcelfile($titles,$stmt)
{
$titles=iconv("UTF-8", "GBK", $titles);
$str = "";
$numFields = sqlsrv_num_fields( $stmt );
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_BOTH))
{
/* Iterate through the fields of each row. */
$rowstr="";
for($i = 0; $i < $numFields; $iPP)
{
$cols=$row[$i];
if($rowstr=="")
{
// $rowstr=$cols;
$rowstr=iconv("UTF-8", "GBK", $cols);
}else{
//$rowstr=$rowstr."\t".$cols;
$rowstr=$rowstr."\t".iconv("UTF-8", "GBK", $cols);
}
}
$str.=$rowstr.PHP_EOL;
}
$str=$titles. PHP_EOL .$str;
$fn=date("YmdHis").rand(100,999).".xls";
$path="download/".$fn;
ob_end_clean();
header("Content-type: text/html; charset=utf-8");
if($f = file_put_contents($path, $str,FILE_APPEND)){// (PHP 5)
return $path;
}else{
return "error";
}
}
the table export function is previously encapsulated.
how can I modify title?
well, if you still use this method, it is recommended to change the excel package and install composer.
:require_once dirname(__FILE__) . '/Classes/PHPExcelCore.php';
:
$date = date('Y-m-d');
$title = array(
array('value' => "",'col' => 19,'row' => 3,
'children' =>
array(
array('value' => ':'.$date,'col' => 2,'row' => 1,
'children' =>
array(
array('value' => '','col' => 1,'row' => 3,'width' => 20),
array('value' => '','col' => 1,'row' => 3,'width' => 25),
array('value' => '','col' => 6,'row' => 1,
'children' =>
array(
array('value' => '','col' => 1,'row' => 2),
array('value' => '','col' => 1,'row' => 2),
array('value' => '','col' => 1,'row' => 2,'width' => 25),
array('value' => '','col' => 1,'row' => 2,'width' => 25),
array('value' => '','col' => 1,'row' => 2,'width' => 25,),
array('value' => '','col' => 1,'row' => 2,'width' => 25)
)
),
array('value' => '','col' => 2,'row' => 1,
'children' =>
array(
array('value' => '','col' => 1,'row' => 2,'width' => 25),
array('value' => '','col' => 1,'row' => 2,'width' => 25),
/*array('value' => '','col' => 1,'row' => 2,'width' => 25)*/
)
),
array('value' => '','col' => 7,'row' => 1,
'children' =>
array(
array('value' => '','col' => 1,'row' => 2,'width' => 25),
array('value' => '','col' => 1,'row' => 2,'width' => 25),
array('value' => '','col' => 1,'row' => 2,'width' => 20),
array('value' => '','col' => 1,'row' => 2,'width' => 20),
array('value' => '','col' => 1,'row' => 2,'width' => 20),
array('value' => '','col' => 1,'row' => 2,'width' => 20),
/*array('value' => '','col' => 1,'row' => 2),
array('value' => '','col' => 1,'row' => 2,'width' => 25),*/
array('value' => '','col' => 1,'row' => 2,'width' => 25)
)
),
array('value' => '','col' => 1,'row' => 3)
)
),
array('value' => '','col' => 14),
array('value' => ':','col' => 2),
)
)
);
$arr = array();
$data = array();
$oneprice = 0;
$oneprice1 = '';
$num = 0;
$appurchrate = 0;
$price = 0;
while ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) {
//
$row['brandname'] = getBrandSystem($row['brand'],1,$db);
$row['systemname'] = getBrandSystem($row['systemid'],2,$db);
// $row['brandname']=$row['brand']==0?'':getbrand($row['brand'],$db);
// if(positive_integer($row['systemid'])){
// $row['systemname']=getsystem($row['systemid'],$db);
// }else{
// $row['systemname']=$row['systemid']=='0'?'':$row['systemid'];
// }
$arr['rownum'] = $row['rownum'];
$arr['companyname'] = $row['companyname'];
$arr['unitspec'] = $row['unitspec'];
$arr['unitprop'] = $row['unitprop'];
$arr['fundspychannels'] = $row['fundspychannels'];
if (!empty($row['officepostnum']))
{
$arr['officepostnum'] = $row['officepostnum'];
}
else
{
$arr['officepostnum'] = '';
}
if (!empty($row['staffnum']))
{
$arr['staffnum'] = $row['staffnum'];
}
else
{
$arr['staffnum'] = '';
}
if (!empty($row['retirednum']))
{
$arr['retirednum'] = $row['retirednum'];
}
else
{
$arr['retirednum'] = '';
}
if (!empty($row['quotacar']))
{
$arr['quotacar'] = $row['quotacar'];
}
else
{
$arr['quotacar'] = '';
}
if (!empty($row['availcar']))
{
$arr['availcar'] = $row['availcar'];
}
else
{
$arr['availcar'] = '';
}
/*if (!empty($row['scrapcar']))
{
$arr['scrapcar'] = $row['scrapcar'];
}
else
{
$arr['scrapcar'] = '';
}*/
if (!empty($row['brandname']) && !empty($row['systemname'])){
$arr['brand'] = $row['brandname']."-".$row['systemname'];
}else {
$arr['brand'] = $row['brandname'].$row['systemname'];
}
$arr['carmodel'] = $row['carmodel'];
$arr['cartype'] = $row['cartype'];
if ($row["output"] && $row["seats"]) {
$arr['output'] = "".$row["output"]."".$row["seats"]."";
} elseif (empty($row["seats"])){
$arr['output'] = $row["output"];
} else {
$arr['output'] = $row["seats"];
}
$arr['num'] = $row['num'];
$arr['oneprice'] = sprintf("%01.3f", $row["oneprice"]);
/*$arr['appurchrate'] = sprintf("%01.3f", $row['appurchrate']);
$arr['price'] = $row["num"]*($row["oneprice"]+$row['appurchrate']);*/
$arr['afundfrom'] = $row['afundfrom'];
$arr['remark'] = $row['remark'];
$num += $row['num'];
$price = sprintf("%01.3f", $row["oneprice"]);
$oneprice += $price;
$oneprice1 += $row["num"]*($row["oneprice"]);
/*$appurchrate += $row['appurchrate'];
$price += $row["num"]*($row["oneprice"]+$row['appurchrate']);*/
array_push($data,$arr);
}
$lData = array('','','','','','','','','','','','','','',$num,':'.$oneprice1,'');
array_push($data,$lData);
$lData1 = array(':','','','','','','',':','','','','',':','','','','','');
array_push($data,$lData1);
$today = date('Y-m-d');
$manname = $_SESSION['user']['manname'];
$lData2 = array('','','','','','','','','','','','','','','','',':',$manname);
array_push($data,$lData2);
$arrayLevel = arrayLevel($title);
$phpExcelCore = new phpExcelCore();
$fn = $phpExcelCore::RecursionCreateExecl($title,$data,$arrayLevel);
echo $fn;
freedbquerystmt($result);
exit;
