Public void BoldRow( int row, Worksheet ws) Listing 3 - Setting a Row to Bold in Excel The EntireRow property has a Font property in which we can manipulate the style of the row. We can access the entire row from a single cell through the EntireRow property of the cell. Bolding a row, as with most applications, is done through the Font property. The last thing we need to be able to automate is the formatting of the spreadsheet. Formatting in Excel is a little less obvious as to how it is performed. We'll start with a simple formatting method in ExcelHelper called BoldRow which allows us to bold an entire row in the spreadsheet. ((Range)ws.Cells).Value2 = item // set the cell value at a row and column Public void AddItemToSpreadsheet( int row, int column, Worksheet ws, string item) Listing 2 - Setting a value inside a cell in Excel The Cells property lets you treat both rows and columns as numbers starting at index = 1. This method uses the Cells property of the worksheet to set a value at a particular row and column index in the spreadsheet.Note that excel has letters for columns and numbers for rows. Now that we opened our spreadsheet, we want to be able to populate the cells at specific locations in the spreadsheet.The ExcelHelper method, AddItemToSpreadsheet, allows us to do that. Worksheet ws = (Worksheet)_excel.ActiveSheet // activate the active worksheet in the workbook _excel.Visible = true // make the excel application visible _excel = new Excel.ApplicationClass() // create a new COM object for excelĮxcel.Workbook workbook = _(Type.Missing) // add a new workbook Listing 1 - Opening a New Excel Spreadsheet from C#
Once you've added the excel reference, you can treat excel as if it were a set of objects in C#. Below is the code to "construct" and activate an excel spreadsheet.
For further information on adding Excel as a reference check out my article on how to read an excel spreadsheet. The COM Callable Wrapper is automatically generated when you add it as a reference to your Visual Studio Project. NET allows us to attach to Excel by building a COM callable wrapper around the Excel COM Object library. Excel has a rich library of objects that lets you take control of every aspect of Excel. We will focus specifically on how to talk to Excel from a. There are many aspects of this application we can talk about in this article. The Query2Excel application takes advantage of the OdbcConnection, OdbcCommand, DataSet, and OdbcAdapter classes to connect to the database and perform the query.įigure 2 - Query2Excel Design Reverse Engineered using the WithClass UML Tool Sql Server, MS Access, Sybase, Oracle, MySQL, and most others).
ODBC (Open Database Connectivity) will allow you to connect to any database that has an ODBC driver associated with it (e.g. It also has several formatting functions (FormatColumn, BoldRow, FormatColumnText, etc.) and it has a method called AddItemToSpreadsheet to allow you to add text to a particular cell in the spreadsheet.ĭatabase connection is done completely through ODBC. It has an activation function, Activate, to open and activate excel. The ExcelHelper class has the minimum number of functions needed to take data from the database and place it into Excel. Inside the form is the ExcelHelper class that allows you to talk directly to excel through the interoperability library. The design of the Query2Excel application is fairly straightforward. It consists of a form that allows you to enter your connection information and your query. This is the tact we took in this article. NET's interoperability feature to pull the data directly into the spreadsheet. csv files can be imported into excel, but it's not the ideal solution. These tools usually allow you to export the data in the form of xml, sql statements, or csv files (comma delimited rows). The question is, how do you get the data from the database into the spreadsheet? One way is to export the data using provided database tools by the database vendor. Once data is inside an Excel spreadsheet, traders can massage the data to suit their needs. Specifically they like Excel spreadsheets. In the trading world, most traders don't know from databases, however, they do know from spreadsheets.