#302 Formating report output

open
nobody
None
1
2012-11-02
2012-10-31
knjigor
No

Hi, is where can i sett width of first column in report page results, or type of view? Problem is that first column is about 18px width and i can't see it's content, can it be setup to fixed width or to flex (depending on longest content of cell).

Second, is there any chance to create results as separate page and without scrolling for rest of table, or to define what fields can be shown, or maybe a print support only for results table?

Discussion

  • knjigor

    knjigor - 2012-10-31

    Here is picture

     
    Last edit: knjigor 2012-10-31
  • Campbell Morrison

    You can change the width of the left hand column by editing line 116 of js/report.js.php. In the line

                                                   "iLeftWidth": leftWidth,
    

    replace leftwidth by eg 400 for 400px. (This solution will work in the short term. In the long term it probably needs something better).

    You should be able to define what fields are shown by using the Show/Hide Columns button above the table on the right. Your selectrions will be remembered.

    You can also change the number of results shown by using the "Show" select button at top left.

    Campbell

     
  • knjigor

    knjigor - 2012-10-31

    Thanx for express help :), i have changed that and it is greate.

    About visible fields, the trouble is that i'm setting booking system for my Faculty and it should be used by students (they would insert they study group and year of study to get timetable), for creating reports for teachers depending on they name so they get they timetable, and administrators. Also there is some type of print report (paper that should be putted on dors).

    Any way, i can explain to them that they must use show/hide button.

    :)

     
  • knjigor

    knjigor - 2012-10-31

    One more question, where are defined widths of right columns, I have realised that if I entries of some columns aren't long and column width is.

     
    Last edit: knjigor 2012-11-01
  • knjigor

    knjigor - 2012-11-02

    Can someone help me with this project, I'm thinking about creating msword or msxecell output format for reports?

     
  • Campbell Morrison

    Is the CSV output not sufficient?

    If you really need Excel output it's quite easy to do. I have already extended MRBS in my own implementation to output reports in .xls or .xlsx format. I used PHPExcel (http://phpexcel.codeplex.com/) which worked very well.

    Campbell

     
  • knjigor

    knjigor - 2012-11-02

    Wel It should be enough, but when I open csv with excell i get what is in the picture, all data is in one (1st) column, no matther what separator I define, allso, it is possible to export it only with utf-16, and i have used utf-8 and don't wont to redefine all from start.

     
  • Campbell Morrison

    Have you tried playing around with the two config variables?

    $csv_charset = 'utf-16';  // try 'utf-8'
    $csv_bom = TRUE;  // try FALSE
    

    Campbell

     
  • knjigor

    knjigor - 2012-11-02

    yes, but then i don't see characters, and it is still formated in one column.

     
  • Campbell Morrison

    If it doesn't contain sensitive data could you post a sample .csv file please?

    Thanks,

    Campbell

     
  • knjigor

    knjigor - 2012-11-02

    Offcourse, in package are diferent files exported with different combinations of $csv_charset and $csv_bom

     
  • knjigor

    knjigor - 2012-11-02

    can you still give me a hint about creating direct excell output?

     
    • Campbell Morrison

      Yes, using PHPExcel I added the following code in report.php

      to open_report()

        if (($output_format == OUTPUT_XLS) || ($output_format == OUTPUT_XLSX))
        {
          $user = getUserName();
          $title = ($output == REPORT) ? get_vocab("report_title") : get_vocab("summary_title");
          $subject = ($output == REPORT) ? get_vocab("report_subject") : get_vocab("summary_subject");
          $description = ($output == REPORT) ? get_vocab("report_description") : get_vocab("summary_description");
          $objPHPExcel->getProperties()->setCreator($user);
          $objPHPExcel->getProperties()->setLastModifiedBy($user);
          $objPHPExcel->getProperties()->setTitle($title);
          $objPHPExcel->getProperties()->setSubject($subject);
          $objPHPExcel->getProperties()->setDescription($description);
          $objPHPExcel->setActiveSheetIndex(0);
        }
      

      the following to close_report()

        elseif (($output_format == OUTPUT_XLS) || ($output_format == OUTPUT_XLSX))
        {
          // Apply some formatting
          $objPHPExcel->getActiveSheet()->freezePaneByColumnAndRow(1,2);
          // Write the spreadsheet out
          if ($output_format == OUTPUT_XLS)
          {
            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
          }
          else
          {
            $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
          }
          $objWriter->save('php://output');
        }
      

      the following to the main code

        switch ($output_format)
        {
          case OUTPUT_CSV:
            $filename .= '.csv';
            $content_type = "text/csv; charset=" . get_csv_charset();
            break;
          case OUTPUT_XLS:  // new code
            require_once "PHPExcel/PHPExcel.php";
            require_once "PHPExcel/PHPExcel/Writer/Excel5.php";
            $objPHPExcel = new PHPExcel();
            $filename .= '.xls';
            $content_type = "application/vnd.ms-excel";
            break;
          case OUTPUT_XLSX:  // new code
            require_once "PHPExcel/PHPExcel.php";
            require_once "PHPExcel/PHPExcel/Writer/Excel2007.php";
            $objPHPExcel = new PHPExcel();
            $filename .= '.xlsx';
            $content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            break;
          default:
            require_once "functions_ical.inc";
            $filename .= '.ics';
            $content_type = "application/ics; charset=" . get_charset() . "; name=\"$filename\"";
            break;
        }
        header("Content-Type: $content_type");
        header("Content-Disposition: attachment; filename=\"$filename\"");
      

      and add two extra buttons

            case 'output_format':
              echo "<div id=\"div_format\">\n";
              $buttons = array(OUTPUT_HTML => "html",
                               OUTPUT_CSV  => "csv",
                               OUTPUT_XLS  => "xls",
                               OUTPUT_XLSX => "xlsx"); 
      

      and the following to output_row()

        global $objPHPExcel;
        static $excel_row = 1;  // Row numbering starts at 1; column numbering at 0!
      

      and

      ~~~~~~
      elseif (($output_format == OUTPUT_XLS) || ($output_format == OUTPUT_XLSX))
      {
      for ($i=0; $i < count($values); $i++)
      {
      $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $excel_row, $values[$i]);
      }
      $excel_row++;
      return;
      }
      ~~~~~~~

      I've probably forgotten a few bits like global declarations, but that's about it. (It's difficult to do an exact diff because I've got other changes in there).

      Campbell

       
  • Campbell Morrison

    I can open report.csv OK in Excel (I had to tell Excel to use delimted fields and a comma separator). I also get the Serbian Latin characters OK.

    Campbell

     
  • knjigor

    knjigor - 2012-11-02

    yes, i played around with excel it is working ok. Still i'm thinking about creating option for printing to excel file directly.

    Can you help me with it?

     
  • knjigor

    knjigor - 2012-11-02

    Thanx for help, haven't tried jet this, ill try it this weekend.

     
    • sayadansystem

      sayadansystem - 2016-06-23

      i am sorry to reply in this old post, but i have similar problem like u, have you solve it?

       
      Last edit: sayadansystem 2016-06-23
  • knjigor

    knjigor - 2012-11-02

    I have tried this, but i'm stuck with following code

    and the following to output_row()

      global $objPHPExcel;
      static $excel_row = 1;  // Row numbering starts at 1; column numbering at 0!
    

    and

        elseif (($output_format == OUTPUT_XLS) || ($output_format == OUTPUT_XLSX))
        {
          for ($i=0; $i < count($values); $i++)
          {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $excel_row, $values[$i]);
          }
          $excel_row++;
          return;
        }
    

    Cant figure out where to put it :( and also what from package PHPExcel_1.7.8-with_documentation-msoffice_format.zip and where should i put it in web folder?

    Sorry for this questions, I know that you have helped me allot with previous post, but...

    Shame for me

     
    Last edit: knjigor 2012-11-02
    • Campbell Morrison

      output_row() should look like this:

      // Output a table row.
      function output_row(&$values, $output_format, $body_row = TRUE)
      {
        global $json_data, $ajax, $csv_col_sep, $csv_row_sep;
        global $objPHPExcel;
        static $excel_row = 1;  // Row numbering starts at 1; column numbering at 0!
      
        if ($ajax && $body_row)
        {
          $json_data['aaData'][] = $values;
        }
        else
        {
          if ($output_format == OUTPUT_CSV)
          {
            $line = '"';
            $line .= implode("\"$csv_col_sep\"", $values);
            $line .= '"' . $csv_row_sep;
            $line = csv_conv($line);
          }
          elseif (($output_format == OUTPUT_XLS) || ($output_format == OUTPUT_XLSX))
          {
            for ($i=0; $i < count($values); $i++)
            {
              $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $excel_row, $values[$i]);
            }
            $excel_row++;
            return;
          }
          elseif ($output_format == OUTPUT_HTML)
          {
            $line = '';
            $cell_tag = ($body_row) ? 'td' : 'th';
            $line .= "<tr>\n<$cell_tag>";
            $line .= implode("</$cell_tag>\n<$cell_tag>", $values);
            $line .= "</$cell_tag>\n</tr>\n";
          }
          echo $line;
        }
      }
      

      Doesn't matter which version of documentation you have with 1.7.8 - the code will all be the same. The code I've given you assumes the library will in in PHPExcel in the web folder. In other words it will look like

      web/PHPExcel/PHPExcel/CachedObjectStorage

      Campbell

       


Anonymous

Cancel  Add attachments