Feature (approved)

export tables as XML or csv-list

Summary

approved
Feb 16, 2006
2 hours ... 2 days
100%
Feb 16, 2006 / pixtur
Jan 5, 2009 / phsouzacruz
burger
 

Attached files

No files uploaded
 
In some cases it would be quiet useful to export a complete list to another programm like Excell. Adding a csv-style to the BlockList-class should be pretty straight forward.

29 Comments

pixtur:With CSS?

11 years ago (4. update 11 years ago)

Do your really think this can be done with CSS only? This would be quiet cool!

tom

burger:Sorry ...

11 years ago (5. update 11 years ago)

Sorry I have to disappoint you :-(.

I didn't solve it at the class ListBlock.

But I implemented:
  1. a function exportToCSV($args)

from std/export.inc.php

function exportToCSV($args)
{
	header('Content-Type: text/csv; charset=iso-8859-15');
	header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
	header('Content-Disposition: attachment; filename=download.xls');
	header('Pragma: no-cache');
	
	$export = "";
	$count = 0;
	$num_col = 0;
	
	## get number of cols ##
	foreach($args as $key => $value)
	{	
		foreach($args[$key] as $k => $value)
		{
			$num_col++;
		}
		break;
	}
	
	## build export-string ##
	for($i = 0; $i < $num_col; $i++)
	{
		foreach($args as $key => $value)
		{	
			$str = $args[$key][$i];
			$str = iconv("utf-8", "iso-8859-15", $str); /* necessary for e.g. German characters like ä, ö, ü and ß */
			$export .= "" . $str . "t";
		}
		$export .= "n";
	}
	
	echo $export;
}

and
  1. functions for each "page" like companiesExport() (at pages/company.inc.php), personsExport() (at pages/person.inc.php) etc.

from pages/effort.inc.php

function effortsExport()
{
	global $PH;
	global $auth;
   	
	### get effort ####
    $ids= getPassedIds('effort','efforts_*');
	
	if(!$ids) {
        $PH->abortWarning(__("Select one or more efforts"));
        return;
	}
	
	$valid = false;
	
	if(isset($args)) {
		unset($args);
	}
		
	$args['project'][0] = __('Project');
	$args['task'][0] = __('Task');
	$args['person'][0] = __('Person');
	$args['effort'][0] = __('Effort');
	$args['time_start'][0] = __('Start');
	$args['time_end'][0] = __('End');
	$args['duration'][0] = __('len');
	
	$count = 1;
	foreach($ids as $id) {
	 	if($e= Effort::getVisibleById($id)) {
			## array with all efforts ##
			$e_array[] = $e;
			
			## check project of first effort ##
            if(count($e_array) == 1) {
                if(!$project = Project::getVisibleById($e->project)) {
                    $PH->abortWarning('could not get project');
                }
				else {
					$valid = true;
				}
            }
			
			if($valid) {
				$args['project'][$count] = $project->name;
			}
			else {
				$args['project'][$count] = '';
			}
			
			## get tasks ##
			if(!$task = Task::getVisibleById($e->task)) {
				$args['task'][$count] = '';
			}
			else {
				$args['task'][$count] = $task->name;
			}
						
			## get persons ##
			if(!$person = Person::getById($e->person)) {
				$args['person'][$count] = '';
			}
			else {
				$args['person'][$count] = $person->nickname;
			}
				
			## get efforts ##
			$args['effort'][$count] = $e->name;
			
			## get time_start ##
			$args['time_start'][$count] = $e->time_start;
			
			## get time_end ##
			$args['time_end'][$count] = $e->time_end;
			
			## get duration ##
			$duration = round((strToGMTime($e->time_end) - strToGMTime($e->time_start))/60/60,1);
			$duration = str_replace('.',',',$duration); /* what about English syntax??? */
			$args['duration'][$count] = $duration;
			
			$count++;
		}
		else {
			$PH->abortWarning(__("You do not have enough rights"), ERROR_RIGHTS);
		}
	}
	
	exportToCSV($args);
}

Do you think that you can live with this kind of export?

Furthermore I would like to start a discussion about how to handle differences in German and English syntax with regard to numbers (e.g. 1,*3 (German), but 1.3 (English))?
The problem is if I have a German Excel I cannot write 1.3.
The other way round I can write 1,3, but that's maybe a little bit confusing for people from Great Britain or the USA?

pixtur:hmmm...

11 years ago (19. update 11 years ago)

Although this solution would work it is not very nice:
  1. It causes too much additional code that has to be updated.
  2. It's works indepent to the rendering of lists (ignores sorting, filtering, etc.)
  3. There is no preview - the result might look different
I would prefer this approach:
  1. On pages featuring one list only (e.g. projViewTasks, companyList, projectList etc.) add an "Export as CSV" link below the table. This link would use the same url as the page with the additional parameter "format=csv"
  2. In renderPage(), renderList(), renderBlock() etc. evaluate this format
  3. Overwrite ListBlock::render_list() to check the format parameter and probably ignore the html rendering of columns and render public fields of each object in the list.
This would have following benefits:
  1. You can use the html-interface to customize the exported list
  2. This approach is open for other formats like RSS. XML etc.
  3. It does not require additional code for each object type / list:
    • There only has to be a generic ListBlock::renderCSV() which is called by ListBlock::renderList().
    • For all lists that overwrite ListBlock::renderList() (there are some) this function has to be called if appropriate. Maybe we should turn Block::renderList() into a wrapper function and place its old content into a new function `Block::renderListHtml()´
Trac illustrates a similar behavior very well.

burger:Right ...

11 years ago (4. update 11 years ago)

Yes, you are absolutely right.

My solution isn't very nice. Therfore I will change it like you recommended :-).

P.S.: What 'Trac' do you mean?

pixtur:I put a link to the trac-folder ;-)

11 years ago (4. update 11 years ago)


burger:changed code

11 years ago (3. update 11 years ago)

I changed the code for the csv-export as stated below
  • example 1

from pages/company.inc.php

function companyList() {
    global $PH;
    global $auth;

    ### create from handle ###
    $PH->defineFromHandle();
	
	$format = get('format');
	
	if($format == 'csv'){
		global $csv_args;
		global $csv_count;
		$csv_count = 0;
		
		$order_str= get("sort_".$PH->cur_page->id."_".$list->id);
		$order_str= str_replace(",",", ", $order_str);
		$companies=Company::getAll(array('order_str'=>$order_str));
		
		$list= new ListBlock_companies();
		$list->format = $format;
		$list->render_list(&$companies);
		
		exportToCSV($csv_args);
	}
	else{
		### set up page and write header ####
		{
			$page= new Page();
			$page->cur_tab='companies';
			$page->title=__("Companies");
			if(!($auth->cur_user->user_rights & RIGHT_VIEWALL)) {
				$page->title_minor=sprintf(__("related projects of %s"), $page->title_minor=$auth->cur_user->name);
			}
			else {
				$page->title_minor=__("admin view");
			}
			$page->type=__("List");
			
			 ### page functions ###
			$page->add_function(new PageFunction(array(
				'target'    =>'companyNew',
				'params'    =>array(),
				'icon'      =>'new',
				'tooltip'   =>__('New Company'),
			)));
	
			/*$page->crumbs[]= new NaviCrumb(array(
				'target_id'     => 'companyList',
			));*/
			$page->options=build_companyList_options();
	
			### render title ###
			echo(new PageHeader);
		}
		echo (new PageContentOpen);
	
		#--- list projects --------------------------------------------------------
		{
	
	
			$list= new ListBlock_companies();
	
			### may user create companies? ###
			if($auth->cur_user->user_rights & RIGHT_COMPANY_CREATE) {
				$list->no_items_html=$PH->getLink('companyNew','',array('person'=>$auth->cur_user->id));
			}
			else {
				$list->no_items_html=__("no companies");
			}
	
	
			$order_str= get("sort_".$PH->cur_page->id."_".$list->id);
	
			$order_str= str_replace(",",", ", $order_str);
			$companies=Company::getAll(array('order_str'=>$order_str));
	
			$list->title= $page->title;
			$list->render_list(&$companies);
			
			## Link to start cvs export
			echo "<div class=description>" . $PH->getLink('companyList', __('Export as CSV'),array('format'=>'csv')) . "</div>";
		}
	
		echo(new PageContentClose);
		echo(new PageHtmlEnd);
	}

}
  • example 2

from render/render_list.inc.php

public function render_list(&$list=NULL)
    {
   		if($this->format == 'csv'){
			$this->renderListCsv($list);
		}
		else{
			$this->renderListHtml($list);
		}
    }
	
	/*
	*format=csv*
	*/
	function renderListCsv(&$list=NULL)
	{
		global $csv_args;
		global $csv_count;
		
		$count = count($this->columns);
		$csv_args[$csv_count++] = $count;
		if($list || !$this->no_items_html) {
			$this->render_thead();
			 if($list) {
			 	foreach($list as $e) {
            		$this->render_trow($e);
        		}
			 }
		}
	}

...
  • example 3

from render/render_list_column_special.inc.php

class ListBlockColDate extends ListBlockCol
{
    public $key= 'modified';

    function __construct($args=NULL) {
        parent::__construct($args);
        if(!$this->name) {
            $this->name= __('Modified');
        }
    }

	function render_tr(&$obj, $style="", $format="html") {
	    global $auth;

		if(!isset($obj) || !is_object($obj)) {
			trigger_error("ListBlockColDate->render_tr() called without valid object", E_USER_WARNING);
   			return;
		}
		
		global $csv_args;
		global $csv_count;
		
		$key= $this->key;
		
		if($format == 'csv'){
			$csv_args[$csv_count++] = $obj->$key;
		}
		else{
			$value_str=renderDateHtml($obj->$key);
			print "<td class='nowrap'>$value_str</td>";
		}        
	}
}

Is it okay like this?

pixtur:cool...

11 years ago (3. update 11 years ago)

I almost as I thought ;-)
I wouldn't have clustered the functions, but actually I just didn't know this was possible.

Some other suggestions:
  • Why not place the rendering of cvs and xml format into a separate render_tr() function -> ignoring all columns. I guess that when exporting csv you could render all available fields, ignoring the visibilty and order of columns ...
function render_trow($obj, $style='', $format='html')
	{
             if($format == 'cvs') {
                 if(isset($obj->fields)) {
                    foreach($obj->fields as $f) {
                       if($f->export_cvs) {
                          $fname = $f->name;
                          echo $obj->$fname . ", ";                          
                       }
                    }
                 }
             }
             else {
              ### do the usual stuff with columns...
		$this->row_count++;
		$oddeven=($this->row_count %2) ? "odd" : "even";

        if(isset($obj->pub_level)) {
            $level=$obj->pub_level;
            global $g_pub_level_names;
            $style.=" pub_".$g_pub_level_names[$level];
        }

So we could just define which fields may be exported as csv for each DbProjectItem type, an than this csv- export works for all list without having to adjust all column rendering...

We could use the field types to render themselfs in the appropriate format like:

[code]
 $values=array();
 foreach($obj->fields as $fname=>$f) {
    $values[]= $f->renderCvsValue($obj);
 }

in List::render_thead() we could then use... $values[]= $f->renderCvsId($obj); to render the correct field id in the title line.

Sorry for being nitpicky...

burger:No problem ... :-)

11 years ago (2. update 11 years ago)

Okay, I will change it.

burger:Some questions ...

11 years ago (2. update 11 years ago)

What do you mean exactely with
  1. "render all available fields, ignoring the visibility and order of columns ..."
  2. "to render correct field id in the title line ..."
?

pixtur:Reply to Some questions ...

11 years ago (2. update 11 years ago)

On fieldsπ

  • Currently each DpProjectItem is made of Fields.
  • Each Field is an instance of class Field like FieldText, FieldInt etc
  • Each Field has a unique Field Name like "name", "brirthday" etc.
  • When exporting to csv you would...
    1. first render a line with all Field Names (like $task->fields[0]->name)
    2. render lines with all Field Values (like $tasks[0]->$fieldname)

Exporting moreπ

When rendering lines for screen display, number of columns is extremely limited, because to user perceptance and limited screen real estate. However, an exported csv list does not need to obey those limitations. We just can export anything that's available.


burger:Hmm...

11 years ago (2. update 11 years ago)

Okay, I think I understand in general what you mean, but e.g.:
How can I get the project name if I have a FieldInt with the project id 1256?

Maybe I haven't understood the whole Field concept?!

pixtur:Accessing project names

11 years ago (4. update 11 years ago)

That's a very good point. I didn't thought about this. We could derive more FieldTypes for such fields...
  • FieldPerson
  • FieldProject
  • etc.
...but this would still not list things like "Assigned Persons" ... I am not sure, how to solve this.

Another solution that would not require to modify all Column-Classes might be deriving ListBlock::render_trow() and ListBlock::render_thead() for those list types. The generic approach in the original ListBlock-class would only write the Project IDs. The derived versions would add special fields like "assigned persons" or "booked efforts".

This morning I had another idea: Should the format-parameter not be a member of the Page class? Sooner or later I need something like format=PDA which would require completely reduced rendering without javascript etc.

Having a framework that would be open for such things would help.

I also may the very early mistake to "print" output at functions like "render_trow()". Either this function should be called "print_trow()" or return a string buffer instead of print it. Returning buffers or arrays might help when assembling more complex formats like XML with global variables.

What do you think about this?

burger:Comment

11 years ago (2. update 11 years ago)

I think I should talk with Thomas (binder) about this topic again, but he is not at the office today.
So I will inform you next week about our results of discussion.

But I think this is an important topic we should think about very seriously to include every export possibility (like csv, xml, pda, etc.).

burger:Comment

11 years ago (2. update 11 years ago)

Thomas thinks we should realize it as you suggested.

Therefore I will start programming immediately :-).

burger:Question

11 years ago (7. update 11 years ago)

Can you give me an more detailed explanation how I should realize the export?!

I have some problems to understand when and where I should call the derived versions of ListBlock::render_trow etc.

And how can I differentiate between a proj-list or a person-list etc.? Because I have to know when I have to add special fields like "assigned person", "booked effort", etc.


pixtur:maybe we could have a sky chat for this?

11 years ago (2. update 11 years ago)


burger:Reply to maybe we could have a sky chat for this?

11 years ago (2. update 11 years ago)

Okay.

Maybe tomorrow afternoon?

But I don't have skype installed at my PC, but Thomas (binder) has. So maybe I can use his PC?!


pixtur:well...

11 years ago (2. update 11 years ago)

Tomorrow afternoon should work. I have time til 17:00 MESZ

burger:added all changes ...

11 years ago (2. update 11 years ago)

Hi Tom,

I added all the discussed changes.
At the moment it is only possible to export all fields of the type 'FieldString' as csv.

It would be nice if you would check my changes.

Thanks :-).


burger:Question

11 years ago (2. update 11 years ago)

What shall we do with ListBlock_tasks?
  1. Problem: It has its own print_automatic() and render_list() functions.
  2. Problem: Function projViewTasks() (at pages/proj.inc.php) prints out some Html-Code.

pixtur:I will have a look at your changes...

11 years ago (2. update 11 years ago)

on Sunday I hopefully will have some time.
The other functions needs to be cleaned up. I cannot check out the current version here, but I will have a look at Sunday.

burger:Can you do me a favour?

11 years ago (3. update 11 years ago)

If there are presets then it isn't possible to insert

from ListBlock_tasks

parent::__construct($this->id, $this->bg_style, $this->title);
$this->format=$this->page->format;

If I would use it then the $_PAGE-object wouldn't be set.

If I don't use it then it isn't possible to use $this->page->format at render_list.inc.php (class ListBlock, function render_list).

And I don't find out what's the problem.

Maybe you can find out what's the reason for this behaviour?


pixtur:being offline / your changes

11 years ago (3. update 11 years ago)

Hi Burger...

sorry for my late reply but as I am being offline most of the time and working with some clients I can sparsly invest time into streber.

It took some time to figure out your problems and to understand you changes to the code. Some comments:
  1. most of you troubles was caused by an incomplete and inconsistent chain of calling the parent-constructors. I refactored most constructors to pass an assoc. arrow to its parent. Although I did some tests, you should check my changes for typos and warnings.
  2. In list_tasks.inc.php you tried to create a ListBlock before you created the actually page. This, of cause, can't work. I moved the $list= new ListBlock_tasks() some lines up.
  3. I personally don't like the conversion from utf8 into iso-8859-15. Exporting utf8 works excellent with openOffice. I guess it would also work with excell.
I will try to add some more FieldExporting as I promised last week.

Excellent work so far. I mean — it was quiet a cool feeling seeing the companies in — open office...

tom

ps. I will have a new flatrate at Wednesday. :-)





burger:Changes

11 years ago (3. update 11 years ago)

Hi Tom,

thanks for checking my changes and for your enhancements.

But I have some problems with your changes at std/export.inc.php:
  1. problem:
## cannot use .csv, because of format-errors
header('Content-Disposition: attachment; filename=' . $pagename . '.csv');

## therefore I changed it to
header('Content-Disposition: attachment; filename=' . $pagename . '.xls');
  1. problem:
## I have to use
$str = iconv("utf-8", "iso-8859-15", $str); 
## because if I don't use it then german characters aren't displayed correctely

Maybe it works with OpenOffice, but it don't works with Microsoft Excel. And I think most of the companies still use Microsoft Excel.
But if you have another idea to solve the problem with the german characters then please let me know.

pixtur:On UTF8 encoding...

11 years ago

When importing the csv-file, OpenOffice explicitly asks me for the Character Encoding. I don't have Excell here, but I would guess, that there is also an option to define the encoding. Using UTF8 will work for all characters without any conversion (also for chinese). If possible, we should be leave this road, even if we have to write into the docu, that the csv is utf8 encoded.

If there is absolutly no way of getting utf8 into Excell, I suggest a config option "EXPORT_CSV_ENCODING". Default is should be "UTF8". Another option should be "ISO8859-15".

madlyr:Reply to On UTF8 encoding...

11 years ago (2. update 11 years ago)

Excel uses WIndows code pages. For example for Polish language it is Windows-1250, check M$ documentation, which country uses which code page.

In my opinion export to csv should be per person configurable.
In profile we could add combo - esport to csv in code page and there everybody could choose if he wants' export in utf-8, or windows or even iso-885x-x if this data will be imported to webpage in other software.

If we add export to xml, then it should be another combo, but I think most exports to xml will be in utf-8.


burger:Reply to On UTF8 encoding...

11 years ago

I made a little research at Google and found out that Microsoft Excel obviously don't supports UTF-8 encoding.

So what should we do?

pixtur:Ok.. let's keep it with "ISO8859-15".

11 years ago

I don't think we need an option for this.

pixtur:seems to work...

11 years ago