PHPEXCEL 使用小记

require("http://www.jb51.net/phpexcel/classes/phpexcel.php");
$file = "d:\datas.xlsx";
if(!file_exists($file)){
die("no file found in {$file}");
}
$datasreader = phpexcel_iofactory::load($file);
$sheets = $datasreader->getallsheets();
//如果有多个工作簿
$countsheets = count($sheets);
$sheetsinfo = array();
$sheetdata = array();
if($countsheets==1){
$sheet = $sheets[0];
$sheetsinfo["rows"] = $sheet->gethighestrow();
$sheetsinfo["column"] = phpexcel_cell::columnindexfromstring($sheet->gethighestcolumn());
for($row=1;$row<=$sheetsinfo["rows"];$row++){
for($column=0;$column<$sheetsinfo["column"];$column++){
$sheetdata[$column][$row] = $sheet->getcellbycolumnandrow($column, $row)->getvalue();
}
}
}else{
foreach ($sheets as $key => $sheet)
{
$sheetsinfo[$key]["rows"] = $sheet->gethighestrow();
$sheetsinfo[$key]["column"] = phpexcel_cell::columnindexfromstring($sheet->gethighestcolumn());
for($row=1;$row<=$sheetsinfo[$key]["rows"];$row++){
for($column=0;$column<$sheetsinfo[$key]["column"];$column++){
$sheetdata[$key][$column][$row] = $sheet->getcellbycolumnandrow($column, $row)->getvalue();
}
}
}
}
echo "<pre>";
print_r($sheetdata);
echo "</pre>";

$sql = sprintf("select * from table where op_id=%d", intval($this->params['id']));
$query = $this->_db->query($sql);
require_once './phpexcel_1.7.4/classes/phpexcel.php';
$objphpexcel = new phpexcel();
$objphpexcel->setactivesheetindex(0);
$objphpexcel->getactivesheet()->getcolumndimension('a')->setwidth(10);
$objphpexcel->getactivesheet()->getcolumndimension('b')->setwidth(15);
$objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(15);
$objphpexcel->getactivesheet()->getcolumndimension('d')->setwidth(15);
$objphpexcel->getactivesheet()->getcolumndimension('e')->setwidth(15);
$objphpexcel->getactivesheet()->setcellvalue('a1', "{$this->_packinfos['o_id']}");
$objphpexcel->getactivesheet()->setcellvalue('b1', "volume weight (kg)");
$objphpexcel->getactivesheet()->setcellvalue('d1', "actual weight (kg)");


$objphpexcel->getactivesheet()->setcellvalue('a2', "box no.");
$objphpexcel->getactivesheet()->setcellvalue('b2', "products");
$objphpexcel->getactivesheet()->setcellvalue('c2', "shipping box");
$objphpexcel->getactivesheet()->setcellvalue('d2', "system");
$objphpexcel->getactivesheet()->setcellvalue('e2', "input");
$objactsheet = $objphpexcel->getactivesheet();
$objactsheet->mergecells("b1:c1");
$objactsheet->mergecells("d1:e1");

$objphpexcel->getactivesheet()->getstyle('a1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_left);
$objphpexcel->getactivesheet()->getstyle('b1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
$objphpexcel->getactivesheet()->getstyle('d1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);

$objphpexcel->getactivesheet()->getstyle('a2'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_left);
$objphpexcel->getactivesheet()->getstyle('b2'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
$objphpexcel->getactivesheet()->getstyle('c2'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
$objphpexcel->getactivesheet()->getstyle('d2'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
$objphpexcel->getactivesheet()->getstyle('e2'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);

if($this->_db->num_rows($query)>0)
{
$i=3;
while ($row = $this->_db->fetch_assoc($query))
{
$objphpexcel->getactivesheet()->setcellvalue('a'.($i),"box ".$row['box_num']);
$objphpexcel->getactivesheet()->setcellvalue('b'.($i),sprintf("%.2f",$row['volume_weight']));
$objphpexcel->getactivesheet()->setcellvalue('c'.($i),sprintf("%.2f",$row['box_weight']));
$objphpexcel->getactivesheet()->setcellvalue('d'.($i),sprintf("%.2f",$row['system_weight']));
$objphpexcel->getactivesheet()->setcellvalue('e'.($i),sprintf("%.2f",$row['real_weight']));

$objphpexcel->getactivesheet()->getstyle('a'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_left);
$objphpexcel->getactivesheet()->getstyle('b'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('c'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('d'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$objphpexcel->getactivesheet()->getstyle('e'.($i))->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_right);
$i++;
}
}

$filename="exportbox.xls";
$filepath = dirname(dirname("__file__"))."/template/".$filename;
$path = "./template/".$filename;
$objwriter = new phpexcel_writer_excel2007($objphpexcel);
if(file_exists($path)){
chmod($path, 0777);
unlink($path);
$objwriter->save($path);
header('application/vnd.ms-excel');
header('content-disposition: attachment;filename=weight-'.$this->_packinfos["o_id"].".xlsx");
readfile($filepath);
die();
}
else
{
$objwriter->save($path);
header('application/vnd.ms-excel');
header('content-disposition: attachment;filename=weight-'.$this->_packinfos["o_id"].".xlsx");
readfile($filepath);
die();
}