Divide Colums in exported CSV file

orik3ll0 · Aug 27, 2016

Hello all. I want to change exported CSV file. Is it possible to download Excel file or PDF? My boss required from me to press EXPORT button from Dashboard and see normal table without comma( with divided columns). I am already have tried to change "ajax.reports.php", "class.auth.php", "class.search.php" files in "Include" folder from "A1" to another columns, but it was waste of time.P.S. 2 attached files describe what I need. Please help if anyone knows the way. Thank you for help.

orik3ll0 · Aug 27, 2016

Pictures from-->tohttps://www.dropbox.com/sh/k75ipybwetcx41q/AABHQlxXY3PeWIdooNHk5jlta?dl=0

citrixraptor · Aug 27, 2016

Hello orik3ll0,

edit only:

/upload/include/ajax.reports.php

from:

function downloadTabularData() {

$data = $this->getData();

$csv = '"' . implode('","',$data) . '"';

foreach ($data as $row)

$csv .= "\n" . '"' . implode('","', $row) . '"';

Http:(

sprintf('%s-report.csv', $this->get('group', __('Department'))),

'text/csv', $csv);

}

to:

// my mod

function downloadTabularData() {

$data = $this->getData();

$csv = '"' . implode('";"',$data) . '"';

foreach ($data as $row)

$csv .= "\n" . '"' . implode('";"', $row) . '"';

Http:(

sprintf('%s-report.csv', $this->get('group', __('Department'))),

'text/csv', $csv);

}

// my mod

orik3ll0 · Aug 27, 2016

Dear Citrixraptor You are genius!) Thank you very much, spent 2 days only because of this "semi comma") İ have one more question. İs it possible make each column with border and all A column with "bold" letters?With PhPexcel i use this code bellow, but here i do not how to do it.$ActiveSheet->getStyle("A1")->applyFromArray(

array(

'borders' => array(

'allborders' => array(

'style' => PHPExcel_Style_Border:,

'color' => array('rgb' => 'DDDDDD')

)

);

citrixraptor · Aug 28, 2016

Hello orik3ll0,

perhaps a help for you:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

$styleArray = array(

'borders' => array(

'outline' => array(

'style' => PHPExcel_Style_Border:,

'color' => array('argb' => 'DDDDDDDD'),

),

);

$objWorksheet->getStyle('A1')->applyFromArray($styleArray);

Tipp:

https://phpexcel.codeplex.com/

Read this:

"PHPExcel_1.8.0_doc\Documentation\PHPExcel developer documentation.doc"

4.6.22. Styling cell borders

In PHPExcel it is easy to apply various borders on a rectangular selection. Here is how to apply a thick red border outline around cells B2.

$styleArray = array(

'borders' => array(

'outline' => array(

'style' => PHPExcel_Style_Border:,

'color' => array('argb' => 'FFFF0000'),

),

);

$objWorksheet->getStyle('B2')->applyFromArray($styleArray);

orik3ll0 · Aug 28, 2016

Problem is how to make PHPExcel working with osticket,,, Actualy I am thinking to write a script which will take CSV file and will construct it with borders and headers, because all my tries to change osticket exported file is without good results, most of all i crash it.

citrixraptor · Aug 29, 2016

Hello orik3ll0,

what exactly is the problem?

I build a own solution that works with PHPExcel ...

Best Regards

citrixraptor

orik3ll0 · Aug 30, 2016

Hello citrixraptor. 1) I tried many times to change ajax.reports.php and class.exported.php file and make it work with PHPExcel, but it is not working, It`s crash everytime. Have u got some example how to integrate PHPExcel to osticket? - I found temporary solution for this, everytime i export csv file I create table by my own) 2) i have one more question. I use LDAP and we have many departments. I want to see count of created tickets of departments and exact person(to compare which department create more tickets). Is it possible?Best RegardsOrik3ll0.

citrixraptor · Aug 31, 2016

Hello orik3ll0,

>> 1. ...

>> Have u got some example how to integrate PHPExcel to osticket?

Yes ...

Download PHPExcel (Used: PHPExcel 1.8.0) and save it in:

\upload\phpexcel

Result:

\upload\phpexcel

\upload\phpexcel\Classes

\upload\phpexcel\Classes\PHPExcel

\upload\phpexcel\Classes\PHPExcel.php

Change the owner and the permissions:

chown -R www-data-data \upload\phpexcel\

chmod 755 -R \upload\phpexcel\

Edit only: \upload\include\ajax.reports.php

// my mod

function downloadTabularData()

{

$data = $this->getData();

/** PHPExcel */

include '../phpexcel/Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */

include '../phpexcel/Classes/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("citrixraptor");

// Optionally, set the title of the Sheet

$objPHPExcel->getActiveSheet()->setTitle('Department Statistics');

$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->SetCellValue('A1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('B1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('C1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('D1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('E1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('F1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('G1', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('H1', $data);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment:);

$objPHPExcel->getActiveSheet()->SetCellValue('A2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('B2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('C2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('D2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('E2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('F2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('G2', $data);

$objPHPExcel->getActiveSheet()->SetCellValue('H2', $data);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="Department_Statistics.xlsx"');

header('Cache-Control: max-age=0');

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

$objWriter->save('php://output');

}

// my mod

Please keep in mind this is a example! ... only the first line of:

Department, Topics and Agent will be exported! ... but it works :-)

in the default osticket installation: v1.9.14 (8b927a0)

The lines for $data); and $data are hardcoded!!! and other var's are hardcoded .... the playground is open :-)

Hope it helps ...

>>2) i have one more question. I use LDAP and we have many departments. I want to >> see count of created tickets of departments and exact person(to compare which

>> department create more tickets). Is it possible?

Hm ... please create a "dummy" Excel Example ... What do you want show?

Best Regards

citrixraptor

orik3ll0 · Sep 14, 2016

blank I am created new Topic, but anyway i will ask you for help here also. Is it possible to call rows not by ID but by variables? I can not find variables for columns. I need to automatic rowspan and add addition columns for exported file. With Id is not possible, i tried many ways. In case of picture is not shown, link for picture what I need:https://www.dropbox.com/s/kgv5c9umgdq692e/%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA.JPG?dl=0

Снимок.JPG

citrixraptor · Sep 14, 2016

Hello orik3ll0,

>> Is it possible to call rows not by ID but by variables?

Not clear, what you mean?!

>> I can not find variables for columns.

Please post the php code ...

>> I need to automatic rowspan and add addition columns for exported file. Are the addition columns hardcoded // dummy columns ... no exists in the mysql Database?!

>> With Id is not possible, i tried many ways. Example code ...

Best Regardscitrixraptor

orik3ll0 · Sep 14, 2016

Dear Citrixraptor

I need to get all tickets in xlsx or csv with these information from export button.

$objPHPExcel->getActiveSheet()->SetCellValue('A1', Date);------------->Osticket(created date) ;

$objPHPExcel->getActiveSheet()->SetCellValue('B1', Department);---->LDAP

$objPHPExcel->getActiveSheet()->SetCellValue('C1', Phone); ----------> LDAP

$objPHPExcel->getActiveSheet()->SetCellValue('D1', From);------------->LDAP(Who created ticket)

$objPHPExcel->getActiveSheet()->SetCellValue('E1', Topic Parent);------->Osticket(parent)

$objPHPExcel->getActiveSheet()->SetCellValue('F1', Topic Child);--------->Osticket(child)

$objPHPExcel->getActiveSheet()->SetCellValue('G1', Status);--->Osticket(opened/closed/overdue/assigned)

$objPHPExcel->getActiveSheet()->SetCellValue('H1', Agent);--------->LDAP(who is responsible for ticket)

Thank you for attention and help.Best Regards

Orik3ll0

orik3ll0 · Sep 14, 2016

Here is picture of "dummy" Excel Example.

Снимок.JPG

orik3ll0 · Sep 14, 2016

+Is it possible to call rows not by ID but by variables?

>>Not clear, what you mean?!

+I mean instead of this ('A1', $data)---> ('A1', $data)

+ I can not find variables for columns.

>>Please post the php code ...

+for example: $helptopic = htmlentities($_POST)----> here i know that name of variable is helptopic and now i can write $objPHPExcel->getActiveSheet()->SetCellValue('A2', $data['helptopic'); and will see row.

+ I need to automatic rowspan and add addition columns for exported file. >>Are the addition columns hardcoded // dummy columns ... no exists in the mysql Database?!

+All these columns are exists in Database

+ With Id is not possible, i tried many ways. >>Example code ...

+I need to set cell value dynamically. Couldn`t do them by ID. It should be smth like code bellow, but it is not working for me

sql = "SELECT * FROM my_table";

$result = mysql_query($sql);

$row = 1;

while($row_data = mysql_fetch_assoc($result)) {

$col = 0;

foreach($row_data as $key=>$value) {

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);

$col++;

}

$row++;

}

citrixraptor · Sep 14, 2016

Hello orik3ll0,

thx for detail info's ... OK

A1', Date);------------->Osticket(created date)OK

B1', Department);---->LDAPWhy that! - > Put the Department info in osticket, (info from LDAP)Have you the plugin: Authentication :: LDAP and Active Directory ?

C1', Phone); ----------> LDAP -> Phone Why that! - > Put the Phone info in osticket, (info from LDAP)D1', From);------------->LDAP(Who created ticket)Why that! - > the info coming from osticket!

E1', Topic Parent);------->Osticket(parent)OK

F1', Topic Child);--------->Osticket(child)OK

G1', Status);--->Osticket(opened/closed/overdue/assigned)Ok

H1', Agent);--------->LDAP(who is responsible for ticket)Why that! - > the info coming from osticket!

The Problem is the connection between osticket and LDAP ... I think the ID do not match !Or am I wrong

Best Regardscitrixraptor

orik3ll0 · Sep 14, 2016

Dear CitrixraptorOk let`s take for example Phone which we can see from picture bellow. It is code part of it :<tr> <th><?php echo __('Phone'); ?>:</th> <td> <span id="user-<?php echo $ticket->getOwnerId(); ?>-phone"><?php echo $ticket->getPhoneNumber(); ?></span> </td> </tr> Should I write it like code bellow?$objPHPExcel->getActiveSheet()->SetCellValue('B2', $ticket->getPhoneNumber());P.S. when i press export button my page is not opening and not dowloading, gives me error of not found page. Also i will remind you that i need to export all tickets which we have in database for let`s say 1 month with all infromation in it.Thank your for your attention and help.Best RegardsOrik3ll0

citrixraptor · Sep 15, 2016

Hello Orik3ll0,Ok .. now i understand it, here the solution:save it as report.php:<?phpinclude 'PHPExcel.php';include 'PHPExcel/Writer/Excel2007.php';$objPHPExcel = new PHPExcel();$conn = mysql_connect('localhost', 'root', 'password');if (!$conn) { die('Could not connect: ' . mysqli_error());}$db_selected = mysql_select_db('ost_ticket', $conn);if (!$db_selected) { die ('Can\'t use foo : ' . mysqli_error());}$objPHPExcel->getProperties()->setCreator("citrixraptor") ->setLastModifiedBy("citrixraptor") ->setTitle("Ticket Report") ->setSubject("Ticket Report") ->setDescription("Test Ticket Report") ->setKeywords("Report phpexcel") ->setCategory("Only a Test");$result = mysql_query("SELECT ost_ticket.ticket_id as 'Ticket ID', ost_user.name as 'User Name', ost_ticket.created as 'Created' FROM ost_user, ost_ticket where ost_user.id = ost_ticket.user_id");$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Ticket ID');$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'User Name');$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Created');$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment:);$colnum=1;while ($row = mysql_fetch_assoc($result)){ $colnum++; $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum", $row); $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum", $row); $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum", $row);}$objWriter = PHPExcel_IOFactory:($objPHPExcel, 'Excel2007');header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename=test_report.xlsx');header('Cache-Control: max-age=0');$objWriter->save('php://output');?>Best Regardscitrixraptor

orik3ll0 · Sep 29, 2016

Dear CitrixraptorThank you for your reply and help.Your code helped me so much. I have some question for this.1) You said that i should save it as report.php and I did it. Q) Should I create new button for it? I paste this code in ajax.report.php and it worked there nice.2) I need to get Excel file like in picture bellow. I added Phone, Topic, Status, Oraganization, Ticket Thread in sql query and it gave me excel file with 10k+ rows, it just repeat and rows for each column each value. Q) Should i use in sql query "JOIN"? here is modify code:$result = mysql_query("SELECT ost_ticket.ticket_id as 'Ticket ID', ost_user.name as 'User Name', ost_ticket.created as 'Created' ost_help_topic.topic as 'Topic' FROM ost_user, ost_ticket, ost_help_topic where ost_user.id = ost_ticket.user_id");$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Ticket ID');$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'User Name');$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Created');$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Topic');$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment:);$colnum=1;while ($row = mysql_fetch_assoc($result)){ $colnum++; $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum", $row); $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum", $row); $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum", $row); $objPHPExcel->getActiveSheet()->SetCellValue('D'."$colnum", $row);}I know that thre is no connection with users id, how connect them without JOIN?I could write it like :Select * FROM ost_user, ost_ticket, ost_help_topic where ost_user.name=$name and ost_ticket.created=$date and ost_help_topic.topic=$topic and ost_user.id = ost_ticket.user_id

orik3ll0 · Sep 29, 2016

But I do not know variables for ost_user.name and etc.P.S. $name, $date, $topic it just example

citrixraptor · Sep 29, 2016

Hello orik3ll0,

>> Q) Should I create new button for it? I paste this code in ajax.report.php and it worked there nice.Yes, create new button to open the report.php ...

Here my query:

SELECT ost_ticket.ticket_id as 'Ticket ID',ost_user.name as 'Username',ost_ticket.created as 'Created',ost_help_topic.topic as 'Topic'FROM ost_user, ost_ticket, ost_help_topicwhere ost_user.id = ost_ticket.user_idand ost_ticket.topic_id = ost_help_topic.topic_idORDER BY `ost_ticket`.`ticket_id` ASC

result see 01.jpg

Best Regardscitrixraptor

Divide Colums in exported CSV file

Oorik3ll0

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Oorik3ll0

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Ccitrixraptor

Oorik3ll0

Oorik3ll0

Ccitrixraptor