PHPExcel writer

Yes, instead of

you need to use a full path to the file on the server where you need to save it.

you need to use a full path to the file on the server where you need to save it.

Thanks , I understand the path part now. Only thing is all my other tables do not require writing to file but just output which works by default . What is the best method to use the function without changing source to write to file .

Do I put function in event code or should I just copy function rename and put into another folder and call in event? Not sure how to use function with output and write to file as I only need it in one event on one table? And other tables are using same function by default to just open .

You need to add a custom button that would save data to Excel file and email it as an attachment. If you make changes to source file it will affect all tables and all projects.

Thanks for reply.
I have the following code in a button on server from a previous forum poster . The excel file writes to the folder with my selected data . works fine . My issue is the email . I get a server error at top of screen with no message . Just the word close. I tried firefox debug , no errors. Does not send email . I am using the runner_mail function as in the manual. For Attachments
My code is as follows 9.8
require_once ('plugins/PHPExcel.php'); // path
{

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("my name")

->setLastModifiedBy("my name")

->setTitle("Allocation Report")

->setSubject("Report")

->setDescription("Auto generated report")

->setKeywords("office 2007 openxml php")

->setCategory("Allocations");
$headings = array('Record1', 'record2', 'Record3', 'Record4', 'Record5', 'Record6'); // Columns
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
// Set auto filters. Here they are accross a range - standard Excel.

$objPHPExcel->getActiveSheet()->setAutoFilter('A1:F1');
// set default font, calibri for me all day!

$objPHPExcel->getDefaultStyle()->getFont()->setName('calibri');

$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);
// Set tab color for current sheet.

$objPHPExcel->getActiveSheet()->getTabColor()->setRGB('00FF00');

$objPHPExcel->getActiveSheet()->setTitle('Allocations');
//fill top row with a back ground color

$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()

->setFillType(PHPExcel_Style_Fill::FILL_SOLID)

->getStartColor()->setARGB('EFFFD6');

//freeze top row. You need to inclide to columns, else you will get a pane not top row.

$objPHPExcel->getActiveSheet()->freezePane('A2');

$objPHPExcel->getActiveSheet()->freezePane('F2');

// Set headings and get data

$cell = "";

$rowNumber = 1;

$col = 'A';

foreach($headings as $cell) {

$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);

$col++;

}

// Go through the results...

$rowNumber = 2;
while( $data = $button->getNextSelectedRecord() ){

$col = 'A';
foreach($data as $cell) {

$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);

$col++;
}

$rowNumber++;

}

// Save to the directory

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save('C:\\Inetpub\\vhosts\\mywebsite.com\files/Allocations.xlsx');
$objPHPExcel->disconnectWorksheets();

unset($objPHPExcel);

exit();

}

echo 'Error Contact Support!';
// The issue seems to be below with the attachment from the directory As the above code ( PHPExcel) writes the excel file to the directory correctly .





// **** Send Advanced email **

$from = "test@test.com";

$to = "test@test.com";

$msg = "Salesman Allocation Request Attached";

$subject="Allocation Request";





$attachments = array();
// Attachments description. The 'path'(a path to the attachment) is required. Others parameters are optional:

//'name' overrides the attachment name, 'encoding' sets a file encoding, 'type' sets a MIME type
$attachments = array(





array('path' => getabspath('C:\\Inetpub\\vhosts\\mywebsite.com\files/Allocations.xlsx')),





'encoding' => 'base64',

'type' => 'application/vnd.ms-excel',

**

) ;
$ret = runner_mail(array('from' => $from, 'to' => $to, 'subject' => $subject, 'body' => $msg, 'attachments' => $attachments));
if(!$ret["mailed"]){
echo $ret["message"];
}







**

try add to phpfunctions.php inside function runner_mail_smtp( $params )

Thanks for the reply . I tried that . All I get is basically same thing . In debug it shows sql query and just a server error. In the buttonhandler there are no errors or warnings . So it just stops with server error as soon as I start the email code.
I have tried this without PHPexcel and just sent the values to the body in html with regular send selected emails also from the manual . Works fine . So it must be in the runner_mail code for attachments . The manual shows it exactly as I have it so I am stumped on this one. only thing that is different is encoding , which I tried both excel and octet-stream and vnd.ms-excel.

[color="#1C2837"]

The code above for PHPexcel writes to file . I have now succesfully sent an email with on server button and writing to the folder directory before email is sent. I seem to be getting close . Although the email shows that the file attached is corrupted and does not open or opens after asking if you want to open corrupted file and there is no data. On server if I download the file it has all the data, so the file has the data , I just can't seem to get it and send without it being corrupted . My code is below .
$email="noreply@mywebsite.com";
$subject="Allocations Attached";

$msg="See Attached Allocations Request";
$attachments = array();
$excel_1 = 'files/Allocations.xlsx';
$attachments[] = array("path" => $excel_1);
$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg, "attachments" => $attachments));
if(!$ret["mailed"])
echo $ret["message"];
I am assuming that that there needs to be headers involved here but have tried that to no avail . I dumped using phpmailer and using built in runner_mail.
For anyone else who has tried this in a button do not exit PHPexcel before sending mail as that was my issue . Although the attachment is corrupted.
Anyone have any ideas here?

See Custom Code Above.

And please , Someone tell me what custom email settings mean . I looked at the runner_mail section at least 30 times . What is custom mail settings exactly ? It does not say it in the manual. As Attachments in runner_mail does not work according to the code in the manual. Tried to no avail . . I even used the same names for the files and directory and have write / read permissions . According to the manual this should work . Click Here . No errors in debug and no email sent. The above custom code works but the files are corrupted and data is empty .

What are your SMTP settings in this project?

From= myemailaddress

Smtp server = mywebsite.com

Smtp = myusername.com

Smtp = mypassword

Smtp port = 25 / Plain
use php mail function is not checked.
I was trying to do get this accomplished with runner_mail instead mail function. Does mail function have to be used instead and then checked off in email settings? As I tried that with other custom code also.

It should work if you are not using built-in PHP's mail function and if your code is correct.

Sergey,

Thanks for the reply. I have php mail function unchecked. And in misc section the same setup as above and use all the time with all of my projects for this particular domain. It is actually hosted under innisprunner hosting , So I assume phpinfo which I looked at is fine. I am missing something

that maybe I don't understand, but I have tried the runner_mail code with attachments as in the manual without anything checked in email settings. I have also tried checking it in the box and putting custom code like ( phpmailer ) and it did not even send the email . The closest I am getting

is with the last code I posted above with code in manual runner_mail but the file sent is corrupt . If I go to the server after filling in the form , I download the excel file and everything looks fine . So it has to be from the code getting the file and sending in the email . I was thinking headers as a problem with the code in the manual it does not show headers in example but in the source code it looks like it takes care of it (9.8 ) , So I am at a lost as to why. I had this same problem years ago with PDF files same issue except error was out of memory and corrupted. Sorry for the long message but this has been an issue for me all along with attachments in ASPrunner and PHPrunner.

[quote name='John' date='06 April 2018 - 06:37 PM' timestamp='1523054256' post='84750']
Solved! [size="2"]Thanks for pointing me in the right direction sergey. Also tempus_eros for a previous post on phpexcel.[/size]
This was the correct code . See Below . Works for me in 9.7 and 9.8.
require_once ('plugins/PHPExcel.php'); // path
{

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("my name")

->setLastModifiedBy("None")

->setTitle("Allocations Report")

->setSubject("Report")

->setDescription("Auto generated report")

->setKeywords("Generated by Forms Data Pro - For mywebsite.com")

->setCategory("Allocations");
$headings = array('Employee', 'EmployeeID', 'Customer', 'CustomerID', 'Brand', 'Size', 'Cases', 'Bottles', 'fsm_email'); // Columns
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setVisible(false);

$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setVisible(false);

$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setVisible(false);
// Set auto filters. Here they are accross a range - standard Excel.

$objPHPExcel->getActiveSheet()->setAutoFilter('A1:I1');
// set default font, calibri for me all day!

$objPHPExcel->getDefaultStyle()->getFont()->setName('calibri');

$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);
// Set tab color for current sheet.

$objPHPExcel->getActiveSheet()->getTabColor()->setRGB('00FF00');

$objPHPExcel->getActiveSheet()->setTitle('Allocations');

$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setVisible(false);

$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setVisible(false);
//fill top row with a back ground color

$objPHPExcel->getActiveSheet()->getStyle('A1:I1')->getFill()

->setFillType(PHPExcel_Style_Fill::FILL_SOLID)

->getStartColor()->setARGB('EFFFD6');

//freeze top row. You need to inclide to columns, else you will get a pane not top row.

$objPHPExcel->getActiveSheet()->freezePane('A2');

$objPHPExcel->getActiveSheet()->freezePane('I2');

// Set headings and get data

$cell = "";

$rowNumber = 1;

$col = 'A';

foreach($headings as $cell) {

$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);

$col++;

}

// Go through the results...

$rowNumber =2;
$msg="";

while( $data = $button->getNextSelectedRecord() ){

$msg= "Do not reply to this message - Reply to : ".$_SESSION["fldemail"]."\r\n"; // this needs to go here to get message for body
$sql = "update allocation set Status='Confirmed Sent to Manager' where ID=" .$data["ID"];

CustomQuery($sql);

$col = 'A';
foreach($data as $cell) {

$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);

$col++;
}

$rowNumber++;

}

// Save to the directory

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();

$objWriter->save('C:\\Inetpub\\vhosts\\yourwebsite.com\files/Allocations.xlsx');

}
// send email with attachment in excel
$email=$_SESSION["fsm_email"];
$subject="Allocation Request";
$attachments = array();
$excel_1 = 'C:\\Inetpub\\vhosts\\yourwebsite.com\files/Allocations.xlsx';
$attachments[] = array("path" => $excel_1);
$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg, "attachments" => $attachments));
if(!$ret["mailed"])
echo $ret["message"];
One issue was [size="2"]ob_end_clean(); needed to be added after save to avoid erroneous characters[/size]

[size="2"]Second issue was getting path in email code . All working good now. [/size]
[size="2"]If using this code beware if any fields in database that are not used in the list page but added to database your unwanted data will show up in a different column in excel . [/size]
[size="2"]To hide field use [/size]

[size="2"]$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setVisible(false); // or whatever column letter the data shows in.[/size]
Client After :
var message = result["txt"] + "message sent"; // although I get undefined message sent? still works

ctrl.setMessage(message);
location.reload(); // to show status field sent in field on list page ( each record selected ) After clicking on button on top . Hidden in excel file