利用phpexcel把excel导入数据库和数据库导出excel实现
<?php
/*
*authorzhy
*date20120612
*forexcel
*/
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");
//根据时间生成采购报表
$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=newPHPExcel();
////////获取文档信息
////////$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","selecti.goods_id,sum(`nums`)ASnum,i.name,i.addon,i.price,g.bnasb,i.bnash,
g.goods_id,i.goods_id,i.order_id
FROM`sdb_b2c_order_items`asi,sdb_b2c_goodsasg
WHEREi.order_idin(selectorder_idfromsdb_b2c_orderswherestatus="active"andcreatetimebetween$stimeand$etime)andi.goods_id=g.goods_idandg.cat_id=173GROUPBYh");
$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($reas$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=newPHPExcel_Worksheet_Drawing();
$objDrawing->setName("wsyImg");
$objDrawing->setDescription("Imageinsertedbyzhy");
$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;
//Excel2007保存
//$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);
//$objWriter->save(str_replace(".php",".xlsx",__FILE__));
//Excel5保存
//$objWriter=newPHPExcel_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);
functioncreateDir($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");
?>
相关文章
- java实现excel表格导入数据库表「建议收藏」
- excel数据导入SQL数据库2021.8.23
- PHPExcel_把Excel数据导入数据库PHP
- mysql导入excel文件_将Excel数据导入MySQL「建议收藏」
- 如何将excel表格导入mysql数据库_MySQL数据库
- Excel公式技巧：Excel公式中的数字9.99999999E+307
- 如何生成A-AZ列 excel表的列 不用序号的那种？
- 使用工具 plsqldev将Excel导入Oracle数据库
- 数据导出为excel表格详解数据库
- 使用Excel快速导入MySQL数据库（excle导入mysql）
- 数据库快速实现Excel导入MySQL数据库（excel导入mysql）
- 批量转换到csvLinux下Excel批量转换为CSV格式的方法（linux下excel）
- 将Excel导入SAP数据库表详解编程语言
- 导入Excel数据到Oracle中的完美解决方案（oracle导入excel）
- 快速完成:从Excel导入Oracle数据库（excel导入oracle数据库）
- 轻松实现Excel导入MySQL数据库（excel导入mysql数据库）
- 导入Excel数据到Oracle数据库（将excel导入oracle数据库）
- MySQL快速导入Excel数据（mysql导入excel数据）
- excel数据分析：SPSS、MySQL和Excel（spssmysql）
- 使用Excel快速读取MySQL数据（excel读取mysql）
- Excel数据导入Mysql数据库的实现代码
- Excel导入Sqlserver数据库脚本
- Excel、记事本数据导入到数据库的实现方法
- c#将Excel数据导入到数据库的实现代码
- Drupal读取Excel并导入数据库实例
- C#窗体读取EXCEL并存入SQL数据库的方法