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

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

Edited 4 Years Ago by renzlo: n/a

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.

Edited 4 Years Ago by thines01: n/a

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

Edited 4 Years Ago by renzlo: n/a

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?

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.

This question has already been answered. Start a new discussion instead.