Export to Excel and List Boxes
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?
Re: Export to Excel and List Boxes
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!
Re: Export to Excel and List Boxes
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
Re: Export to Excel and List Boxes
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.
Re: Export to Excel and List Boxes
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.
Re: Export to Excel and List Boxes
You can use this pass in either a datatable or a datagridview: (+1 overloads)
Code:
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