php教程分享

  1. php教程首页
  2. php面试

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

•php面试•阅读 5405

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

本文介绍利用phpexcel对数据库数据导入excel（excel筛选）、导出excel，大家参考使用吧利用phpexcel把excel导入数据库和数据库导出excel实现

代码如下:

<?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");

//根据时间生成采购报表

$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_);

$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');

?>

赞 (0)
上一篇10-10
下一篇 11-9

相关推荐

  • SQL Server教程：三种导入导出数据使用方式比较

    SQLServer教程：三种导入导出数据使用方式比较在我们建立一个数据库时，并且想将分散在各处的不同类型的数据库分类汇总在这个新建的数据库中时，尤其是在进行数据检验、净化和转换时，将会面临很大的挑战。幸好SQLServer为我们提供了强大、…
    •Mysql
    2015-06-1517898

  • 将csv文件导入到mysql数据库

    将csv文件导入到mysql数据库统一编码方式为utf-8，将csv文件编码方式设置为utf-8.在数据库中新建一个表，列属性和csv文件要相符，注意将**月**日等在csv中设置为日期格式，而不是字符串之类的，这样可以将时间等导入。设置方…
    •Mysql
    2015-07-125826

  • 如何利用phpMyAdmin批量导入Excel内容到MySQL

    如何利用phpMyAdmin批量导入Excel内容到MySQL1、第一步我们得到了一个excel表，里面有很多需要我们导入的数据。如何利用phpMyAdmin批量导入Excel内容到MySQL。2、删除第1行”准考证号”"XXX”….只保留…
    •Mysql
    2015-07-246470

  • php生成excel文件的简单方法

    php生成excel文件的简单方法生成excel文件，最简单的莫过于把数据库的数据导入到excel就行了，下面有个不错的示例，大家可以参考下生成excel当然使用的是phpExcel这个类库了,可是它太麻烦了,对于只要简单生成来说有点不值得…
    •php文件操作
    2015-08-0817100

  • phpexcel导入excel数据使用方法实例

    phpexcel导入excel数据使用方法实例phpexcel导入excel数据使用方法,大家参考使用吧将Excel文件数据进行读取，并且返回错误的信息代码如下:/***导入商品基本信息*/publicfunctionimportProdu…
    •php类库
    2015-09-127914

  • mysql 导入导出数据库以及函数、存储过程的介绍

    mysql导入导出数据库以及函数、存储过程的介绍本篇文章是对mysql中的导入导出数据库命令以及函数、存储过程进行了详细的分析介绍，需要的朋友参考下mysql常用导出数据命令：1.mysql导出整个数据库mysqldump-hhostnam…
    •Mysql
    2015-12-147368

  • MySQL数据库导出与导入及常见错误解决

    MySQL数据库导出与导入及常见错误解决MySQL数据库导出与导入的过程中将会发生众多不可预知的错误，本文整理了一些常见错误及相应的解决方法，遇到类似情况的朋友可以参考下，希望对大家有所帮助MySQL命令行导出数据库：1，进入MySQL目录…
    •Mysql
    2015-12-184613

  • 如何导入导出MySQL数据库*.sql文件操作

    如何导入导出MySQL数据库*.sql文件操作1.概述MySQL数据库的导入，有两种方法：1)先导出数据库SQL脚本，再导入；2)直接拷贝数据库目录和文件。在不同操作系统或MySQL版本情况下，直接拷贝文件的方法可能会有不兼容的情况发生。所…
    •Mysql
    2016-01-2313687