Listbox to Excel?

General
1

Hi,
does anyone know if there are any example projects of how to export a Listbox’s content as an excel spreadsheet?

I have a simple 6 column listbox and need to be able to export as Excel via a button press.
Thank you all for any help given.

2

monkey bread software has a plugin I am using now for exporting data to excel without needing excel installed but is not freeware

XL plugin which requires lib xl

if excel is installed on the machine, you could go this route: Excel Application

3

If you don’t mind spending a few dollars I recommend Eugene Dakin’s book Program Excel 2010 with Xojo in Windows

http://great-white-software.com/rblibrary/index.php?main_page=product_info&cPath=16&products_id=125

A simple tab separated or CSV file will work as well.

4

Thanks rich, I already own the MBS complete plugins, but an extra $199 for the Lib XL is wayyyyy too expensive for my needs on this particular project :frowning:

I just wanted to offer the option - it’s not a critical feature.

Thank you anyway.

5

Peter,
Do you mean that if I exported as a CSV, the user could then open it up in Excel?

If so, my question then changes to - any resources / links to exporting ListBox contents as CSV?

:slight_smile:

6

Well as alternative you may export the Listbox to HTML formated file with columns and rows as TABLE.
After exporting just open it with your desired Spreadsheet/ Calc/ Excel App. This has the advantage that a User is not depended on Microsoft Excel and may use Libre/ Open Office aswell.

7

[quote=79525:@Richard Summers]Peter,
Do you mean that if I exported as a CSV, the user could then open it up in Excel?

If so, my question then changes to - any resources / links to exporting ListBox contents as CSV?

:)[/quote]

Actually, all you need to do is output the following format and save as csv:

“text goes here”, “text goes here”, “text goes here”
“text goes here”, “text goes here”, “text goes here”
“text goes here”, “text goes here”, “text goes here”

this should be enough for excel to open and put the data in column and rows for you.

the only catch is that there would be zero format applied in which the end user would have to do once in excel.

8

Rich / Tomas,
thank you both for your advice - I think I will need to search further for some actual code examples, as I am only a VERY part time Developer, and will grasp the concept much quicker if I can find some code to tear apart and study, so I can see exactly what is going on.

Thank you both very much.

9

Richard,

take a look at the TextOutputStream

in a nutshell, you would iterate through your listbox rows and output the results using TextOutputStream.

I recommend playing with TextOutputStream first to get the hang of writing files. Once you have this set, you can go back and pull the data from the listbox.

10

Dim rowCount, columnCount As Integer Dim rowOut() As String Dim excelOut As TextOutputStream Dim f As FolderItem = SpecialFolder.Desktop.Child("My Test.txt") if f <> nil then excelOut = TextOutputStream.Create(f) if excelOut <> nil then rowCount = Listbox1.ListCount - 1 columnCount = Listbox1.ColumnCount - 1 For row As integer = 0 to rowCount redim rowOut(-1) For column As Integer = 0 to columnCount rowOut.Append Listbox1.Cell(row,column) Next excelOut.WriteLine chr(34) + Join(rowOut,chr(34) + "," + chr(34)) + chr(34) next excelOut.Close end if end if

11

Peter - Thank you very much.
After a quick glance I am presuming your code outputs a CSV from a ListBox containing any amount of columns.

I will try it out later when I get home.

Thank you so much!

12

It may make more sense as an extends

[code]Sub CSVOut(extends lb As Listbox)
Dim rowCount, columnCount As Integer
Dim rowOut(), outputString As String
Dim excelOut As TextOutputStream

Dim dlg as New SaveAsDialog
Dim f as FolderItem

dlg.InitialDirectory=SpecialFolder.Desktop
dlg.promptText=“Save CSV As”
dlg.SuggestedFileName=“My CSV Out.csv”
dlg.Title=“CSV Output”

f=dlg.ShowModal()

If f <> Nil then
excelOut = TextOutputStream.Create(f)
if excelOut <> nil then
rowCount = lb.ListCount - 1
columnCount = lb.ColumnCount - 1

  For row As integer = 0 to rowCount
    redim rowOut(-1)
    For column As Integer = 0 to columnCount
      rowOut.Append lb.Cell(row,column)
    Next
    excelOut.WriteLine chr(34) + Join(rowOut,chr(34) + "," + chr(34)) + chr(34)
  next
  excelOut.Close
end if

Else
//user canceled
End if

End Sub
[/code]

Add this to a module and you can call it from any listbox

myListbox.CSVOut

1 Like
13

Wa Wa Wee Wa !

Thanks Peter - I will also try that later when I get home.

14

Instead of iterating through all rows and columns you can load the listbox into a tab delimited string.

Dim s as string = me.listbox.cell(-1,-1)

It would simplify the routine

excelOut = TextOutputStream.Create(f)
    if excelOut <> nil then
           excelOut.Write me.cell(-1,-1)
      excelOut.Close
end
15

Jim - I’m a bit lost now?
So what would the new, edited, complete code look like now?

Thanks.

16

using this one ‘excelOut.Write me.cell(-1,-1)’ does not create comma delimeter

17

???
Are you saying that the code provided by Peter would not work?

18

ok… found out ‘excelOut.Write me.cell(-1,-1)’ is tab delimited instead…

19

???
Totally lost now :slight_smile:

  1. Would Peter’s code work?
  2. If yes, what would it export as?

Thanks.

20

Old Dog -> New Trick. Thanks Jim

[code]Sub
textOut(extends lb As Listbox)

Dim excelOut As TextOutputStream

Dim dlg as New SaveAsDialog
Dim f as FolderItem

dlg.InitialDirectory=SpecialFolder.Desktop
dlg.promptText=“Save Text As”
dlg.SuggestedFileName=“My Text Out.txt”
dlg.Title=“Text Output”

f=dlg.ShowModal()

If f <> Nil then
excelOut = TextOutputStream.Create(f)
if excelOut <> nil then
excelOut.Write lb.cell(-1,-1)
excelOut.Close
end if
Else
//user canceled
End if

End Sub[/code]

This will result in a tab delimited file which Excel can open.