XLS.Varios - Ampliar funciones en creación de documentos excel

Se desea generar documentos excel con mayores detalles de look y funcionalidad desde Genexus.

Algunos de los requerimientos son: colorear celdas, definir bordes de las celdas, insertar filas, insertar fórmulas....

Dado que las funciones propias de Genexus no lo permiten, una alternativa para lograrlo, es incluir macros que realicen las funciones deseadas e invocarlas pasándo los parámetros necesarios, ej:

&ExcelDocument.RunMacro( 'InsertarFila',2 )
&ExcelDocument.RunMacro( 'ColoresCelda',2,5,2 )

Etc...


Este es el código fuente del modulo Visual Basic del archivo excel del cual se invocarán las macros.

Sub InsertarFila(Fila As Integer)
Row = Trim(Str(Fila)) + ":" + Trim(Str(Fila))
Rows(Row).Select
Selection.Insert Shift:=xlDown, copyOrigin:=xlFormatFromRightOrBelow
End Sub


Sub ColoresCelda(RangoCeldas As String, ColorFondo As Integer, ColorTexto As Integer)

' FormatoCelda Macro

'Selecciono Celda
Range(RangoCeldas).Select
'Color Fondo
With Selection.Interior
.ColorIndex = ColorFondo
.Pattern = xlSolid
End With

'Color Texto
Selection.Font.ColorIndex = ColorTexto
End Sub

Sub BordesCelda(RangoCeldas As String, TipoBorde As Integer)


If TipoBorde = 0 Then

'Bordes transparentes
Range(RangoCeldas).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End If


If TipoBorde = 1 Then

'Bordes linea Fina
Range(RangoCeldas).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End If

If TipoBorde = 2 Then

'Bordes linea gruesa
Range(RangoCeldas).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

End If
End Sub

Sub BorrarFila(Fila As Integer)
Row = Trim(Str(Fila)) + ":" + Trim(Str(Fila))
Rows(Row).Select
Selection.Delete
End Sub

XLS.Varios - Insertar fórmula en una celda al generar archivo excel

Para insertar un fórmula en una celda de un archivo excel generado con Genexus utilizar la siguiente sentencia:

&ExcelDocument.Cells(1,5).Text = '=IF(C1>10,1,2)'

Criterio:
- Poner la instrucción en Inglés.
- Poner comas en lugar de punto y comas para separar.