I am having trouble exporting my data to excel. my application uses an ArrayList() to house the data. I don't completely understand arraylist's. Inside my ArrayList() is a MyDataClass, then the Val1, Val2, etc...The array has 7 columns and can have anywhere from 1 to 300 rows of data. When I do this, I can't seem to get down to the Val1 data, my excel sheet in cell A1 is saying, MyDataClass. Can someone please help me with this, thank you in advance. I am on visual studio 2010 and Office 2010.

Protected Sub btnExport_Click_Backup(sender As Object, e As System.EventArgs) Handles btnExport.Click

    Dim ItemList As New ArrayList()
    ItemList = Session("sessItemList2")
    ItemList.Reverse()


    Dim dt As New System.Data.DataTable()
    dt.Columns.Add("SerlNmbr")
    dt.Columns.Add("ItemNmbr")
    dt.Columns.Add("LocnCode")
    dt.Columns.Add("Status")
    dt.Columns.Add("MSL")
    dt.Columns.Add("InvoiceNumber")
    dt.Columns.Add("ActivationStatus")

' THIS IS WHERE I AM DOING SOMETHING WRONG...

    For i As Integer = 0 To ItemList.Count - 1

        Dim dr As DataRow

        dr = dt.NewRow

        dr.Item(0) = ItemList(0).Val1.ToString

        dt.Rows.Add(dr)

    Next

    Try
        Dim oExcel As Interop.Excel.Application
        Dim oBook As Workbook
        Dim oSheet As Worksheet
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add(Type.Missing)
        oSheet = oBook.Worksheets(1)

        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        'Export the Columns to excel file
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            oSheet.Cells(1, colIndex) = dc.ColumnName
        Next

        'Export the rows to excel file
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next

        'Set final path
        Dim fileName As String = "c:\temp\ExportedFile" + ".xls"
        Dim finalPath = fileName
        oSheet.Columns.AutoFit()
        'Save file in final path
        oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)



        'Release the objects
        ReleaseObject(oSheet)
        oBook.Close(False, Type.Missing, Type.Missing)
        ReleaseObject(oBook)
        oExcel.Quit()
        ReleaseObject(oExcel)
        'Some time Office application does not quit after automation: so i am calling GC.Collect method.
        GC.Collect()


        '  Open the file for the user
        Dim oxcel As Interop.Excel.Application
        Dim owb As Workbook
        Dim osht As Worksheet
        Dim ocell As Range

        oxcel = New Interop.Excel.Application
        oxcel.Visible = True

        ' my cpu errors out here becz my Office is not registered, you can go to excel & remove Registration Msgbox then press continue
        owb = oxcel.Workbooks.Open(finalPath)
        osht = owb.Worksheets(1)
        ocell = osht.Range("A1")
    Catch ex As Exception
        '  MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
        '  Need an error
    End Try

End Sub

Related posts

Recent Viewed