Vai al contenuto

Esportare una listbox su excel

Spesso capita di dover esportare i dati di una Listbox su un foglio di Excel. Le soluzioni per farlo sono diverse a seconda delle esigenze, vediamo alcuni esempi, dal più generico a quello più sofisticato.


Innanzi tutto cosa vogliamo fare? Un copia e incolla o generare un file?
Come vogliamo interagire con la ListBox? Vogliamo mantenere la formattazione? Vogliamo gestire casi particolari come ad esempio le date? Vogliamo poter inserire volendo le formule?

La soluzione che andremo ad implementare sarà basata su un modulo che estende le funzionalità della Listbox, questo perché ci permette di utilizzarla anche in sottoclassi della Listbox senza dover creare una catena di ereditarietà particolare.

Per cui lanciamo Xojo e creiamo un modulo a cui diamo il nome ToExcel.

Primo metodo: Esportazione semplice

In questo caso vogliamo solo esportare il contenuto della listbox in celle di Excel senza badare alla formattazione. Visto che sia l’esportazione per copia e incolla che quella su file hanno entrambe bisogno del contenuto espresso come testo creiamo prima una funzione che ci restituisce questo testo e poi le due funzionalità per l’azione che vogliamo.

Private Function soloTesto(lb as Listbox) as Text
  return=lb.Cell(-1, -1).ToText
End Function

Tutto qui. Per avere il contenuto di tutte le celle della Listbox basta richiamare la proprietà Cell con i valori -1 per le riga e -1 per la colonna. Questa funzionalità di Cell ci permette, volendo di prendere l’intero contenuto di una riga con Cell(riga, -1) o di una colonna con Cell(-1, colonna).
Il testo restituito da Cell(-1, -1) è una Stringa i valori delle celle con le righe separate da EndOfLine e le colonne da tabulatori.

Il problema con questo metodo è che non vengono restituite le intestazioni.

Per cui modifichiamo il metodo per prevedere la possibilità di avere le intestazioni se desiderato e se sono presenti.

Private Function soloTesto(lb as Listbox, withHeading as Boolean=true) as Text
  dim allHeading as String
  //Se voglio le intestazioni e sono presenti riempio la variabile allHeading
  if withHeading and lb.HasHeading then
    allHeading=lb.Heading(-1)+EndOfLine
  end if
 
  //Ottengo il contenuto della Listbox
  dim allText as Text=lb.Cell(-1,-1).ToText
 
  //Restituisco header (che sarà eventualmente vuoto) e il contenuto
  return allHeading.toText+allText  
End Function

Ora abbiamo la possibilità di volere o meno le intestazioni e se queste esistono saranno aggiunte al nostro risultato.
La chiamata Heading(-1) restituisce tutte le intestazioni separate dal carattere tabulatore.

La funzione è privata perché ci serve all’interno del modulo. La vera funzionalità che estende la Listbox sarà invece:

Public Sub copiaTesto(extends lb as Listbox, withHeading as Boolean=true)
  dim c as new Clipboard
  c.Text=soloTesto(lb, withHeading)
End Sub

In questo modo possiamo copiare il contenuto di una Listbox in una finestra, ad esempio LaMiaListbox, con il semplice comando: LaMiaListbox.copiaTesto e poi passare su Excel e incollare il contenuto.

Secondo metodo: Esportazione complessa

In questo caso l’esportazione vuole mantenere le stesse impostazioni visualizzate nella cella, quindi allineamento, grassetto e così via.
Ma visto che esportiamo con la formattazione magari vogliamo avere qualche funzionalità per poter esprimere altre informazioni particolari che magari rendiamo nell’evento CellTextPaint della ListBox, o vogliamo scrivere il testo in modo diverso perché è una data o una formula.
Le date e le formule sono cose ambigue in un file Excel, per sicurezza le date conviene crearle nel formato YYYY-MM-GG per evitare ambiguità e le formule sono in genere localizzate sul sistema (per cui in italiano avremo =SOMMA… in inglese =SUM…)

L’approccio generale è lo stesso di prima. Otteniamo un testo e poi lo rendiamo a disposizione degli appunti i in questo caso anche per salvare il file.
Il testo generato sarà un HTML in particolare un frammento HTML relativo ad una TABLE.

Iniziamo a scrivere la nostra funzionalità base e poi aggiungeremo i vari comandi.
dovendo scrivere del codice HTML, ma anche per semplificare la scrittura utilizziamo delle costanti:
kTableOpen as Text=”<table>”
kTableClose as Text=”</table>”
kRowOpen as Text=”<tr>”
kRowClose as Text=”</tr>”
kCellOpen as Text=”<td>”
kCellClose as Text=”</td>”

Private Function withFormat(lb as Listbox, withHeading as Boolean) as Text
  //Otteniamo il numero di righe presenti
  dim numRows as integer=lb.ListCount-1
 
  //Otteniamo il numero di colonne presenti
  dim numCols as integer=lb.ColumnCount-1
 
  //Creiamo un vettore che conterrà tutto il nostro risultato
  dim allText() as String
  //Creiamo la tabella
  allText.Append kOpenTable
  //Contenuto dell'header se desiderato e presente
  if withHeading and lb.HasHeading then
    allText.Append kOpenRow
    for col as integer=0 to numCols
      allText.Append kCellOpen
      allText.Append lb.Heading(col).toText
      allText.Append kCellClose
    next
    allText.Append kRowClose
  end if
 
  //Per le celle è la stessa cosa ripetuta per tutte le righe
  for row as integer=0 to numRows
    allText.Append kOpenRow
    for col as integer=0 to numCols
      allText.Append kCellOpen
      allText.Append lb.Cell(row, col).toText
      allText.Append kCellClose
    next
    allText.Append kRowClose
  next
 
  //Chiudiamo la tabella
  allText.Append kTableClose
  //Restituiamo il valore
  return Join(allText, "").ToText
End Function

Però in questo modo non abbiamo nulla per la formattazione.
Per formattare il contenuto della cella o dell’header utilizzeremo gli stili HTML per cui cambiamo la definizione di apertura della cella in:
kCellOpen as Text=”<td style='”
e introduciamo una nuova costante per chiudere questa apertura:
kCellOpenClose as Text=”‘>”

Per avere i diversi parametri degli stili utilizziamo una struttura per cui definiamo:

Structure cellStyle
  align as alignMode
  bold as boolean
  fontSize as integer
  fontColor as color
  background as color
End Structure

Una struttura di questo genere ci permette di poter espandere eventualmente le proprietà senza dover riscrivere il codice.
Definiamo una enumerazione per l’allineamento che tenga conto delle differenze tra l’allineamento della Listbox e dell’HTML:

Public Enum alignMode
Left
Center
Right
End Enum

Per evitare di utilizzare inutilmente i colori definiamo una costante che rappresenta il nessun colore:

noColor as Color= &c000000FF

Definiamo a questo punto una funzione per inizializzare correttamente la nostra struttura, per gli header useremo il parametro row=-1

Private Function standardFormat(lb as Listbox, column as integer, row as integer=-1) as cellStyle
  dim cs as cellStyle
  //Variabile d'appoggio per verificare l'allineamento utilizzato
  dim align as integer
  if row=-1 then
    //Heading
    align=lb.ColumnAlignment(column)
  else
    //Cella: verifichiamo quello della cella, se è Default utilizziamo
    //quello della colonna
    align=lb.CellAlignment(row, column)
    if align=Listbox.AlignDefault then
      align=lb.ColumnAlignment(column)
    end if
  end if
  //Trasformiamo l'allineamento trovato in quello previsto nella enumerazione
  select case align
  case Listbox.AlignRight
    cs.align=alignMode.Right
  case Listbox.AlignCenter
    cs.align=alignMode.Center
  case else
    cs.align=alignMode.Left
  end select
 
  //Le celle possono essere in grassetto gli header no
  //(almeno non possiamo leggerlo dalla Listbox)
  if row>-1 then cs.bold=lb.CellBold(row, column)
 
  //Assegnamo al colore del font e del background il nessun colore
  //Ovvero utilizzeremo dopo quello predefinito
  cs.fontColor=noColor
  cs.background=noColor  
 
  //Restituiamo la struttura correttamente inizializzata
  Return cs
End Function

Scriviamo una funzione di supporto che converte un colore in RGB

Private function toWeb(c as color) as Text
  return "rgb("+c.Red.ToText)+", "+c.Green.ToText+", "+c.Blue.ToText+")"
end function

A questo punto scriviamo la funzione per convertire lo stile in informazioni per lo stile. Tutte queste informazioni saranno aggiunte al nostro vettore del risultato.

Public Sub creaStile(theText() as String, theStyle as cellStyle)
  //TheText è il vettore del risultato
  //TheStyle è lo stile corrente
 
  //Analizziamo l'allineamento 
  //Se è left non scriviamo nulla
  Select case theStyle.align
  case alignMode.Right
    theText.Append "text-align:right;"
  case alignMode.Center
    theText.Append "text-align:center;"
  End Select
 
  //Grassetto
  if theStyle.bold then theText.Append "font-weight:bold;"
 
  //FontSize
  if theStyle.fontSize <> 0 then theText.Append "font-size:"+theStyle.fontSize.ToText+"px;"
 
  //FontColor
  if theStyle.fontColor <> noColor then  theText.Append "color:"+theStyle.fontColor.toWeb+";"
 
  //BackgroundColor
  if theStyle.background <> noColor then theText.Append "background-color:"+theStyle.background.toWeb+";"  
End Sub

A questo punto possiamo scrivere correttamente lo stile della cella o dell’header nel nostro testo HTML.
Ma come facciamo ad ottenere lo stile necessario?
Lo facciamo introducendo due delegate.
I delegate sono strutture delle chiamate a metodi o funzioni a cui possiamo assegnare dei metodi o funzioni che rispettano la stessa firma.
Per cui definiamo per gli stili:

Public Sub HeadingFormat(lb as Listbox, col as integer, byRef style as cellStyle)
Public Sub CellFormat(lb as Listbox, row as integer, col as integer, byRef style as cellStyle)

e per i contenuti:

Public Function HeadingText(lb as Listbox, col as integer, byRef theText as Text)
Public Function CellText(lb as Listbox, row as integer, col as integer, byRef theText as Text)

A questo punto possiamo riscrivere la nostra funzione withFormat in questo modo:

Private Function withFormat(lb as Listbox, withHeading as Boolean, cellContent as CellText=nil, cellStyleFormat as CellFormat=nil, headerText as HeadingText as nil, headerFormat as HeadingFormat=nil) as Text
  //Otteniamo il numero di righe presenti
  dim numRows as integer=lb.ListCount-1
 
  //Otteniamo il numero di colonne presenti
  dim numCols as integer=lb.ColumnCount-1
 
  //Dichiariamo una variabile per lo stile
  dim styleForCell as cellStyle
 
  //Dichiariamo una variable per il testo corrente
  dim aText as Text
 
  //Creiamo un vettore che conterrà tutto il nostro risultato
  dim allText() as String
  //Creiamo la tabella
  allText.Append kOpenTable
  //Contenuto dell'header se desiderato e presente
  if withHeading and lb.HasHeading then
    allText.Append kOpenRow
    for col as integer=0 to numCols
      allText.Append kCellOpen
      styleForCell=standardFormat(lb, col)
      if headerFormat <> nil then headerFormat.invoke(lb, col, styleForCell)
      creaStile(allText, styleForCell)
      allText.append kCellOpenClose
 
      //Aggiungiamo il testo utilizzando di base quello presente,
      //in questo modo la funzione eventualmente chiama dovrà solo modificarlo dove necessario
      aText=lb.Heading(col).toText
      if headerText <> nil then headerText.invoke(lb, col, aText)
      allText.append aText
 
      allText.Append kCellClose
    next
    allText.Append kRowClose
  end if
 
  //Per le celle è la stessa cosa ripetuta per tutte le righe
  for row as integer=0 to numRows
    allText.Append kOpenRow
    for col as integer=0 to numCols
      allText.Append kCellOpen
      styleForCell=standardFormat(lb, col, row)
      if cellStyleFormat <> nil then cellStyleFormat.invoke(lb, col, row, styleForCell)
      creaStile(allText, styleForCell)
      allText.append kCellOpenClose
 
      aText=lb.Cell(row, col).toText
      if cellContent <> nil then cellContent.invoke(lb, row, col, aText)
      allText.append aText
 
      allText.Append kCellClose
    next
    allText.Append kRowClose
  next
 
  //Chiudiamo la tabella
  allText.Append kTableClose
  //Restituiamo il valore
  return Join(allText, "").ToText
End Function

Questa soluzione ci da la flessibilità necessaria per avere o meno dei contenuti o stili speciali definiti a livello di finestra o classe dove si trova la nostra Listbox.

Inoltre il sistema è facilmente espandibile senza dover scrivere molto.

Ad esempio, poniamo di voler aggiungere i bordi con uno spessore e un colore.
Visto che i bordi sono 4 aggiungiamo una struttura per gestirli:

Structure border
  width as integer
  borderColor as color
End Structure

Aggiungiamo alla struttura cellStyle i 4 bordi:

//... al termine della struttura:
   borderLeft as border
   borderTop as border
   borderRight as border
   borderBottom as border

Aggiungiamo una funzione per generare lo stile a seconda del bordo:

Private Sub addBorder(theText() as string, borderName as string, theBorder as border)
  if theBorder.borderColor <> noColor and theBorder.width>0 then
    theText.Append "border-"+borderName+":"+theBorder.width.ToText+"px solid "+theBorder.borderColor.toWeb+";"
  end if
End Sub

E infine aggiungiamo le chiamate a questa funzione nel generatore degli stili creaStile:

//...al termine della funzione
  addBorder theText, "left", theStyle.borderLeft
  addBorder theText, "top", theStyle.borderTop
  addBorder theText, "right", theStyle.borderRight
  addBorder theText, "bottom", theStyle.borderBottom

La chiamata pubblica sarà per la copia:

Public Sub copiaConFormato(extends lb as Listbox, withHeading as Boolean, cellContent as CellText, cellStyleFormat as CellFormat, headerText as HeadingText, headerFormat as HeadingFormat)
  dim c as new Clipboard
  c.Text=withFormat(lb, withHeading, cellContent, cellStyleFormat, headerText, headerFormat)
End Sub

Mentre per il salvataggio:

Public Sub salvaConFormato(extends lb as Listbox, f as folderItem, withHeading as Boolean=true, cellContent as CellText=nil, cellStyleFormat as CellFormat=nil, headerText as HeadingText=nil, headerFormat as HeadingFormat=nil)
  if f <> nil then
    dim t as TextOutputStream
    try
      t=TextOutputStream.Create(f)
      t.Write withFormat(lb, withHeading, cellContent, cellStyleFormat, headerText, headerFormat)
    end try
  end if
End Sub
Questo sito registra informazioni relative al browser utilizzato. Questi dati sono utilizzati per fornire una esperienza d\'uso più personalizzata e per consentire di comprendere l\'uso del sito da parte degli utenti sempre nell\'ottica del rispetto della privacy (GDPR). Se si decide di non voler far registrare questi dati in futuro, imposteremo un cookie nel browser per registrare questa scelta per un anno. Accetto la registrazione dei dati, Nego la registrazione dei dati
709