Post
Ask Question

Populate Excel Files From Data Source

Scope

In this article we'll see how to use Visual Basic .NET, together with Excel Interops, to connect to a data source and extract information to be imposed to an existing Excel model. In other words, how is it possible to write a simple Excel reporting software.

Introduction

Recently I encountered the necessity to connect software I've made to a complex hierarchy of pre-existing Excel files. What I was asked for was to make my software able to compile reports as native Excel, to keep unaltered the old print model (and facilitate final users, for they will continue to receive the files they always had). Technically, the task was pretty clear, starting from the fact that my application was interfacing with SQL Server, I needed to find a way to extract a certain record to write it on the Excel files that were given to me. Plus, those files can undergo a revision process, in other words the authorized user could decide a determined value must not be printed again.  For example, not in cell A1, but on C14 from now on. So my Excel-populating program needs a sort of "map" to understand what-goes-where (allowing me or anybody else to quickly adapt to a modified situation).
Here I'll present a simplified version of what I'm talking about. I think it could be a useful introductive reading into inter-procedurality, having a central software that will manage a SQL Server session, when writing an Excel files, basing the latter on further access to external parameters. I hope you'll enjoy it.
To make things as reusable as possible, we'll create a stand-alone project that will execute a SQL query on its own. Think of it this way, if you have a program that could execute an external executable, feeding it some starting parameters, then we could use our project as an all-purpose Excel printer, that can be executed from other programs simply by launching it with some "switches".

Prerequisites

  • Windows 7 or later.
  • Visual Studio 2013 Community Edition or later.
  • Office 2010 (It's the version used in this article. With other versions, the referenced Interop we'll see something could change.)

Step 1: Create a new project, add references and settings

Let's create a new Visual Basic project, selecting Windows Form. Then, go to Project » Add Reference and select Microsoft Excel and Microsoft Office 14.0 Object Libraries. That will provide us the tools to interact with our Excel models. In our main form, we need to specify the following in the import section the following:
  1. Imports Microsoft.Office.Interop  
That will make it easier to reference the Office objects we'll use later.

 
 
 
Realistically, we can think our database connection will be always the same once defined and the same can be said about the path in which our Excel models will reside. So we can define two Settings variables at the application level, to store them in the application config file, accessing them using the My.Settings namespace.
 
 

Step 2: Create a parameter file functional to our means

Our program will surely need a sort of rules file, something from which to read the query to be executed and what are the cells we need to write (and the specific field that will go to that specific cell). Also, we may want to indicate the type of data we need to write. For example, if we wish to paste images into on our Excel then we must have a method the application will use to determine the read data is a path to open and not a string to be written.
For the sake of simplicity, I've chosen to use here a CSV file that we'll process to extract what we need. Here's the sample, more on it later.
  1. Query# SELECT 'TEXTCODE' + '?' AS Code, 'This is a sample' AS Descr  
  2. C4   # Code  #TEXT  
  3. C5   # Descr #TEXT  
  4. H4   # c:\tmp\sample.jpg # IMAGE  
As you can see, each line is introduced by a fixed text. In the case of the first line, we'll use the string "Query" as a sort of function name, so when our program will meet it, it will know what follows is to be executed against a valid data source (we'll see how in minutes). Ignore the "?" character for now, let's focus only on the file structure. The lines from the second one are introduced by the cell name as it appears in Excel (so C4, C5, H4 are actual cells in our model). For each line, I've used the "#" character as the field separator. For the first line, the second line field is the query to be executed, whereas for other lines, the second parameter is the name of the query field to be written in that specific cell. A third field identifies the typology of the data. TEXT for textual data, IMAGE for paths from which retrieve an image (and that could be extended, of course).
When our program runs, it should read a file like this, storing its information to be able to subsequently exploit our Excel model in the correct way.

The question mark in the query is an hypothetical launch argument we need to operate with. In the example case, it will be simply a string that will be attached to the TEXTCODE string, but in a real scenario it could be an argument to be passed to a WHERE clause.
 
 
 
With an option file like the preceding, it's easy to jot down a function to retrieve its contents, storing them in memory. Here follows the code, with some comments after it.
  1. Private Structure Corrispondenze  
  2.      Dim Cella As String  
  3.      Dim Variabile As String  
  4.      Dim Tipo As String  
  5. End Structure  
  6.   
  7. Private Sub LeggiOpzioni(modello As String, parametro As String)  
  8.    Using sR As New IO.StreamReader(My.Settings.ModelDirectory & "\" & modello & ".dat")  
  9.       _mappa = New List(Of Corrispondenze)  
  10.       While Not (sR.EndOfStream)  
  11.          Dim parms() As String = sR.ReadLine.Split("#")  
  12.    
  13.          Select Case parms(0).Trim()  
  14.             Case "Query"  
  15.                _sqlCmd = parms(1).Replace("?", parametro)  
  16.    
  17.             Case Else  
  18.                Dim item As New Corrispondenze  
  19.                item.Cella = parms(0).Trim  
  20.                item.Variabile = parms(1).Trim  
  21.                item.Tipo = parms(2).Trim  
  22.                _mappa.Add(item)  
  23.             End Select  
  24.    
  25.         End While  
  26.    End Using  
  27. End Sub  
The LeggiOpzioni function requires two arguments:
  • "modello" is the name of our Excel model. I've imagined that for each Excel file to be used, that file must be accompanied by its parameter file. So, for example, we could have a "TEST.xls" file (with all its fixed text, formulas, predefined cells, style and so on), and a "TEXT.dat" file, in which we'l define the query to be runned and what cells must be written. A model file and its rules, to put it another way.
  • "parametro" is the string/number we could want to start our program with. It's the value that will replace the question mark in the query and,  again,  it could be used any way you like, as a concatenating string (like in the previous query), or as a WHERE clause match value and so on. If you've found a potential security flaw in this, you're right,  what if a user passes a parameter containing T-SQL, special characters and so on? Exactly, he could mess badly with our databases. It will be necessary to use a parametrized query, but since it's beyond the scope of the current article, I won't present it here, adding a bibliography link to further study it.
Our function will open the DAT file named as our model, read each line and splits each of them using our separator, "#". Then it proceeds in analyzing the first field obtained. If it's "Query", we'll save in a string variable the read T-SQL query, simply substituting the question mark with the start argument (and, like I said few lines above, THIS IS BAD and must not be used in production environment, preferring a parametrized query approach). In cases that differs from the string "Query", we know we're talking about cells. So I've created a structure named Corrispondenze, to create a new List(Of Corrispondenze) in which to store the content of each option line, to use them later. From the first field we extract the cell name, from the second the name of a specific T-SQL field and from the third the type of data we'll write (in our example, TEXT or IMAGE).

Step 3: Extract and write data on pre-existing Excel file

We have now all that it takes to process an Excel model. Here we'll see the subroutine that does the variable assignment and file saving step-by-step.

Step 3.1: Connect to database and fill DataTable

To optimize database interaction, we'll open a connection, execute our query and store the retrieved records in a DataTable object. That way, we could close the connection immediately after query execution, with no need at all to mantain busy our data source. We'll do that in the following way:
  1. Dim connection As New SqlConnection(My.Settings.ConnectionString)  
  2. connection.Open()  
  3. Dim adapter As New SqlDataAdapter(_sqlCmd, connection)  
  4. Dim builder As New SqlCommandBuilder(adapter)  
  5. Dim dt As New DataTable  
  6. adapter.Fill(dt)  
  7. connection.Close()  
We could summarize this snippet as in the following. Open the connection specified in My.Settings.ConnectionString, execute the given query with a DataAdapter then fill a DataTable using what DataAdapter has read. Then, close the connection. Now for our example we'll have a single record, but the structure of the DataTable object will be shaped like the query definition. In other words, in our DataTable we'll find rows and columns, the latter named as our T-SQL fields.

Step 3.2: Create and reference Excel objects

Now that we've the data to work with, it's time to write them down. But first, we need to open the model file, positioning us on a certain worksheet and, more generally, referencing the variable that will provide the ability to access our Excel file.
  1. Dim oExcel As Object 
  2. oExcel = CreateObject("Excel.Application")  
  3. oExcel.Workbooks.Open(My.Settings.ModelDirectory & "\" & modello & ".xlsx")  
  4. Dim oBook As Excel.Workbook  
  5. Dim oSheet As Excel.Worksheet  
  6. oBook = oExcel.ActiveWorkbook  
  7. oSheet = oExcel.Worksheets(1)  
I've used the My.Settings.ModelDirectory setting here, as the place from which our models will be read. As you can see from the last line, I've hard-coded the value of 1 as the worksheet to use, assuming our models will have a single worksheet.

Step 3.3: Writing on cells

In reading our model rules file, we've compiled a List(Of Corrispondenze) variables named _mappa and now it's the moment to use it. We know each entry in _mappa is a cell to be written, so we loop the entire list, using each item's properties to do what we need. In the following snippet, you can see the first test is made on the "Tipo" property, in other words what kind of cell it is, textual or image? In case it's textual, we simply compile the cell specified by the property Cella with the requested content (Variabile property).
  1. For Each c As Corrispondenze In _mappa  
  2.   If c.Tipo.CompareTo("IMAGE") = 0 Then  
  3.      Dim oRange As Excel.Range = CType(oSheet.Range(c.Cella), Excel.Range)  
  4.      Dim Left As Double = oRange.Left  
  5.      Dim Top As Double = oRange.Top  
  6.      Dim imagesize As Integer = 32  
  7.    
  8.      oSheet.Shapes.AddPicture(c.Variabile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize)  
  9.   Else  
  10.      oSheet.Range(c.Cella).Value = dt.Rows(0).Item(c.Variabile)  
  11.   End If  
  12. Next  

Step 3.4: Saving the file

At the end of the loop, our model will be compiled and ready to be saved. Since we don't want to overwrite the model that must stay intact using executions, we need to save the new file in a temporary location.
  1. Dim resFile As String = System.IO.Path.GetTempPath() & "\" & modello & "_" & Now.Year.ToString("0000") & Now.Month.ToString("00") & Now.Day.ToString("00") & Now.Hour.ToString("00") & Now.Minute.ToString("00") & Now.Second.ToString("00") & ".xls"  
  2. resFile = resFile.Replace("\\", "\")  
  3. oExcel.DisplayAlerts = False  
  4. oBook.SaveAs(resFile, 1)  
We use the GetTempPath() function to retrieve the current user's temporary path, forging a name file with the present timestamp. Using our Interop objects, we then proceed in saving a copy of our modified model (the constant 1 in the SaveAs method means the file must be saved in XLS format). A final routine will release the used objects and open our saved file as a Process() to show it to the user. Let's see now the complete source code, with a live example of its use.

Step 4: Putting all together

  1. Imports Microsoft.Office.Interop  
  2. Imports System.Data.SqlClient  
  3.    
  4. Public Class MainFrm  
  5.     Private Structure Corrispondenze  
  6.         Dim Cella As String  
  7.         Dim Variabile As String  
  8.         Dim Tipo As String  
  9.     End Structure  
  10.    
  11.     Dim _sqlCmd As String = ""  
  12.     Dim _mappa As List(Of Corrispondenze)  
  13.    
  14.     Private Sub LeggiOpzioni(modello As String, parametro As String)  
  15.         Using sR As New IO.StreamReader(My.Settings.ModelDirectory & "\" & modello & ".dat")  
  16.             _mappa = New List(Of Corrispondenze)  
  17.    
  18.             While Not (sR.EndOfStream)  
  19.                 Dim parms() As String = sR.ReadLine.Split("#")  
  20.                 Select Case parms(0).Trim()  
  21.                     Case "Query"  
  22.                         _sqlCmd = parms(1).Replace("?", parametro)  
  23.                     Case Else  
  24.                         Dim item As New Corrispondenze  
  25.                         item.Cella = parms(0).Trim  
  26.                         item.Variabile = parms(1).Trim  
  27.                         item.Tipo = parms(2).Trim  
  28.                         _mappa.Add(item)  
  29.                 End Select  
  30.             End While  
  31.         End Using  
  32.     End Sub  
  33.    
  34.     Private Sub LoadVariables(modello)  
  35.         Try  
  36.             Application.DoEvents()  
  37.    
  38.             Dim connection As New SqlConnection(My.Settings.ConnectionString)  
  39.             connection.Open()  
  40.             Dim adapter As New SqlDataAdapter(_sqlCmd, connection)  
  41.             Dim builder As New SqlCommandBuilder(adapter)  
  42.    
  43.             Dim dt As New DataTable  
  44.             adapter.Fill(dt)  
  45.             connection.Close()  
  46.    
  47.             Dim p As New Process()  
  48.             Dim ps As New ProcessStartInfo("cmd.exe", "/C taskkill.exe /IM EXCEL.EXE /F")  
  49.             p.StartInfo = ps  
  50.             p.Start()  
  51.             Threading.Thread.Sleep(2000)  
  52.    
  53.             Dim oExcel As Object  
  54.             oExcel = CreateObject("Excel.Application")  
  55.             oExcel.Workbooks.Open(My.Settings.ModelDirectory & "\" & modello & ".xlsx")  
  56.             Dim oBook As Excel.Workbook  
  57.             Dim oSheet As Excel.Worksheet  
  58.             oBook = oExcel.ActiveWorkbook  
  59.             oSheet = oExcel.Worksheets(1)  
  60.    
  61.             For Each c As Corrispondenze In _mappa  
  62.                 If c.Tipo.CompareTo("IMAGE") = 0 Then  
  63.                     Dim oRange As Excel.Range = CType(oSheet.Range(c.Cella), Excel.Range)  
  64.                     Dim Left As Double = oRange.Left  
  65.                     Dim Top As Double = oRange.Top  
  66.                     Dim imagesize As Integer = 32  
  67.    
  68.                     oSheet.Shapes.AddPicture(c.Variabile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize)  
  69.                 Else  
  70.                     oSheet.Range(c.Cella).Value = dt.Rows(0).Item(c.Variabile)  
  71.                 End If  
  72.             Next  
  73.    
  74.             Dim resFile As String = System.IO.Path.GetTempPath() & "\" & modello & "_" & Now.Year.ToString("0000") & Now.Month.ToString("00") & Now.Day.ToString("00") & Now.Hour.ToString("00") & Now.Minute.ToString("00") & Now.Second.ToString("00") & ".xls"  
  75.             resFile = resFile.Replace("\\", "\")  
  76.    
  77.             oExcel.DisplayAlerts = False  
  78.             oBook.SaveAs(resFile, 1)  
  79.    
  80.             ReleaseObject(oSheet)  
  81.             oBook.Close(False, Type.Missing, Type.Missing)  
  82.             ReleaseObject(oBook)  
  83.             oExcel.Quit()  
  84.             ReleaseObject(oExcel)  
  85.             GC.Collect()  
  86.    
  87.             Dim pC As New Process  
  88.             Dim pCs As New ProcessStartInfo(resFile)  
  89.             pC.StartInfo = pCs  
  90.             pC.Start()  
  91.         Catch ex As Exception  
  92.             MsgBox(ex.Message & Environment.NewLine & ex.StackTrace)  
  93.         End Try  
  94.     End Sub  
  95.    
  96.     Private Sub ReleaseObject(ByVal o As Object)  
  97.         Try  
  98.             While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)  
  99.             End While  
  100.         Catch  
  101.         Finally  
  102.             o = Nothing  
  103.         End Try  
  104.     End Sub  
  105.    
  106.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
  107.         If Environment.GetCommandLineArgs.Length < 2 Then  
  108.             Application.ExitThread()  
  109.         Else  
  110.             Dim modello As String = Environment.GetCommandLineArgs(1).ToString  
  111.             Dim parametro As String = Environment.GetCommandLineArgs(2).ToString  
  112.    
  113.             LeggiOpzioni(modello, parametro)  
  114.             LoadVariables(modello)  
  115.             Application.ExitThread()  
  116.         End If  
  117.     End Sub  
  118. End Class  
A sample session of the running program could be seen here.

Source code

The source code used in our example could be dowloaded from here.


Similar Articles