User Tools

Site Tools


Sidebar

documentation:programmation:web:php:generation_fichier_excel

Générer des fichiers Excel en PHP

Source : http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/

Un exemple de code :

<?php
 
require_once "class.writeexcel_workbook.inc.php";
require_once "class.writeexcel_worksheet.inc.php";
 
class c_statistiques_export
{
    public function __construct()
    {}
 
    public function __destruct()
    {}
 
// Méthode permettant de générer le fichier xls des statistiques
    function Start_Export()
    {
        $statistiques = new c_statistiques("array");
 
        set_time_limit(190);
 
        // Création du fichier
        $filename ="statistiques.xls";
        $fname = tempnam("tmp", $filename);
        $workbook = &new writeexcel_workbook($fname);
 
        # Create a format for the column headings
        $header =& $workbook->addformat();
        $header->set_align('center');
        $header->set_align('top');
        $header->set_text_wrap();
        $header->set_color('black');
        $header->set_border(1);
        $header->set_bg_color(0x2F);
 
        # Create a format for the stock price
        $f_price =& $workbook->addformat();
        $f_price->set_align('right');
        $f_price->set_border(1);
        $f_price->set_num_format('0.00');
 
        $f_percent =& $workbook->addformat();
        $f_percent->set_align('right');
        $f_percent->set_border(1);
        $f_percent->set_num_format('0.00%');
 
        # Create a format for the stock volume
        $f_ean =& $workbook->addformat();
        $f_ean->set_align('center');
        $f_ean->set_border(1);
        $f_ean->set_num_format('00000');
 
        $f_change =& $workbook->addformat();
        $f_change->set_align('right');
        $f_change->set_border(1);
 
        $f_text =& $workbook->addformat();
        $f_text->set_align('left');
        $f_text->set_border(1);
 
 
 
        $arr = $statistiques->sortFamille();
 
        $i = 1;
        $currentCat = "0";
 
        // Génération de la première feuille
        foreach( $arr as $key => $row)
        {
            if($currentCat != "1"){
 
                # Write out the data
                $col = 0;
                $currentCat= "1";
 
                //$worksheet =& $workbook->addworksheet(substr($row['nomSFamille'], 30 ));
                $worksheet =& $workbook->addworksheet(utf8_decode("Synthèse globale"));
 
                $worksheet->set_column(0, 2, 60); 
                $worksheet->set_column(2, 16, 20);
 
                $worksheet->write(0,  $col ++, 'FAMILLE', $header);
                $worksheet->write(0,  $col ++, 'SOUS FAMILLE',   $header);
 
                if( $_POST['filtreCmd'] == 1 )
                {
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité Commandée'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('CA Commandé'),  $header);
                }    
 
                if( $_POST['filtreExp'] == 1 )
                {
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité Expédiée'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('CA Expédié'),  $header);
                }
 
                if( $_POST['filtreLsp'] == 1 )
                {
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité Lsp'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('CA Lsp'),  $header);
                }
 
                if( $_POST['filtreRep'] == 1 )
                {
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité Reprise'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('CA Repris'),  $header);
                }
 
                if( $_POST['filtreFact'] == 1 )
                {
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité livrée Facturée'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('Quantité reprise Facturée'),  $header);
                    $worksheet->write(0,  $col ++, utf8_decode('CA Facturée'),  $header);    
                }
 
                $worksheet->write(0,  $col ++, utf8_decode('Prix d\'achat'),  $header);
                $worksheet->write(0,  $col ++, utf8_decode('Taux de marge'),  $header);
 
                $worksheet->write(0,  $col ++, utf8_decode('Pmpa'),  $header);
                $worksheet->write(0,  $col ++, utf8_decode('Taux de marge'),  $header);
 
                $i = 1;
 
 
            }// end if 
            //print_r($row);
            $col = 0;
 
            if( $key != 0)
            {
                $worksheet->write($i,  $col ++, utf8_decode($row['nomFamille']), $f_text);
                $worksheet->write($i,  $col ++, utf8_decode($row['nomSFamille']),   $f_text);
 
                if( $_POST['filtreCmd'] == 1 )
                {
                    $worksheet->write($i,  $col ++, $row['periodeA_cmd_qtelivree'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_cmd_puHTnet'],  $f_price);
                }    
 
                if( $_POST['filtreExp'] == 1 )
                {
                    $worksheet->write($i,  $col ++, $row['periodeA_exp_qtelivree'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_exp_puHTnet'],  $f_price);
                }
 
                if( $_POST['filtreLsp'] == 1 )
                {
                    $worksheet->write($i,  $col ++, $row['periodeA_lsp_qtelivree'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_lsp_puHTnet'],  $f_price);
                }
 
                if( $_POST['filtreRep'] == 1 )
                {
                    $worksheet->write($i,  $col ++, $row['periodeA_rep_qtelivree'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_rep_puHTnet_repris'],  $f_price);
                }
 
                if( $_POST['filtreFact'] == 1 )
                {
                    $worksheet->write($i,  $col ++, $row['periodeA_fact_qtelivree'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_fact_qterepris'],  $f_change);
                    $worksheet->write($i,  $col ++, $row['periodeA_fact_puHTnet']+$row['periodeA_fact_puHTnet_repris'],  $f_price);
                }
 
                $worksheet->write($i,  $col ++, $row['prixAchat'],  $f_price);
                $worksheet->write($i,  $col ++, $row['TauxPrixAchat'],  $f_percent);
 
                $worksheet->write($i,  $col ++, $row['prixPmpa'],  $f_price);
                $worksheet->write($i,  $col ++, $row['TauxPmpa'],  $f_percent);
 
                $i ++;
            }
        }// next  
 
        $i = 1;
        $currentCat = "0";
 
        // On créé de nouvelles feuilles pour chaque sous famille de produit
        foreach( $arr as $key => $row)
        {
            if( $key > 0 && $key < (count($arr)-1) )
            {
                if($currentCat != $row['idsfamille']){
 
                    # Write out the data
                    $col = 0;
                    $currentCat= $row['idsfamille'];
 
                    //$worksheet =& $workbook->addworksheet(substr($row['nomSFamille'], 30 ));
                    $titreF = $row['nomFamille'];
                    $titreSF = $row['nomSFamille'];
                    $titre = $i."_".$titreF." - ". $titreSF;
                    $titre = substr($titre,0,31);
 
                    $worksheet =& $workbook->addworksheet($titre);
 
                    $worksheet->set_column(0, 1, 60); 
                    $worksheet->set_column(1, 16, 20);
 
                    $worksheet->write(0,  $col ++, utf8_decode($row['nomFamille']." / ".$row['nomSFamille']), $header);
 
                    if( $_POST['filtreCmd'] == 1 )
                    {
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                    }    
 
                    if( $_POST['filtreExp'] == 1 )
                    {
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                    }
 
                    if( $_POST['filtreLsp'] == 1 )
                    {
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                    }
 
                    if( $_POST['filtreRep'] == 1 )
                    {
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                    }
 
                    if( $_POST['filtreFact'] == 1 )
                    {
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                        $worksheet->write(0,  $col ++, '', $header);
                    }
 
                    $worksheet->write(0,  $col ++, '', $header);
                    $worksheet->write(0,  $col ++, '', $header);
                    $worksheet->write(0,  $col ++, '', $header);
                    $worksheet->write(0,  $col ++, '', $header);
                    $col = 0;
 
                    $worksheet->write(1,  $col ++, utf8_decode('Désignation'), $header);
 
                    if( $_POST['filtreCmd'] == 1 )
                    {
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité Commandée'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('CA Commandé'),  $header);
                    }    
 
                    if( $_POST['filtreExp'] == 1 )
                    {
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité Expédiée'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('CA Expédié'),  $header);
                    }
 
                    if( $_POST['filtreLsp'] == 1 )
                    {
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité Lsp'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('CA Lsp'),  $header);
                    }
 
                    if( $_POST['filtreRep'] == 1 )
                    {
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité Reprise'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('CA Repris'),  $header);
                    }
 
                    if( $_POST['filtreFact'] == 1 )
                    {
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité livrée Facturée'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('Quantité reprise Facturée'),  $header);
                        $worksheet->write(1,  $col ++, utf8_decode('CA Facturée'),  $header);
                    }
 
                    $worksheet->write(1,  $col ++, utf8_decode('Prix d\'achat'),  $header);
                    $worksheet->write(1,  $col ++, utf8_decode('Taux de marge'),  $header);
 
                    $worksheet->write(1,  $col ++, utf8_decode('Pmpa'),  $header);
                    $worksheet->write(1,  $col ++, utf8_decode('Taux de marge'),  $header);
 
                    $i = 2;
 
 
                }// end if 
                //print_r($row);
 
                $_POST['idsfamille'] = $row['idsfamille'];
                $lignes = $statistiques->getStatsProduits();
 
                foreach ($lignes as $k => $v) {
                    $col = 0;
 
                    if( $k != 0)
                    {
                        $worksheet->write($i,  $col ++, utf8_decode($v['designation']), $f_text);
 
                        if( $_POST['filtreCmd'] == 1 )
                        {
                            $worksheet->write($i,  $col ++, $v['periodeA_cmd_qtelivree'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['periodeA_cmd_puHTnet'],  $f_price);
                        }    
 
                        if( $_POST['filtreExp'] == 1 )
                        {
                            $worksheet->write($i,  $col ++, $v['periodeA_exp_qtelivree'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['nomFperiodeA_exp_puHTnet'],  $f_price);
                        }
 
                        if( $_POST['filtreLsp'] == 1 )
                        {
                            $worksheet->write($i,  $col ++, $v['periodeA_lsp_qtelivree'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['periodeA_lsp_puHTnet'],  $f_price);
                        }
 
                        if( $_POST['filtreRep'] == 1 )
                        {
                            $worksheet->write($i,  $col ++, $v['periodeA_rep_qtelivree'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['periodeA_rep_puHTnet_repris'],  $f_price);
                        }
 
                        if( $_POST['filtreFact'] == 1 )
                        {
                            $worksheet->write($i,  $col ++, $v['periodeA_fact_qtelivree'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['periodeA_fact_qterepris'],  $f_change);
                            $worksheet->write($i,  $col ++, $v['periodeA_fact_puHTnet']+$row['periodeA_fact_puHTnet_repris'],  $f_price);
                        }
 
                        $worksheet->write($i,  $col ++, $v['prixAchat'],  $f_price);
                        $worksheet->write($i,  $col ++, $v['TauxPrixAchat'],  $f_percent);
 
                        $worksheet->write($i,  $col ++, $v['prixPmpa'],  $f_price);
                        $worksheet->write($i,  $col ++, $v['TauxPmpa'],  $f_percent);
 
                        $i ++;                        
                    }
                }    
            }
        }
 
        $workbook->close();
 
        header("Content-Type: application/x-msexcel; name=\"".$filename."\"");
        header("Content-Disposition: inline; filename=\"".$filename."\"");
        $fh=fopen($fname, "rb");
        fpassthru($fh);
        unlink($fname);
    }
}
documentation/programmation/web/php/generation_fichier_excel.txt · Last modified: 2017/07/14 19:45 (external edit)