1. Technology
Accessing and managing MS Excel sheets with Delphi
Page 5: How to modify Excel sheets: edit, add, and delete rows.
 Win prizes by sharing code!
Do you have some Delphi code you want to share? Are you interested in winning a prize for your work?
Delphi Programming Quickies Contest
 More of this Feature
• Page 1: Connect to: MS Excel
• Page 2: Creating an Excel spreadsheet editor
• Page 3: Retrieving the data from Excel
• Page 4: Excel field (column) types
• Page 6: Transfering data to Excel

• Page 7: Full source code
 Join the Discussion
"Post your views, comments, questions and doubts to this article."
Discuss!
 Related Resources
• A Beginners Guide to ADO programming
• Automation with Excel and Delphi

The boring theory is behind us, we've talked about connecting to Excel (e.g. the ConectionString), about Excel columns and data types; finally it's time to do the easy (and interesting) work: edit Excel worksheet's data using a Delphi DBGrid!

As simple as click, type, click!
The most funny part of the whole idea of managing Excel data from Delphi is that the editing is just as easy as editing any "normal" database data.

Given the fact we are approaching to Excel using ADO, all the methods and features of the ADO dataset object are available to process the Excel data. The dataset's query result is bound to the DBGrid (through the TDataSource), and the grid is populated automatically. Any changes you make to the grid will be reflected in the underlying dataset - the Excel worksheet! To make the process even more "friendly", we attached the DBNavigator control to ease the most common dataset operations.

Editing
You edit Excel data using the well-known Delphi's Dataset's methods and mechanisms. Here's a simple edit operation in pictures:

1. Started editing a value in DBGrid.
2. Pressed the nbPost button on the DBNavigator:
Editing Excel data within a Delphi DBGrid

3. The result is the changed cell's value in Excel!!
Editing Excel data within a Delphi DBGrid results in altered Excel data

Caution: As speculated on the previous page, fields that (in Excel) contain formulas are read-only and cannot be edited. If you try to edit such a field, an error message "Cannot update (expression); field not updateable" will be raised.
What more, don't expect the field "Function" to act as a Delphi calculated field! If you change any of values in fields "F4" or "Points", the value of the field "Function" (in the same row) will not be changed - you'll need to re-fetch the data to see the changes (as those were actually made in Excel). Even more, if you do change any of those two fields and Post the changes, everything will look fine, but if you try to edit any more values, in the same row, the error "Row cannot be located..." will be displayed.
Adding rows
In general, you issue a new record (row) by calling the Insert method of the ADOQuery1 (the nbInsert button on the DBNavigator). Note: if you have used named or unnamed range to retrieve the data from Excel, after you add the new row, it's values are not visible if you re-fetch on the original range pattern.
Deleting rows
Deleting the selected "record" is somehow problematic. You cannot delete the record in Excel data source, since rows in Excel are not compatible with rows in traditional (relation) databases. What you can do, is to clear the contents of every cell (field) the row contains.
Caution: If you try to delete an entire record (AdoQuery1.Delete or nbDelete on the DBNavigator), an error "Deleting data in a linked table is not supported by this ISAM." will stop the operation. If, ot the other hand, you try to delete a value in the "Formula" cell, "Operation is not allowed in this context." will be raised.

This concludes our editing adventures of an Excel worksheet's data. What remains unanswered is how to transfer your data from any "normal" database to Excel. This is the topic we discuss on the next page...

Next page > Transfering data from a Delphi application to Excel > Page 1, 2, 3, 4, 5, 6, 7

©2014 About.com. All rights reserved.