Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Divide Colums in exported CSV file

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.

Comments

  • Hello orik3ll0,

    edit only:
    /upload/include/ajax.reports.php

    from:

    function downloadTabularData() {
    $data = $this->getData();
    $csv = '"' . implode('","',$data['columns']) . '"';
    foreach ($data['data'] as $row)
    $csv .= "\n" . '"' . implode('","', $row) . '"';
    Http::download(
    sprintf('%s-report.csv', $this->get('group', __('Department'))),
    'text/csv', $csv);
    }


    to:

    // my mod
    function downloadTabularData() {
    $data = $this->getData();
    $csv = '"' . implode('";"',$data['columns']) . '"';
    foreach ($data['data'] as $row)
    $csv .= "\n" . '"' . implode('";"', $row) . '"';
    Http::download(
    sprintf('%s-report.csv', $this->get('group', __('Department'))),
    'text/csv', $csv);
    }
    // my mod
  • 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:Z1")->applyFromArray(
    array(
    'borders' => array(
    'allborders' => array(
    'style' => PHPExcel_Style_Border::BORDER_THIN,
    'color' => array('rgb' => 'DDDDDD')
    )
    )
    )
    );
  • Hello orik3ll0,

    perhaps a help for you:


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


    $styleArray = array(
    'borders' => array(
    'outline' => array(
    'style' => PHPExcel_Style_Border::BORDER_THICK,
    'color' => array('argb' => 'DDDDDDDD'),
    ),
    ),
    );
    $objWorksheet->getStyle('A1:Z1')->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:G8.

    $styleArray = array(
    'borders' => array(
    'outline' => array(
    'style' => PHPExcel_Style_Border::BORDER_THICK,
    'color' => array('argb' => 'FFFF0000'),
    ),
    ),
    );
    $objWorksheet->getStyle('B2:G8')->applyFromArray($styleArray);

  • 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.
  • Hello orik3ll0,

    what exactly is the problem?

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

    Best Regards
    citrixraptor
  • 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 Regards
    Orik3ll0.
  • 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:www-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['columns'][0]);
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', $data['columns'][1]);
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', $data['columns'][2]);
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', $data['columns'][3]);
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', $data['columns'][4]);
    $objPHPExcel->getActiveSheet()->SetCellValue('F1', $data['columns'][5]);
    $objPHPExcel->getActiveSheet()->SetCellValue('G1', $data['columns'][6]);
    $objPHPExcel->getActiveSheet()->SetCellValue('H1', $data['columns'][7]);


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

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

    $objPHPExcel->getActiveSheet()->SetCellValue('A2', $data['data'][0][0]);
    $objPHPExcel->getActiveSheet()->SetCellValue('B2', $data['data'][0][1]);
    $objPHPExcel->getActiveSheet()->SetCellValue('C2', $data['data'][0][2]);
    $objPHPExcel->getActiveSheet()->SetCellValue('D2', $data['data'][0][3]);
    $objPHPExcel->getActiveSheet()->SetCellValue('E2', $data['data'][0][4]);
    $objPHPExcel->getActiveSheet()->SetCellValue('F2', $data['data'][0][5]);
    $objPHPExcel->getActiveSheet()->SetCellValue('G2', $data['data'][0][6]);
    $objPHPExcel->getActiveSheet()->SetCellValue('H2', $data['data'][0][7]);

    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::createWriter($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['columns']); and $data['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
  • image

    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/Снимок.JPG?dl=0
    Снимок.JPG
    1554 x 152 - 81K
  • 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 Regards
    citrixraptor

  • edited September 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

  • Here is picture of "dummy" Excel Example.
    Снимок.JPG
    1554 x 152 - 81K
  •  +Is it possible to call rows not by ID but by variables?

    >>Not clear, what you mean?!

    +I mean instead of this ('A1', $data['columns'][0])---> ('A1', $data['column_variable'])

    + I can not find variables for columns.

    >>Please post the php code ... 

    +for example: $helptopic = htmlentities($_POST["helptopic"])----> 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++;

    }

  • Hello orik3ll0,

    thx for detail info's ... OK

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

    B1', Department);---->LDAP
    Why 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 Regards
    citrixraptor

  • edited September 2016
    Dear Citrixraptor

    Ok 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 Regards
    Orik3ll0
    2.JPG
    917 x 226 - 33K
  • edited September 2016
    Hello Orik3ll0,

    Ok .. now i understand it, here the solution:

    save it as report.php:

    <?php
    include '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:C1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $colnum=1;
    while ($row = mysql_fetch_assoc($result))
    {
        $colnum++;
        $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum", $row["Ticket ID"]);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum", $row["User Name"]);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum", $row["Created"]);
    }

    $objWriter = PHPExcel_IOFactory::createWriter($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 Regards
    citrixraptor
    test_result.jpg
    337 x 145 - 14K
  • edited September 2016
    Dear Citrixraptor

    Thank 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:D1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $colnum=1;
    while ($row = mysql_fetch_assoc($result))
    {
        $colnum++;
        $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum", $row["Ticket ID"]);
        $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum", $row["User Name"]);
        $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum", $row["Created"]);
        $objPHPExcel->getActiveSheet()->SetCellValue('D'."$colnum", $row["Topic"]);
    }
    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


    2525.JPG
    1613 x 165 - 93K
  • But I do not know variables for ost_user.name and etc.

    P.S. $name, $date, $topic it just example
  • 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_topic
    where 
    ost_user.id = ost_ticket.user_id
    and 
    ost_ticket.topic_id = ost_help_topic.topic_id
    ORDER BY `ost_ticket`.`ticket_id`  ASC

    result see 01.jpg


    Best Regards
    citrixraptor

    01.jpg
    744 x 513 - 168K
  • Dear Citrixraptor

    Thank you very much for reply and help. It is working fine in ajax.report.php. I found button in ajax file and I do not know Ajax, thats why I will try to add button in php, somewhere in dashboard.php.

    Best regards
    Orik3ll0
  • Dear Citrixraptor.

    I added button and just noticed that unicode is not working. I added charset=UTF-8 in header and still I have unicode problem. How can I solve this unicode problem?

    Best Regards
    Orik3ll0
    2527.jpg
    541 x 150 - 35K
  • "ЛОЖЬ" means lie. I do not know why it write it... i do not have it in my table
  • Hello orik3ll0,


    >> "ЛОЖЬ" means lie. I do not know why it write it... i do not have it in my table
    Ok ... ЛОЖЬ is the result of non utf8 coding ! ... see 01.jpg !!!

    Add the yellow highlighting code in your reporting php file:


    $objPHPExcel = new PHPExcel();

    $conn = mysql_connect('localhost', 'root', 'password');

    mysql_set_charset('utf8',$conn);

    if (!$conn) {
        die('Could not connect: ' . mysqli_error());
    }


    ... now the utf8 coding is ok ... for example:   äüöß and your ЛОЖЬ  ... i use both as example username;-)
    see 02.jpg and 03.jpg


    My example user page in the osticket database.
    see 03.jpg 


    Hope, this solve your UTF8 problem

    Best Regards
    citrixraptor
    01.jpg
    299 x 139 - 15K
    02.jpg
    314 x 157 - 16K
    03.jpg
    776 x 251 - 44K
  • Dear Citrixraptor

    Thank you very much for help and reply, it helped me and works perfect!) Please do not close topic, because maybe I will have 1 more question.

    Best Regards
    Orik3ll0
  • Hello orik3ll0,

    >> Thank you very much for help and reply, it helped me and works perfect!)
    You are welcome ... i think a nice thread also for other people in the forum :-)

    >> Please do not close topic, because maybe I will have 1 more question.
    Mh ... Ok


    Best Regards
    citrixraptor
  • Hi citrixraptor,
    I try your trick with semi coma in /upload/include/ajax.reports.php (better open csv in excel), but it is not work. 
    osTicket 1.9.14. (I replace coma with semi coma, but export file with tickets steel contains coma).

    Steve
  • Hi,
    bbetter export to excel I solved add str_replace function to replate cona with semi coma for output.
    File: /include/class.export.php

    Original:

    if ($stuff)
        Http::download($filename, "text/$how", $stuff);

    New:

    $stuff = str_replace(',', ';', $stuff); // replace
    if ($stuff)
        Http::download($filename, "text/$how", $stuff);

Sign In or Register to comment.