WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Knowing how to work with Microsoft Office applications, especially Excel, can prove vital in any environment. At my work, there are a few options for exporting data to the users. These options include the ever-popular PDF format, as well as Excel. PDF-based reports are read-only, whereas the Excel Reports provide more user activity. Today, I will show you how to work productively with Excel Worksheets.
Not much more to talk about, for a change! Let's jump straight in with a project.
Before you start with the code, you need to set a Reference to your Excel libraries on your computer. You have to keep in mind that you have to choose the correct Excel version libraries; otherwise, some of the newer features may not work and it will cause your program to break. So, find out which version of Excel is being used before you reference the wrong Excel Libraries.
Adding a Reference
- Click Project, Add Reference.
- Select the COM Tab.
- Scroll down until you find Microsoft Excel xx.x Object Library. In my case, it is the Microsoft Excel 15.0 Object Library.
- Add a checkmark next to it and select OK.
Figure 1: Excel Object Reference
Add the following line of code to complete setting up the Excel references:
Imports Excel = Microsoft.Office.Interop.Excel
Creating a New Excel Workbook and Worksheet
Add the following code to create all the Excel objects (Excel Application, Excel Workbook, Excel Worksheet):
Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet
Inside the New button's code, add the following to complete the Excel object creation process:
xlWorkBook = xlApp.Workbooks.Add xlApp.Visible = True xlWorkSheet = xlWorkBook.Sheets("Sheet1")
This instantiates an Excel Application object, adds a new Workbook, and specifies the Worksheet we will be working on. In this case, it will be Sheet1.
Adding Data to the Worksheet
Continue where you left off. In the New button's click event code, add the following to add some data into Sheet1:
With xlWorkSheet .Range("A1").Value = "Month" .Range("A2").Value = "January" .Range("A3").Value = "February" .Range("A4").Value = "March" .Range("A5").Value = "April" .Range("B1").Value = "Loan Repayment" .Range("B2").Value = "1000.00" .Range("B3").Value = "1200.00" .Range("B4").Value = "1300.00" .Range("B5").Value = "1600.00" .Range("A6").Value = "Total Paid" .Range("B6").Formula = "=Sum(B2:B5)"
This adds the specified text in each designated cell using the Range properties of the Worksheet object. The last line of code adds the SUM formula in cell B6. I didn't close the With structure on purpose because we will keep adding code into it, so please keep following.
Adding Formatting to Cells
Add the following code to format our cells:
With .Range("A1:B1") .Interior.ColorIndex = 4 With .Font .ColorIndex = 2 .Size = 8 .Name = "Comic Sans MS" .Bold = True End With End With With .Range("A6:A6") .Interior.ColorIndex = 4 With .Font .ColorIndex = 2 .Size = 8 .Name = "Comic Sans MS" .Bold = True End With End With .Range("B2:B6").NumberFormat = "R#,##0.00"
Cells A1 and B1 and A6 get filled in with ColorIndex 4, which is green. The font gets changed to "Comic Sans MS," the size to 8, and it will be Bold.
Add the following code to add Borders around some of your cells:
With .Range("A1:B6") With .Borders(Excel.XlBordersIndex.xlEdgeLeft) .LineStyle = Excel.XlLineStyle.xlDouble .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With .Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With .Borders(Excel.XlBordersIndex.xlEdgeBottom) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With .Borders(Excel.XlBordersIndex.xlEdgeRight) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With .Borders(Excel.XlBordersIndex.xlInsideVertical) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With With .Borders(Excel.XlBordersIndex.xlInsideHorizontal) .LineStyle = Excel.XlLineStyle.xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = Excel.XlBorderWeight.xlThin End With End With .Columns("A:B").EntireColumn.AutoFit()
With the preceding code, I set up the borders for our two columns and their respective cells that hold data. Here is more information on Excel Border types that can be found. The very last line Auto fits all the data inside the columns. Your worksheet should look like Figure 2:
Figure 2: The formatted worksheet
Adding a Chart
Add the following code to add a 3D Pie chart with its Legend and a proper Title:
.Shapes.AddChart.Select() With xlApp.ActiveChart .ApplyCustomType(Excel.XlChartType.xl3DPie) .SetSourceData(Source:=xlWorkSheet.Range("$A$1:$B$5")) xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _ Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorAccent6 xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.TintAndShade = 0 xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.Brightness = -0.2 xlApp.ActiveChart.ChartArea.Format.Fill.Transparency = 0 xlApp.ActiveChart.ChartArea.Format.Fill.Solid() .Parent.RoundedCorners = True With .PlotArea .Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse .Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse End With With .Legend With .Format.TextFrame2.TextRange.Font.Fill .Visible = Microsoft.Office.Core.MsoTriState.msoTrue .ForeColor.RGB = RGB(0, 0, 128) .Transparency = 0 .Solid() End With With .Format.Fill .Visible = Microsoft.Office.Core.MsoTriState.msoTrue .ForeColor.ObjectThemeColor = Microsoft.Office.Core. _ MsoThemeColorIndex.msoThemeColorBackground2 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = -0.25 .Transparency = 0 .Solid() End With End With .ChartTitle.Format.TextFrame2.TextRange.Font.UnderlineStyle = _ Microsoft.Office.Core.MsoTextUnderlineType.msoUnderlineWavyLine End With End With
Your chart should resemble Figure 3:
Figure 3: Our Chart
Saving Your Work
Add the following code to save your work:
Private Sub btnSave_Click(sender As Object, e As EventArgs) _ Handles btnSave.Click xlWorkBook.SaveAs(Filename:="C:\TEMP\Example.xlsx", _ FileFormat:=51) xlWorkBook.Close() xlApp.Quit() ReleaseAll(xlApp) ReleaseAll(xlWorkBook) End Sub Private Sub ReleaseAll(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub
The previous code saves the workbooks and quits Excel properly via the use of the ReleaseAll sub that removes all the Excel references from memory.
It is always very handy to know how to work with external libraries, especially the libraries available in Microsoft Office. I hope you enjoyed this article as much as I did. Until next time, cheers!