Visual Basic

System.Diagnostics.Debug.WriteLine(ExcelSheet.cells(i, 1).Value)

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

Benniiit wrote:

but nothing displays

Sub datagridToExcel(filename As String, source As DataGridView, header As String, footer As String, convertToXls As Boolean)


    ' use a static file path for the moment
    'Dim BackupPath As String

    Dim datatable As DataTable = CType(source.DataSource, Data.DataTable)

    Dim Headers As String = String.Empty
    Dim TempRow As String = String.Empty
    Dim SeparationMarker As String = ControlChars.Tab

    'Dim FolderBrowserDialog1 As New System.Windows.Forms.FolderBrowserDialog()


    'FolderBrowserDialog1.ShowDialog()
    'BackupPath = FolderBrowserDialog1.SelectedPath

    If filename <> String.Empty Then

        If File.Exists(filename) Then

            File.Delete(filename)

        End If

        Dim SW As New System.IO.StreamWriter(filename, False, System.Text.Encoding.Unicode)

        ' get the column header names and add to the backup file

        Dim i As Integer = 0

        If Not header = String.Empty Then

            SW.WriteLine(header)
            SW.WriteLine(String.Empty)

        End If


        ' add the headers
        For i = 0 To source.Columns.Count - 1

            Headers &= source.Columns(i).Name & SeparationMarker

        Next

        SW.WriteLine(Headers.ToString)


        ' add each row
        For Each row As DataGridViewRow In source.Rows


            For a As Integer = 0 To datatable.Columns.Count - 1


                'check if it's null

                If IsDBNull(row.Cells(a)) Then

                    TempRow &= String.Empty & SeparationMarker

                Else


                    Dim fmt As String = String.Empty

                    For gridIdx As Integer = 0 To source.ColumnCount - 1

                        If source.Columns(gridIdx).Name = datatable.Columns(a).ColumnName AndAlso source.Columns(gridIdx).DefaultCellStyle.Format() <> String.Empty Then

                            fmt = "{0:" + source.Columns(gridIdx).DefaultCellStyle.Format() + "}"
                            Exit For

                        End If


                    Next

                    If fmt <> String.Empty Then

                        Dim tmpdat As String = String.Format(fmt, row.Cells(a).Value)
                        tmpdat = tmpdat.ToString.Replace(SeparationMarker, String.Empty)

                        TempRow &= tmpdat & SeparationMarker

                    Else

                        TempRow &= row.Cells(a).Value.ToString.Replace(SeparationMarker, String.Empty) & SeparationMarker


                    End If

                End If



            Next

            TempRow = TempRow.Replace(vbCrLf, String.Empty).Replace(vbCr, String.Empty).Replace(vbLf, String.Empty)

            SW.WriteLine(TempRow.ToString)
            TempRow = String.Empty

        Next

        If Not footer = String.Empty Then

            SW.WriteLine(String.Empty)
            SW.WriteLine(footer)

        End If



        SW.Close()


        If convertToXls Then

            Try


                If File.Exists(filename.Substring(0, filename.Length - 4) + ".xlsx") Then

                    File.Delete(filename.Substring(0, filename.Length - 4) + ".xlsx")

                End If


                Dim xlsapp As New Microsoft.Office.Interop.Excel.Application
                Dim xlswb As Microsoft.Office.Interop.Excel.Workbook
                xlsapp.Visible = False

                xlswb = CType(xlsapp.Workbooks.Open(filename), Excel.Workbook)
                CType(xlswb.Worksheets(1), Excel.Worksheet).Activate()
                xlsapp.ActiveWindow.SplitColumn = 0
                xlsapp.ActiveWindow.SplitRow = 1
                xlsapp.ActiveWindow.FreezePanes = True


                CType(xlswb.Worksheets(1), Excel.Worksheet).Cells.AutoFilter(1)
                CType(xlswb.Worksheets(1), Excel.Worksheet).Columns.AutoFit()
                xlswb.SaveAs(filename.Substring(0, filename.Length - 4) + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing)
                xlswb.Close()
                xlsapp.Quit()
                File.Delete(filename)

                MsgBox("data exported to:" + vbNewLine + filename.Substring(0, filename.Length - 4) + ".xlsx", vbInformation, "exported to excel")

            Catch ex As Exception

            End Try


        Else

            MsgBox("Data exported to:" + vbNewLine + filename, vbInformation, "Exported to text file")


        End If



    Else

        MsgBox("Data not exported.", vbInformation, "Exported Cancelled")

    End If


End Sub

From oh In context.Order_History
Join sts In context.Shipping_TaxStates On oh.SalesTaxState Equals sts.SalesTaxState
Where oh.OrderDate >= m_startDate And oh.OrderDate <= m_stopDate And oh.OrderStatus = "COMPLETED"
Group By sts.SalesTaxState Into GroupedHistory = Group, SubTotal = Sum(oh.SubTotal), SalesTax = Sum(oh.SalesTax)
Select State = GroupedHistory.Select(Function(m) m.sts).First(), SubTotal, SalesTax
Select State.StateCode, State.StateName, State.HasSalesTax, SubTotal, State.StateTaxRate, SalesTax
Order By StateName

Member 11915345 wrote:

Not sure why I got downvoted either.

Mika Wendelius wrote:

As a side-note, not sure why you got downvoted...