Listbox to Excel?

To be really pedantic a tab-delimited file should really have the extension “.tsv”.

Here in the Netherlands we use the semi-column instead of the comma in csv-files.
Main reason is the fact that the comma is used here as a decimal separator.
I believe the semi-column is more reliable then using the commonly used comma.
Just my 2 cents.

You might be interested in two classes that I have created for CSV file handling. One for CSV input and one for CSV output.

Get them here: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/sbCSV_In_Out.zip

Tim’s suggestion is a good one. CSV is an ill-conceived, inconsistent mess and must die:
http://www.fourthworld.com/embassy/articles/csv-must-die.html

Please help it die: make importers where needed, but never write CSV exporters.

@Simon: many apps just use .tab for the saner tab-delimited format.

BTW: If anyone here wants to contribute a Xojo example of a CSV parser for that page that’s complicated enough to correctly handle the example data shown there I’d be happy to add it.

take a look at http://www.scout-soft.com/xlistbox/

or maybe http://www.smoothpaygold.com/realbasic/excelwriter.html with this classes you can export to excel using the same recordset that fill a listbox, its works under any platform.

[quote=80796:@Richard Gaskin]Tim’s suggestion is a good one. CSV is an ill-conceived, inconsistent mess and must die:
http://www.fourthworld.com/embassy/articles/csv-must-die.html

Please help it die: make importers where needed, but never write CSV exporters.

@Simon: many apps just use .tab for the saner tab-delimited format.

BTW: If anyone here wants to contribute a Xojo example of a CSV parser for that page that’s complicated enough to correctly handle the example data shown there I’d be happy to add it.[/quote]
Actually I agree with you, but CSV is here and used a great deal in business so I’m not sure that it can die!

I have slightly changed my csv class and it now supports your example in the blog.

Download and try: https://dl.dropboxusercontent.com/u/10747116/RealStudioClasses/sbCSV_In_Out.zip

Changed the routines so now will work with both of the examples you showed in the blog item. The same download link as above.

you can replace tab

excelOut.Write me.cell(-1,-1).ReplaceAll(chr(9),",")
1 Like

Is it possible to copy selected rows as tab delimited strings to the clipboard? If yes, how would that .copy code look like?

Thanks!

[quote=239729:@Dodo Hunziker]Is it possible to copy selected rows as tab delimited strings to the clipboard? If yes, how would that .copy code look like?

Thanks![/quote]
Something like this.
It’s written from my head and not tested but you’d get the idea.

(Pseudo code)

  Dim c As New Clipboard
  Dim toAdd as String
  
  For i as Integer = 0 To Listbox1.ListCount -1
    If Listbox1.Selected(i) Then
      toAdd = toAdd + chr(9) + Listbox1.Cell(i, 0)
    End If
  Next
  
  c.text = Replace(toAdd, Chr(9), "") // Remove the first tab.
  c.Close

Thanks a lot Albin K! I got the idea. I improved the code a bit, so that all the columns are now stored. Like this you are able to paste the copied rows to an excel sheet:

[code] Dim c As New Clipboard
Dim toAdd as String

For i as Integer = 0 To wMain.budget.ListCount -1
If wMain.budget.Selected(i) Then

  for columnCounter as Integer = 0 to budget.ColumnCount // gehe durch alle definierten spalten (columns)
    
    toAdd = toAdd + wMain.budget.Cell(i, columnCounter) 
    if columnCounter <> 0 then
      toAdd = toAdd + chr(9) 
    End if
    
  next
  
  toAdd = toAdd + chr(10)
  
End If

Next

c.text = toAdd
c.Close
[/code]

My question is now, how to bring the text string from c back to a database format, so that I can insert row? Didn’t manage to do that yet.

btw, I was hoping to transform the string with tabs and line breaks to a recordSet. But didn’t succeed so far. Any other method to achieve the above?

Actually it’s a lot simpler.


Dim OutArr() as String
Dim  ub as integer = theListbox.ListCount-1
Dim Found as Integer, SelCount as Integer = theListbox.SelCount 

For i as Integer = 0 to ub
if theListbox.Selectecd(i) Then
    OutArr.Append theListbox.cell(i,-1)
    Found = Found + 1
  If Found = SelCount then Exit
Next I

Dim c As New Clipboard 
C.TExt = Join(OutArr,EndOfLine)
  • Karen

Thanks Karen. Still trying to insert the copied lines in the clipboard back into the listbox…

I think this will work

Dim C as New Clipboard
Dim InArr() as String = Split(C.text, EndOfLine)
C.Close

For Each LineData as String in InArr
  theListbox.AddRow ""
  theListBox.Cell(theListbox.Lastindex, -1) = LineData
Next

Yes!! That works! Thanks so much Karen.

[quote=79676:@Petteri Aaltonen]Hi,

some partial copy paste from my own app to help you get started if you want to use xojo’s excelapplication

Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
dim f as FolderItem
dim i as integer

f=GetFolderItem(“c:\data\OrderForm.xlsx”)
excel.Workbooks.Open(f.AbsolutePath)
excel.Visible = true
excel.Range(“C10”).value = txtDevices.Text
excel.Range(“G69”).value = labToday.Text
excel.Range(“B73”).value = txtOmanimi.Text
excel.Range(“E16”).Value = “Yes”

for i = 1 to val(txtDevices.Text)
excel.Range(“J” + str(i + 20)).Value = “1”
excel.Range(“D” + str(i + 20)).Value = “N”
next

That fills excel workbook (.xslx) file with few cell data from textfields and at the end it fills a couple column of data with predefined data (1 and N), leaves the excel application open to be edited.

Before working officeautomation apps with xojo, remember to copy MSOfficeAutomation.rbx from Xojo (version) \Extras\Office Automation\ folder to Xojo (version)\Plugins folder. Oh exceptions,
Exception err as OLEException
MsgBox err.message

hth, petteri[/quote]

How do I do this on the MAC? I want the excel spreadsheet to open in Excel and present itself to the user. Don’t want to just spit out a delimited text file.

I just give atab delimited Textfile a .XLS extension.

  • Karen

Download one of the free excel writers here and write an entire spreadsheet

You can also my free virtualTable class to do the export as csv, tab or whatever you specify as delimiter. It’s really fast and does a great job for most tasks.

[code]Sub Action()
dim data as Text = Listbox1.cell(-1,-1).ToText
dim vt as VirtualTable = self.VirtualTable1
dim stream as xojo.IO.TextOutputStream
dim csvFile as FolderItem = SpecialFolder.Documents.Child(“output.csv”)
dim tabFile as FolderItem = SpecialFolder.Documents.Child(“output.tab”)
dim file as xojo.IO.FolderItem

vt.createTable( data )
if vt.lastError=0 then
if csvFile<>nil then
// just for demo the class
vt.insertColumn(0, vt.newColumn(“Hello”) )
vt.removeColumn( vt.columnCount-1 )
file = new xojo.IO.FolderItem( csvFile.NativePath.ToText)
if file<>nil then
vt.setDelimiters( vt.defaultRowDelimiter, “;”)
stream = xojo.io.TextOutputStream.Create(file, xojo.core.TextEncoding.UTF8)
stream.Write( vt.toData )
stream.Close
end if

  if tabFile<>nil then
    file = new xojo.IO.FolderItem( tabFile.NativePath.ToText)
    if file<>nil then
      vt.setDefaultDelimiters
      stream = xojo.io.TextOutputStream.Create(file, xojo.core.TextEncoding.UTF8)
      stream.Write( vt.toData )
      stream.Close
    end if
  end if
end if

end if
End Sub[/code]

https://www.dropbox.com/s/0eed06xmnubj4v4/virtualTable1.xojo_binary_project?dl=0