Solved

What is  System.Reflection.Missing.Value

Posted on 2010-09-01
18
1,608 Views
Last Modified: 2012-05-10
Hello,
I'm trying to debug a vb.net program.  It uses automation to create graphs and it is failing at  
 Dim misValue As Object = System.Reflection.Missing.Value
I've been trying to find out what System.Reflection.Missing.Value is and what it does
0
Comment
Question by:chtullu135
  • 9
  • 9
18 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33581070
maybe this as described here http://msdn.microsoft.com/en-us/library/system.reflection.missing.value.aspx
used to hold something for handle default parameter values on functions
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 33581083
code example here, easier than me trying to explain lol http://msdn.microsoft.com/en-us/library/system.reflection.missing.aspx
0
 

Author Comment

by:chtullu135
ID: 33581344
I've got a hazy idea of what it does.  Basically it substitutes for a missing argument.  Basically I am getting an com error on the line .  I am trying to pass a worksheet to a function where a graph will be added to it.
excelSheet.Range(excelRange, misValue).Value2 = rawData
 Public Shared Sub ExportMultipleGraphToExcel(ByVal datTable As DataTable, ByVal strMetricName As String, ByVal strUnits As String, ByVal strGraphStyle As String, ByVal MyWs As Excel.Worksheet)

        'Dim filename As String

        Dim excelApp As New Excel.Application

        'Try

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



            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(MyWs.Name)

            excelSheet = MyWs

            'excelApp.ScreenUpdating = True

            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)

            '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")

            xlDataTable.Columns.Remove("MetricName")

            'xlDataTable.Columns.Remove("Units")

            'xlDataTable.Columns.Remove("TechnologySetCode")



            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

            Next

            '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

                Next

            Next

            ' 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

            'MessageBox.Show(R.ToString)

            With oChart

                'set data range for chart

                Dim chartRange As Excel.Range

                chartRange = excelSheet.Range(strFirstCell, strLastCell)

                .SetSourceData(chartRange)

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

                '.PlotBy = Excel.XlRowCol.xlColumns

                .PlotBy = Excel.XlRowCol.xlRows



                .SeriesCollection(1).Delete()

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



                'set data lables for bars

                .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

                '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

                        Next

                    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("A:A").Insert(Excel.XlDirection.xlToRight)



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

                'Next c



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

                    If t = 1 Then

                        Continue For

                    Else



                        Dim z As String = ""



                        Dim strT As String = t.ToString



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



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





                    End If

                Next



                '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)



            'Else

            '    excelWorkbook.SaveAs(filename)

            'End If



            MsgBox("File generated successfully at ") '& filename)



        End If





        'Catch ex As Exception

        'MessageBox.Show(ex.Message.ToString)



        ' Finally

        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

                    Marshal.FinalReleaseComObject(ws)

                    ws = Nothing

                Next

                wb.Close(False)

                Marshal.FinalReleaseComObject(wb)

                wb = Nothing

            Next

            excelApp.Workbooks.Close()

        End If

        excelApp.DisplayAlerts = False

        excelApp.Quit()

        GC.Collect()

        GC.WaitForPendingFinalizers()



        If excelApp IsNot Nothing Then

            Dim excelProcessId As Integer

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



            If excelProcessId > 0 Then

                KillExcel(excelApp)

            End If

        End If





        'End Try



    End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33581541
In this line
excelWorkbook = excelApp.Workbooks.Add(misValue)

Can you get away with not having to specify misValue? I think it takes a template name which is optional anyway.


Regarding the dodgy line, I am not sure what you are trying to do there. Setting a range will work with just one argument surely

excelSheet.Range(excelRange).Value2 = rawData

the 2nd argument is if you want to specify another cell.
0
 

Author Comment

by:chtullu135
ID: 33581679
I've tried as you suggested and received the following erro at line
 excelSheet.Range(excelRange).Value2 = rawData
System.Runtime.InteropServices.COMException was unhandled

  ErrorCode=-2147221080

  Message="Exception from HRESULT: 0x800401A8"

  Source="Microsoft.Office.Interop.Excel"

  StackTrace:

       at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)

       at BADS.Utilities.ExportMultipleGraphToExcel(DataTable datTable, String strMetricName, String strUnits, String strGraphStyle, Worksheet& MyWs) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\Utilities.vb:line 295

       at BADS.frmExportGraph.CreateGraphTable(Int32 intDatasetGroupId, String strName, Worksheet ws) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmExportGraph.vb:line 218

       at BADS.frmExportGraph.btnExport_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmExportGraph.vb:line 149

       at System.Windows.Forms.Control.OnClick(EventArgs e)

       at System.Windows.Forms.Button.OnClick(EventArgs e)

       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

       at System.Windows.Forms.Control.WndProc(Message& m)

       at System.Windows.Forms.ButtonBase.WndProc(Message& m)

       at System.Windows.Forms.Button.WndProc(Message& m)

       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

       at System.Windows.Forms.Application.RunDialog(Form form)

       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)

       at System.Windows.Forms.Form.ShowDialog()

       at BADS.frmMultipleDatasetGraphs.ExportGraphsToolStripMenuItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\jvelasqu\My Documents\Visual Studio 2008\Projects\BADS\BADS\frmMultipleDatasetGraphs.vb:line 757

       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)

       at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)

       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)

       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)

       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)

       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)

       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)

       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

       at System.Windows.Forms.Control.WndProc(Message& m)

       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

       at System.Windows.Forms.ToolStrip.WndProc(Message& m)

       at System.Windows.Forms.MenuStrip.WndProc(Message& m)

       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

       at System.Windows.Forms.Application.Run(ApplicationContext context)

       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

       at BADS.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: 

Open in new window

0
 

Author Comment

by:chtullu135
ID: 33581862
Basically, What I am trying to do is pass a worksheet to the subroutine ExportMultipleGraphToExcel and populate it with a graph.  After it is populated, the next worksheet is populated with a graph and so on
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33581956
brb, getting visual studio 2010 installed. I will double check it then
0
 

Author Comment

by:chtullu135
ID: 33582129
Thanks
0
 

Author Comment

by:chtullu135
ID: 33582296
Here is the complete code as well as an explanation
I have a form called ExportGraph.  It has a button called btnExport which when clicked loops through a datagridview in order to populate a list object called dgl

I then call Utilities.CreateWorkbook() in order to create a workbook.  I then loop through dgl in order to add a new worksheet for every object in the list to the workbook I created.  This is done via function OpenWorkbook.  I return a worksheet from OpenWorkbook which I then pass to subroutine CreateGraphTable.  At the end of this subroutine I call the ExportMultipleGraphToExcel subroutine which is where the graphs are created and where they populate specific spreadsheet.  This is where I am having my problem
 Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim dgl As New List(Of DatasetGroup)

        'Try

        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

                cell = row.Cells(1)

                dg.Name = cell.Value

                dgl.Add(dg)

            End If

        Next



        Dim strWorkbookPath As String = Utilities.CreateWorkbook()

        MessageBox.Show(strWorkbookPath, "Returned Workbook Path")

        For Each dg As DatasetGroup In dgl

            Dim strName As String = dg.Name.ToString

            Dim ws As New Excel.Worksheet

            ws = Utilities.OpenWorkbook(strWorkbookPath, strName)

            CreateGraphTable(Convert.ToInt32(dg.DatasetGroupId), strName, ws)

        Next

        'Catch ex As Exception

        'MessageBox.Show(ex.Message, ex.GetType.ToString)

        'End Try

    End Sub



 Public Shared Function CreateWorkbook() As String

        Dim filename As String

        Dim excelApp As Excel.Application

        Dim excelWorkbook As Excel.Workbook

        Dim excelSheet As Excel.Worksheet

        Dim SaveFileDialog As New SaveFileDialog







        If My.Settings.DefaultLocation = "" Then

            SaveFileDialog.InitialDirectory = "C:\"

        Else

            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.ToString

        End If



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

        SaveFileDialog.FilterIndex = 2

        SaveFileDialog.RestoreDirectory = True



        'Try

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



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



            filename = SaveFileDialog.FileName



            'Validate file name

            If Convert.ToString(filename) = "" Then

                Return Nothing

                Exit Function

            End If



            'Create excel instance

            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

            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)

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

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

                excelWorkbook.SaveAs(filename, 56)

            Else

                excelWorkbook.SaveAs(filename)

            End If



            MsgBox("File generated successfully at " & filename)

            excelWorkbook.Close()

            excelApp.Quit()

            excelWorkbook = Nothing

            excelApp = Nothing

            Return filename

        Else

            Return Nothing

        End If

        KillExcel(excelApp)

    End Function



Public Shared Function OpenWorkbook(ByVal strFilePath, ByVal strWorkSheetName) As Excel.Worksheet



        'declares the variables

        Dim excelApp As New Excel.Application

        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)

        Dim ws As Excel.Worksheet = Nothing

        'try to open the workbook and a worksheet

        Try

            excelBook = excelApp.Workbooks.Open(strFilePath)

            ws = excelBook.Worksheets.Add

            excelApp.Visible = True

            If Len(strWorkSheetName) > 31 Then

                strWorkSheetName = InputBox("The length of " & strWorkSheetName & " exceeds the limit of 31 characters " & vbCrLf & "Please enter a name not exceeding 31 characters", "Max Size Exceeded")

            End If

            ws.Name = strWorkSheetName

            Return ws

        Catch ex As Exception

            Return Nothing

            MsgBox(ex.Message, ex.GetType.ToString)

        Finally

            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this

            'The service (excel.exe) will continue to run

            excelBook.Close()

            excelApp.Quit()



            excelBook = Nothing

            excelApp = Nothing



            NAR(excelApp)

            'VERY IMPORTANT

            GC.Collect()

        End Try



    End Function

    Public Shared Sub NAR(ByVal o As Object)

        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

        Catch ex As Exception

        Finally

            o = Nothing

        End Try

    End Sub



 Private Sub CreateGraphTable(ByVal intDatasetGroupId As Integer, ByVal strName As String, ByVal ws As Excel.Worksheet) '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)

        'Try

        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 = strName

            objgraph.GraphType = dp.GraphType

            graphs.Add(objgraph)

        Next



        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

            Else

                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



            dset.Tables.Add(dtgv)

            For Each table As DataTable In dset.Tables

                newtable.Merge(table)

            Next

            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



        Next

        Dim strGraphStyle As String = MyGraphStyle

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

        'Catch ex As Exception

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

        'End Try

    End Sub



  'Dim filename As String

        Dim excelApp As New Excel.Application

        'Try

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



            Dim excelWorkbook As Excel.Workbook

            Dim excelSheet As Excel.Worksheet

            MessageBox.Show(System.Reflection.Missing.Value.ToString)

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

            'excelApp = New Excel.ApplicationClass

            'excelApp.Visible = True



            ' excelWorkbook = excelApp.Workbooks.Add(misValue)

            'excelSheet = excelWorkbook.Sheets(MyWs.Name)

            excelSheet = MyWs



            'excelApp.ScreenUpdating = True

            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)

            '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")

            xlDataTable.Columns.Remove("MetricName")

            'xlDataTable.Columns.Remove("Units")

            'xlDataTable.Columns.Remove("TechnologySetCode")



            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

            Next

            '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

                Next

            Next

            ' 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

            'excelSheet.Range(excelRange).Value2 = rawData

            excelSheet.Range(excelRange).Value = 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

            'MessageBox.Show(R.ToString)

            With oChart

                'set data range for chart

                Dim chartRange As Excel.Range

                chartRange = excelSheet.Range(strFirstCell, strLastCell)

                .SetSourceData(chartRange)

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

                '.PlotBy = Excel.XlRowCol.xlColumns

                .PlotBy = Excel.XlRowCol.xlRows



                .SeriesCollection(1).Delete()

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



                'set data lables for bars

                .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

                '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

                        Next

                    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("A:A").Insert(Excel.XlDirection.xlToRight)



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

                'Next c



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

                    If t = 1 Then

                        Continue For

                    Else



                        Dim z As String = ""



                        Dim strT As String = t.ToString



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



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





                    End If

                Next



                '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)



            'Else

            '    excelWorkbook.SaveAs(filename)

            'End If



            MsgBox("File generated successfully at ") '& filename)



        End If





        'Catch ex As Exception

        'MessageBox.Show(ex.Message.ToString)



        'Finally

        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

                    Marshal.FinalReleaseComObject(ws)

                    ws = Nothing

                Next

                wb.Close(False)

                Marshal.FinalReleaseComObject(wb)

                wb = Nothing

            Next

            excelApp.Workbooks.Close()

        End If

        excelApp.DisplayAlerts = False

        excelApp.Quit()

        GC.Collect()

        GC.WaitForPendingFinalizers()



        If excelApp IsNot Nothing Then

            Dim excelProcessId As Integer

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



            If excelProcessId > 0 Then

                KillExcel(excelApp)

            End If

        End If





        'End Try



    End Sub

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:rockiroads
ID: 33582887
Right, regarding the initial problem, trying to understand why setting the range value fails. I have a new install and added excel library as well as used excel namespace yet I can't use early binding. I must be missing something simple.
Anyways, I decided to knock up a test bed using late binding so here I am creating object.

I manually created a datatable and tried to dump that, set your rawdata, convert to dataset but failed to get it going.
So another option, not so elegant but works, is to iterate thru your datatable


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim excelApp As Object
        Dim excelWorkbook As Object
        Dim excelSheet As Object
        Dim excelRange As Object
        Dim sRange As String = "A1:B3"
        Dim xlDataTable As DataTable = Nothing
        Dim row1, row2, row3 As DataRow

        xlDataTable = New DataTable("ExampleExcelAuto")
        Dim fruit As DataColumn = New DataColumn("Fruit")
        fruit.DataType = System.Type.GetType("System.String")
        xlDataTable.Columns.Add(fruit)

        Dim col As DataColumn = New DataColumn("Colour")
        col.DataType = System.Type.GetType("System.String")
        xlDataTable.Columns.Add(col)

        row1 = xlDataTable.NewRow()
        row1.Item("Fruit") = "Apple"
        row1.Item("Colour") = "Red"
        xlDataTable.Rows.Add(row1)
        row2 = xlDataTable.NewRow()
        row2.Item("Fruit") = "Bananas"
        row2.Item("Colour") = "Yellow"
        xlDataTable.Rows.Add(row2)
        row3 = xlDataTable.NewRow()
        row3.Item("Fruit") = "Grapes"
        row3.Item("Colour") = "Black"
        xlDataTable.Rows.Add(row3)

        Dim ds As New DataSet()
        ds = New DataSet()
        'creating a dataset
        ds.Tables.Add(xlDataTable)

        'xlDataTable.Columns.Remove("MetricName")
        Dim rawData(xlDataTable.Rows.Count, xlDataTable.Columns.Count) As Object

        excelApp = CreateObject("Excel.Application")
        excelApp.visible = True
        excelWorkbook = excelApp.Workbooks.Add
        excelSheet = excelWorkbook.sheets(1)
        excelRange = excelSheet.range(sRange)

---> ITERATE THRU DT HERE
        Dim j As Integer = 1
        For Each dr As DataRow In xlDataTable.Rows
            j += 1
            For i As Integer = 1 To xlDataTable.Columns.Count
                ' Add the header the first time through
                If j = 2 Then
                    excelSheet.Cells(1, i) = xlDataTable.Columns(i - 1).ColumnName
                End If
                excelSheet.Cells(j, i) = dr.Item(i - 1).ToString
            Next
        Next
    End Sub



Here I create a datatable with 2 cols and 3 rows. I created the excel range A1:B3 which is now redundant cos I couldn't get it going.
So the last loop, I populate from A1 onwards. This needs to change according to your cell.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33582905
0
 

Author Closing Comment

by:chtullu135
ID: 33586102
Thanks for the help.  I've decided to give you full credit because you did answer my initial question.  I will be posting the latter part of the question as "How can I pass a worksheet to a function"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33586638
I wanted to solve your initial problem first and get feedback. I assume it worked.

Regarding passing worksheet name, well here are some suggestions, number 1 you can try later on as part of code cleanup if you wish

1. Don't keep creating excel app object as it will slow down your code. Create once and leave workbook open, either declare as global or pass as parameter into functions

2. Try passing down the worksheet by reference instead of value eg ByRef instead of ByVal.

What is the problem when you export?
0
 

Author Comment

by:chtullu135
ID: 33587007
I think the problem had to do with how I was passing the worksheet.  I'm going to have another fresh look at the code.  I worked on it last night and still had the same problem.  i.e. the error Exception from HRESULT: 0x800401A8"
After I clean up the code, I'll see what happens.   Oh I did try passing the worksheet by reference.  I have an idea of where the problem lies
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33587131
you could create class level variables so you don't need to keep passing them around if you are going to use them in more than one place within that class
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33587146
actually dont for worksheets because you keep creating new ones so maybe pass the worksheet name and reference the sheet by name there
0
 

Author Comment

by:chtullu135
ID: 33587397
I think I see one of my problems and it relates to what you mentioned earliers about opening and closing, then reopening the workbook.  In the finally clause of the try catch statement, Iof the OpenWorkbook function I saw that I was closing the workbook and quiting Excel.  I was then passing the resuliting worksheet by value to the next function.  Now the next function also has a excel quiting and clean up routine.  I'm thinking of commenting out the code in the finally clause of the OpenWorkbook function, this way the workbook will be open when the worksheet is passed to the next function
0
 

Author Comment

by:chtullu135
ID: 33587873
That was part of the problem, as well as how I was passing the worksheet.  I can now create multiple graphs however, they are being put into multiple workbooks instead of a single workbook.  I still need to fix this.  I'll post it as a new question along with the code.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

17 Experts available now in Live!

Get 1:1 Help Now