数据库数据 【导出】
/** 导出 Excel **/
if ($_REQUEST['act'] == 'print_excel')
{
$filter['gift_name'] = empty($_REQUEST['gift_name']) ? '' : trim($_REQUEST['gift_name']); // 名称
$filter['gift_type'] = empty($_REQUEST['gift_type']) ? '' : trim($_REQUEST['gift_type']); // 类型
$filter['is_putaway'] = empty($_REQUEST['is_putaway']) ? '': trim($_REQUEST['is_putaway']); // 是否上架
if (isset($_REQUEST['is_ajax']) && $_REQUEST['is_ajax'] == 1)
{
$filter['gift_name'] = json_str_iconv($filter['gift_name']);
$filter['gift_type'] = json_str_iconv($filter['gift_type']);
$filter['is_putaway'] = json_str_iconv($filter['is_putaway']);
}
$where = 'WHERE 1';
if (!empty($filter['gift_name']))
{
$where .= " AND gift_name LIKE '%" . mysql_like_quote($filter['gift_name']) . "%'";
}
if (!empty($filter['gift_type']))
{
$where .= " AND gift_type=".$filter['gift_type'];
}
if (!empty($filter['is_putaway']))
{
$where .= " AND is_putaway LIKE '%" . mysql_like_quote($filter['is_putaway']) . "%'";
}
$sql = 'SELECT `id`,`gift_type`,`gift_name`,`is_putaway`,`need_integral`,`need_money`,`stock_number`,`already_exchanged_number` FROM ' .$GLOBALS['ecs']->table('gift_exchange'). $where;
$res=$GLOBALS['db']->getAll($sql);
// 引入phpexcel核心类文件
require_once ROOT_PATH . '/includes/phpexcel/Classes/PHPExcel.php';
// 实例化excel类
$objPHPExcel = new PHPExcel();
// 操作第一个工作表
$objPHPExcel->setActiveSheetIndex(0);
// 设置sheet名
$objPHPExcel->getActiveSheet()->setTitle('积分商城商品列表');
//设置默认行高
// $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(50);
// //设置第一行行高
// $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
// 设置表格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
// 列名表头文字加粗
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
// 列表头文字居中
$objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 列名赋值
$objPHPExcel->getActiveSheet()->setCellValue('A1', '序号');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '类型');
$objPHPExcel->getActiveSheet()->setCellValue('C1', '名称');
$objPHPExcel->getActiveSheet()->setCellValue('D1', '是否上架');
$objPHPExcel->getActiveSheet()->setCellValue('E1', '所需积分数');
$objPHPExcel->getActiveSheet()->setCellValue('F1', '所需现金');
$objPHPExcel->getActiveSheet()->setCellValue('G1', '可兑换数量');
$objPHPExcel->getActiveSheet()->setCellValue('H1', '已兑换数量');
// 数据起始行
$row_num = 2;
// 向每行单元格插入数据
foreach($res as $key => $value)
{
switch ($value['gift_type']) {
case '1':
$gift_type='优惠券';
break;
case '2':
$gift_type='商品';
break;
case '3':
$gift_type='票券';
break;
}
// 设置排序列、是否显示列居中显示
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'H' . $row_num)->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置所有垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_num . ':' . 'H' . $row_num)->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置单元格数值
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row_num, $value['id']);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $row_num, $gift_type);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $row_num, $value['gift_name']);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $row_num, ($value['is_putaway'] ? '是' : '否'));
$objPHPExcel->getActiveSheet()->setCellValue('E' . $row_num, $value['need_integral']);
$objPHPExcel->getActiveSheet()->setCellValue('F' . $row_num, $value['need_money']);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $row_num, $value['stock_number']);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $row_num, $value['already_exchanged_number']);
$row_num++;
}
$outputFileName = '积分商城商品列表' . time() . '.xls';
$xlsWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="' . $outputFileName . '"');
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$xlsWriter->save("php://output");
echo file_get_contents($outputFileName);
}
数据库数据 【导入】
if ($_REQUEST['act'] == 'insert_excel')
{
$tmp = $_FILES['file_excel']['tmp_name'];
$tmp_name = $_FILES['file_excel']['name'];
$file_type = strtolower(substr($tmp_name,strrpos($tmp_name,'.')+1));
$exceldata = readExcel($tmp,$file_type);
if(empty($exceldata)){
sys_msg("您选择的文件类型不正确!");
}
$insertdata = insertdata($exceldata);
if($insertdata['status']==1){
sys_msg("数据导入失败: " . $insertdata['msg']);
}
if($insertdata['status']==0){
sys_msg("数据导入失败: " . $insertdata['msg']);
}
}
function readExcel($path,$file_type)
{
include_once(ROOT_PATH .'/includes/phpexcel/Classes/PHPExcel.php');
include_once(ROOT_PATH .'/includes/phpexcel/Classes/PHPExcel/IOFactory.php');
switch ($file_type) {
case 'xls':
$type = 'Excel5';
break;
case 'xlsx':
$type = 'Excel2007';
break;
}
$xlsReader = PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$Sheets = $xlsReader->load($path);
$dataArray = $Sheets->getSheet(0)->toArray();
return $dataArray;
}
function insertdata($exceldata)
{
$rowins = 0;
$str = array();
$res = '';
foreach ($exceldata as $key => $value) {
if ($key== 0) {
if (empty($value) || trim($value[0]) != '票券号' || trim($value[1]) != '合作方') {
sys_msg("文件的格式不正确!");
}
}
if ($key > 0) {
$ticket_number = trim(addslashes($value[0]));
$partners = trim(addslashes($value[1]));
$is_take = '0';
$taked_time = '';
$create_time = local_date('Y-m-d H:i:s');
$is_putaway = '1';
$str[]= "('','$ticket_number','$partners','$is_take','$taked_time','$create_time','$is_putaway')";
if (!empty($value) && !empty($ticket_number) && !empty($partners)) {
$rowins++;
}
}
}
$sql = "select ticket_number from ecs_tickets where ticket_number='$ticket_number'";
$number[$key]=$GLOBALS['db']->getRow($sql);
$ticket_number='';
foreach ($number as $key => $value) {
if(!empty($value)){
$ticket_number.= $value['ticket_number'].',';
}
}
if(empty($ticket_number)){
$sql="INSERT INTO ecs_tickets (id, ticket_number, partners, is_take, taked_time, create_time, is_putaway ) VALUES ".implode(',', $str);
$insertdata = $GLOBALS['db']->query($sql);
if($insertdata){
$res['status'] =1;
$res['msg'] ="共成功导入 '$rowins' 条记录'";
}
}else{
$res['status'] =0;
$res['msg'] ="票券号".$ticket_number."重复";
}
return $res;
}