Export to Excel and List Boxes

[boutells]

I am doing a project and want to export the values of several two dimensional arrays to Excel. Searching the help files and this forum leads me to think this is doable but I can't find the procedures to get started. Can someone advise.

On another note, the present method of viewing the output is in a series of 4 side by side list boxes. Each one has been loded in order so that the output in each box lines up with the corresponding line in the other boxes.

Is there a way to synchronise the scrolling of all the list boxes so that the output remains in sync as the user scrolls down?

[keystone_paul]

The easiest way of writing data out to Excel is to write a CSV (comma separated values) file rather than trying to talk to Excel directly : Excel is perfectly happy to read CSV files.

If you need help on how to create a CSV file let me know.

Your second query sounds to me like rather than using lots of list boxes you should be using a listview control in "detail mode" with four columns. Its very straightforward to do and the help files should show you how to do it.

Hopefully that will point you in the right direction but if you are struggling just shout!

[boutells]

Thanks for the input.

Yes, I would appreciate syntax help on the CSV files.

Essentially, I have two arrays, Boardresult(numboards, numteams) and teamresult(numteams, numboards) and want to export those arrays to a separate sheet in the same workbook (or just export as separate csv files and import them into excel.

The Listview box does appear to be a preferred alternative but I am running into syntax issues here as well in getting the output to load into the listbox. (My reference sources are not particularly helpful within the context of this use).

The code I am using for the listboxes is as follows:

For i = 0 To (Numteams - 1)


For j = 0 To (Numteams - 1)

If ranking(j) = (i + 1) Then

Results.TmNumber.Items.Add(Teamnumber(j))
Results.Tmname.Items.Add("" & Teamname(j))
Results.TmResult.Items.Add(teamtotal(j))
Results.Tmprcnt.Items.Add("" & teamprcnt(j).ToString("0.00") & "%")

If istied(j) = False Then
Results.Tmrank.Items.Add(ranking(j))
Else
Results.Tmrank.Items.Add("" & ranking(j) & "=")
End If

End If

When I try and do the same for the listview box, I don't seem to have the option to add the item to a specific column.

Thanks for your help.
Next

Next

[boutells]

I have solved the listboxes but would still appreciate a response as to how to export either direcly to Excel ot alternativley as a csv file.

[keystone_paul]

There are a few ways of writing a CSV file as it is just a text file with a specific format, a textwriter being one option. You can see examples on how to use them here.

Basically you would create one string per record (or row if you are thinking in Excel terms) with each field (column) separated by commas, and then use the WriteLine method to output it to the file.

[ForumAccount]

You can use this pass in either a datatable or a datagridview: (+1 overloads)

Public Shared Function WriteCSV(ByVal dt As DataTable, ByVal fullfilename As String) As Boolean 
        Try 
            Using sw As New System.IO.StreamWriter(fullfilename) 
                Dim cols As String = String.Empty 
                For Each c As DataColumn In dt.Columns 
                    cols &= c.ColumnName & "," 
                Next 
                sw.WriteLine(cols.TrimEnd(","c)) 
                For Each r As DataRow In dt.Rows 
                    Dim vals As String = String.Empty 
                    For cl As Integer = 0 To dt.Columns.Count - 1 
                        vals &= CStr(r.Item(cl)) & "," 
                    Next 
                    sw.WriteLine(vals) 
                Next 
            End Using 
            Return True 
        Catch ex As Exception 
            MessageBox.Show(ex.Message) 
            Return False 
        End Try 
    End Function 
    Public Shared Function WriteCSV(ByVal dgv As DataGridView, ByVal fullfilename As String) As Boolean 
        Try 
            Using sw As New System.IO.StreamWriter(fullfilename) 
                Dim cols As String = String.Empty 
                For Each c As DataGridViewColumn In dgv.Columns 
                    cols &= c.HeaderText & "," 
                Next 
                sw.WriteLine(cols.TrimEnd(","c)) 
                For Each r As DataGridViewRow In dgv.Rows 
                    If Not r.IsNewRow Then 
                        Dim vals As String = String.Empty 
                        For Each cl As DataGridViewCell In r.Cells 
                            vals &= CStr(cl.Value) & "," 
                        Next 
                        sw.WriteLine(vals) 
                    End If 
                Next 
            End Using 
            Return True 
        Catch ex As Exception 
            MessageBox.Show(ex.Message) 
            Return False 
        End Try 
    End Function