Implement PHPExcel function, MYSQL to Excel

[masfoto]

Hello fabrik users,

Situation:
There is simple database made with fabrik.
I need to fetch data from MYSQL database and put it in Excel template.

There is already installed PHPExcel library and I can make code for inserting not dynamic (words) data in excel template .

Can anyone post some PHPExcel code for getting data from MYSQL and put the data in excel file in defined cells.
The data must be the same as a record row in the list. And other point is, how to make button on view of an record, which will start PHPExcel function.
Now I need to make front page of an order which will be printed and package order.

PHPExcel is the best solution, I think.
If anyone have other suggestions, please let me know.

I was trying to use dompdf library, but it's not working, and it's not so easy to make print lay-out in the way you want.

I am not a programmer, but can understand and modify some PHP/ MYSQL code.
Please post some examples of PHPExcel code and where I need to put it to activate button in fabrik view.

Thank you in advance!

Sorry for my bad English.

 

[troester]

You can do a CSV export of Fabrik lists in Excel format
http://fabrikar.com/forums/index.php?wiki/list-edit/#csv

You can create a custom form template and use it as PDF template with domPDF
http://www.fabrikar.com/forums/index.php?wiki/form-and-details-templates/
http://fabrikar.com/forums/index.php?wiki/pdf-output/

You can add a php list plugin to do any php stuff on list records
http://fabrikar.com/forums/index.php?wiki/php-list-plugin/

 

[masfoto]

Troester, thank you very much for your answers!

I need customized function to export data in lay-out like invoice, order.
CSV export can't do that.
I don't need to export a list.
I need to export data from each record separated and put this data to excel template file.
DomPDF will be the way, but it's to difficult to get it working.


Dompdf don't work for me, it show just black/ empty page.
I have trying everything with this library, but dompdf don't work for me.
I read bunch of stuff on wiki, furums about domPDF blank page issue, but it's won't work.
I have tried various webservers (XAMPP and USB webserver on windows and various linux based servers)
Yes, I have updated from github but no succes.

Thank you for mentioning PHP list plugin.

It is useful function.

 

[Bauer]

I use the PHPExcel library extensively in my project. (It may be good and the best solution - but it sure is slow)

The easiest way would be to create an Excel file as a template (with just the column headers and any other styling/formatting you want to use in the headers). That lets you design the page heading without having to do much coding. I created a folder /libraries/phpexcel/library/PHPExcel/Templates and copied the pre-designed template file there.

Then when you open the PHPExcel library start by opening that 'blank' template.
If you create the right mySQL query (that includes only the fields/columns used in the template) you should be able to import the query results, row by row, into that PHPExcel worksheet.

Here are a few lines of some sample code...

// INITIALIZE PHPExcel library and set some variables
jimport('phpexcel.library.PHPExcel');
// Create new PHPExcel object using the template file
$objPHPExcel = PHPExcel_IOFactory::load(JPATH_ROOT.'/libraries/phpexcel/library/PHPExcel/Templates/report_breakouts.xlsx');

run the query and save the results as an associative array - e.g.
$listRows = $db->loadAssocList();

using a simple loop import each row of the query results into $objPHPExcel

$rowsCount = count($listRows);
// (set $headerRows to the number of rows in the header - or just hard code instead of using variable)
$headerRows = 2;
for ($row = 0; $row < $rowsCount; $row++) {
    $thisRow = $row + $headerRows;
    $objPHPExcel->getActiveSheet()->fromArray($listRows[$row], null, "A".$thisRow );
}

Before saving the file as a new name, lock the top few rows (as many rows as are used in the column header) so the columns remain fixed at the top as the list is scrolled.

$objPHPExcel->getActiveSheet()->freezePane("A3");

Save the $objPHPExcel object to new file in the desired format. (The first 2 lines below are all that is really needed. )
(In this example I had already assigned a value to $outfilepath and $filename - to create a unique filename containing the user ID. I also am using the JSmallfib extension to create unique folders for each user (and allow me to store the files outside the web root for added security). Also, via a system message, a link is provided to that JSmallfib article - which then displays the user's folder/files for them to view/download.)

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save( str_replace(__FILE__,  $outfilepath.'/'.$filename.'.xlsx', __FILE__) );
if(file_exists($outfilepath.'/'.$filename.'.xlsx')){
    $msg .= ' - <a href="/index.php?option=com_content&view=article&id=1186&Itemid=1364&jsmallfib=1&dir=JSROOT/'.$user->username.'/Reports">Download File(s)</a>';
}else{
    $msg .= 'Failed to write Report file '.$filename.'.xlsx';
}
JFactory::getApplication()->enqueueMessage($msg,'notice');

Hopefully this will get you started in the right direction.

 

[masfoto]

Hello Bauer,

Thank you for your post and code!

I will try your code now.

At this moment I can put static data to Excel template with following code, but no data from mysql database:


mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);



//your MySQL Database Name of which database to use this
$tablename = "bedrijf"; //your MySQL Table Name which one you have to create excel file
// your mysql query here , we can edit this for your requirement
$sql = "SELECT id, bedrijfsnaam FROM $tablename WHERE id=30";
//create code for connecting to mysql
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($dbname, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query


/** PHPExcel */
include 'PHPExcel.php';



/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

date_default_timezone_set('Europe/Amsterdam');

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

// Read from Excel (.xls) template
$objReader = PHPExcel_IOFactory::createReader('Excel5');


$templateFile = "templates/invoice.xls";


$objPHPExcel = $objReader->load($templateFile);


// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Michael M");
$objPHPExcel->getProperties()->setLastModifiedBy("Michael M");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");


// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);

//IF YOU SEE, I CAN PUT SOME DATA IN EXCEL TEMPLATE, BUT GET IT FROM MYSQL WITH QUERY DON'T WORK. I DO SOMETHING WRONG.
//I AM TRYING TO PUT VALUE IN CELL E15 FROM SQL QUERY AND I GET FOLLOWING ERROR
//Warning: Illegal string offset 'bedrijfsnaam' in C:\USBWebserver v8.6(2)\root\rashod\libraries\phpexcel\library\test.php on line 77

$objPHPExcel->getActiveSheet()->SetCellValue('E15', $sql ['bedrijfsnaam']);
$objPHPExcel->getActiveSheet()->SetCellValue('A22', 'Test 2');
$objPHPExcel->getActiveSheet()->SetCellValue('E13', 'Test 3');
$objPHPExcel->getActiveSheet()->SetCellValue('D22', 'Test4');

// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

 

[Bauer]

If you are using the PHPExcel library designed specifically for Joomla (https://github.com/vdespa/PHPExcel-Joomla-Library), and assuming the tables you are referencing are in the same database, you shouldn't need to worry about setting up the database connection. Direct link to download: Download Install kit for Joomla 2.5/3.x

Then just write your code - and anything referencing the database tables - using the Joomla API - (as if using the fabrik php plugin). https://docs.joomla.org/Selecting_data_using_JDatabase

 

[masfoto]

Baurer, thank you for this advice!
Yes, I use PHPExcel library which is designed for Joomla.

I will try to get data with my code without database connection settings.