webERP Forum

Full Version: webERP and Excel
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2
Hi all:

I need to export some webERP data to an excel file for a 3rd party online supplier, so I've been googling "export data from PHP to excel".

I've found a module that looks pretty good https://github.com/PHPOffice/PHPExcel but before starting with this task:

1) has anyone done a similar thing?
2) Do you know a better module to staret with?
3) Is anyone else interested in this line of work, so we can add it to the main weberp trunk?
I've thought about it .. downloaded it but never got around to it.
Great initiative I think and definitely belongs in the trunk so we can embrace and extend as we want!
Hi, Andrew,

If we only need to export an excel file, the class seems overkill.

You can directly transfer html files to excel files. It's fast and simple. I think there is a thread from Tim that he has show an example. You can find it in the forum.
The code looks like this:

function headExcel($Html){
$file="test.xls";// the file to download
header("Content-type: application/vnd.ms-excel;charset=utf-8");
header("Content-Disposition: attachment; filename=$file");
$Html = chr(239).chr(187). chr(191).$Html;//make the file charset to be utf8;
echo $Html;//the html string to convert to excel file

}
Best regards!

Exson
i have added PHP Excell to my instalation, it's easy,I include the files at header,and just follow the instruction. there are some easy examples too..
Just make sure the requirement as follow already installed on server
- PHP version 5.2.0 or higher
- PHP extension php_zip enabled (only needed by PHPExcel_Reader_Excel2007)
- PHP extension php_xml enabled
- PHP extension php_gd2 enabled (if not compiled in)

trima kasih..
(08-21-2014 02:45 PM)kelo Wrote: [ -> ]i have added PHP Excell to my instalation, it's easy,I include the files at header,and just follow the instruction. there are some easy examples too..

Hi Kelo:

Good to know it's possible. I'm trying and getting garbage on the screen but no file yet...

- I copied the Classes and Example folders to the weberp installation.

- Tried with some examples as suggested in PHPExcel documentation and it worked perfectly, so the library has all the required components. Tested 01simple-download-xlsx.php and showed a dialog to download, and downloaded a well formed excel file.

- I created the file ExcelGLTransactionsPajak.php (based on SalesInquiry.php) so I can test it with some webERP GL data.

-Copied into this script all the steps shown on the example file to create and download an excel.

- If I run it, I get only garbage on the screen after the html table.

- If I comment line 150, it does not show garbage (but not downloading), so I'm supposing webERP does mess with the "->save" thing, because it works on the example given (without the weberp framework).

Any idea why?

I attach the webERP script and the PHPExcel example.

Thanks!
Halo om richard..
sorry for late response,.
here is my example at GLTagProfit_Loss.php to show tag report for 12 months (period) and produce it in excell report..

Code:
else if (isset($_POST['PrintEXCELL']))
{
    include('includes/header.inc');
    echo '<form method="post" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '">';
    echo '<div>';
    echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
        <input type="hidden" name="FromPeriod" value="' . $_POST['FromPeriod'] . '" />
        <input type="hidden" name="ToPeriod" value="' . $_POST['ToPeriod'] . '" />';

    $NumberOfMonths = $_POST['ToPeriod'] - $_POST['FromPeriod'] + 1;

    if ($NumberOfMonths >12)
    {
        echo '<br />';
        prnMsg(_('A period up to 12 months in duration can be specified') . ' - ' . _('the system automatically shows a comparative for the same period from the previous year') . ' - ' . _('it cannot do this if a period of more than 12 months is specified') . '. ' . _('Please select an alternative period range'),'error');
        include('includes/footer.inc');
        exit;
    }  
  
    $sql = "SELECT lastdate_in_period
            FROM periods
            WHERE periodno='" . $_POST['ToPeriod'] . "'";
    $PrdResult = DB_query($sql, $db);
    $myrow = DB_fetch_row($PrdResult);
    $PeriodToDate = MonthAndYearFromSQLDate($myrow[0]);




    require_once dirname(__FILE__) . '/includes/excell/PHPExcel.php';
    
    $objPHPExcel = new PHPExcel();
    
    $objPHPExcel->getProperties()->setCreator("WebERP")
                             ->setLastModifiedBy("WebERP")
                             ->setTitle("Office 2007 XLSX Document")
                             ->setSubject("Office 2007 XLSX Document")
                             ->setDescription("WebERP.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Office Documents");
    
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Period ' . $_POST['FromPeriod'] . 'To : ' . $_POST['ToPeriod'] . ' Actual Cost')->setCellValue('C2', $_POST['FromPeriod']);
    $objPHPExcel->getActiveSheet()->setCellValue('D2', $_POST['FromPeriod']+1)              ->setCellValue('E2', $_POST['FromPeriod']+2);
    $objPHPExcel->getActiveSheet()->setCellValue('F2', $_POST['FromPeriod']+3)              ->setCellValue('G2', $_POST['FromPeriod']+4);
    $objPHPExcel->getActiveSheet()->setCellValue('H2', $_POST['FromPeriod']+5)              ->setCellValue('I2', $_POST['FromPeriod']+6);
    $objPHPExcel->getActiveSheet()->setCellValue('J2', $_POST['FromPeriod']+7)              ->setCellValue('K2', $_POST['FromPeriod']+8);
    $objPHPExcel->getActiveSheet()->setCellValue('L2', $_POST['FromPeriod']+9)              ->setCellValue('M2', $_POST['FromPeriod']+10);
    $objPHPExcel->getActiveSheet()->setCellValue('N2', $_POST['FromPeriod']+11)              ->setCellValue('O2', $_POST['FromPeriod']+12);
                                  
                                  
                                  
// Tampilkan tags,
    $SQL = "SELECT tagref,
                tagdescription
                FROM tags
                ORDER BY tagdescription";

    $result=DB_query($SQL,$db);
    
    $k=3;
    while ($myrow=DB_fetch_array($result)) // loop all tags
    {
        $k++;
        $buff="A".$k;
        $objPHPExcel->getActiveSheet()->setCellValue($buff,$myrow['tagdescription']);
        
### BEGIN : Show all account with this->tags
            
        /*
         *      $SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
                               Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
                               Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
                        FROM chartmaster
                        INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                        INNER JOIN gltrans ON chartmaster.accountcode= gltrans.account
                        WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "'
                        GROUP BY accountgroups.sectioninaccounts,
                            accountgroups.groupname,
                            accountgroups.parentgroupname,
                            gltrans.account,
                            chartmaster.accountname
                        ORDER BY accountgroups.sectioninaccounts,
                            accountgroups.sequenceintb,
                            accountgroups.groupname,
                            gltrans.account";        
            */
            
            /* patch by kelox */
            $SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
                               Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
                               Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
                        FROM gltrans
                        INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
                        INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                        WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "'
                        GROUP BY accountgroups.sectioninaccounts,
                            accountgroups.groupname,
                            accountgroups.parentgroupname,
                            gltrans.account,
                            chartmaster.accountname
                        ORDER BY accountgroups.sectioninaccounts,
                            accountgroups.sequenceintb,
                            accountgroups.groupname,
                            gltrans.account";    
            
            $restag = DB_query($SQLTAGS,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));

            while ($tagrow=DB_fetch_array($restag)) //loop total
            {
                //echo '<pre>'; print_r($tagrow); echo '</pre>';
                $k++;
                $buffB="B".$k;
                $tagdesc= $tagrow['account'] . " " . $tagrow['accountname'];
                $objPHPExcel->getActiveSheet()->setCellValue($buffB,$tagdesc);
                
                $buffTotal="O".$k;
                $objPHPExcel->getActiveSheet()->setCellValue($buffTotal,locale_number_format($tagrow['TotalAllPeriods'],$_SESSION['CompanyRecord']['decimalplaces']));
                
                ## populate for 12 periods from $post['fromPeriod'];
                ##first periods
                $firstperiod=$_POST['FromPeriod'];
                $firstperiod=$firstperiod-1;
                
                //Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sept    Oct    Nov    Des

                //$arrayMONTH=array{'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sept','Oct','​Nov','Des'};
                $arrayCELL = array("C","D","E","F","G","H","I","J","K","L","M","N");
                
                for($periodcount=0;$periodcount<12;$periodcount++)
                {
                    //echo $arrayCELL[$periodcount]; //Cell
                    $firstperiod=$firstperiod+1;  // Increment each month until 12 month
/*
                    $SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
                                   FROM chartmaster
                                   INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                                   INNER JOIN gltrans ON chartmaster.accountcode= gltrans.account
                                   WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "' AND account='". $tagrow['account'] ."'
                                   GROUP BY accountgroups.sectioninaccounts,
                                           accountgroups.groupname,
                                           accountgroups.parentgroupname,
                                           gltrans.account,
                                           chartmaster.accountname
                                   ORDER BY accountgroups.sectioninaccounts,
                                           accountgroups.sequenceintb,
                                           accountgroups.groupname,
                                           gltrans.account";                       
*/
                    $SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
                                   FROM gltrans
                                   INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
                                   INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                                   WHERE accountgroups.pandl=1 AND gltrans.tag='" . $myrow['tagref'] . "' AND account='". $tagrow['account'] ."'
                                   GROUP BY accountgroups.sectioninaccounts,
                                           accountgroups.groupname,
                                           accountgroups.parentgroupname,
                                           gltrans.account,
                                           chartmaster.accountname
                                   ORDER BY accountgroups.sectioninaccounts,
                                           accountgroups.sequenceintb,
                                           accountgroups.groupname,
                                           gltrans.account";
                
                    $reseachtag = DB_query($SQLeachtag,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
                    $tagthisperiod = DB_fetch_array($reseachtag);

                    
                    
                    $buffhorizontal = $arrayCELL[$periodcount] . $k; // ex : C1, D1, E1, F1.. etc..
                    $objPHPExcel->getActiveSheet()->setCellValue($buffhorizontal,$tagthisperiod['TTLthisPeriod']);
                }
                ##end populate for 12 periods from $post['fromPeriod'];
                
            }
## END : Show all account with this->tags
      
    }
    
/*
* SHOW ALL ACCOUNT THAT HAS NOT BEEN TAGGED..
*/
        $k=$k+3;
        
        $NoTag = "A".$k;
        $objPHPExcel->getActiveSheet()->setCellValue($NoTag,"Account with no tag ");
    
##########################


    $k=$k+3;
        $k++;
        $buff="A".$k;
        
            /* patch by kelox */
            $SQLTAGS = "SELECT accountgroups.sectioninaccounts, accountgroups.groupname, accountgroups.parentgroupname, gltrans.account, chartmaster.accountname,
                               Sum(CASE WHEN (gltrans.periodno>='" . $_POST['FromPeriod'] . "' AND gltrans.periodno<='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalAllPeriods,
                               Sum(CASE WHEN (gltrans.periodno='" . $_POST['ToPeriod'] . "') THEN gltrans.amount ELSE 0 END) AS TotalThisPeriod
                        FROM gltrans
                        INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
                        INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                        WHERE accountgroups.pandl=1 AND gltrans.tag=0
                        GROUP BY accountgroups.sectioninaccounts,
                            accountgroups.groupname,
                            accountgroups.parentgroupname,
                            gltrans.account,
                            chartmaster.accountname
                        ORDER BY accountgroups.sectioninaccounts,
                            accountgroups.sequenceintb,
                            accountgroups.groupname,
                            gltrans.account";    
            
            $restag = DB_query($SQLTAGS,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));

            while ($tagrow=DB_fetch_array($restag)) //loop total
            {
                //echo '<pre>'; print_r($tagrow); echo '</pre>';
                $k++;
                $buffB="B".$k;
                $tagdesc= $tagrow['account'] . " " . $tagrow['accountname'];
                $objPHPExcel->getActiveSheet()->setCellValue($buffB,$tagdesc);
                
                $buffTotal="O".$k;
                $objPHPExcel->getActiveSheet()->setCellValue($buffTotal,locale_number_format($tagrow['TotalAllPeriods'],$_SESSION['CompanyRecord']['decimalplaces']));
                
                ## populate for 12 periods from $post['fromPeriod'];
                ##first periods
                $firstperiod=$_POST['FromPeriod'];
                $firstperiod=$firstperiod-1;
                
                //Jan    Feb    Mar    Apr    May    Jun    Jul    Aug    Sept    Oct    Nov    Des

                //$arrayMONTH=array{'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sept','Oct','​Nov','Des'};
                $arrayCELL = array("C","D","E","F","G","H","I","J","K","L","M","N");
                
                for($periodcount=0;$periodcount<12;$periodcount++)
                {
                    $firstperiod=$firstperiod+1;  // Increment each month until 12 month

                    $SQLeachtag = "SELECT Sum(CASE WHEN (gltrans.periodno='" . $firstperiod . "') THEN gltrans.amount ELSE 0 END) AS TTLthisPeriod
                                   FROM gltrans
                                   INNER JOIN chartmaster ON chartmaster.accountcode= gltrans.account
                                   INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname
                                   WHERE accountgroups.pandl=1 AND gltrans.tag=0 AND account='". $tagrow['account'] ."'
                                   GROUP BY accountgroups.sectioninaccounts,
                                           accountgroups.groupname,
                                           accountgroups.parentgroupname,
                                           gltrans.account,
                                           chartmaster.accountname
                                   ORDER BY accountgroups.sectioninaccounts,
                                           accountgroups.sequenceintb,
                                           accountgroups.groupname,
                                           gltrans.account";
                
                    $reseachtag = DB_query($SQLeachtag,$db,_('No general ledger accounts were returned by the SQL because'),_('The SQL that failed was'));
                    $tagthisperiod = DB_fetch_array($reseachtag);

                    $buffhorizontal = $arrayCELL[$periodcount] . $k;
                    $objPHPExcel->getActiveSheet()->setCellValue($buffhorizontal,$tagthisperiod['TTLthisPeriod']);
                }
                
            }

##########################               
    
    
    //echo date('H:i:s') , " Set column widths" , EOL;
    //$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);                              
    
    //echo date('H:i:s') , " Rename worksheet" , EOL;
    $objPHPExcel->getActiveSheet()->setTitle('Tag Reports');
    
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    
    //simpan
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
    
    
    //$excellfile= 'GLTagProfit_Loss.xlsx';
    //echo Dirname($_SERVER[PHP_SELF]);
    //echo str_replace('.php','.xlsx',htmlspecialchars($_SERVER['PHP_SELF']));
    
    echo '<a href='. str_replace('.php','.xlsx',htmlspecialchars($_SERVER['PHP_SELF'])) . '>Download Here</a>';
    //header('Location: '.$excellfile );
        
    
    
}

i guess your problem is that the error happened when you try to directly redirect to download.. try to create the excell file at the server first, and create a download link to download it manually..
I created the attached report using PHPExcel and found it easy to use.
I also think the report is pretty good from a business perspective if you have controlled items (Lot / Serial).
The report lists all OH Lots assuming you have applied the SQL below and ages them by date they were created. So you can see old inventory you might need to have a fire sale on or get your salespeople to push. Then it also lists incoming inventory and expected dates. It gives yhou a very nive picture of your inventory

ALTER TABLE `stockserialitems` ADD `createdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
ADD INDEX ( `createdate` ) ;
UPDATE stockserialitems SET createdate = NULL;

UPDATE stockserialitems as stockserialitems SET createdate=
(SELECT trandate FROM (select trandate, stockserialitems.serialno, stockserialitems.stockid from stockserialitems
LEFT JOIN stockserialmoves ON stockserialitems.serialno=stockserialmoves.serialno
LEFT JOIN stockmoves ON stockserialmoves.stockmoveno=stockmoves.stkmoveno
GROUP BY stockserialitems.stockid, stockserialitems.serialno
ORDER BY trandate) as ssi
WHERE ssi.serialno=stockserialitems.serialno
AND ssi.stockid=stockserialitems.stockid);
Awesome!
We need to add this PHPExcel to the repo and get into a few more excel exports I think .. this inventory report and the Excel export of the P & L should also be in the repo
I know it took me a long time, sorry for this.

I just commited one script PcAnalysis.php that creates an excel file with a lot of petty cash data to analyse and check "desviations" of the expenses reported by employees.

Also commited the PHPExcel Class. I've been using it for some months and has been working OK.
Being a lot of files, I preferred to commit all the class, as it includes plenty of exmaples.

Please check the commit and clean it up if needed.
Nice one ... will study - plenty of potential for improved reporting with this.
Pages: 1 2
Reference URL's