EXCEL分页导出压缩包
public function goods()
{
$store_id = $this->store_id;
if (IS_POST) {
$page = I('post.page') ? I('post.page') : 1;
$limit = 100;
$directory_name = I('post.directory_name');
$param = array(
'store_id' => $store_id,
'showpurch_price' => session('global_store_jurisdiction')['showpurch_price'],
'identity' => I('session.identity'),
'page' => $page,
'limit' => $limit,
'fields' => array(
'id',
'goods_class_id',
'goods_name',
'goods_number',
'goods_code',
'purchase_price',
'retail_price',
'online_price',
'store_mem_status',
'store_mem_price',
'plus_mem_status',
'plus_price',
'online_state',
'shop_mem_status',
'shop_mem_price',
'vip_online_state',
'vip_retail_state',
'integral_mem_status',
'integral',
'guide_royalty_state',
'guide_royalty_custom_state',
'guide_royalty_price',
'guide_royalty_online_price',
'goods_num',
'more_num',
'less_num',
'sell_num',
'fictitious_num',
'company',
'specifications',
'pricing_method',
'production_date',
'shelf_life',
'status',
),
);
$SkuGoodsViewModel = new SkuGoodsViewModel();
$ret = $SkuGoodsViewModel->getGoodsList($param);
if (empty($ret['data'])) {
$this->ajaxReturn(array(
'status' => 0,
'msg' => '暂无商品可导出'
));
}
import("Vendor.PHPExcel.PHPExcel");
import("Vendor.PHPExcel.Writer.Excel5");
import("Vendor.PHPExcel.IOFactory.php");
$filename = "storeGoods_NO.{$page}";
$headArr = array(
array('id', 'ID'),
array('class_name', '分类'),
array('goods_name', '商品名称'),
array('goods_number', '货号'),
array('goods_code', '条码'),
array('purchase_price', '成本价'),
array('retail_price', '零售价'),
array('online_price', '线上价'),
array('store_mem_name', '是否开启店内会员价'),
array('store_mem_price', '店内会员价格'),
array('plus_mem_name', ' plus会员价开关'),
array('plus_price', 'Plus员价'),
array('online_state_name', '是否在商城出售'),
array('shop_mem_name', '是否开启商城会员价'),
array('shop_mem_price', '线上会员价'),
array('vip_online_name', '线上折扣'),
array('vip_retail_name', '线下折扣'),
array('integral_mem_name', '会员购买获得积分'),
array('integral', '会员消费送积分'),
array('guide_royalty_name', ' 购买该商品获得提成的状态'),
array('guide_royalty_custom_name', ' 导购员获得提成自定义状态'),
array('guide_royalty_price', ' 导购员线下固定金提成'),
array('guide_royalty_online_price', ' 导购员线上固定金提成'),
array('goods_num', '商品系统库存'),
array('more_num', '库存上限(超量值)'),
array('less_num', '库存下限(预警值)'),
array('sell_num', '已售数量'),
array('fictitious_num', '虚拟销量'),
array('company', '单位'),
array('specifications', '规格'),
array('pricing_method_name', '称重'),
array('production_date', '生产日期'),
array('shelf_life', '到期时间'),
array('status_name', '状态'),
);
$str = $filename . ' 表格生成时间:' . date('Y-m-d H:i:s');
if (!$directory_name) {
$num = createKey();
$directory_name = "./Uploads/storeGoodsExcel/{$store_id}/{$num}";
}
$this->setExcel($filename, $headArr, $ret['data'], $str, $directory_name);
$res = array(
'status' => 1,
'type' => 1,
'directory_name' => $directory_name,
'msg' => '请继续生成文件'
);
if (count($ret['data']) < $limit) {
$res['type'] = 2;
$res['msg'] = '数据生成完毕';
}
$this->ajaxReturn($res);
} else if (IS_GET) {
$directory_name = I('get.directory_name');
if (is_dir($directory_name)) {
$zip = new \ZipArchive();
$dir = ltrim($directory_name, './');
$arr = explode('/', $dir);
unset($arr[count($arr) - 1]);
$zipName = implode('/', $arr);
$zip_url = './' . $zipName . '/商品档案导出' . date('Y-m-d_H:i:s') . '.zip';
if ($zip->open($zip_url, \ZipArchive::CREATE) === TRUE) {
var_dump(123);
$this->addFileToZip($dir, $zip, '商品档案导出');
$zip->close();
if (!file_exists($zip_url)) {
echo "文件不存在";
exit();
}
$file = fopen($zip_url, "r");
Header("Content-type:application/octet-stream");
header("Accept-Ranges: bytes");
header("Accept-Length: " . filesize($zip_url));
header("Content-Disposition: attachment; filename=商品档案导出" . date('Y-m-d_H:i:s') . ".zip");
echo fread($file, filesize($zip_url));
fclose($file);
$this->delDirFile($directory_name);
rmdir($directory_name);
unlink($zip_url);
}
} else {
echo '文件不存在';
}
}
}
private function addFileToZip($path, $zip, $name = '商品档案导出')
{
$handler = opendir($path);
while (($filename = readdir($handler)) !== false) {
if ($filename != "." && $filename != "..") {
if (is_dir($path . "/" . $filename)) {
$this->addFileToZip($path . "/" . $filename, $zip);
} else {
$name = $name . "-第" . explode('.', $filename)[1] . "页.xls";
$zip->addFile($path . "/" . $filename, $name);
}
}
}
@closedir($path);
}
private function setExcel($expTitle, $expCellName, $expTableData, $common = "", $directory_name = "./Public")
{
$fileName = $expTitle;
$cellNum = count($expCellName);
$dataNum = count($expTableData);
vendor("PHPExcel.PHPExcel");
ob_clean();
$objPHPExcel = new \PHPExcel();
$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $common);
for ($i = 0; $i < $cellNum; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
}
for ($i = 0; $i < $dataNum; $i++) {
for ($j = 0; $j < $cellNum; $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), ' ' . $expTableData[$i][$expCellName[$j][0]]);
}
}
foreach ($cellName as $value) {
$objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$this->createDir($directory_name);
$path = "{$directory_name}/{$fileName}.xls";
$objWriter->save($path);
}
private function createDir($directory_name)
{
if (!is_dir($directory_name)) {
$temp = ltrim($directory_name, './');
$arr = explode('/', $temp);
$str = './';
foreach ($arr as $key => $sv) {
if (!is_dir("{$str}/{$sv}")) {
mkdir("{$str}/{$sv}", 0777);
}
$str = "{$str}/{$sv}";
}
}
}
private function delDirFile($dir)
{
$dh = opendir($dir);
while ($file = readdir($dh)) {
if ($file != "." && $file != "..") {
$full_path = $dir . "/" . $file;
if (!is_dir($full_path)) {
unlink($full_path);
} else {
$this->delDirFile($full_path);
}
}
}
closedir($dh);
}