A little help here

Question

renzlo 4 Junior Poster in Training

12 Years Ago

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

vb.net

3 Contributors
16 Replies
281 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by renzlo

thines01 401 Postaholic

12 Years Ago

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

thines01 401 Postaholic

12 Years Ago

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.

thines01 401 Postaholic

12 Years Ago

Both

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

Here's a sample project using Excel

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

2012-02-28_09-15_ExcelExportListView.zip (208.47 KB)

Reply to this topic

Be a part of the DaniWeb community

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

renzlo 4 Junior Poster in Training · Answer 1 · 2012-02-28T04:05:01+00:00

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

renzlo 4 Junior Poster in Training · Answer 2 · 2012-02-28T04:10:19+00:00

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

thines01 401 Postaholic Team Colleague Featured Poster · Answer 3 · 2012-02-28T04:21:43+00:00

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

renzlo 4 Junior Poster in Training · Answer 4 · 2012-02-28T04:28:01+00:00

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.

thines01 401 Postaholic Team Colleague Featured Poster · Answer 5 · 2012-02-28T05:24:50+00:00

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.

score 0 · Answer 6 · 2012-02-28T08:32:24+00:00

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.

renzlo 4 Junior Poster in Training · Answer 7 · 2012-02-28T20:03:49+00:00

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

score 0 · Answer 8 · 2012-02-28T20:13:24+00:00

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

renzlo 4 Junior Poster in Training · Answer 9 · 2012-02-28T20:47:45+00:00

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?

score 0 · Answer 10 · 2012-02-28T21:14:23+00:00

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.

renzlo 4 Junior Poster in Training · Answer 11 · 2012-02-28T21:17:30+00:00

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

renzlo 4 Junior Poster in Training · Answer 12 · 2012-02-28T21:38:38+00:00

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.