#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

  • 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
    Attachments
  • knjigor
    knjigor
    2012-11-02

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

     
  • 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.

     
    Attachments
  • 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.

     
  • 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

     
    Attachments
  • knjigor
    knjigor
    2012-11-02

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

     
    • 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

       
  • 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.

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