I have a self service application running and I'd like to be able to first create an excel spreadsheet (using focus where output is EXL2k) and display it in a window/frame/table, allow the user to update the spreadsheet, press a submit button and then use the newly updated spreadsheet as input to a database update program. Has anyone done this? What technique was used? Thanks

Gary, you need to update your profile so that we know what version you're on.
I have the same situation.
To read back an excel file, you have to have created an ODBC connection, which requires a master file for a RANGE name in the excel file.
Trouble is, focus 5 couldn't give you an excel file with a pre-named RANGE.
So, for this very reason, i worked with the PM at IBI who is responsible for the excel output, and he promised that this RANGE naming feature would be available in 7.6 (i may have the release wrong..but thats what i remember).
so for the time being, my users are filling in the excel file, saving it as a .csv, and i can read that very easily. However, the csv file is best made from a FORMAT EXCEL not a FORMAT EXL2K; the EXL2k format won't export column titles nicely, so it makes reading the .csv file a real nightmare.

Gary,

For your basic requirement, my guess is that you would need to look at using Maintain. For that I would strongly recommend taking the IB Course first.

With Maintain you should be able to load your data into a grid simulating an Excel worksheet, allow the end user to make changes and then submit the grid for update.

T

Gary
There are actually 2 Maintain solutions here. The first is the WebFocus Excel Connector. This tool allows you to populate an Excel Spreadsheet with Table, update the data, and then save it back to the database with Maintain. When using this tool, the spreadsheet must be created before hand. There are a number of macros that are used so that the data can be saved to the database.

The second solution, as Tony mentions, is to create a Maintain form using the Read / Write Grid. Theis object allows you to return an answer set and let the user update both rows and columns. Then the data is saved back to the database.

I hope this helps.

Mark Derwin

As an add on to Mark's post, a Maintain Read/Write grid is easily populated using a WebFOCUS report. It's a great technique to leverage existing WF code....

Kevin

Don't forget that WF has a lot of different output options -- including relational database tables such as MS SQL Server or Oracle.

I did a POC awhile back where I used an Excel file as the source to a report (using a named range) -- and the output of that report was to a MS SQL Server database table using ON TABLE HOLD.

e.g.

 ON TABLE HOLD AS TDH_JUNK FORMAT SQLMSS 

A couple of things to keep in mind:

• This method will do a "Create Table TDH_JUNK" -- so the table cannot exist beforehand. We had planned on getting around this by creating a subsequent process that is ran that copies/moves/updates the data from the newly created "work" table into the "final" table and then drops the new table.
  
• The table structure (including datatypes) will be driven by your report output -- and that includes computes, defines, etc. IIRC.
  
• You need to set the default connection prior to executing something like this so WF knows where it's creating the database table.
  e.g.
  

   ENGINE SQLMSS SET DEFAULT_CONNECTION foo_test 

  
  
• The default connection in question has to have create table permissions on the database.
  

This may or may not be feasible in your situation, but frankly it will allow you to bypass the use of Maintain and if you're savvy on the database side with stored procedures or whatnot, this may actually be easier. It's a bit more "brute force", but we're talking minimal coding effort (IMO).

Has anybody had experience with the Excel Connector for WebFocus? Anything to share?

Hi Sandy,

Things to remember are -

• You must used named ranges within the Excel worksheet as the name of the range will be your MFD name.
  
  
• Include column headings (do not use spaces in the headings) as these will become the field names.
  
  
• On your Reporting Server, declare the Excel workbook as a "System DSN" within ODBC data sources (Start / Control Panel / Administrative Tools / ODBC (Data Sources).

Once you have the workbook declared then you should be able to open the Reporting Server Console (http://[servername]//ibi_apps/webconsole/iwaynode_EDASERVE/webconsole) and choose Data Adapters from the main menu.
If you do not have ODBC already configured then choose New Adapter and then ODBC to add a new connection.
You should be able to use the name of the ODBC System DSN you declared above as the datasource and then just click "Configure".
If all is well then you should now be able to add metadata for the named range(s) within the workbook.

One thing I would note is that you should go into the MFD created and specify the size of alpha numeric fields as they all appear to be A255V as a default.

Good luck

T