Page 1 of 1

Using Excel in VB.NET Rate Topic: -----

#1 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 69
  • View blog
  • Posts: 310
  • Joined: 12-January 11

Posted 16 August 2011 - 08:30 AM

Recently, I had a customer request to send a bunch of data to a single Excel file. Normally, this would not be an issue if I were using VB6 or VBA, but I'm not. I am a VB.NET developer (I have finally overcome my phobia of actually calling myself a "developer"). I work with .NET FW 3.5 in VS2010. There just isn't a lot of information out there on how to do some things, so I decided I would pass on the stuff I have gleaned from hours and hours of searching books, the internet, and trial and error. Here is the setup: I have two ReportViewers (RV) and a few DataGridViews (DGV). I want to transfer all of the data for each of the controls to separate worksheets within the same Excel workbook so I will have one file with all of my data.

First of all, you have to have the correct assemblies added to your References tab. This may seem simple, but I spent a lot of time trying to figure out exactly which ones to use. I'm not saying the ones I use will work for everyone, as there are different versions of Excel out there and the assemblies may not be compatible. The one that I used is the Microsoft Excel 11.0 Object Library. There are other versions out there too, but I stuck with the one that worked and I have yet to have problems with it. Keep in mind that this is a COM assembly.

For my particular program, there is a lot of data being transferred so I created a backgroundworker (BGW) to handle the transfer. Using a BGW you can bring up a splash screen or some other control that will let the user know how the transfer is progressing.

The first step in processing is to create the file containing the first RV report. The reason I start this way is that it is much easier to create an Excel file with a report than it is to add a report to an existing Excel file (I'll show how I accomplish this later). That being the case here is the code I use to create the Excel file with one sheet containing the RV report:
Private Sub SaveReportToExcelFile(ByVal fileName As String, ByRef rptVwr As ReportViewer)

        Dim warnings() As Microsoft.Reporting.WinForms.Warning = Nothing
        Dim streamids() As String = Nothing
        Dim mimeType As String = Nothing
        Dim encoding As String = Nothing
        Dim extension As String = Nothing
        Dim bytes() As Byte
        bytes = rptVwr.LocalReport.Render("Excel", Nothing, mimeType, encoding, extension, streamids, warnings)
        Dim fs As New FileStream(fileName, FileMode.Append)
        fs.Write(bytes, 0, bytes.Length)

End Sub

As you can see, I pass in the name that I want the file to be called and the name of the RV that contains my report. There isn't really a lot to say about this method as it is pretty self explanatory for the parts that we may need to change (name of the report and the report control). You may have occasion to use a server report rather than a local report, but I have not tried that yet so you are pretty much on your own. I can't imagine it being much different from the way a local report is processed though.

My next step was to start my BGW and pass in the name of my file as the only argument.

Hopefully, I don't need to explain this.

Now we are into the meat of the whole process. How do I now add sheets to the existing Excel file and populate those sheets? Well, the first thing you do is to dimension an Excel Application like this:
Dim excelApp As New Excel.ApplicationClass

This is the foundation of using Excel. Everything we do is going to chain together, so if any of the steps are missed, you are pretty much hosed. Next, you need to have a workbook to work with. There are quite a lot of parameters used to create the workbook and I'm not quite sure what all of them define, but the most important one for making your code work is the file name as follows:
Dim WkBk As Excel.Workbook = excelApp.Workbooks.Open(e.Argument, 0, False, 5, "", "", False, Excel.XlPlatform.xlWindows, "", True, False, 0, True, False, False)

I have contemplated making a separate method for creating and adding worksheets, but I haven't gotten around to it yet. The basic (repetitive) idea is to dimension a sheet, add it to the workbook, change the display name, then send it the data you want to save. Here is the general code:
'create worksheet
Dim mySheet As Excel.Worksheet
'add the sheet to the workbook and rename it
mySheet = WkBk.Worksheets.Add
mySheet.Name = "Your Text Here"
'populate the summary sheet with the summary data
exportDGV(myDGV, mySheet)

You will notice the "exportDGV" method used. Fear not, that is one I created to handle the data exporting process. Here is the code for that method:
Private Sub exportDGV(ByRef dgv As DataGridView, ByRef sheet As Excel.Worksheet)

        'populate the column headers
        'populate the data
        DataToExcel(sheet, dgv)
        'format the excel sheet headers and data to make it more readable
        FormatExcelSheet(sheet, dgv.Columns.Count)

End Sub

Now I know what you are thinking..."Holy Jesus! This guy creates a rabbit hole a hundred feet deep!" Ya, I like lots of small methods rather than a single big one. It makes my code much easier to reuse AND easier to debug when/if the time comes that it needs to be debugged. So here are the four methods mentioned above:
Private Sub MakeExcelHeaders(ByRef sheet As Excel.Worksheet, ByVal dgv As DataGridView)

        For i = 0 To dgv.Columns.Count - 1
            sheet.Range(GetColumnLetter(i + 1) & "1").Value = dgv.Columns(i).HeaderText

End Sub

Private Sub DataToExcel(ByRef sheet As Excel.Worksheet,ByRef dgv As DataGridView)

        For r = 0 To dgv.Rows.Count - 1
            For c = 0 To dgv.Columns.Count - 1
                sheet.Range(GetColumnLetter(c + 1) & (r + 2).ToString).Value = dgv.Rows(r).Cells(c).Value

End Sub

Private Sub FormatExcelSheet(ByRef sheet As Excel.Worksheet, ByVal dgvColCount As Integer)

        With sheet
            'change the column headers to bold font
            .Range(GetColumnLetter(1) & "1", GetColumnLetter(dgvColCount) & "1").Font.Bold = True
            'autofit the column width to the data
            'center the values in the column
            .Columns.HorizontalAlignment = Excel.Constants.xlCenter
        End With

End Sub

''' <summary>
''' Returns the letter string for excel when given the column number
''' </summary>
''' <param name="ColumnNumber"></param>
''' <returns></returns>
''' <remarks>Taken from</remarks>
Function GetColumnLetter(ByVal ColumnNumber As Integer) As String
        If ColumnNumber > 26 Then
            ' 1st character:  Subtract 1 to map the characters to 0-25,
            '                 but you don't have to remap back to 1-26
            '                 after the 'Int' operation since columns
            '                 1-26 have no prefix letter
            ' 2nd character:  Subtract 1 to map the characters to 0-25,
            '                 but then must remap back to 1-26 after
            '                 the 'Mod' operation by adding 1 back in
            '                 (included in the '65')
            GetColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                           Chr(((ColumnNumber - 1) Mod 26) + 65)
            ' Columns A-Z
            GetColumnLetter = Chr(ColumnNumber + 64)
        End If
End Function

Notice that I used another developers method to generate the column letter for Excel (which is why I included my XML comment block). As a side note, I use the XML comment blocks on all of my methods. It makes the program look more professional and helps people who are perusing your code to know exactly what that method is doing...end side note.

So lets recap what we have now. We have a workbook with two sheets: one sheet contains the report and one sheet contains the DGV data (or many sheets with different DGV data). Now what? Well, now we need to add another report. You may say "well thats simple! Just use the method we created before to do the same thing!" That would work and I had considered it, but that particular method creates a NEW Excel file with the report in it. What we want is to add the report to our existing file. I have tried to mess with the code, particularly the file stream. I tried changing the file mode to any other that made sense, but it never worked out. The way I ended up doing it (which still has issues so I am open to suggestions) is to create a new Excel file containing the report, copy the worksheet from the new workbook to the original workbook, and then delete the new workbook. Here is the code:
Dim tempFile As String="YourTempFile.xls"
SaveReportToExcelFile(tempFile, myReportViewer)
CopyExcelWorksheet(excelApp,tempFile,WkBk,"Sheet Name/Name of Report")
WkBk.Worksheets("Sheet Name/Name of Report").Name="New Name for Sheet"

And here is the code for the methods I haven't shown you yet (recall that SaveReportToExcelFile was already shown):
''' <summary>
''' Copies a source Excel worksheet to a destination Excel workbook.
''' </summary>
''' <param name="xApp">The Excel application.</param>
''' <param name="sourceFile">The source file name.</param>
''' <param name="destWkBk">The destination workbook.</param>
''' <param name="sourceSheetName">Name of the source sheet.</param>
Private Sub CopyExcelWorksheet(ByRef xApp As Excel.Application, ByVal sourceFile As String, ByRef destWkBk As Excel.Workbook, ByVal sourceSheetName As String)

        Dim sourceWkBk As Excel.Workbook = xApp.Workbooks.Open(sourceFile, 0, False, 5, "", "", False, Excel.XlPlatform.xlWindows, "", True, False, 0, True, False, False)  
            Dim srcSheet As Excel.Worksheet=sourceWkBk.Worksheets(sourceSheetName)
        Catch ex As Exception
        End Try        

End Sub

It is a simple matter of getting the source sheet, activating it, and copying it to the destination workbook. The sheet.Copy method has three possible configurations. Leave it blank which, as I understand it, will create a new file with the copied sheet. Fill in the first parameter which will copy the sheet BEFORE the sheet you entered as a parameter (this is the one in my code). Fill in the second parameter which will copy the sheet AFTER the sheet you entered as a parameter (which would look like this: srcSheet.Copy(nothing,destWkBk.Worksheets(1)).

The last thing you need to do is clean up after yourself. This includes saving the workbook and closing the workbook:
'save and close the workbook

That's it, you're done! While this may not be every bit of information you may ever need on dealing with Excel from within VB.NET, it is a whole lot more than I could get from the internet in one place. I would love to see comments on how this code could be improved, ways in which you overcame Excel obstacles, or general ideas of how to better handle situations discussed here. Thanks for reading!

Is This A Good Question/Topic? 1
  • +

Replies To: Using Excel in VB.NET

#2 sigridish  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 09-November 11

Posted 28 November 2011 - 07:37 PM

hi psyguy! are you online right now? i have few questions
Was This Post Helpful? 0
  • +
  • -

#3 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Posted 19 December 2011 - 07:20 PM


"I've got error Type ReportViewer is not defined."
I used Microsoft Office 12.0 Object Library.
Is it because of that reference?
Was This Post Helpful? 0
  • +
  • -

#4 DimitriV  Icon User is online

  • >not activating your almonds
  • member icon

Reputation: 560
  • View blog
  • Posts: 2,669
  • Joined: 24-July 11

Posted 19 December 2011 - 07:23 PM

@Elda - he was using the Microsoft Office Object library 11.0
Was This Post Helpful? 0
  • +
  • -

#5 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 69
  • View blog
  • Posts: 310
  • Joined: 12-January 11

Posted 22 December 2011 - 11:32 AM

Sorry for taking so long to get back, work has been a little hectic. Yes, I was using MS Office Object Library 11.0, but I am not too sure why a newer version wouldn't work similarly. We all know how weird some of those updates can get.

I do, however, think that your problem may be with the version of .NET you are using. The ReportViewer object is not native to VS2008/fw 3.5. If you are using VS2010/fw 4.0 then you should have it already. You can download the ReportViewer from Microsoft for framework 3.5.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1