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) fs.Close() 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 MakeExcelHeaders(sheet,dgv) '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 Next 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 Next Next 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 .Columns.AutoFit() '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 http://www.freevbcode.com/ShowCode.asp?ID=4303</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) Else ' 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) Try Dim srcSheet As Excel.Worksheet=sourceWkBk.Worksheets(sourceSheetName) sourceWkBk.Activate() srcSheet.Copy(destWkBk.Worksheets(1)) Catch ex As Exception MsgBox(ex.Message) Finally sourceWkBk.Close File.Delete(sourceFile) 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 WkBk.Save() WkBk.Close()
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!