Sometime as a PHP Developer, I usually get task to create report in excel. Of course I use PHPExcel to generate excel document. To use PHPExcel library, I must do this step below:

   1. Include PHPExcel library
   2. Create Object of PHPExcel
   3. Set Active Sheet
   4. Set style of column and row
   5. Set value by column and row
   6. Set filename and save

If we just need 1 column and 1 row with a less data we can do it fast and simple. For example we need to create 1 excel file with 5 rows data, result from mysql query, there are:
No    Name        City
1    Dono        Yogyakarta
2    Rina        Wonosobo
3    Danu        Purworejo
4    Riki        Jakarta
5    Agnes        Bogor


First, we need to set each column style (A, B and C column because we just need No, Name, and City for header):

$this->sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); (to set center alignment);
$this->sheet->getColumnDimension('A1')->setWidth(5); (to set column size);
$this->sheet->setCellValueByColumnAndRow('A', 1, 'No');

Do the same thing to B and C column. Of course we can set others style that has described in PHPExcel Documentation.

If we done with the header section, then we need to set style and value of each data, we can use for loop:

$lastRow = 2;
foreach ($resultData AS $key => $value) {
    $no = $key+1;
    $this->sheet->setCellValueByColumnAndRow('A', $lastRow, $no);
    $this->sheet->setCellValueByColumnAndRow('B', $lastRow, $value['name']);
    $this->sheet->setCellValueByColumnAndRow('C', $lastRow, $value['city']);
    $lastRow++;
}

We also set its style, if needed. After set the style and value, we need to set filename and then download it:

$this->obj_PHPExcel->SetFileName('Data'.$datetime . '.xls');
$this->obj_PHPExcel->Save();
exit;

All clear, but if we have too many data to export, we need more time to set style and value of each column and row with above way. And I have create functions to make it more easy, these functions describes below:

    /**
     * Use to set title of excel sheet (title location is above header)
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param int $row : row index
     * @param array $arrTitle : title (title1, title2, ...)
     * @param int $column : column name -> using getColumnString($number)
     */
    function title($row, $arrTitle, $column) {
        if (!empty($arrTitle)) {
            for ($i = 0; $i < count($arrTitle); $i++) {


                $this->sheet->mergeCells('A' . $row . ':' . $column . $row)->setCellValue('A' . $row, $arrTitle[$i]);
                //echo "<pre>"; echo print_r($arrTitle[$i]);exit();
                $this->sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $this->sheet->getStyle('A' . $row)->applyFromArray($this->fontBold);

                $row++;
            }
            $this->index = $row;
        }
    }

    /**
     * This function used to create footer of excel sheet
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param int $row : row index
     * @param array $arrTitle : title (title1, title2, ...)
     * @param int $column : column name -> using getColumnString($number)
     */
    function footer($last, $arrTitle, $column) {
        if (!empty($arrTitle)) {
            for ($i = 0; $i < count($arrTitle); $i++) {
                $this->sheet->mergeCells('D' . $last . ':' . $column . $last)->setCellValue('D' . $last, $arrTitle[$i]);
                $this->sheet->getStyle('D' . $last)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->sheet->getStyle('D' . $last)->applyFromArray($this->fontBold);
                $row++;
            }
            $this->index = $row;
        }
    }

    /**
     * This function used to create header table
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param array $cols
     * @param int $last_col : column index
     * @param int $last_row : row index
     */
    private function setHeader($cols, $last_col, $last_row) {
        foreach ($cols as $col) {
            $this->sheet->setCellValueByColumnAndRow($last_col, $last_row, $col['header']);
            if ($col['size'])
                $this->sheet->getColumnDimension(getColumnString($last_col))->setWidth($col['size']);
            else
                $this->sheet->getColumnDimension(getColumnString($last_col))->setWidth()->setAutoSize(true);
            $this->sheet->getStyleByColumnAndRow($last_col, $last_row)->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $this->sheet->getStyle('A' . $last_row . ':' . (getColumnString(count($cols) - 1)) . $last_row)->applyFromArray($this->border);
            $this->sheet->getStyle('A' . $last_row . ':' . (getColumnString(count($cols) - 1)) . $last_row)->applyFromArray($this->fontBold);
            $last_col++;
        }
        $this->index = $last_row;
    }

    /**
     * This function used to initialize $cols variable ($cols used to write data into column)
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param string $arrData : field
     * @param array $arrAlign : column align
     * @param array $arrSize : column width
     * @param array $arrHeader : header table
     * @return array
     */
    private function setCols($arrData, $arrAlign, $arrSize, $arrHeader) {
        $cols = array();
        if (!empty($arrData)) {
            for ($i = 0; $i < count($arrData); $i++) {
                $cols[$i] = array(
                    'header' => $arrHeader[$i],
                    'data' => $arrData[$i],
                    'size' => $arrSize[$i],
                    'align' => $arrAlign[$i]
                );
            }
        } else if (!empty($arrHeader)) {
            for ($i = 0; $i < count($arrHeader); $i++) {
                $cols[$i] = array(
                    'header' => $arrHeader[$i],
                    'align' => 'center',
                    'size' => $arrSize[$i]
                );
            }
        }
        return $cols;
    }

    # make excel function
    /**
     * This function used to write data into excel table
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param array $data : The data from query that use to write to excel table
     * @param array $cols : array that define header, data, align and size
     * @param boolean $total : if report need total row at the bottom of table
     */
    private function makeExcel($data, $cols, $total , $firstRow) {
        $last_row = 1 ;
        $first_row = $last_row;
        $last_col = 0;
        $columnName = getColumnString(count($cols) - 1);
        $title = array($this->title1, $this->label_jalur_global);
        $this->title($last_row , $title, $columnName);
        $last_row = $this->index + 1 + $firstRow;
        $this->setHeader($cols, $last_col, $last_row);

        if (!empty($data)) {
            // foreach($mergeHorizontal as $key => $merge){
            //     $this->sheet->mergeCells(getColumnString($merge[$key]['first_column']) . $merge[$key]['first_row'] . ':' . $merge[$key]['last_column'] . $merge[$key]['last_row']);
            // }
            $no = 1;
            foreach ($data as $rows) {
                $last_row++;
                $first_row = $last_row;
                $rows['no'] = $no;
                $last_col = 0;
                $this->writeDataCol($last_col, $last_row, $rows, $cols);
                $no++;
                $this->sheet->getStyle('A' . $first_row . ':' . (getColumnString(count($cols) - 1)) . ($last_row))->applyFromArray($this->border);
            }
            $last_row++;
            if ($total) {
                $this->total((count($cols) - 1), $last_row, $last_row, $cols, $data);
            }
        } else {
            $last_row = $this->index + 1;
            $this->sheet->mergeCells('A' . $last_row . ':' . $columnName . $last_row)->setCellValue('A' . $last_row, '-- TIDAK ADA DATA --');
            $this->sheet->getStyle('A' . $last_row)->getAlignment()->setWrapText(true)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $this->sheet->getStyle('A' . $last_row)->applyFromArray($this->fontBold);
            $this->sheet->getStyle('A' . $last_row . ':' . $columnName . $last_row)->applyFromArray($this->border);
        }
    }

    /**
     * This function used to write data to table column
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param int $last_col : column index
     * @param int $row : row index
     * @param array $rows : data
     * @param array $cols
     */
    function writeDataCol($last_col, $row, $rows, $cols, $idLap = '') {
        foreach ($cols as $col) {
            $string = $rows[$col['data']];
            if ($string[0] == '0') {
        # if first carachter of string is 0, than set format cell to String
                $this->sheet->setCellValueExplicit(getColumnString($last_col) . $row, $string, PHPExcel_Cell_DataType::TYPE_STRING);
            } else {
                $this->sheet->setCellValueByColumnAndRow($last_col, $row, $string);
            }
            if (isset($col['align'])) {
        # set column alignment of cells
                switch ($col['align']) {
                    case 'center':
                        $align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
                        break;
                    case 'left':
                        $align = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
                        break;
                    case 'right':
                        $align = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
                        break;
                    case 'justify':
                        $align = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
                        break;
                }
                $this->sheet->getStyle(getColumnString($last_col) . $row)->getAlignment()->setHorizontal($align);
            }
            if (isset($col['size'])) {
        # set column size
                $this->sheet->getColumnDimension(getColumnString($last_col))->setWidth($col['size']);
            } else {
        # set to auto size
                $this->sheet->getColumnDimension(getColumnString($last_col))->setWidth()->setAutoSize(true);
            }
            if (isset($col['wrap']) AND $col['wrap'] === true) {
        # set to wrap text
                $this->sheet->getStyleByColumnAndRow($last_col, $row)->getAlignment()->setWrapText(true);
            }
            $last_col++;
            $this->last_col = $last_col;
        }
    }

    /**
     * This function set TOTAL row, if needed. This row will be add at the end of table data row
     * @param int $start : The first index column that will be merged
     * @param int $first_row : The first index row after header table
     * @param int $last_row : The last index row
     * @param array $cols
     * @param array $data
     */
    private function total($start, $first_row, $last_row, $cols, $data) {
        # The first data that will be sum
        $data_pertama = $last_row - (count($data));
        # The last column
        $last_col_name = (getColumnString(count($cols) - 1));
        if ($start > 0) {
            $this->sheet->mergeCells('A' . $last_row . ':' . getColumnString($start - 1) . $last_row);
        }
        $this->sheet->setCellValue('A' . $last_row, 'TOTAL');
        for ($i = $start; $i < count($cols); $i++) {

            $this->sheet->setCellValue(getColumnString($i) . $last_row, '=SUM(' . getColumnString($i) . $data_pertama . ':' . getColumnString($i) . ($last_row - 1) . ')');
        }
        $this->sheet->getStyle('A' . $first_row . ':' . $last_col_name . ($last_row))->applyFromArray($this->border);
        $this->sheet->getStyle('A' . $last_row . ':' . $last_col_name . ($last_row))->applyFromArray($this->fontBold);
        $this->sheet->getStyle('A' . $last_row . ':' . $last_col_name . ($last_row))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    }

    /**
     * This function used to export to excel file (2003 -> .xls)
     * @author Kartika Yudha Pratama <kartikayudhapratama@gmail.com>
     * @param String $name : nama untuk file excel nya
     */
    private function printExcel($name) {
        $this->obj_PHPExcel->SetFileName($name . '.xls');
        $this->obj_PHPExcel->Save();
        exit;
    }

And here is example how to do with the functions:
The data result should be like this (multidimensional array) :
Array {
    [0] =>
        [name] => Dino
        [address] => Papringan
        [city] => Yogyakarta
        [gender] => Male
        [phone] => 085227052001
    [1] =>
        [name] => Rina
        [address] => Grogol
        [city] => Jakarta
        [gender] => Female
        [phone] => 088774567123
}

    private function report() {
        $arrHeader = array('No', 'Name', 'Address', 'City', 'Gender', 'Phone Number');
        $arrData = array('no', 'name', 'address', 'city', 'gender', 'phone');
        $arrSize = array(5, 35, 35, 30, 10, 15);
        $arrAlign = array('center', 'left', 'left', 'left', 'left', 'center');
        $cols = $this->setCols($arrData, $arrAlign, $arrSize, $arrHeader);
        $data = $this->obj_recap->getRecapData();
        $this->makeExcel($data, $cols, false);
        $this->printExcel('Recap Data' . '_' . $this->date);
    }

If we must create many reports in a single php file, this function is usefull for me, just copy functions above in a single php file, then include it in php file that use to create excel file. But this code still can improved, but I don't have much time to improve it anymore :D, if you have your own idea to share please don't hesitate to write comment below.

Sharing is carring :D

Read More

Begin from a task given by my boss to create a standalone application using PHP running on windows operating system. Got confused thinking about how to bundling web server + PHP + MySQL into an .exe file (Windows Executable File). Finally, I can do this, helped by my coworker and I will share to you all.

First thing first, you need to collect the tools:

1. PHP Application that you want to bundle
2. Portable Web Server, I use xampplite 1.7.3 as my web server because my PHP Application using PHP 5.3. There are other portable web server that you can use, server2go but I will tell it how to do with it later.
3. Winrar.

If you have collected all, then do the following step:

1. Backup your database application.
2. Extract xampplite 1.7.3 to C:\. Why I choose C root directory, because this is normally installation folder location for windows operating system and this folder absolutely exist.
3. Copy your PHP Application to htdocs in xampplite 1.7.3 extracted folder.
4. Make sure that no one web server / mysql server running, then you can run apache and mysql by double clicking apache_start.bat and mysql_start.bat or you can run xampplite control panel to start apache and mysql. 
5. Access your phpmyadmin by type localhost/phpmyadmin using your favourite browser.
6. Create new database and then restore your backed up database via phpmyadmin. 

          If you got this following error, when try to access phpmyadmin,

 
Open database_interface.lib.php located in C:\xampplite\phpMyAdmin\libraries, then comment at this line:



Then run your phpmyadmin again. If you have restored the database, setting your application then try to running your application via browser by type: localhost/myapplication. If everything's gonna be okay, then we can start to the next step.
Before bundle this application, we need to create batch file that will be executed after installation process was done. Create new file, filename.bat then open it with your favourite text editor, and add this codes,

call apache_start.bat 

call mysql_start.bat

if exist {"C:\Program Files\Mozilla Firefox\firefox.exe"} {
    "C:\Program Files\Mozilla Firefox\firefox.exe" localhost/myapplication
}

Above code is used to run apache_start.bat, mysql_start.bat and run your web application using firefox (x86-windows 32 bit).


Now we have to create an .exe file to run your web application. You just need Winrar to do that. Select all folder under xampplite, right click then choose add to archive,

 Fill archive name with application name, check create SFX archive and create soli, then move to Advanced tab, shown below,


I suggest to fill this path to extract, same as the extracted xampplite folder (C:\xampplite\), and don't change it, because if you change it, you could change many files under xampplite folder with your custom folder. Run after extraction is used to execute batch file (that has created before) after extraction was completed. You can try to custom in this advanced SFX options, like installer title, installer icon, license and many more. If you have done to custom your installer, then just click OK and wait the process, and when it done, just try to install it to other computer (make sure there is not web server running).


Conclusion
This method usefull enough to create web application into an .exe file. This is actually not create an application (like desktop application) but just compress portable web server that include your application and databases and then it could be portable.




If you have a question don't hestitate to comment below.

Read More

If you are using tinyMCE on your website, and you found NS_ERROR_UNEXPECTED (if you are using firebug, it shown at console) when you try to submit a form that contain tinyMCE or you can submit a form but the content of tinyMCE won't be posted, don't worry I will share how to solve this error.

This error happen because tinyMCE input box is not textarea but a div and it will not post when you submit a form where textarea placed. What you need to do are add theses following code on submit event,

tinyMCE.triggerSave();

So the complete code be like this,

$("#btnSave").click(function() {
      tinyMCE.triggerSave();
     $("#frmPost").submit();
     return false;
})

That command is used to load tinyMCE input to a hidden textarea and of course this will be posted when you submit a form that contain tinyMCE.

Read More