Solved

Help with Code Refactoring

Posted on 2014-11-20
15
56 Views
Last Modified: 2016-02-11
I am trying to tighten up a code that I have written to create 6 worksheets with an SQL query that I created without so much code.  Any help would be greatly appreciated.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel



Public Class Form1
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
                & "Trusted_Connection=Yes;"
    Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlSheet6 As Excel.Worksheet
        Dim xlSheet5 As Excel.Worksheet
        Dim xlSheet4 As Excel.Worksheet
        Dim xlSheet1 As Excel.Worksheet
        Dim xlSheet2 As Excel.Worksheet
        Dim xlSheet3 As Excel.Worksheet
        Dim xlSheet7 As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value


        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)


        xlWorkBook.Sheets.Add(, , 4)


        xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        xlSheet6.Activate()
        xlSheet6.Name = "9-2-2014 S"
        xlSheet6.Tab.ColorIndex = CType(3, Excel.XlColorIndex)

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using sheet6sda As New SqlDataAdapter()

                Using sheet6cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet6cmd.CommandTimeout = 0
                    sheet6cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
                    sheet6cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet6sda.SelectCommand = sheet6cmd

                    Using ds As New DataSet

                        sheet6sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet6.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet6.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

                        xlSheet6.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet6.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet6.ListObjects("Table1").TableStyle = "TableStyleMedium2"

                        '~~> Create Borders
                        With xlSheet6.UsedRange

                            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

                        xlSheet6.Columns.AutoFit()
                    End Using

                End Using

            End Using

            ''NAME EXCEL SHEET 2''
            xlSheet5 = CType(xlWorkBook.Worksheets("Sheet5"), Excel.Worksheet)
            xlSheet5.Activate()
            xlSheet5.Name = "9-2-2014 Q"
            xlSheet5.Tab.ColorIndex = CType(6, Excel.XlColorIndex)

        'Run SQL'
            Using sheet5sda As New SqlDataAdapter()

                Using sheet5cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet5cmd.CommandTimeout = 0
                    sheet5cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
                    sheet5cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet5sda.SelectCommand = sheet5cmd

                    Using ds As New DataSet

                        sheet5sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet5.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet5.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next



                        'Format Table'

                        xlSheet5.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet5.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet5.ListObjects("Table1").TableStyle = "TableStyleMedium4"

                        '~~> Create Borders
                        With xlSheet5.UsedRange

                            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

                        xlSheet5.Columns.AutoFit()
                    End Using
                End Using
            End Using



            ''NAME EXCEL SHEET 3''
            xlSheet4 = CType(xlWorkBook.Worksheets("Sheet4"), Excel.Worksheet)
            xlSheet4.Activate()
            xlSheet4.Name = "10-13-2014 Q"
            xlSheet4.Tab.ColorIndex = CType(9, Excel.XlColorIndex)

            'Run SQL'
            Using sheet4sda As New SqlDataAdapter()

                Using sheet4cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet4cmd.CommandTimeout = 0
                    sheet4cmd.Parameters.AddWithValue("@PROGRAM_START", "10/13/2014")
                    sheet4cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet4sda.SelectCommand = sheet4cmd

                    Using ds As New DataSet

                        sheet4sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet4.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet4.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

                        xlSheet4.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet4.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet4.ListObjects("Table1").TableStyle = "TableStyleMedium1"

                        '~~> Create Borders
                        With xlSheet4.UsedRange

                            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

                        xlSheet4.Columns.AutoFit()
                    End Using
                End Using
            End Using


            ''NAME EXCEL SHEET 4''
            xlSheet1 = CType(xlWorkBook.Worksheets("Sheet1"), Excel.Worksheet)
            xlSheet1.Activate()
            xlSheet1.Name = "10-27-2014 S"
            xlSheet1.Tab.ColorIndex = CType(29, Excel.XlColorIndex)

            'Run SQL'
            Using sheet1sda As New SqlDataAdapter()

                Using sheet1cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet1cmd.CommandTimeout = 0
                    sheet1cmd.Parameters.AddWithValue("@PROGRAM_START", "10/27/2014")
                    sheet1cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet1sda.SelectCommand = sheet1cmd

                    Using ds As New DataSet

                        sheet1sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet1.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet1.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

                        xlSheet1.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet1.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet1.ListObjects("Table1").TableStyle = "TableStyleMedium3"

                        'Create Borders
                        With xlSheet1.UsedRange

                            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
                    End Using
                End Using
            End Using


            xlSheet1.Columns.AutoFit()


            ''NAME EXCEL SHEET 5''
            xlSheet2 = CType(xlWorkBook.Worksheets("Sheet2"), Excel.Worksheet)
            xlSheet2.Activate()
            xlSheet2.Name = "12-01-2014 Q"
            xlSheet2.Tab.ColorIndex = CType(12, Excel.XlColorIndex)

            'Run SQL'
            Using sheet2sda As New SqlDataAdapter()

                Using sheet2cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet2cmd.CommandTimeout = 0
                    sheet2cmd.Parameters.AddWithValue("@PROGRAM_START", "12/1/2014")
                    sheet2cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet2sda.SelectCommand = sheet2cmd

                    Using ds As New DataSet

                        sheet2sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet2.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet2.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

                        xlSheet2.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet2.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet2.ListObjects("Table1").TableStyle = "TableStyleMedium6"

                        '~~> Create Borders
                        With xlSheet2.UsedRange

                            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

                        xlSheet2.Columns.AutoFit()

                    End Using
                End Using
            End Using



            ''NAME EXCEL SHEET 6''
            xlSheet3 = CType(xlWorkBook.Worksheets("Sheet3"), Excel.Worksheet)
            xlSheet3.Activate()
            xlSheet3.Name = "01-05-2015 S"
            xlSheet3.Tab.ColorIndex = CType(22, Excel.XlColorIndex)

            'Run SQL'
            Using sheet3sda As New SqlDataAdapter()

                Using sheet3cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    sheet3cmd.CommandTimeout = 0
                    sheet3cmd.Parameters.AddWithValue("@PROGRAM_START", "1/5/2015")
                    sheet3cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet3sda.SelectCommand = sheet3cmd

                    Using ds As New DataSet

                        sheet3sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet3.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet3.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next



                        'Format Table'

                        xlSheet3.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet3.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet3.ListObjects("Table1").TableStyle = "TableStyleMedium9"

                        '~~> Create Borders
                        With xlSheet3.UsedRange

                            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

                        xlSheet3.Columns.AutoFit()
                    End Using
                End Using
            End Using


            ''NAME EXCEL SHEET 7''
            xlSheet7 = CType(xlWorkBook.Worksheets("Sheet7"), Excel.Worksheet)
            xlSheet7.Activate()
            xlSheet7.Name = "Summary"
            xlSheet7.Tab.ColorIndex = CType(14, Excel.XlColorIndex)

            xlApp.Visible = True

        End Using


        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlSheet6)
        releaseObject(xlSheet5)
        releaseObject(xlSheet4)
        releaseObject(xlSheet1)
        releaseObject(xlSheet2)
        releaseObject(xlSheet3)
        releaseObject(xlSheet7)

    End Sub

    Private Sub releaseObject(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
End Class

Open in new window

0
Comment
Question by:cmed
  • 6
  • 4
  • 2
  • +1
15 Comments
 

Author Comment

by:cmed
Comment Utility
Thanks
0
 
LVL 35

Expert Comment

by:Miguel Oz
Comment Utility
What do you need exactly?
e.g. Minimize code duplication: Create new methods to avoid logic duplication.
e.g. Code complexity: if you are comfortable maintaining only methods and/or classes and/or generics, etc.

Notes: 1) I assume this code is working fine.
2) Performance: Code constructs could be slow depending of number of rows and columns to fill, but I suppose this could be another question.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
I would do two things:

1) separate SQL data retrival from the Excel generation.
2) use Excel templates with macros for the formatting.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
I created a routine to populate and format the sheets.  Please test this.
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
                & "Trusted_Connection=Yes;"

    Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlApp.ScreenUpdating = False
        
        xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlWorkBook.Sheets.Add(, , 4)

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            PopulateFormatSheet cnn, xlWorkBook.Worksheets("Sheet6"), #9/2/2014#, "S", 3
            PopulateFormatSheet cnn, xlWorkBook.Worksheets("Sheet5"), #9/2/2014#, "Q", 6
            PopulateFormatSheet cnn, xlWorkBook.Worksheets("Sheet4"), #10/3/2014#, "Q", 9
            PopulateFormatSheet cnn, xlWorkBook.Worksheets("Sheet2"), #12/1/2014#, "Q", 12
            PopulateFormatSheet cnn, xlWorkBook.Worksheets("Sheet3"), #1/5/2015#, "S", 22
            
        End Using

        xlWorkSheet = CType(xlWorkBook.Worksheets("Sheet7"), Excel.Worksheet)
        xlWorkSheet.Name = "Summary"
        xlWorkSheet.Tab.ColorIndex = CType(14, Excel.XlColorIndex)

        xlApp.ScreenUpdating = True
        xlApp.Visible = True

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub PopulateFormatSheet(parmConn, parmSheet, parmDate, parmTerm, parmColor)
        parmSheet.Name = parmDate & " " & parmTerm      '"9-2-2014 S"
        parmSheet.Tab.ColorIndex = CType(parmColor, Excel.XlColorIndex)

        Using da As New SqlDataAdapter()

            Using cmd As New SqlCommand(My.Resources.SQL_new_student_metric, parmConn)

                cmd.CommandTimeout = 0
                cmd.Parameters.AddWithValue("@PROGRAM_START", parmDate.ToString)
                cmd.Parameters.AddWithValue("@TERM_TYPE", parmTerm)
                da.SelectCommand = sheet6cmd

                Using ds As New DataSet

                    da.Fill(ds)

                    'Display Headers, Columns, and Rows'

                    For i = 0 To ds.Tables(0).Columns.Count - 1
                        parmSheet.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                    Next
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        For j = 0 To ds.Tables(0).Columns.Count - 1
                            parmSheet.Cells(i + 2, j + 1) = ds.Tables(0).Rows(i).Item(j)
                        Next
                    Next

                    'Format Table'

                    parmSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet6.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                    parmSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"

                    '~~> Create Borders
                    With parmSheet.UsedRange

                        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

                    parmSheet.Columns.AutoFit()
                End Using

            End Using
    
    End Sub
    
    Private Sub releaseObject(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
End Class

Open in new window

0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
@Alimark, nice work.

There is an OO principle called Separation of Concerns. This should be also applied to methods. So I would strongly recommend, that the overloaded method PopulateFormatSheet should be split into a Populate and one Format method.
0
 

Author Comment

by:cmed
Comment Utility
@ Miguel,

I just want to figure out a way to avoid logic duplication.
0
 

Author Comment

by:cmed
Comment Utility
@alimark

Thank you for the code, but I am running into another issue.  Please see the screencapture. Any tips. Calvin-code-error.PNG
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@ste5an

I understand those principles.  I did a quick edit by hand on the code I posted and did not use any refactoring tools.  The posted version of the code was rudimentary.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@Calvin

try parmDate.ToString
0
 

Author Comment

by:cmed
Comment Utility
@Aikimark

I will try it in a few.  I had to add somethings to my sheet7.
0
 
LVL 35

Expert Comment

by:Miguel Oz
Comment Utility
Actually ToString will return the wrong name too ("9/2/2014 12:00:00 AM")
You should change your code and defined parmDate as string parameter.
Private Sub PopulateFormatSheet(ByVal parmConn, ByVal parmSheet, ByVal parmDate As String, ByVal parmTerm As String, ByVal parmColor As Integer)
and change the callers to string. ("9-2-2014")

Some extra suggestions:
- Do not use ByVal myParameter, the compiler will interpret it as Object if the value passed is not a variable. Use the appropriate type for your parameters.
- The code suggested by Aikimark is leaking all the sheet instances. (It looks like VBA code)
- When releasing COM objects, your code should release them in the reverse creation order (Application instance should be the last one):
        releaseObject(xlSheet7)
        releaseObject(xlSheet6)
        releaseObject(xlSheet5)
        releaseObject(xlSheet4)
        releaseObject(xlSheet3)
        releaseObject(xlSheet2)
        releaseObject(xlSheet1)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)

Open in new window

Explanation: xlWorkBook will not be released from memory until all sheets are gone. xlApp will not be released from memory until all workbooks are gone.
0
 

Author Comment

by:cmed
Comment Utility
@Miguel

Thanks for the releasing info.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The date string is messing you up.  You can't use a slash character in the worksheet name.  You will probably need to use the Format() function.
0
 

Author Comment

by:cmed
Comment Utility
@ aikimark

I complete this almost a month ago.  I am sorry that I did not update.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now