Recently I had the pleasure to get involved with an MS Excel automation project. The project’s main goal was to read a number of Excel documents in a specific folder, validate their contents, and use the contents to generate a new Excel documents as an output.
Although I have been working with ASP.NET Excel automation before, I still feel there are a lot of things that I don’t know. So as I browsed through the list of URL given by Google’s Search Result, I found a number of interesting things that I would like to share with you.
Gary Read shares his experience on Automating Excel using .NET. I list a few Gary’s tips which are my favourites:
- Tip #1: Seriously Consider NOT Automating Excel from .NET
In short, only automate Excel when no budget (apart from your salary :D) is allocated to the project.
- Tip #2: Use VB.NET instead of C#
The best tip for me. If you are already starting to write the code in C#–like I did when I read this article, immediately switch to VB.NET. The switch to VB.NET makes it a hell lot easier to complete the project compared to C#.
- Tip #5: Wrap your Excel communication points
Instead of cluttering your program with repeating lines of getting the cell and extracting the value, refactor the methods to make the program less hurting your eyes.
- Tip #7: Watch out interruptions and disconnects
IMHO, to avoid this problem basically we just need to ensure that only one process/user is opening MS Excel. In other words, combining automation and manual excel is a recipe for disaster.
As my project is now completed, I would like share a number of simple codes that I found useful. Hopefully it will be useful for you too.
How to create Excel document
Imports Microsoft.Office.Interop.Excel '..... Dim appExcel As New ApplicationClass Dim wbObject As Workbook = appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet) Dim wsObject As Worksheet = wbObject.Worksheets.Item(1) wsObject.Name = "Overall Summary" 'Set Worksheet Title '....Modify the Worksheet content here wbObject.SaveAs("C:\Output.xls") 'Save the file wbObject.Close() appExcel.Quit() '.....
How to open existing Excel document
Imports Microsoft.Office.Interop.Excel '..... Dim appExcel As New ApplicationClass Dim wbObject As Workbook = appExcel.Workbooks.Open("C:\Output.xls", False, False) Dim wsObject As Worksheet = wbObject.Worksheets.Item(1) '....Modify the Worksheet content here wbObject.Save() 'Save the file wbObject.Close() appExcel.Quit() '.....
How to manipulate Excel Worksheet
Imports Microsoft.Office.Interop.Excel '..... Public Function AddWorksheet(ByVal strName As String, ByVal wbObject As Workbook) As Worksheet Dim wsResult As Worksheet wsResult = wbObject.Worksheets.Add() 'Add as the first Worksheet wsResult.Name = strName Return wsResult End Function '.... Public Sub RemoveWorksheet(ByVal strName As String, ByVal wbObject As Workbook) Dim wsResult As Worksheet wsResult = GetWorksheetByName(strName, wbObject) If Not wsResult Is Nothing Then wsResult.Delete() End If End Sub '.... Public Function GetWorksheetByName(ByVal strName As String, ByVal wbObject As Workbook) As Worksheet Dim wsResult As Worksheet If Not IsNothing(wbObject) And Not IsNothing(strName) Then For Each wsObject As Worksheet In wbObject.Worksheets If wsObject.Name.ToLower().Trim().Equals(strName.ToLower().Trim()) Then wsResult = wsObject End If Next End If Return wsResult End Function '.... Public Sub MoveWorksheet(ByVal strWorksheetName As String, ByVal pos As SheetPosition, ByVal wbObject As Workbook) Dim wsResult As Worksheet = GetWorksheetByName(strWorksheetName, wbObject) If Not wsResult Is Nothing Then If pos = SheetPosition.First Then If wsResult.Index > 1 Then 'ensure the target is not the first worksheet Dim tempws As Worksheet = wbObject.Worksheets(1) 'Object Index starts with 1 wsResult.Move(tempws) End If ElseIf pos = SheetPosition.Last Then If wsResult.Index < wbObject.Worksheets.Count Then Dim tempws As Worksheet = wbObject.Worksheets(wbObject.Worksheets.Count) wsResult.Move(, tempws) End If End If End If End Sub '.....
That's all for now 🙂