Accessing and Managing MS Excel Sheets With Delphi

Accessing and Managing MS Excel Sheets With Delphi This step-by-step guide describes how to connect to Microsoft Excel, retrieve sheet data, and enable editing of the data using the DBGrid. Youll also find a list of the most common errors that might appear in the process, plus how to deal with them. Whats Covered Below: Methods for transferring data between Excel and Delphi. How to connect to Excel with ADO  (ActiveX Data Objects) and Delphi.Creating an Excel spreadsheet editor using Delphi and ADORetrieving the data from Excel. How to reference a table (or range) in an Excel workbook.A discussion on Excel field (column) typesHow to modify Excel sheets: edit, add  and delete rows.Transferring data from a Delphi application to Excel. How to create a worksheet and fill it with custom data from an MS Access database. How to Connect to Microsoft Excel Microsoft Excel is a powerful spreadsheet calculator and data analysis tool. Since rows and columns of an Excel worksheet closely relate to the rows and columns of a database table, many developers find it appropriate to transport their data into an Excel workbook for analysis purposes; and retrieve data back to the application afterwards. The most commonly used approach to data exchange between your application and Excel is  Automation. Automation provides a way to read Excel data using the Excel Object Model to dive into the worksheet, extract its data, and display it inside a grid-like component, namely DBGrid or StringGrid. Automation gives you the greatest flexibility for locating the data in the workbook as well as the ability to format the worksheet and make various settings at run time. To transfer your data to and from Excel without Automation, you can use other methods such as: Write data into a comma-delimited text file, and let Excel parse the file into cellsTransfer data using DDE (Dynamic Data Exchange)Transfer your data to and from  a worksheet using ADO Data Transfer Using ADO Since Excel is JET OLE DB compliant, you can connect to it with Delphi using ADO (dbGO or AdoExpress) and then retrieve the worksheets data into an ADO dataset by issuing an  SQL query  (just like you would open a dataset against any database table). In this way, all the methods and features of the ADODataset object are available to process the Excel data. In other words, using the ADO components let you build an application that can use an Excel workbook as the database. Another important fact is that Excel is an out-of-process ActiveX server. ADO runs in-process  and saves the overhead of costly out-of-process calls. When you connect to Excel using ADO, you can only exchange raw data to and from a workbook. An ADO connection cannot be used for sheet formatting or implementing formulas to cells. However, if you transfer your data to a worksheet that is pre-formatted, the format is maintained. After the data is inserted from your application to Excel, you can carry out any conditional formatting using a (pre-recorded) macro in the worksheet. You can connect to Excel using ADO with the two OLE DB Providers that are a part of MDAC: Microsoft Jet OLE DB Provider or Microsoft OLE DB Provider for ODBC Drivers. Well focus on Jet OLE DB Provider, which can be used to access data in Excel workbooks through installable Indexed Sequential Access Method (ISAM) drivers. Tip: See the  Beginners Course to Delphi ADO Database Programming  if youre new to ADO. The ConnectionString Magic The ConnectionString property tells ADO how to connect to the datasource. The value used for ConnectionString consists of one or more arguments ADO uses to establish the connection. In Delphi, the TADOConnection component encapsulates the ADO connection object; it can be shared by multiple ADO dataset (TADOTable, TADOQuery, etc.) components through their Connection properties. In order to connect to Excel, a valid connection string involves only two additional  pieces of information -  the full path to the workbook and the Excel file version. A legitimate connection string could look like this: ConnectionString : ProviderMicrosoft.Jet.OLEDB.4.0;Data SourceC:\MyWorkBooks\myDataBook.xls;Extended PropertiesExcel 8.0;; When connecting to an external database format supported by the Jet, the extended properties for the connection needs to be set. In our case, when connecting to an Excel database, extended properties are used to set the Excel file version.   For an Excel95 workbook, this value is Excel 5.0 (without the quotes); use Excel 8.0 for Excel 97, Excel 2000, Excel 2002, and ExcelXP. Important:  You must use the Jet 4.0 Provider since Jet 3.5 does not support the ISAM drivers. If you set the Jet Provider to version 3.5, youll receive the Couldnt find installable ISAM error. Another Jet extended property is HDR. HDRYes means that there is a header row in the range, so the Jet will not include the first row of the selection into the dataset. If HDRNo is specified, then the provider will include the first row of the range (or named range) into the dataset. The first row in a range is considered to be the header row by default (HDRYes). Therefore, if you have column heading, you do not need to specify this value. If you do not have column headings, you need to specify HDRNo. Now that youre all set, this is the part where things become interesting since were now ready for some code. Lets see how to create a simple Excel Spreadsheet editor using Delphi and ADO. Note:  You should proceed even if you lack knowledge on ADO and Jet programming. As youll see, editing an Excel workbook is as simple as editing data from any standard database.

3 Tips for Staying Focused at Work During the Holidays

3 Tips for Staying Focused at Work During the Holidays Don’t let your holiday shopping, budgeting, travel-planning- or the weather!- get the better of you this season. Stay focused on wrapping up your year and gearing up for 2017. 1. Deflect the interruptionsThe holidays are always extra distracting. Prepare yourself for the inevitable onslaught and don’t let yourself give in. Instead, use the interruptions to structure your day. Online shopping session? Office cookie break? Set these as deadlines for you to work toward and remain diligent  until its time for your break.2. Look for the right projectsEveryone’s schedules are erratic during the holidays. If you’re stuck working when everyone else is out of office, use the peace and quiet to get work done on your pet projects and big picture tasks. And don’t forget that holiday parties and lingering team lunches can be a great networking opportunity, rather than a waste of time.3. Don’t take it homeStaying focused at the desk means you don’ t have to stay late to get ahead or be on your email 24/7. Make sure to save your off-work hours for all of those distractions you’ve been fending off so successfully. Save time for your shopping and traveling and package-mailing and savor it. Guard it, even. And if you’re worried about getting those things done, schedule them as though they were after-hours meetings.Being mindful about distractions and staying focused will make you more relaxed throughout the holiday season. You’ll probably find you’re way ahead of the game once the fun is over and everybody’s back at work.