Newbie - List based on Excel Spreadsheet

[ccc_ar]

Hi,

I need a drop down list but wish to read the drop down contents from an Excel (Office 2007) spreadsheet. i.e. for a particular column, each row represents an item in the drop down list.

Can someone guide me with this (simple ABC instructions at his stage). We use javascript in our scripts and are using designer version 4.4.3.

We don't want to create a database at this stage
If not possible to read directly from Excel, can you help with reading from a comma delimted file.

Much appreciated

[Steve]

Hi,

You will need some form of external API to access Excel spreadsheets from JavaScript. You could try this:

http://jexcelapi.sourceforge.net/

To access the API you will need to copy the excel jar files into the UfsServer/tomcat/webapps/ufs/WEB-INF/lib folder.

In the documentation Javascript_developer_guide.htm you can see how to access the Java API.

Create a field in your form called: MY_DROP_DOWN_FIELD and make it a dropdown field.

The JavaScript to load the contents of the field should look something like:

Code: Select all

importPackage(import java.io);
importPackage(import jxl);

var list = fields.MY_DROP_DOWN_FIELD.createCustomList();

var workbook = Workbook.getWorkbook(new File("myfile.xls"));

var sheet = workbook.getSheet(0);
var rows = sheet.getRows();

//iterate Exel columns (not sure of the exact API)
for&#40;var i=0; i<rows; i++&#41;
&#123;
  var cell = sheet.getCell&#40;0, row&#41;;
  list.add&#40;cell.getContents&#40;&#41;&#41;;
&#125;

Note that you will not see all the autocomplete for the external content such as 'workbook', because the editor does not know about the external API.

Hope this helps.

Steve

[jig]

Hi,

If your Excel file is simple with no formulas (just data), you can always save it as a CSV (still use Excel to maintain it) and then use standard JavaScript to read/write to it.

Hope this helps!

Regards
Jignesh

[ccc_ar]

We are new to Xbase XI and we got into a big muddle with trying to work with APIs. We also are not Java experts. To read a file, we really do not want to spend £1000s on consultancy to set it up so we have tried the following...

"If your Excel file is simple with no formulas (just data), you can always save it as a CSV (still use Excel to maintain it) and then use standard JavaScript to read/write to it."

OK, this is exactly what we want. We found this function and although it works from a HTML file perfectly (tested and happy for it just to work in IE), it does not work from Xbase XI. Any idea's? So far ascertained Ebase XI does not like "window.". If we then trim down the code below just to have trimmed version (see second function below), req is returned as false.

Any help would be really appreciated as this has taken a lot of effort already. A code example that works in Ebase XI (javascript) would be great.

// Original version
function loadFile(url)
{
var req = false;
// branch for native XMLHttpRequest object
if (window.XMLHttpRequest && !(window.ActiveXObject))
{
try { req = new XMLHttpRequest() }
catch(e) { req = false }
}
else // branch for IE/Windows ActiveX version
{
if (window.ActiveXObject)
{
try { req = new ActiveXObject("Msxml2.XMLHTTP") }
catch(e) { try { req = new ActiveXObject("Microsoft.XMLHTTP") }
catch(e) { req = false }
}
}
}
if(req)
{
req.open("GET", url, false);
req.send("");
return req.responseText
}
return ''
}

//trimmed version
function loadFiletrim(url)
{
var req = false;
try { req = new ActiveXObject("Msxml2.XMLHTTP") }
catch(e) { try { req = new ActiveXObject("Microsoft.XMLHTTP") }
catch(e) { req = false }

if(req)
{
req.open("GET", url, false);
req.send("");
return req.responseText
}
return ''
}

[jig]

Hi,

We have used CSV files in previous projects.

If you can send me a sample CSV and a brief description of what you are trying to achieve, we will do whatever we can

Regards
Jignesh

[Steve]

Hi

You will not be able to use Client side (Browser) JavaScript on the Server. All Action Script events are driven Server side and use Mozilla Rhino JavaScript runtime. This give you access to primitive JavaScript types (String, Number, Boolean etc..) and usual things like for, while loops etc...., but no client side stuff. Client side JavaScript will include window, document etc..

You will also have access to the entire Java API.

You can find more documentation here:

http://dev-docs.verj.io/ufs/doc/Javascr ... _guide.htm
and
http://dev-docs.verj.io/ufs/doc/Javascr ... ipting.htm


Hopefully Jig will supply an example online

Steve

[Steve]

Hi,

Just to clarify, are you trying to load the Excel document from the client (browser) or the server (Ebase)? These implementations will be quite different.

I was assuming you wanted to load the excel document from the server. I am not sure of the security rules for loading a file from within a browser.

Steve