It's hard to top the Delphi IDE as a productive editing environment, but sometimes it's handy to harness a spreadsheet and use its power to build constructions that are quite tedious to type manually. Small databases can be concocted quickly in Excel. The magic of cut-and-paste is extended when we capture a Stringlist and paste it into Excel. Then we can use the spreadsheet's rows, columns, and formulas to tweak and build code very quickly. Since we lifted the variable names in the first place, the constructed code is more likely to compile the first time, which is -- as they say -- priceless.

Code that was a bear to input becomes untouchable. If we can more quickly build the code we will not become attached to it. Conversely, some code construction is overlooked because of the time required to implement it and the sheer complexity of it. With a power-tool like Excel to help we can quickly construct the solutions. When a more elegant solution becomes apparent, as often happens in spiral development, we can quickly implement it. And if we can construct quickly perhaps an insight or enlightenment will not get lost in the grind. We can spend more time being verbose in the code and the coffee house.

If Excel is too slow copy the exe as XL97.exe to another directory and don't register it. XL97 is very lithe when not bogged down with the registery.

10. Data in & out fast

This is an Excel-only thing. The OLEVariant will allow spreadsheet data to be operated on inside your program space, minimizing OLE routines. The speed comes from using Delphi runtime speed on the data image already loaded in Excel.

procedure TxlRunner.XLtoStringGrid(AStringGrid: TStringgrid; AWorkSheet: _workSheet );

var
ArrV: OLEVariant
M, n: integer;
begin
  ArrV:= WorkSheet.UsedRange[0].Value;
  // m:= WorkSheet.UsedRange[0].Rows.Count; these work using Excel side
  // n:= WorkSheet.UsedRange[0].Columns.Count; save for VB World
     m:= VarArrayHighBound(ArrV, 1);// rocket fast using Borland side stuff
     n:= VarArrayHighBound(ArrV, 2);
  ...
  // Note with StringGrid.Cells Rows, Columns are turned and have a 0 Index 
  StringGrid1.Cells[ j - 1, i - 1 ]:= varToStr(Arrv[i ,j ]);
  ...
end;

To speed the development of the output we add a worksheet to the Excel workbook that the user is free to format as desired:

{ The output side }
procedure TxlRunner.StringGridtoXLDump(AStringGrid: TStringgrid, AWorkSheet: _workSheet );
Var
  R, C, RowCount, ColumnCount: Integer;
  ARange: Range;
begin
  Source:  
  ARange:= AWorkSheet.Range['A1'];
	 // "alt" ARange:= AWorkSheet.Cells; 
  RowCount:= AStringGrid.RowCount;
	 Columncount:= AStringGrid.ColCount;
  For R:= 0 to Pred(Rowcount) do
	   For C:= O to Pred(ColumnCount)do
		    ARange.Offset[R, C]:= AStringGrid.Cells[C, R];
		    // "alt" ARange.Item[R + 1, C + 1]:= AStringGrid.Cells[C, R];
	   // C
	 // R	   
end;

Let's use the friendly range.offset to write back to the worksheet. (Assigning to UsedRange blows the rest of the worksheet's data image.)

I like to use a crosstable construction in Excel, where all fields are inserted into a table that is updated by our runtime program. The report references this table as needed and the user can pretty up the output as desired. This allows the data to be inspected in the untouched state for debugging and simultaneously gives the end user considerable room for modifications to the report -- not the original data.

The Excel formula that works best is Index(CrossTable, M1, N1). M1 and N1 are relative references within the layout scheme. Remember to populate unused CrossTable entries with white space to avoid the unsitely 0 or #things returned when using formulas.

9. CSV files

Use Excel to build tables which are used by Developer's Express dxMemDataSet (it's on the Companion disk) to build small databases without BDE or ADO engine overhead. We build a database table from scratch in the Excel environment. First, put the field names across the top row. The following rows are the records. We can then fill in by using the friendly Excel feature that advances to the next row when the Enter key is pressed.

The field names across the top must be unique and have no white space -- this allows scaling to other databases later. We select the top row and use Paste Special to transpose the field names into a another sheet which has the field names of field_name, type, and comments. We save this sheet as a CSV and load it in our project runtime to add comments or hints to the UI as needed...the comments regarding the exact nature or necessity of a field can be quite a time saver six months down the road!

The former table sheet is saved as a CSV as well. We just have to remember that the Excel multiple sheets will be lost if we don't do a final save in the native XLS format.

8. Fixing the database

In development or down the road an additional field may becoming necessary -- say an e-mail address to enhance a contact list. Or perhaps a field for secondary telephone numbers.

For the first case we just load the table into Excel and add a column. Save as a CSV file and the database now has e-mail addresses. To get the dxMemData fitted to the modified table we cut it from the form and paste in a text box in Excel -- or use Notepad and edit the dxMemData in text mode. Using a another field as a guide, add the new property line. The dxMemData text is directly pasted into the form.

The second job is a little tougher. We don't want to revisit the compiler every time an additional telephone device is added, nor do we want to add fields to a database if they're not needed.

We build a new table consisting of the key, phone type, and phone number. We use the control key when copying non-contiguous columns out of the first sheet into the new table. Get the record number, user name and existing telephone number. The additional phone numbers are added using the user name and key to allow RD power... Now another DxMemData is added to surface the new table in the project.

{ Data-driven example }
Copy Con MakeDirDataFile.Bat
Dir *.old.email.and.Sources.xtns >> DirData.prn
Excel DirData.prn

{ Enter above in a command line and close it with Alt-F6 & enter keying. 
This .bat file redirects the console output to a text file that Excel 
recognizes as a space-delimited data file. We insert field names at the top 
and add a column named "Comments." Save as a CSV file and we've got a good 
start for a file-commenting program.}

Amazing fact. After about 200K, Excel-compressed files are nearly equal in size to their CSV counterparts. But Excel files often load faster. Perhaps an XML data file is even smaller than a large compressed native XLS file. Use a ClientDataSet's ability to save as XML to find out. Just drop one in the form and connect it to the dxMemData. You even even save it as XML in the IDE.

Load trick. Save Excel data as a CSV file, load the file into a text editor, and paste it into a memo component line editor or RichtextEdit control. Voila -- a built in table for demos that the user can't edit or lose! Also, you can get the CSV file much smaller by removing the thousands of commas in Excel and then removing the quotes around the numbers in a text editor.

7. Component to string

Surface a component's properties so we can operate on them in Excel.

To get the names or the values, build a project that uses David Body's Componenttostring routine and build a name extractor in Delphi.

procedure TForm1.PropertyNameExtractor(theComponent: Tcomponent);
var
  Idx: integer;
begin
  // Extract ComponenttoString function from Delphi help or
  // use David Body's CompStream Unit from past Delphi Informant 
  // article vintage 98
  Memo1.Lines.Add(componenttoString(theComponent));
  // get names only
  For Idx:= 0 to pred(Memo1.Lines.Count) do
  Memo2.lines.add(Memo1.Lines.Names[Idx]);
end;

The easy method is to simply select and copy the component on the form, then paste it into an Excel text box. The property text can be massaged by trimming the white space. The white space may be left, since any code construction made to reference properties accessed with ComponentToString need the whitespace offset to find the property in a runtime stringlist.

Caveat 1: Default properties need to be added manually if you want to change them.

Caveat 2: The same for non-streaming properties. Sometimes.

To learn more about VCLs, build the picclip VCL in the VbtoDelphi.wri Delphi 1 vintage edition. Then use Delphi help or documentation to go through building the Calendar VCL sample.

6. Image and text library

All the images a project uses can be collected in the Excel worksheet. When you are ready to use them, paste into the Image editor. You can simply cut-and-paste your way up the side of Deadline Mountain. Usage 1: Copy a bunch of little images gathered in Excel. to one large one in Resource Editor. Usage 2: Mark up image details in a nearby cell.

Text boxes are available under the Drawing toolbar allow us to copy text into Excel.

The text boxes are colored yellow to match their real-world counterparts. And they serve as a handy staging area for building code.

For a deluxe text box use Insert Object to bring up the OLE thing and select Word. Now you can have Tab stops. We also can add VCLs to our library, but we should add a few dummy fields to CDS to use as source when we proceed to build the project.

5. Define constants in Excel

Excel allows us to name variables with the Insert Menu Name define Option. These defined variables can be pasted into the code line build formula with the F3 key after entering the edit formula mode with the F2 key.

To transfer these constants in Object Pascal code we add a column to the left and insert a formula: = B1 & Assigned & C1 & sC & " //" & D1.

We can get the functionality of the above Time function by building a runtime routine to write to an include file every minute. Just have the routine write this line:

'TIMEIS = ' + DateTimetoStr(now)

4. StringArrays

Paste the strings in Column C.

Add this formula to Column A:

= QT & C3 & QT & Comma

Drag the formula down to catch all the Strings. (Select the formula and drag the cross down, copying the formula to each cell in the column.) This produces the lines that are copied into the IDE. Slick, huh?

3. Enumerations

Enumerations are much the same. We just add the enumeration prefix to the formula:

= "et" & C3 & Comma

Note that if the stringtable contains sub properties we search and replace the dot with an underbar; top and left need underbar prefixes if we are working with components.

2. Jump tables

We can build a supersized Case table using a few more pieces. We would put the enumerated types in Column B and the Action in Column C. The Formula would look like this:

= B3 & " :" & wS2 & Result & equals & sQ & B4 & sQ & sC

Add a begin statement and Case else with a showmessage('Blowed Case Table Contact ' + BUILDER) before a Case end; now copy the begin and end and every in between into a procedure in the IDE.

Once you have copied the properties out of the extractor you use the Excel & string operator to quickly build a line of code in Excel. Use the drag facility to copy the formula down the column. Alias the white spaces in with wS(n)to beautify the code.

1. Home-made proofer

Click on the sheet tab.

Insert a new sheet.

Name this sheet Proofer.

Set the width to 80.

Set color and font to IDE colors.

Set font to Courier.

Drag formatting down 50 to 100 lines.

Hint: Use Begin and End to denote the start and stop of ranges instead of the old Lotus trick of inserting "----" to denote the start and stop of ranges. (We always insert in a range so the sheet automaticly reworks the references.)

Copy the formula range you just built and then paste in the copied cells as links with Paste Special -- these cells actually reference the copied cells. We just want to pretty up the code produced without disrupting the build sheet.

This version is somewhat lazy; we have to manually proof the code produced for mistakes before dropping it into the IDE.

Patrick Foley is a chief power plant operator at Iowa State University in Ames, Iowa, and the owner of a start-up named vPowerHouse that deals in power plant simulation. He had a "birds-of-a-feather" session at 1999 BorCon in PhilaDELPHIa.