public function downloadClearance(){
$idArr = (array)(json_decode(html_entity_decode($this->post['id'])));
$config = [
'path' => $this->getTmpDir() . '/',
];
$name = "订单统计信息" . date("YmdHis") . ".xlsx";
$fileName = $name;
$xlsxObject = new \Vtiful\Kernel\Excel($config);
// Init File
// $fileObject = $excel->fileName($fileName);
$fileObject = $xlsxObject->fileName($fileName);
$head = array();
$word = array();
$sqlWord = array();
for ($i = 0;$i<14;$i++){
$head[] = "";
}
for ($i = 0;$i<4;$i++){
$box[] = "";
}
$box[] = "法定数量";
$box[] = "法定单位";
$box[] = "第二法定数量";
$box[] = "第二法定单位";
for ($i = 0;$i<8;$i++){
$box[] = "";
}
$box[] = "备注";
$box[] = "";
$box[] = "收货人姓名";
$box[] = "收货人电话";
$box[] = "收货人城市";
$box[] = "收货人洲";
$box[] = "收货人地址";
$box[] = "生产销售企业代码";
$box[] = "生产销售企业名称";
$word[] = $box;
$item_num = 2;
//循环运单ID,在循环中查询具体数据
foreach ($idArr as $id) {
$thisOrder = $this->sqlQuery("select
addressee_name,country,`state`,city,address_detail,phone,tracking_number,
express_number,`number`
from order_data where id = '$id' ");
$all_item = $this->sqlQueryAll("
select `name`,sku,`count`,unit_of_measurement,suttle,rough_weight,
price,specification,text
from order_item
where order_data_id = '$id' order by time ");
foreach ($all_item as $array){
$box = array(); //一维数组,放每个数据,直到塞满一条就清空
$box[] = $array['name'];
$box[] = $array['sku'];
$box[] = $array['count'];
$box[] = $array['unit_of_measurement'];
$box[] = "";
$box[] = "";
$box[] = "";
$box[] = "";
$box[] = $array['count'];
$box[] = $array['suttle']*$array['count'];
$box[] = $array['rough_weight']*$array['count'];
$box[] = "";
$box[] = $array['price'];
$box[] = $array['price']*$array['count'];
$box[] = "";
$box[] = $array['specification'];
$box[] = $array['text'];
$box[] = $thisOrder['country'];
$box[] = $thisOrder['addressee_name'];
$box[] = $thisOrder['phone'];
$box[] = $thisOrder['city'];
$box[] = $thisOrder['state'];
$box[] = $thisOrder['address_detail'];
$box[] = "";
$box[] = "";
$box[] = $thisOrder['number'];
$box[] = $thisOrder['tracking_number'];
$box[] = $thisOrder['express_number'];
$box[] = "";
$word[] = $box; //二维数组,[0] => 第一条数据,[1] => 第二条数据
}
$item_num += count($all_item);
}
// Writing data to a file ......
$fileObject->header($head)->
data($word);
$fileHandle = $fileObject->getHandle();
//这里分别使用了不同的样式对象去处理一行中不同格的样式
$format1 = new \Vtiful\Kernel\Format($fileHandle);
$format2 = new \Vtiful\Kernel\Format($fileHandle);
$format3 = new \Vtiful\Kernel\Format($fileHandle);
$format4 = new \Vtiful\Kernel\Format($fileHandle);
$format5 = new \Vtiful\Kernel\Format($fileHandle);
$format6 = new \Vtiful\Kernel\Format($fileHandle);
$format7 = new \Vtiful\Kernel\Format($fileHandle);
/**
* 背景颜色
* 只有在这里使用复合样式才能对已经有样式的区域添加样式
*/
$backgroundStyleBlue = $format1->background(
\Vtiful\Kernel\Format::COLOR_BLUE
)->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
$backgroundStyleSilver = $format2->background(
\Vtiful\Kernel\Format::COLOR_SILVER
)->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
$backgroundStyleYellow = $format3->background(
\Vtiful\Kernel\Format::COLOR_YELLOW
)->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
$backgroundStyleOrange = $format4->background(
\Vtiful\Kernel\Format::COLOR_ORANGE
)->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
$backgroundStyleCyan = $format5->background(
\Vtiful\Kernel\Format::COLOR_CYAN
)->border(\Vtiful\Kernel\Format::BORDER_THIN)->toResource();
/**
* 字体颜色
*
*/
$colorStyleRed = $format6->background(
\Vtiful\Kernel\Format::COLOR_CYAN)->fontColor(\Vtiful\Kernel\Format::COLOR_RED)->toResource();
/**
* 全局样式
*/
$default = $format7
->border(\Vtiful\Kernel\Format::BORDER_THIN)
->toResource();
/**
* 对有样式的列foreach添加样式,但是我发现还是不行,因为这里的遍历使用mergeCells会把表格中
* 原本添加了数据的单元格中的数据搞消失掉,只剩下覆盖的样式了,我将表格添加数据放在添加样式之后
* 就变成了表格中数据覆盖掉样式的情况
*/
for ($i = 3;$i<=$item_num;$i++){
$fileObject->mergeCells('B'.$i.':B'.$i,"", $backgroundStyleYellow);
}
// Outptu
$filePath =
$fileObject
->defaultFormat($default)//在这里添加了全局样式,但是没有加样式的表格部分还是没有这个表格边框的样式
->mergeCells('A1:A1',"表项说明:", $backgroundStyleCyan)
->mergeCells('B1:E1', '黄色底色：表示客户需提供的必填信息 ',$backgroundStyleSilver)
->mergeCells('F1:I1', '白色底色：表示非必须填报内容')
->mergeCells('J1:O1', '绿色底色：表示运营人员填报的信息',$backgroundStyleOrange)
->mergeCells('P1:P1', '申报类型',$colorStyleRed)
->mergeCells('Q1:Q1', '简化申报',$backgroundStyleCyan)
->mergeCells('R1:R1', '申报方式',$backgroundStyleYellow)
->mergeCells('S1:S1', '9810',$backgroundStyleYellow)
->mergeCells('A2:A2', '商品品名',$backgroundStyleSilver)
->mergeCells('B2:B2', '商品编码(税则号)',$backgroundStyleSilver)
->mergeCells('C2:C2', '成交数量',$backgroundStyleSilver)
->mergeCells('D2:D2', '成交单位',$backgroundStyleSilver)
->mergeCells('I2:I2', '件数',$backgroundStyleSilver)
->mergeCells('J2:J2', '净重(千克)',$backgroundStyleSilver)
->mergeCells('K2:K2', '毛重(千克)',$backgroundStyleSilver)
->mergeCells('L2:L2', '币制',$backgroundStyleSilver)
->mergeCells('M2:M2', '成交单价',$backgroundStyleSilver)
->mergeCells('N2:N2', '成交总价',$backgroundStyleSilver)
->mergeCells('O2:O2', '货主信息',$backgroundStyleSilver)
->mergeCells('P2:P2', '品牌型号',$backgroundStyleSilver)
->mergeCells('R2:R2', '收货人国家',$backgroundStyleSilver)
->mergeCells('Z2:Z2', '订单号',$backgroundStyleYellow)
->mergeCells('AA2:AA2', '运单号',$backgroundStyleYellow)
->mergeCells('AB2:AB2', '提运单号',$backgroundStyleOrange)
->mergeCells('AC2:AC2', '导入批次',$backgroundStyleOrange)
// ->setColumn(('B3:B'.$item_num), 30, $backgroundStyleYellow)
// ->setColumn('B3:B10', 30, $backgroundStyleYellow)
->output();
// Set Header
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Content-Length: ' . filesize($filePath));
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Cache-Control: max-age=0');
header('Pragma: public');
ob_clean();
flush();
if (copy($filePath, 'php://output') === false) {
// Throw exception
var_dump("导出出现问题");
}
// Delete temporary file
@unlink($filePath);
}