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.
Lesson Learned
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 🙂
