By jeffreyvddb on

Hi all,

I'm working on a small website for a foundation I work for. In this website we want to administrate forms, which are input for our invoices. So far I've created a form with CCK fields and some Views. This all works fine and meets our requirements. Only thing I came along was the formatting of a form. Does anyone by any chance know how I can adjust the formatting of this form?

Furthermore, we want the possibilty to export the information from a node (e.g. name, address, postal code) to Excel. But not as a plain list (which can be done with Views), but with formatting so it meets our current invoices. For instance: the field with fieldname 'name' has to be put in field A1 in Excel. Does anyone have an idea how to achieve this? Import to say is that I'm not a technical person. Would be great if it could be achieve with a module or a 'simple' workaround ;)

Regards,
Jeffrey

⋅ Categories: Drupal 6.x

Comments

bakr’s picture

bakr commented

One little trick:

Create an HTML file
All the CSS styles should be inline

Rename the file as xls

Open in Excel, press 'Yes' to overcome file format warning.

You get your file rendered with inherited html formating, including row and font colors.

bakr’s picture

bakr commented

I have raised such a request here.
#721094: Theme exported Excel based on table-highlighter styles

jeffreyvddb’s picture

jeffreyvddb commented

Just saw your post, since I've been working on some other things for a while..

I don't really understand what you mean. I can try to create a HTML that looks like our invoice and rename it to .xls. But how can I import the data from a single node into that HTML / XLS file?

f0ns’s picture

f0ns commented

I've made a module this week that allows me to:

1. Select a content type in a dropdown
2. The system shows me all fields of that dropdown
3. Press export
4. The system generates a Comma seperated string of all this data and offers the .csv file for download to the user

I guess you are looking for something similar here. You'll need to have basic knowledge of how to make a module in drupal in order to make something like this i'm afraid.

Have you looked at the stuff that exists allready? Views Bonus Pack offers exportation in different formats using the 'feed'. Check it out and get back to me :)

jeffreyvddb’s picture

jeffreyvddb commented

Sounds like a good module. Is it also possible to create an XLS with it? If so, it sounds about right..

I've tried the Views Excel Export module, but unfortunatelly I'm not getting anywhere now (see http://drupal.org/node/778588). In the past I was able to create a Excel file, just by making a new feed. For some reason it's working different on my current project (all data is placed in one cell, instead of seperated)..

f0ns’s picture

f0ns commented

shouldn't be a problem to deliver it in an XLS file. I don't think you can choose what cells take what kind of information.

maybe you should generate your data surrounded with a table and then put it in a .xls file.. maybe then it works.. don't know..

this is my code, probably not the best way (new to drupal module development) but it teached me alot of stuff.

<?php
//$Id$

/***
 *	Menu
 ***/
function exporteren_menu() {
	$items = array();
	$items['admin/content/exporteren'] = array (
		'title' => t('Exporteer inhoud'),
	        'access arguments' => array('access content'),
	        'page callback' => 'drupal_get_form',
	        'page arguments' => array('exporteren_dashboard'),
	        'type' => MENU_NORMAL_ITEM,
	);	
	return $items;
}

/***
 *	Het formulier
 ***/
function exporteren_dashboard($form_state) {
	$form = array();
	
	ahah_helper_register($form, $form_state);
	 	
	if (!isset($form_state['storage']['exporteren']['contenttype'])) {
		 $alleTypes = exporteren_contenttypes();
		 $usage_default_value = $alleTypes[1] ;
	} else {
		 $usage_default_value =  $form_state['storage']['exporteren']['contenttype'];
  	}

	$form['exporteren'] = array (
		'#type' => 'fieldset',
		'#title' => t('Gegevens exporteren'),
		'#prefix' => '<div id="exporteren-wrapper">',
		'#suffix' => '</div>',
		'#tree' => TRUE,
	);
	
	$form['exporteren']['contenttype'] = array (
		'#type' => 'select',
		'#title' => t('Kies een type'),
		'#options' => array_merge((array)array(0 => '--selecteer--'), (array)exporteren_contenttypes()),
		'#default_value' => $usage_default_value,
		'#ahah' => array (
			'event' => 'change',
			'path' => ahah_helper_path(array('exporteren')),
			'wrapper' => 'exporteren-wrapper',
			'effect' => 'fade',
		),
	);
	
	$form['exporteren']['update_contenttype'] = array(
		'#type'  => 'submit',
		'#value' => t('Update usage'),
		'#submit' => array('ahah_helper_generic_submit'),
		'#attributes' => array('class' => 'no-js'),
  	);
	
	$form['exporteren']['uitvoer'] = array (
		'#title' => t('Uitvoer: '),
	);
	
	$form['exporteren']['submit'] = array (
		'#type' => 'submit',
		'#value' => t('Exporteer gegevens'),
	);
	
	
	if (isset($form_state['storage']['exporteren']['contenttype'])) {
		$keuze = strtolower($form_state['storage']['exporteren']['contenttype']);
		if($keuze != '0') {
			$form['exporteren']['uitvoer']['#value'] = exporteren_ullijst($keuze);
		}
	}
	
	
	return $form;
}

/***
 *	Lijst van contenttypes om de dropdown mee op te vullen
 **/
function exporteren_contenttypes() {
	$types = node_get_types();	
	$aantal = 1;
		foreach ($types as $item) {
			$key = $aantal;
			$value = $item->name;
			$dropdown_array[$value] = $value;
			$aantal++;
	}	
	return $dropdown_array;
}

/***
 *	Unordered list om op het scherm weer te geven na het selecteren van een contenttype
 ***/
 
function exporteren_ullijst($keuze) {
	$alle_mogelijke_velden = exporteren_alle_mogelijke_velden($keuze);
	$uitvoer .= 'Gegevens voor export:';
	$uitvoer .= '<ul>';
	foreach($alle_mogelijke_velden as $item) {
		if($item != " ") {
			$uitvoer .= '<li>' .$item. '</li>';
		}
	}
	
	$uitvoer .= '</ul>';

	return $uitvoer;
}


/***
 *	Toont lijst van alle beschikbare velden voor een bepaald contenttype
 ***/
function exporteren_alle_mogelijke_velden($contenttype) {
	$alle_gegevens = _content_type_info();
	$uitvoer = array();
		
	foreach($alle_gegevens['content types'][$contenttype]['fields'] as $label =>$inhoud) {
		array_push($uitvoer, $label);
	}
	
	$verwerkte_uitvoer = array(0 => ' ');
	
	foreach ($uitvoer as $item) {
		$verwerkte_uitvoer[$item] = $item;
	}
	return $verwerkte_uitvoer;
}

/***
 *	Handler voor submit knop
 ***/
function exporteren_dashboard_submit($form) {
	$keuze = $form['exporteren']['contenttype']['#default_value'];
	exporteren_maak_csv($keuze);	
}

/***
 *	Methode die de CSV string zal aanmaken die uiteindelijk zal weggeschreven worden naar een bestand
 ***/
function exporteren_maak_csv($keuze) {
	$alle_velden = exporteren_alle_mogelijke_velden(strtolower($keuze));
	$nodes = db_query("SELECT * FROM {node} WHERE type = '%s'", $keuze);
	$csv = '';
	
	$csv .= $keuze.";";
	foreach($alle_velden as $item) {
			if($item != " ") {
				$header = substr($item, 6);
				$csv .= ucfirst($header).';';
			}
	}
	$csv .= "\r\n";
	
	while($node = db_fetch_array($nodes))
	{
		$een_node = node_load($node['nid']);
		$csv .= $een_node->title.';';
		foreach($alle_velden as $item) {;
			if($item != " ") {
				$veld = $een_node->$item;
				
				if(!isset($veld[0]['value'])) {
					if(isset($veld[0]['nid'])) 
					{
						$field_ref_node = node_load($veld[0]['nid']);
						$csv .= str_replace("\r\n", " ", $field_ref_node->title).';';
					} 
					else if(isset($veld[0]['email']))
					{
						$csv .= str_replace("\r\n", " ", $veld[0]['email']).';';
					}
					else
					{
						$csv .= ' ;';
					}
				} else {
					$csv .= str_replace("\r\n", " ", $veld[0]['value']).';';
				}
			}
		}
		
	$csv .= "\r\n";
	}	
	$bestandsnaam = $keuze.'_export-'.date('d/m/Y').'.csv';
	
	header("Content-Disposition: attachment; filename=$bestandsnaam");
	print($csv);
	exit;
	
}