Using Phpexcel to import Excel into database and database to export Excel _php tutorial

Copy CodeThe code is as follows:
/*
*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: '
');

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 "
";
$objProps->setdescription ("test_123456");
Print_r ($objProps);

$objPHPExcel->setactivesheetindex (0)
->setcellvalue (' A5 ', ' Product Code ')
->setcellvalue (' B5 ', ' article number ')
->setcellvalue (' C5 ', ' Product name ')
->setcellvalue (' D5 ', ' purchasing volume ');

//Set the selected sheet table name
$objPHPExcel->getactivesheet ()->settitle (' ancestor name ');
Set font style
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfont (' Arial ') SetSize (+);//////->setunderline (true);/////->getcolor ()->setargb (' FFFF0000 ');///->setbold (true);
//Merge cells assign values (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 ', "Purchase Date:". $date _now. "　". $apm." ");
Set the single column 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 to 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 ()->setargb (' 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 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 ');

?>

http://www.bkjia.com/PHPjc/676876.html www.bkjia.com true http://www.bkjia.com/PHPjc/676876.html techarticle Copy the code as follows: PHP/* *author zhy *date *for Excel */Date_default_timezone_set ("PRC"); error_reporting (E_all) ; error_reporting (0); Ini_set (' display_errors ' ...

