Hi All,

I can't figure this out, I have a listview and I want to export it and finally found how to do it but I'm stuck here..

Here's the code.

Dim Row0 As WorksheetRow = sheet.Table.Rows.Add
        Row0.AutoFitHeight = False
        Row0.Cells.Add("PROJECT_NAME", DataType.[String], "s67")
        Row0.Cells.Add("USER_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("JOBNAME", DataType.[String], "s68")
        Row0.Cells.Add("BATCH_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("PROCESS_CODE", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("UNIT_TYPE", DataType.[String], "s68")
        Row0.Cells.Add("USER_SHIFT", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("JOB_STATUS", DataType.[String], "s68")
        Row0.Cells.Add("START_TIME", DataType.[String], "s68")
        Row0.Cells.Add("END_TIME", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_START", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_END", DataType.[String], "s68")
        Row0.Cells.Add("TRANSACTION_TYPE", DataType.[String], "s69")

Now, I want to increment the "0" in "Row0" by one based on the count of items in listview, so I did this:

Dim totRows As Integer = ListView1.Items.Count
        For i As Integer = 1 To totRows
        ''''I'm stucked here, what's next?
        Dim Row0 As WorksheetRow = sheet.Table.Rows.Add
        Row0.AutoFitHeight = False
        Row0.Cells.Add("PROJECT_NAME", DataType.[String], "s67")
        Row0.Cells.Add("USER_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("JOBNAME", DataType.[String], "s68")
        Row0.Cells.Add("BATCH_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("PROCESS_CODE", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("UNIT_TYPE", DataType.[String], "s68")
        Row0.Cells.Add("USER_SHIFT", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("JOB_STATUS", DataType.[String], "s68")
        Row0.Cells.Add("START_TIME", DataType.[String], "s68")
        Row0.Cells.Add("END_TIME", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_START", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_END", DataType.[String], "s68")
        Row0.Cells.Add("TRANSACTION_TYPE", DataType.[String], "s69")
        Next

Recommended Answers

I'm confused.
You want to change the name of the variable based on the contents of the variable?

Jump to Post

You can't do that.
The best thing you could do is to create an array of rows and use the new array index.
Row[0], Row[1], Row[2], etc.

Jump to Post

Here's a sample project using Excel

Jump to Post

All 16 Replies

I'm confused.
You want to change the name of the variable based on the contents of the variable?

Hi thines,

I want to increment the number "0" on the string "Row0":

FOr example:

Dim Row0 As WorksheetRow = sheet.Table.Rows.Add
        Row0.AutoFitHeight = False
        Row0.Cells.Add("PROJECT_NAME", DataType.[String], "s67")
        Row0.Cells.Add("USER_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("JOBNAME", DataType.[String], "s68")
        Row0.Cells.Add("BATCH_NUMBER", DataType.[String], "s68")
        Row0.Cells.Add("PROCESS_CODE", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("UNIT_TYPE", DataType.[String], "s68")
        Row0.Cells.Add("USER_SHIFT", DataType.[String], "s68")
        Row0.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row0.Cells.Add("JOB_STATUS", DataType.[String], "s68")
        Row0.Cells.Add("START_TIME", DataType.[String], "s68")
        Row0.Cells.Add("END_TIME", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_START", DataType.[String], "s68")
        Row0.Cells.Add("SHIFT_END", DataType.[String], "s68")
        Row0.Cells.Add("TRANSACTION_TYPE", DataType.[String], "s69")

to this:

Dim Row1 As WorksheetRow = sheet.Table.Rows.Add
        Row1.AutoFitHeight = False
        Row1.Cells.Add("PROJECT_NAME", DataType.[String], "s67")
        Row1.Cells.Add("USER_NUMBER", DataType.[String], "s68")
        Row1.Cells.Add("JOBNAME", DataType.[String], "s68")
        Row1.Cells.Add("BATCH_NUMBER", DataType.[String], "s68")
        Row1.Cells.Add("PROCESS_CODE", DataType.[String], "s68")
        Row1.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row1.Cells.Add("UNIT_TYPE", DataType.[String], "s68")
        Row1.Cells.Add("USER_SHIFT", DataType.[String], "s68")
        Row1.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row1.Cells.Add("JOB_STATUS", DataType.[String], "s68")
        Row1.Cells.Add("START_TIME", DataType.[String], "s68")
        Row1.Cells.Add("END_TIME", DataType.[String], "s68")
        Row1.Cells.Add("SHIFT_START", DataType.[String], "s68")
        Row1.Cells.Add("SHIFT_END", DataType.[String], "s68")
        Row1.Cells.Add("TRANSACTION_TYPE", DataType.[String], "s69")

looping till the end of ListView....

Dim Row2 As WorksheetRow = sheet.Table.Rows.Add
        Row2.AutoFitHeight = False
        Row2.Cells.Add("PROJECT_NAME", DataType.[String], "s67")
        Row2.Cells.Add("USER_NUMBER", DataType.[String], "s68")
        Row2.Cells.Add("JOBNAME", DataType.[String], "s68")
        Row2.Cells.Add("BATCH_NUMBER", DataType.[String], "s68")
        Row2.Cells.Add("PROCESS_CODE", DataType.[String], "s68")
        Row2.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row2.Cells.Add("UNIT_TYPE", DataType.[String], "s68")
        Row2.Cells.Add("USER_SHIFT", DataType.[String], "s68")
        Row2.Cells.Add("TOTAL_UNITS", DataType.[String], "s68")
        Row2.Cells.Add("JOB_STATUS", DataType.[String], "s68")
        Row2.Cells.Add("START_TIME", DataType.[String], "s68")
        Row2.Cells.Add("END_TIME", DataType.[String], "s68")
        Row2.Cells.Add("SHIFT_START", DataType.[String], "s68")
        Row2.Cells.Add("SHIFT_END", DataType.[String], "s68")
        Row2.Cells.Add("TRANSACTION_TYPE", DataType.[String], "s69")

You can't do that.
The best thing you could do is to create an array of rows and use the new array index.
Row[0], Row[1], Row[2], etc.

Is it possible to put it is a string variable then execute the variable, like Eval("variable") in vb6?

Not yet, but I heard it's coming.
You should be able to get around that with good planning and maybe some delegates.

Hmm..thanks thines for replying, I really appreciate it, I'll wait for someone who can do what I wanted to do. I am using that code in exporting my listview items to excel that doesn't require excel to be installed, I've learned from google that I can use CarlosAg.ExcelXmlWriter.dll. Alternatively, if you have any idea on how to export listview items to excel without requiring excel to be installed, it will be a great help.

You can always write it as XML or tab-delimited Text.
...but that doesn't seem to be the problem.

I thought you were asking how to change the name of a variable base on its contents.
If what you wanted to do can't be found here or through Google or Bing, maybe a design change is in order.

What am I missing here? I'm seeing a lot of brute force code to simply export a listview. What are you trying to export the listview to? What viewmode is the listview in? I've written a few examples of exporting listviews and gridviews to Excel spreadsheets and none of them used brute force. If you are asking what I think you are asking I should be able to help you out but I need more details.

What am I missing here? I'm seeing a lot of brute force code to simply export a listview. What are you trying to export the listview to? What viewmode is the listview in? I've written a few examples of exporting listviews and gridviews to Excel spreadsheets and none of them used brute force. If you are asking what I think you are asking I should be able to help you out but I need more details.

Hi Jim,

Thanks for replying, I'm brute forcing cause I'm lacking of options, but it is just for me. What I have is an unbound listview, I just wanted to export the contents to excel without requiring excel to be installed.

- renzlo

Try this

Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click

        SaveFileDialog1.Title = "Save Excel File"
        SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
        SaveFileDialog1.ShowDialog()

        'exit if no file selected

        If SaveFileDialog1.FileName = "" Then
            Exit Sub
        End If

        'create objects to interface to Excel

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        'create a workbook and get reference to first worksheet

        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet

        'step through rows and columns and copy data to worksheet

        Dim row As Integer = 1
        Dim col As Integer = 1

        For Each item As ListViewItem In lvwToExport.Items
            For i As Integer = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next

        'save the workbook and clean up

        xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    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

Try this

Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click

        SaveFileDialog1.Title = "Save Excel File"
        SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
        SaveFileDialog1.ShowDialog()

        'exit if no file selected

        If SaveFileDialog1.FileName = "" Then
            Exit Sub
        End If

        'create objects to interface to Excel

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        'create a workbook and get reference to first worksheet

        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet

        'step through rows and columns and copy data to worksheet

        Dim row As Integer = 1
        Dim col As Integer = 1

        For Each item As ListViewItem In lvwToExport.Items
            For i As Integer = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next

        'save the workbook and clean up

        xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    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

this requires an excel to be installed? or do i need do add reference of a COM object?

Both

You'd have to have Excel installed otherwise you won't have access to the Eccel objects. I don't think they get installed if you just have (for example) Microsoft Word. Another option would be to export as a CSV file. If you do that then I recommend ensuring that all fields are enclosed in " as in

"fld1","fld2","fld3"
etc

That's to prevent problems if any fields contain a comma. You could modify the code slightly and replace the Excel parts with code to generate the text strings.

thanks thines01 and Jim for the assistance, I will try it now.

Here's a sample project using Excel

thanks to this Jim, I've learned form the google that there's a library called ExcelLibrary and NPOI, I'm sure you know this, I just need not to use COM automation for my program.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.