[PHP-users 21490] Spreadsheet_Excel_Writer

[t-yagi ＠ system-exe.co.jp]

$B$$$D$bMxMQ$5$;$FD:$$$F$*$j$^$9!#H,LZ$H?=$7$^$9!#(B

$B8=:_!"$"$k%W%m%8%'%/%H$G(B
PEAR$B!'(BSpreadsheet_Excel_Writer(Ver.7)
$B$r;HMQ$7(BEXCEL$B%U%!%$%k$r:n ＠ .$7$F$*$j$^$9$,(B
$B4|BTDL$j$NF0:n$r$7$F$/$l$:$*CN7C$r$*<Z$j$7$?$$(B
$B$H9M$($F$*$j$^$9!#(B


$B!&4D6-(B
$B!!(BSolaris$B!!(B5.8
$B!!(BOracle   8.1.7
  PHP      4.3.3
  PEAR$B!'(BSpreadsheet_Excel_Writer(Ver.7)

$B!&8=>](B
$B!!-!(BEXCEL$B=PNO;~$K%;%k$KBP$7=q<0 ＠ _Dj$r9T$$(B
$B!!!!(BEXCEL$B=PNO$r$9$k!#(B
$B!!-":n ＠ .$7$?(BEXCEL$B$r3+$/$HBP>]$N%;%k$N=q<0$O(B
$B!!!!JQ99$5$l$F$$$k$K$b$+$+$o$i$:(B
$B!!!!%G!<%?$,F~NO$5$l$?=q<0$K$J$C$F$/$l$J$$!#(B
$BNc(B:)
    $B!&(BORACLE$B$G$NF~NO%G!<%?!'(B25000(NUMBER)
    $B!&(BEXCEL$B$G$N=q<0(B:       "#,##0"($BDL2_(B)
    $B!&(BSpreadsheet_Excel_Writer
      $B$G:n ＠ .$7$?(BExcel$B>e$N%G!<%?(B:"25000"$B$HI=<((B
    $B!&4|BT$7$F$$$k7k2L!'(B"25,000"

    $B$=$7$F=q<0$,JQ99$5$l$F$$$k$K$b$+$+$o$i$:!"(B
    SUM$B=87W$J$I=PMh$J$$$G$$$^$9!#(B
    $B%+!<%=%k$rEv$F$F%;%k$N%G!<%?$rG'<1$7$J$*$7$F(B
    $B=i$a$F=q<0E,MQ$5$l$k$h$&$K$J$C$F$$$^$9!#(B

    $B4|BT$7$?$$$N$O:n ＠ .$7$?CJ3,$GDL>o$N(BEXCEL$B$N$h$&$K(B
    $B=q<0E,MQ$5$;(BEXCEL$B$r5!G=$5$;$?$$$N$G$9$,!#(B

    $B$J$K$+$h$$CN7C$,$4$6$$$^$7$?$i$h$m$7$/(B
    $B$*4j$$$$$?$7$^$9!#(B

$BH,LZ!!CR90(B


//SQL$B<B9T(B
    $sql=stripslashes($_POST['p_sql']);
    ora_parse($cur,$sql);
    ora_exec($cur);

    //$BNs?t<hF@(B
    $num=ora_numcols($cur);
    
    //$B%X%C%@$rG[Ns$K3JG<(B
    $wk_ary=explode(",",$_POST['p_head']);

    //$BNs?t$r3NJ](B
    $wk_cnt =count($wk_ary);

    $z=0;
    $sheetname="worksheet0";
    $worksheet[$z]=& $workbook->addWorksheet($sheetname);
     
    //$B%X%C%@=PNO(B
    for($i=0;$i<=$wk_cnt;$i++){
        //SJIS$BJQ49(B
	      $val = mb_convert_encoding($wk_ary[$i],"SJIS-WIN","EUC-JP");
        //$BBP>]%G!<%?$r=PNO(B
        $worksheet[$z]->write(0,$i,$val);
    }
  
    //ORACLE$B$N%G!<%?7?$KBP$7$F(BEXCEL$B$N=q<0$r ＠ _Dj(B
    $general=  & $workbook->addFormat(array('Align' => 'left','NumFormat' => 
'General'));
    $varchar2= & $workbook->addFormat(array('Align' => 'left','NumFormat' => 
'@'));
    $number=& $workbook->addFormat(array('Align' => 'right','NumFormat' => '#,
##0'));
    
    //$BG[Ns$KBP>]=q<0$N%G!<%?$r3JG<(B
    $wk_format_ary=array("GENERAL"=>$general,"VARCHAR2"=>$varchar2,
"NUMBER"=>$number);

    //$BBP>]%G!<%?$r%k!<%W(B
    $i=1;   
    $wk_format="";
    While (Ora_Fetch($cur) == TRUE) {
        for($j=0;$j<$num;$j++){
               
               //$B%G!<%?7?$r<hF@(B
               $wk_format = Ora_columntype($cur,$j);
               
               //$B<B%G!<%?$r<hF@(B
               $val="";
               $val = Ora_GetColumn($cur,$j);
               
               //SJIS$BJQ49(B
               $val = mb_convert_encoding($val,"SJIS-WIN","EUC-JP");

               //$B%G!<%?7?$K$"$o$;=q<0 ＠ _Dj(B
               if ($wk_format=="VARCHAR2"){
                   $format =$wk_format_ary["VARCHAR2"];
               }else if($wk_format=="NUMBER"){
                   $format =$wk_format_ary["NUMBER"];
                   $val=number_format(intval($val));                   
               }else{
                   $format =$wk_format_ary["GENERAL"];
               }

               //$BBP>]%G!<%?$r=PNO(B
               $worksheet[$z]->writestring($i,$j,$val,$format);
               $i++;
        }

    //ORACLE$B%/%m!<%:(B
    ora_close($cur);
    ora_logoff($db);

// Excel$B%/%m!<%:(B
    $workbook->close();
    $B=PNO$5$l$k$N$O(B25000
$B!!(B




$B!&%=!<%9(B
//SQL$B<B9T(B
    $sql=stripslashes($_POST['p_sql']);
    ora_parse($cur,$sql);
    ora_exec($cur);

    //$BNs?t<hF@(B
    $num=ora_numcols($cur);
    
    //$B%X%C%@$rG[Ns$K3JG<(B
    $wk_ary=explode(",",$_POST['p_head']);

    //$BNs?t$r3NJ](B
    $wk_cnt =count($wk_ary);

    $z=0;
    $sheetname="worksheet0";
    $worksheet[$z]=& $workbook->addWorksheet($sheetname);
     
    //$B%X%C%@=PNO(B
    for($i=0;$i<=$wk_cnt;$i++){
        //SJIS$BJQ49(B
	      $val = mb_convert_encoding($wk_ary[$i],"SJIS-WIN","EUC-JP");
        //$BBP>]%G!<%?$r=PNO(B
        $worksheet[$z]->write(0,$i,$val);
    }
  
    //ORACLE$B$N%G!<%?7?$KBP$7$F(BEXCEL$B$N=q<0$r ＠ _Dj(B
    $general=  & $workbook->addFormat(array('Align' => 'left','NumFormat' => 
'General'));
    $varchar2= & $workbook->addFormat(array('Align' => 'left','NumFormat' => 
'@'));
    $number=& $workbook->addFormat(array('Align' => 'right','NumFormat' => '#,
##0'));
    
    //$BG[Ns$KBP>]=q<0$N%G!<%?$r3JG<(B
    $wk_format_ary=array("GENERAL"=>$general,"VARCHAR2"=>$varchar2,
"NUMBER"=>$number);

    //$BBP>]%G!<%?$r%k!<%W(B
    $i=1;   
    $wk_format="";
    While (Ora_Fetch($cur) == TRUE) {
        for($j=0;$j<$num;$j++){
               
               //$B%G!<%?7?$r<hF@(B
               $wk_format = Ora_columntype($cur,$j);
               
               //$B<B%G!<%?$r<hF@(B
               $val="";
               $val = Ora_GetColumn($cur,$j);
               
               //SJIS$BJQ49(B
               $val = mb_convert_encoding($val,"SJIS-WIN","EUC-JP");

               //$B%G!<%?7?$K$"$o$;=q<0 ＠ _Dj(B
               if ($wk_format=="VARCHAR2"){
                   $format =$wk_format_ary["VARCHAR2"];
               }else if($wk_format=="NUMBER"){
                   $format =$wk_format_ary["NUMBER"];
                   $val=number_format(intval($val));                   
               }else{
                   $format =$wk_format_ary["GENERAL"];
               }

               //$BBP>]%G!<%?$r=PNO(B
               $worksheet[$z]->writestring($i,$j,$val,$format);
               $i++;
        }

    //ORACLE$B%/%m!<%:(B
    ora_close($cur);
    ora_logoff($db);

// Excel$B%/%m!<%:(B
    $workbook->close();