<?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");
Generate purchase reports based on time
$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 '));
}
Instantiating an Excel class
$objPHPExcel = new Phpexcel ();
Get Document Information
$objProps = $objPHPExcel->getproperties ();
Print_r ($objProps);
echo "<br/>";
$objProps->setdescription ("test_123456");
Print_r ($objProps);
$objPHPExcel->setactivesheetindex (0)
->setcellvalue (' A5 ', ' Commodity code ')
->setcellvalue (' B5 ', ' Item # ')
-> Setcellvalue (' C5 ', ' Product name ')
->setcellvalue (' D5 ', ' Purchase quantity ');
//Set selected sheet table name
$objPHPExcel->getactivesheet ()->settitle (' ancestor name ');
Set font style
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfont ()->setname (' Arial ')-> SetSize ();//////->setunderline (true);/////->getcolor ()->setargb (' FFFF0000 ');///->setbold (true);
//Merge cells assign values to cells (numeric, string, formula)
$objPHPExcel->getactivesheet ()->mergecells (' A1:d3 ')->setcellvalue (' A1 ', ' Zhongyi list ');
///////$objPHPExcel->getactivesheet ()->mergecells (' A4:d4 ')->setcellvalue (' A4 ', =sum (E4:F4));
$date _now = Date ("y-m-d");
$objPHPExcel->getactivesheet ()->mergecells (' A4:d4 ')->setcellvalue (' A4 ', "date of purchase:". $date _now. " ". $apm." ");
Set single row width
$objPHPExcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (15);
$objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setwidth;//$objPHPExcel-> Getactivesheet ()->getcolumndimension (' G ')->setrowheight (50);
$objPHPExcel->getactivesheet ()->getcolumndimension (' C ')->setwidth (44);
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (15);
Large border style border bold
$lineBORDER = Array (
' Borders ' => array (
' Outline ' => Array (
' Style ' => Phpexcel_style_border::border_thick,
' Color ' => array (' ARGB ' => ' 000000 '),
),
),
);
Table header Style
$head = Array (
' Font ' => array (
' Bold ' => true
),
' Alignment ' => array (
' Horizontal ' => phpexcel_style_alignment::horizontal_center,
' Vertical ' => phpexcel_style_alignment::vertical_center
),
);
Heading styles
$title = Array (
' Font ' => array (
' Bold ' => true
),
);
Center Alignment
$CENTER = Array (
' Alignment ' => array (
' Horizontal ' => phpexcel_style_alignment::horizontal_center,
' Vertical ' => phpexcel_style_alignment::vertical_center
),
);
Align Right
$RIGHT = Array (
' Alignment ' => array (
' Horizontal ' => phpexcel_style_alignment::horizontal_right,
' Vertical ' => phpexcel_style_alignment::vertical_center
),
);
Thin border Style
$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); Wrap line
$objPHPExcel->getactivesheet ()->getstyle (' A4:d4 ')->applyfromarray ($RIGHT);
$objPHPExcel->getactivesheet ()->getstyle (' A5:d5 ')->applyfromarray ($title);
Fill Color
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor () (" FFFF0000 ');
Inserting data
$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, $row 1);
$num = $row 1[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);
//Set print margins
$objPHPExcel->getactivesheet ()->getpagemargins ()->settop (0);
$objPHPExcel->getactivesheet ()->getpagemargins ()->setright (0);
$objPHPExcel->getactivesheet ()->getpagemargins ()->setleft (0);
$objPHPExcel->getactivesheet ()->getpagemargins ()->setbottom (0);
//Set paper type
$objPHPExcel->getactivesheet ()->getpagesetup ()->setpapersize () phpexcel_worksheet_ PageSetup::P apersize_a4);
//Set AutoFilter
$objPHPExcel->getactivesheet ()->setautofilter (' A5:d '. $row _count);
Set Wrap
$objPHPExcel->getactivesheet ()->getstyle (' b6:b '. $row _count)->getalignment ()-> Setwraptext (TRUE);
//Set formatted number
$objPHPExcel->getactivesheet ()->getstyle (' a6:a '. $row _count)->getnumberformat ()-> Setformatcode (' 0000000000 ');
//Set security level
$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);
Add a picture
/*
$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);
*/
Header and Footer
$objPHPExcel->getactivesheet ()->getheaderfooter ()->setoddheader (' Zhy ');
$objPHPExcel->getactivesheet ()->getheaderfooter ()->setoddfooter (' End ');
$objPHPExcel->setactivesheetindex (0);
$tname =date (' Y-m-dh ', Time ());
$tnam =iconv (' UTF-8 ', ' GBK ', ' Ancestral name order ');
$tname = $tnam. $tname;
Excel 2007 Save
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);
$objWriter->save (Str_replace ('. php ', '. xlsx ', __file__));
Excel 5 Save
$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 ');
?>