利用phpexcel把excel导入数据库和数据库导出excel实现

2020-03-22 17:22:45

字体：大中小

来源：转载

供稿：网友

复制代码代码如下:
?php
/*
*author zhy
*date 2012 06 12
*for excel
*/
date_default_timezone_set("PRC");
error_reporting(E_ALL);
error_reporting(0);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : ' br / require_once ('../Classes/PHPExcel.php');
require_once("config.php");
require_once("mysql.html' target='_blank'>class.php");//根据时间生成采购报表
$time = date("a");
$minute = date("i");
$apm = "";
if($time=='pm'){
$apm = $time;
$stime = mktime(12,00,00,date('m'),date('d')-1,date('Y'));
$etime = mktime(11,59,59,date('m'),date('d'),date('Y'));
}else{
$apm = $time;
$stime = mktime(12,00,00,date('m'),date('d')-1,date('Y'));
$etime = mktime(11,59,59,date('m'),date('d'),date('Y'));
}//实例化excel类
$objPHPExcel = new PHPExcel();////////获取文档信息
////////$objProps = $objPHPExcel- getProperties();
///////print_r($objProps);
///////echo " br/
///////$objProps- setDescription("test_123456");
///////print_r($objProps);
$objPHPExcel- setActiveSheetIndex(0)
- setCellValue('A5','商品编码')
- setCellValue('B5','货号')
- setCellValue('C5','商品名称')
- setCellValue('D5','采购量');//设置选定sheet表名
$objPHPExcel- getActiveSheet()- setTitle('祖名');
//设置字体样式
$objPHPExcel- getActiveSheet()- getStyle('A1')- getFont()- setName('Arial')- setSize(25);//////- setUnderline(true);/////- getColor()- setARGB('FFFF0000');///- setBold(true);
//合并单元格给单元格赋值(数值，字符串，公式)
$objPHPExcel- getActiveSheet()- mergeCells('A1:D3')- setCellValue('A1', 'zhongyi清单');
///////$objPHPExcel- getActiveSheet()- mergeCells('A4:D4')- setCellValue('A4', "=SUM(E4:F4)");$date_now = date("Y-m-d");
$objPHPExcel- getActiveSheet()- mergeCells('A4:D4')- setCellValue('A4', "采购日期：".$date_now." ".$apm." ");
//设置单列宽度
$objPHPExcel- getActiveSheet()- getColumnDimension('A')- setWidth(15);
$objPHPExcel- getActiveSheet()- getColumnDimension('B')- setWidth(20);//$objPHPExcel- getActiveSheet()- getColumnDimension('G')- setRowHeight(50);/
$objPHPExcel- getActiveSheet()- getColumnDimension('C')- setWidth(44);
$objPHPExcel- getActiveSheet()- getColumnDimension('D')- setWidth(15);//大边框样式边框加粗
$lineBORDER = array(
'borders' = array(
'outline' = array(
'style' = PHPExcel_Style_Border::BORDER_THICK,
'color' = array('argb' = '000000'),
),
),
);
//表头样式
$head = array(
'font' = array(
'bold' = true
),
'alignment' = array(
'horizontal' = PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' = PHPExcel_Style_Alignment::VERTICAL_CENTER
),

);
//标题样式
$title = array(
'font' = array(
'bold' = true
),
);
//居中对齐
$CENTER = array(
'alignment' = array(
'horizontal' = PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' = PHPExcel_Style_Alignment::VERTICAL_CENTER
),
);
//靠右对齐
$RIGHT = array(
'alignment' = array(
'horizontal' = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
'vertical' = PHPExcel_Style_Alignment::VERTICAL_CENTER
),
);
//细边框样式
$linestyle = array(
'borders' = array(
'outline' = array(
'style' = PHPExcel_Style_Border::BORDER_THIN,
'color' = array('argb' = 'FF000000'),
),
),
);
$objPHPExcel- getActiveSheet()- getStyle('A1:D3')- applyFromArray($head);///- getAlignment()- getHorizontal('');///- getBorders()- getTop()- setBorderStyle('');
//- setWrapText(true);自动换行
$objPHPExcel- getActiveSheet()- getStyle('A4:D4')- applyFromArray($RIGHT);
$objPHPExcel- getActiveSheet()- getStyle('A5:D5')- applyFromArray($title); //填充色
/////$objPHPExcel- getActiveSheet()- getStyle('A1')- getFill()- getStartColor()- setARGB('FFFF0000');/
//插入数据
$dsql- Execute('omebrand_list',"select i.goods_id , sum( `nums` ) AS num, i.name,i.addon,i.price,g.bn as b,i.bn as h,
g.goods_id,i.goods_id,i.order_id
FROM `sdb_b2c_order_items` as i,sdb_b2c_goods as g
WHERE i.order_id in (select order_id from sdb_b2c_orders where status ='active' and createtime between $stime and $etime) and i.goods_id=g.goods_id and g.cat_id=173 GROUP BY h");
$m = 0;
unset($re);
while($row=$dsql- GetObject('omebrand_list'))
{$re[$m] = get_object_vars($row);
$m++;
}
$row_count = 5;
$objPHPExcel- setActiveSheetIndex(0)
- setCellValue('A6', 12325416541)
- setCellValue('B6', 4962132165262)
- setCellValue('C6', 121515212515241521)
- setCellValue('D6', 96215465415);
foreach($re as $r = $dataRow) {
$baseRow = 6;
$row = $baseRow + $r;
$bn=$dataRow[h];
$goods_id = $dataRow[goods_id];
$spec_value = "";
$aa = unserialize($dataRow[addon]);
if ($aa['product_attr']){
foreach ($aa['product_attr'] as $arr_special_info) {
$spec_value = $arr_special_info['value'];
}
}

preg_match_all('//-?/d+/.?/d*/i',$spec_value,$row1);
$num = $row1[0][0];
$all = $num*$dataRow[num];
if($spec_value==''){
$all=$dataRow['num'];
//$prce=$dataRow[price];
}
$objPHPExcel- setActiveSheetIndex(0)
- setCellValue('A'.$row, $dataRow['b'])
- setCellValue('B'.$row, $bn)
- setCellValue('C'.$row, $dataRow['name'])
- setCellValue('D'.$row, $all);
$objPHPExcel- getActiveSheet()- getStyle('A'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('B'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('C'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('D'.$row_count)- applyFromArray($linestyle);

$baseRow++;
$row_count++;
}
$objPHPExcel- getActiveSheet()- getStyle('A'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('B'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('C'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('D'.$row_count)- applyFromArray($linestyle);
$objPHPExcel- getActiveSheet()- getStyle('A5:D'.$row_count)- applyFromArray($CENTER);
$objPHPExcel- getActiveSheet()- getStyle('A1:D'.$row_count)- applyFromArray($lineBORDER);
//设置打印页边距
$objPHPExcel- getActiveSheet()- getPageMargins()- setTop(0);
$objPHPExcel- getActiveSheet()- getPageMargins()- setRight(0);
$objPHPExcel- getActiveSheet()- getPageMargins()- setLeft(0);
$objPHPExcel- getActiveSheet()- getPageMargins()- setBottom(0);
//设置纸张类型
$objPHPExcel- getActiveSheet()- getPageSetup()- setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//设置自动筛选
$objPHPExcel- getActiveSheet()- setAutoFilter('A5:D'.$row_count);
//设置自动换行
$objPHPExcel- getActiveSheet()- getStyle('B6:B'.$row_count)- getAlignment()- setWrapText(true);
//设置格式化数字
$objPHPExcel- getActiveSheet()- getStyle('A6:A'.$row_count)- getNumberFormat()- setFormatCode('0000000000');//设置安全级别
$md=md5(time());
$md=substr($md,0,8);
$objPHPExcel- getActiveSheet()- getProtection()- setPassword("$md");
$objPHPExcel- getActiveSheet()- getProtection()- setSheet(true);//
$objPHPExcel- getActiveSheet()- getProtection()- setSort(true);
$objPHPExcel- getActiveSheet()- getProtection()- setInsertRows(true);
$objPHPExcel- getActiveSheet()- getProtection()- setFormatCells(true);//添加图片
/*
$obj=$objPHPExcel- getActiveSheet();
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing- setName('wsyImg');
$objDrawing- setDescription('Image inserted by zhy');
$objDrawing- setPath('./wsy.jpg');
$objDrawing- setHeight(50);
$objDrawing- setCoordinates('H23');
$objDrawing- setOffsetX(60);
$objDrawing- setRotation(-10); /
$objDrawing- getShadow()- setVisible(true);
$objDrawing- getShadow()- setDirection(-20); /
$objDrawing- setWorksheet($obj);
*///页眉页脚
//$objPHPExcel- getActiveSheet()- getHeaderFooter()- setOddHeader('zhy');
//$objPHPExcel- getActiveSheet()- getHeaderFooter()- setOddFooter('end');
$objPHPExcel- setActiveSheetIndex(0);
$tname=date('Y-m-dH',time());
$tnam=iconv('UTF-8','GBK','祖名订单');
$tname=$tnam.$tname;
// Excel 2007保存
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//$objWriter- save(str_replace('.php', '.xlsx', __FILE__));

// Excel 5保存
//$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//$objWriter- save(str_replace('.php', '.xls', __FILE__)); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter- save(str_replace('.php', '.xls', __FILE__));
//$url = "/data/home/htdocs/ec/public/files/".date("Y")."/".date("Ym")."/";
createDir($url);
function createDir($dir) {
if (!is_dir ($dir )) {
mkdir($dir, 0777, true);
chmod($dir, 0777);
chown( $dir, 'daemon' );
chgrp( $dir, 'daemon' );
}
}
$name='forexmple_excel';
rename(str_replace('.php', '.xls', __FILE__), $name.'.xls');
?
PHP教程

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。

上一篇：php+ajax导入大数据时产生的问题处理

下一篇：关于PHP中strtr函数一些奇怪行为的解释