利用phpexcel把excel導(dǎo)入數(shù)據(jù)庫和數(shù)據(jù)庫導(dǎo)出excel實(shí)現(xiàn)
<?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.class.php");
//根據(jù)時(shí)間生成采購報(bào)表
$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'));
}
//實(shí)例化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','貨號(hào)')
->setCellValue('C5','商品名稱')
->setCellValue('D5','采購量');
//設(shè)置選定sheet表名
$objPHPExcel->getActiveSheet()->setTitle('祖名');
//設(shè)置字體樣式
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Arial')->setSize(25);//////->setUnderline(true);/////->getColor()->setARGB('FFFF0000');///->setBold(true);
//合并單元格 給單元格賦值(數(shù)值，字符串，公式)
$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." ");
//設(shè)置單列寬度
$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
),
);
//標(biāo)題樣式
$title = array(
'font' => array(
'bold' => true
),
);
//居中對(duì)齊
$CENTER = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
);
//靠右對(duì)齊
$RIGHT = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
);
//細(xì)邊框樣式
$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);自動(dòng)換行
$objPHPExcel->getActiveSheet()->getStyle('A4:D4')->applyFromArray($RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A5:D5')->applyFromArray($title);
//填充色
/////$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FFFF0000');/
//插入數(shù)據(jù)
$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);
//設(shè)置打印頁邊距
$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0);
$objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0);
//設(shè)置紙張類型
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//設(shè)置自動(dòng)篩選
$objPHPExcel->getActiveSheet()->setAutoFilter('A5:D'.$row_count);
//設(shè)置自動(dòng)換行
$objPHPExcel->getActiveSheet()->getStyle('B6:B'.$row_count)->getAlignment()->setWrapText(true);
//設(shè)置格式化數(shù)字
$objPHPExcel->getActiveSheet()->getStyle('A6:A'.$row_count)->getNumberFormat()->setFormatCode('0000000000');
//設(shè)置安全級(jí)別
$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');
?>
- 利用phpExcel實(shí)現(xiàn)Excel數(shù)據(jù)的導(dǎo)入導(dǎo)出(全步驟詳細(xì)解析)
- ThinkPHP使用PHPExcel實(shí)現(xiàn)Excel數(shù)據(jù)導(dǎo)入導(dǎo)出完整實(shí)例
- phpExcel導(dǎo)出大量數(shù)據(jù)出現(xiàn)內(nèi)存溢出錯(cuò)誤的解決方法
- 使用PHPExcel實(shí)現(xiàn)數(shù)據(jù)批量導(dǎo)出為excel表格的方法(必看)
- 完美解決phpexcel導(dǎo)出到xls文件出現(xiàn)亂碼的問題
- php實(shí)現(xiàn)利用phpexcel導(dǎo)出數(shù)據(jù)
- Yii中使用PHPExcel導(dǎo)出Excel的方法
- PHP導(dǎo)出EXCEL快速開發(fā)指南--PHPEXCEL的使用詳解
- PHPExcel導(dǎo)出2003和2007的excel文檔功能示例
- 使用PHPExcel導(dǎo)出Excel表
相關(guān)文章
php版微信公眾平臺(tái)開發(fā)之驗(yàn)證步驟實(shí)例詳解
這篇文章主要介紹了php版微信公眾平臺(tái)開發(fā)之驗(yàn)證步驟,結(jié)合實(shí)例形式詳細(xì)分析了php微信公眾平臺(tái)驗(yàn)證的操作步驟與相關(guān)參數(shù)含義,需要的朋友可以參考下2016-09-09
DataTables 是一個(gè) jQuery 的表格插件，記錄一下在 Laravel 中使用的常用功能和用法，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2018-05-05
thinkPHP框架RBAC實(shí)現(xiàn)原理分析
這篇文章主要介紹了thinkPHP框架RBAC實(shí)現(xiàn)原理,結(jié)合實(shí)例形式分析了thinkPHP框架中RBAC角色權(quán)限控制相關(guān)實(shí)現(xiàn)原理與操作技巧,需要的朋友可以參考下2019-02-02
本篇文章是對(duì)獲取PHP警告錯(cuò)誤信息的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
Symfony2獲取web目錄絕對(duì)路徑、相對(duì)路徑、網(wǎng)址的方法
這篇文章主要介紹了Symfony2獲取web目錄絕對(duì)路徑、相對(duì)路徑、網(wǎng)址的方法,結(jié)合實(shí)例形式分析了Symfony獲取路徑與網(wǎng)址的常用操作技巧,并給出了Request完整文件的源碼供大家下載參考,需要的朋友可以參考下2016-11-11
這篇文章主要介紹了Smarty模板變量調(diào)節(jié)器用法,較為詳細(xì)的分析了Smarty模板變量調(diào)節(jié)器的功能與具體使用技巧,需要的朋友可以參考下2016-05-05
CI框架無限級(jí)分類+遞歸的實(shí)現(xiàn)代碼
CodeIgniter是一個(gè)輕量級(jí)但功能強(qiáng)大的PHP框架，基于MVC設(shè)計(jì)模式，提供了一套豐富的類庫，簡單易學(xué)，高效實(shí)用。下面給大家介紹CI框架無限級(jí)分類+遞歸的實(shí)現(xiàn)代碼，感興趣的朋友參考下吧2016-11-11
laravel框架select2多選插件初始化默認(rèn)選中項(xiàng)操作示例
這篇文章主要介紹了laravel框架select2多選插件初始化默認(rèn)選中項(xiàng)操作,結(jié)合實(shí)例形式分析了laravel框架select2多選插件的基本初始化、設(shè)置默認(rèn)選中項(xiàng)等相關(guān)操作技巧,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了php順序查找和二分查找示例,需要的朋友可以參考下2014-03-03
PHP實(shí)現(xiàn)手機(jī)歸屬地查詢API接口實(shí)現(xiàn)代碼
主要使用curl實(shí)現(xiàn)，需要開啟php對(duì)curl的支持2012-08-08