How do I populate multiple worksheets in a workbook from

Posted on 2010-08-31
Last Modified: 2012-05-10
I have developed the following code (with the frequent help of experts-exchange) that loops through checked items in a datagridview and populates workbooks.  So for example, if three items are selected from a datagridview, those items are used to populate three different workbooks with the corresponding data pertaining to that selection.  I'm now trying to modify the code so that instead of populating different workbooks -  Yes you've guessed it, I now want to populate mutiple sheets in a single workbook.  So if five items are selected in the datagridview, those five items would be used to populate five sheets in a workbook
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim dgl As New List(Of DatasetGroup)

        For Each row As DataGridViewRow In dgvDatasetGroups.Rows

            Dim cell As DataGridViewCell = row.Cells(8)

            If CBool(cell.Value) = True Then

                Dim dg As New DatasetGroup

                Dim indexcell As DataGridViewCell = row.Cells(0)

                dg.DatasetGroupId = indexcell.Value


            End If


        For Each dg As DatasetGroup In dgl




    End Sub

    Private Sub CreateGraphTable(ByVal intDatasetGroupId) 'As DataTable

        Dim graphs As New List(Of GraphReport)

        Dim dset As New DataSet("MyDataSet")

        Dim strTitleName As String = ""

        Dim strUnits As String = ""

        Dim dpl As New List(Of DatasetPair)


            dpl = DatasetPairDB.GetExportDatasetGroupDetail(intDatasetGroupId)

            For Each dp As DatasetPair In dpl

                Dim objgraph As New GraphReport

                objgraph.DataVersionIdOne = dp.DataVersionIdOne.ToString

                objgraph.DataVersionIdTwo = dp.DataVersionIdTwo.ToString

                objgraph.MetricName = dp.MetricName.ToString

                objgraph.GraphTitle = "TEST"

                objgraph.GraphType = dp.GraphType



            For Each g As GraphReport In graphs

                If MatchingMetricUnits(g) = Nothing Then

                    MessageBox.Show(g.DataVersionIdOne.ToString & " and " & g.DataVersionIdTwo.ToString & " do not have corresponding units!", "Erro")

                    'Return Nothing

                    Exit Sub


                    strUnits = MatchingMetricUnits(g)

                    Units = strUnits

                End If

                Dim ds As New DataTable

                ds = ReportDB.GetDeltaSeriesData(g.DataVersionIdOne, g.DataVersionIdTwo, g.MetricName)

                Dim dtgv As New DataTable

                dtgv = ReportDB.GetDeltaReportData(g.DataVersionIdOne, g.DataVersionIdTwo, g.MetricName)

                If dtgv.Rows.Count < 1 Then

                    MessageBox.Show("No Data")

                    Exit Sub

                End If

                Dim newtable As New DataTable


                For Each table As DataTable In dset.Tables



                dgvGraphResults.DataSource = newtable

                Me.DgvTable = newtable

                Dim strSeriesName As String = g.DataVersionIdOne.ToString & " - " & g.DataVersionIdTwo.ToString

                MySeriesName = strSeriesName

                strTitleName = g.GraphTitle.ToString

                MyMetricName = g.GraphTitle.ToString

                MyGraphStyle = g.GraphType


            Dim strGraphStyle As String = MyGraphStyle

            Utilities.ExportMultipleGraphToExcel(DgvTable, MyMetricName, Units, strGraphStyle)

        Catch ex As Exception

            MessageBox.Show(ex.Message.ToString & Err.Number.ToString)

        End Try

    End Sub

    Private Function MatchingMetricUnits(ByVal g As GraphReport) As String

        Dim strMetricUnitsOne As String

        Dim strMetricUnitsTwo As String

        strMetricUnitsOne = MetricsDB.GetMetricUnits(g.DataVersionIdOne.ToString, g.MetricName.ToString)

        strMetricUnitsTwo = MetricsDB.GetMetricUnits(g.DataVersionIdTwo.ToString, g.MetricName.ToString)

        If strMetricUnitsOne = strMetricUnitsTwo Then

            Return strMetricUnitsOne


            Return Nothing

        End If

    End Function

Open in new window

Question by:chtullu135
  • 3
LVL 20

Expert Comment

ID: 33570837
All the hard work is done by a function called "Utilities.ExportMultipleGraphToExcel" which you have not shown here.

If that is not a custom function that you have access to the code for, then you will have to rewrite the entire feature from scratch.

Author Comment

ID: 33570901
My mistake.  I forgot to include the code for that function. I've attached it below
 Public Shared Sub ExportMultipleGraphToExcel(ByVal datTable As DataTable, ByVal strMetricName As String, ByVal strUnits As String, ByVal strGraphStyle As String)

        Dim filename As String

        Dim excelApp As New Excel.Application

        Dim savefiledialog1 As New SaveFileDialog

        If My.Settings.DefaultLocation = "" Then

            savefiledialog1.InitialDirectory = "C:\"


            savefiledialog1.InitialDirectory = My.Settings.DefaultLocation.ToString

        End If

        savefiledialog1.Filter = "xlsx files (*.xlsx)|*.xlsx"

        savefiledialog1.FilterIndex = 2

        savefiledialog1.RestoreDirectory = True


        '***************************Creates Spreadsheet*********************************************

        If savefiledialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            filename = savefiledialog1.FileName

            If Not datTable Is Nothing AndAlso datTable.Rows.Count > 0 Then

                'Validate file name 

                If Convert.ToString(filename) = "" Then Exit Sub

                'Create excel instance 

                Dim excelWorkbook As Excel.Workbook

                Dim excelSheet As Excel.Worksheet

                Dim misValue As Object = System.Reflection.Missing.Value

                excelApp = New Excel.ApplicationClass

                'excelApp.Visible = True

                excelWorkbook = excelApp.Workbooks.Add(misValue)

                excelSheet = excelWorkbook.Sheets("sheet1")

                excelApp.ScreenUpdating = True


                excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper

                ' Dim visibleColCount As Integer = 0 

                Dim i As Integer

                Dim j As Integer

                ' Copy array of object for store data  

                Dim xlDataTable As DataTable = Nothing

                xlDataTable = datTable

                ' xlDataTable.Columns.Remove("BudgetYear")




                Dim rawData(xlDataTable.Rows.Count, xlDataTable.Columns.Count - 1) As Object

                'Counter for adding visible columns to array  

                Dim colCounter As Int16 = 0

                For i = 0 To xlDataTable.Columns.Count - 1

                    'Str += dt.Columns(i).HeaderText & vbTab 

                    rawData(0, colCounter) = xlDataTable.Columns(i).ColumnName

                    colCounter += 1


                'Counter for add rows in array 

                For i = 0 To xlDataTable.Rows.Count - 1

                    colCounter = 0

                    For j = 0 To xlDataTable.Columns.Count - 1

                        ' Copy the values to the object array                              

                        rawData(i + 1, colCounter) = xlDataTable.Rows(i)(j)

                        colCounter += 1



                ' Calculate the final column letter 

                Dim finalColLetter As String = String.Empty

                Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

                Dim colCharsetLen As Integer = colCharset.Length

                If xlDataTable.Columns.Count > colCharsetLen Then

                    finalColLetter = colCharset.Substring( _

                     (xlDataTable.Columns.Count - 1) \ colCharsetLen - 1, 1)

                End If

                finalColLetter += colCharset.Substring((xlDataTable.Columns.Count - 1) Mod colCharsetLen, 1)

                ' Fast data export to Excel 

                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, xlDataTable.Rows.Count + 1)

                '****The following code is used to create variables that will be used in the Create Graph section****

                'Creates an array to hold the upper and lower limits of the range of the excel table

                Dim strExcelRange As String() = excelRange.Split(CChar(":"))

                'Stores the first cell in the range

                Dim strFirstCell As String = strExcelRange(0)

                'Stores the last cell in the range

                Dim strLastCell As String = strExcelRange(1)


                excelSheet.Range(excelRange, misValue).Value2 = rawData

                ' Mark the first row as BOLD 

                CType(excelSheet.Rows(1, Type.Missing), Excel.Range).Font.Bold = True

                '---------------------------Create Graph----------------------------------------------

                'create chart objects

                Dim oChart As Excel.Chart

                Dim MyCharts As Excel.ChartObjects

                Dim MyCharts1 As Excel.ChartObject

                MyCharts = excelSheet.ChartObjects

                'set chart location

                MyCharts1 = MyCharts.Add(150, 290, 400, 250)

                oChart = MyCharts1.Chart

                'use the follwoing line if u want 

                'to draw chart on the default location

                oChart.Location(Excel.XlChartLocation.xlLocationAsObject, excelSheet.Name)

                Dim R As Integer = xlDataTable.Rows.Count + 1


                With oChart

                    'set data range for chart

                    Dim chartRange As Excel.Range

                    chartRange = excelSheet.Range(strFirstCell, strLastCell)


                    'set how you want to draw chart i.e column wise or row wise

                    '.PlotBy = Excel.XlRowCol.xlColumns

                    .PlotBy = Excel.XlRowCol.xlRows


                    'MessageBox.Show("Total Number of dataseries " & .SeriesCollection.count.ToString)

                    'set data lables for bars


                    'set legend to be displayed or not

                    .HasLegend = True

                    'set legend location

                    .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight

                    'select chart type

                    Select Case strGraphStyle

                        Case "xlLine"

                            .ChartType = Excel.XlChartType.xlLine

                            For Each excChartSeries As Excel.Series In oChart.SeriesCollection

                                excChartSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleAutomatic


                        Case "xlColumnClustered"

                            .ChartType = Excel.XlChartType.xlColumnClustered

                        Case "xlAreaStacked"

                            .ChartType = Excel.XlChartType.xlAreaStacked

                        Case "xlColumnStacked"

                            .ChartType = Excel.XlChartType.xlColumnStacked

                    End Select

                    Dim Headers As String() = Nothing

                    'Set to number of new headers

                    Dim intH As Integer = 1

                    ReDim Headers(intH)

                    Headers(0) = "MetricName"

                    Dim strx As String = "A1"

                    Dim c As Integer = 0

                    'For c As Integer = 0 To 1


                    excelApp.Range(strx).Value = Headers(c).ToString

                    'Next c

                    For t As Integer = 1 To chartRange.Rows.Count

                        If t = 1 Then

                            Continue For


                            Dim z As String = ""

                            'z = excelApp.Range("A" & t).Value.ToString

                            ' Dim strDvid As String() = z.Split(CChar("-"))

                            Dim strT As String = t.ToString

                            excelApp.Range("A" & strT).Value = strMetricName

                            excelSheet.Range("A:A").HorizontalAlignment = Excel.Constants.xlCenter


                        End If


                    'chart title

                    .HasTitle = True

                    .ChartTitle.Text = strMetricName

                    'set titles for Axis values and categories

                    Dim xlAxisCategory, xlAxisValue As Excel.Axes

                    xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

                    xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True

                    xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Forecast Years"

                    If xlDataTable.Columns.Count > colCharsetLen Then

                        finalColLetter = colCharset.Substring( _

                         (xlDataTable.Columns.Count - 1) \ colCharsetLen - 1, 1)

                    End If

                    finalColLetter += colCharset.Substring( _

                  (xlDataTable.Columns.Count - 1 + intH) Mod colCharsetLen, 1)

                    excelRange = String.Format("A1:{0}{1}", finalColLetter, xlDataTable.Rows.Count + 1)

                    '****The following code is used to create variables that will be used in the Create Graph section****

                    'Creates an array to hold the upper and lower limits of the range of the excel table

                    Dim strChartRange As String() = excelRange.Split(CChar(":"))

                    'Stores the first cell in the range

                    strFirstCell = strChartRange(0)

                    'Stores the last cell in the range

                    strLastCell = strChartRange(1)

                    'MessageBox.Show(Mid(strLastCell, 1, Len(strLastCell) - 1).ToString)

                    .SeriesCollection(1).XValues = "=Sheet1!$C$1:$" & Mid(strLastCell, 1, Len(strLastCell) - 1) & "$1"

                    '.SeriesCollection(1).XValues = "=Sheet1!$F$1:$N$1"

                    xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

                    xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True

                    xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = strUnits

                    excelSheet.Range("A2", strLastCell).NumberFormat = "0.0"

                    MessageBox.Show("Chart Completed")

                End With

                'Set file format number to 56 if excel version <> 2007 

                If Not filename.EndsWith(".xlsx") Then

                    excelWorkbook.SaveAs(filename, 56)



                End If

                MsgBox("File generated successfully at " & filename)

            End If

        End If

        'Catch ex As Exception



        If Not excelApp.Workbooks Is Nothing Then

            Dim wb As Microsoft.Office.Interop.Excel.Workbook

            Dim ws As Microsoft.Office.Interop.Excel.Worksheet

            For Each wb In excelApp.Workbooks

                For Each ws In wb.Worksheets


                    ws = Nothing




                wb = Nothing



        End If

        excelApp.DisplayAlerts = False




        If excelApp IsNot Nothing Then

            Dim excelProcessId As Integer

            GetWindowThreadProcessId(New IntPtr(excelApp.Hwnd), excelProcessId)

            If excelProcessId > 0 Then


            End If

        End If

        'End Try

    End Sub

Open in new window


Author Comment

ID: 33571866
I've had some additional thoughts.  Why don't I create a function called create workbook which would create a workbook and return the file path of that workbook.  I could call that function from the click event of the export button.  I can then pass the returned file path to other code which could open it and populate the workbooks sheets via a loop.  I'm just thinkng out loud here
LVL 33

Accepted Solution

Norie earned 500 total points
ID: 33575852
Could you not pass the worksheet to the function that's creating the charts?

It could replace adding a workbook and referencing the worksheet in that workbook.

Or pass a reference to the workbook you want to put the graphs in.

If you passed the worksheet you could be specific about where the charts went.

You could even create or open a workbook and then create a new worksheet in it and
then pass that as the worksheet for the charts.

PS This is just thinking out loud too - don't have time to test anything.

Author Closing Comment

ID: 33578639
Thanks.  Your advice put me on the right track

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 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

20 Experts available now in Live!

Get 1:1 Help Now