How to serve and read native files with Lasso

This article describes a method for using lasso to serve and read native files, i.e. native Word, Excel etc files, generated by Lasso.

There are many reasons you might want to serve for instance MS Word or Excel files to your users, files where the contents comes from Lasso in one way or another.

This has been the topic many times in the Lasso community forums, where various solutions have been discussed. For instance, for Excel files, one way to deal with the problem is to build and serve a .CSV file, which is nothing more than a text file with special formatting, or to build an html tabel and serve with a .xls file ending.

The problem with serving text files are several, amongst which are that you have to take care to encode the file with the right encoding according to the plattform it is being served to, and you have to make certain the cell delimiter character, such as comma, semi-colon or tab does not occur as part of the data you pour into the file.

The problems with serving an html-table file are that different versions of Excel will read the file slightly different (and some might fail), and of course, it is not a native Excel file, therefore it will not open directly in other applications that can open native Excel files, such as SPSS.

The same considerations and problems are common with all attempts to simulate one (native) file type by serving another.

This describes a method where you can read and write 100% native files to the client. This is done by using the native application as a file parser on the server side. Therefore, a few caveats exist:

• The server has to be a Mac (at least using this method)
• The application whose native file type you want to serve has to support a minimum of Applescripting, at least open and save commands.

Server setup

• Install the application on the server. We'll use Mcrosoft Excel 2008 as an example. For the simplest setup, install on the same machine running Lasso. It should be possible to use another machine on the network and use remote Apple Events in Applescript, but this is not decribed here, nor have I attempted it.
• Install the OS Process tag in the Lasso Application Folder, if necessary. The tag is an optional install in the Lasso Application 8 folder, subfolder Extensions. Restart Lasso
• In Site admin, create a user for the OS Process Users group. I will call this user 'process_user'
• Check, in Site Admin, that the permissions and file types have been sat in such a way that Lasso has permission to manipulate the file types (through file extensions) you wish to use.

How to serve a native file

The chain of events in your Lasso script is this:

• Create the contents of the file like you would normally do for writing an old-type file simulation, typically by generating the data that should go into the file in a variable. I used to generate Excel files by generating a simple html file with an html table with the data, into a variable. I will call this variable $filecontents
• Write that file to disk, instead of serving it
• Through [OS_Process], using AppleScript (OS_Process running '/usr/bin/osascript') open that file in the application of your choice, i.e. Excel, then have the application save the file out as a native file (or file type of your choice).
• Have Lasso pick up the saved file and serve it to the client

Here is a code sample for an Excel file:

// set up some variables. These will vary according to your setup
	Var: 'fileName' = 'myExcelFile';
	Var: 'webDir' = '/Users/Library/Documents/'  //from the root of the HD
	Var: 'myDir'='/users/files/'; //relative to the root of the web folder
	Var: 'filetype' = 'application/excel';
	Var: 'fileending'='.xls';
//make sure the working directory exists
	If: !(File_Exists: $myDir);
		File_Create: $myDir;
	/If;
// write the file, in this case an html file (which Excel can parse and open)
	Inline: -username='fileuser', -password='secretpassword';
		File_Create: ($myDir + $filename + '.html'), -FileOverWrite;
		File_Write: ($myDir + $filename + '.html'), $filecontents, -FileOverWrite;
	/Inline;
//Call Applescript through OS_process, which will have Microsoft Excel
//open the html file and then save out a native file. Notice that the argument
//for OS_process is one string, but the line breaks represent the line breaks that
//Applescript expects (a bit mangled in display, look at it in the editor to view the line breaks correctly).
	Inline: -username='process_user', -password='secretpassword2';
		Var: 'os' = (OS_Process: '/usr/bin/osascript', (Array: '-'));
		$os->(Write: 'tell application "Microsoft Excel"
		open "' + $webDir + $myDir + $filename + '.html"
		save in "' $filename + $fileending'" as Excel98to2004 file format
		close workbook 1
	end tell');
		$os->CloseWrite;
		$os->Read;
		$os->Close;
	/Inline;
//We then delete the html file and serve the native file Excel wrote to disk
	Inline: -username='fileuser', -password='secretpassword';
		File_Delete: ($authorDir + $filename + '.html');
		File_Stream: -File=($authorDir + $filename + $fileending), -Name=($filename + $fileending), -Type=$filetype;
	/Inline;

We use the construct 'as Excel98to2004 file format' to have Excel save in a format that should be as universal across Excel Mac/Windows as possible.

Notice that we have Excel close the document, and we delete the .html file, but we cannot trash the native document after we've served it, since the [File_Stream] tag aborts the page execution. If you want to get rid of the file once it's been served, you will have to set up an Event to do so.

Also notice that while Lasso takes its file paths from the root of the website folder, Applescript needs to be told from the root of the HD.

The technique is very similar for MS Word files, except the Applescript is slightly different. This works for me:

$os->(Write: 'tell application "Microsoft Word"
	open "' + $webDir + $myDir + $filename + '.html"
	set default file path file path type documents path path (("' + $webDir + $myDir + '" as POSIX file) as text)
	save as active document file name "' + $filename + '.doc" file format format document97
	close active document
end tell');

Word is not so forgiving as Excel is when it comes to specifying paths using slashes, so we'll have to resort to some POSIX acrobatics. And the line set default file path file path type documents path path (("....
is not a typo. Go figure.

Allthough the preceeding has descibed only serving, i.e. writing, native files, to read from native files should be straightforward, using a reverse process:

• Read the file from the client and put on disk
• Have the parser application read it
• Have the parser application write it out in something Lasso can parse, like a CSV file
• Read the parsed file into Lasso

Other file types should work just as well, provided that a) you have a way to get the application to parse and open a file produced with Lasso, and b) as stated, that the application is applescriptable,  at least to the extent that it can open and save files.

Author: Lars A. Gundersen
Created: 23 Sep 2010
Last Modified: 24 Mar 2011