Rotina de geração de gráfico para o BrOffice (Calc)

Sub Click(Source As Button)
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
Set objCoreReflection = objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
Set StarDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
Set dispatcher = objServiceManager.createInstance("com.sun.star.frame.DispatchHelper")
Set teste = objServiceManager.createInstance("com.sun.star.table.TableColumn")

' Cria um novo documento do Calc
Dim args()
sUrl = "private:factory/scalc"
Set oDoc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, args())
Set frame = oDoc.CurrentController.Frame

' obtém a primeira planilha no documento
Set oPlanilha = oDoc.getSheets().getByIndex(0)


'Montando os valores pré-definidos (TABELA) *Dados Fictícios
'LINHA
Set oCelula = oPlanilha.getCellByPosition(0, 0)
oCelula.setString("RELATÓRIO:")
Set oCelula = oPlanilha.getCellByPosition(1, 0)
oCelula.setString("Satisfeito")
Set oCelula = oPlanilha.getCellByPosition(2, 0)
oCelula.setString("Nem Satisfeito / Nem Insatisfeito")
Set oCelula = oPlanilha.getCellByPosition(3, 0)
oCelula.setString("Insatisfeito")

'COLUNA
Set oCelula = oPlanilha.getCellByPosition(0, 1)
oCelula.setString("Tempo de Resposta")
Set oCelula = oPlanilha.getCellByPosition(0, 2)
oCelula.setString("Conteúdo da Resposta")
Set oCelula = oPlanilha.getCellByPosition(0, 3)
oCelula.setString("Facilidade de Acesso ao Sistema")

'Valores internos na tabela
For i=1 To 3
Set oCelula = oPlanilha.getCellByPosition(1, i)
oCelula.setValue(i+2)
Set oCelula = oPlanilha.getCellByPosition(2, i)
oCelula.setValue(i+4)
Set oCelula = oPlanilha.getCellByPosition(3, i)
oCelula.setValue(i-1)
Next

'GRÁFICO
Set classSize = objCoreReflection.forName("com.sun.star.awt.Rectangle")
classSize.createObject aStruct
Set Rect = aStruct

Dim RangeAddress(0)
Set classSize = objCoreReflection.forName("com.sun.star.table.CellRangeAddress")
classSize.createObject aStruct
Set RangeAddress(0) = aStruct

Set Charts = oDoc.getSheets().getByIndex(0).Charts

Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 3
RangeAddress(0).EndRow = 3

Call Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Set Chart = Charts.getByName("MyChart").embeddedObject

Set Chart.Diagram = Chart.createInstance("com.sun.star.chart.BarDiagram")

Chart.HasMainTitle = True
Chart.Title.String = "Title"
Chart.HasSubTitle = True
Chart.Subtitle.String = "Subtitle"
Chart.HasLegend = True
Chart.Legend.CharHeight = 7
Chart.Diagram.HasYAxisTitle = True
Chart.Diagram.YAxisTitle.String = "Total de Respostas"
End Sub

Mascara para qualquer tipo de campo

Uutilizada no evento onkeypress

HORA: return txtBoxFormat(this.form, this.name, '99:99', event);
CEP: return txtBoxFormat(this.form, this.name, '99999-999', event);
TELEFONE: return txtBoxFormat(this.form, this.name, '(99)9999-9999', event);
CPF: return txtBoxFormat(this.form, this.name, '999.999.999-99', event);


function txtBoxFormat(objForm, strField, sMask, evtKeyPress) {
var i, nCount, sValue, fldLen, mskLen,bolMask, sCod, nTecla;

if(document.all) { // Internet Explorer
nTecla = evtKeyPress.keyCode;
} else if(document.layers) { // Nestcape
nTecla = evtKeyPress.which;
} else {
nTecla = evtKeyPress.which;
if (nTecla == {
return true;
}
}

sValue = objForm[strField].value;
// Limpa todos os caracteres de formatação que
// já estiverem no campo.
// toString().replace [transforma em sring e troca elementos por ""]
sValue = sValue.toString().replace( "-", "" );
sValue = sValue.toString().replace( "-", "" );
sValue = sValue.toString().replace( ".", "" );
sValue = sValue.toString().replace( ".", "" );
sValue = sValue.toString().replace( "/", "" );
sValue = sValue.toString().replace( "/", "" );
sValue = sValue.toString().replace( "/", "" );
sValue = sValue.toString().replace( "(", "" );
sValue = sValue.toString().replace( "(", "" );
sValue = sValue.toString().replace( ")", "" );
sValue = sValue.toString().replace( ")", "" );
sValue = sValue.toString().replace( " ", "" );
sValue = sValue.toString().replace( " ", "" );
sValue = sValue.toString().replace( ":", "" );
sValue = sValue.toString().replace( ":", "" );
fldLen = sValue.length;
mskLen = sMask.length;

i = 0;
nCount = 0;
sCod = "";
mskLen = fldLen;

while (i <= mskLen) { bolMask = ((sMask.charAt(i) == "-") || (sMask.charAt(i) == ":") || (sMask.charAt(i) == ".") || (sMask.charAt(i) == "/")) bolMask = bolMask || ((sMask.charAt(i) == "(") || (sMask.charAt(i) == ")") || (sMask.charAt(i) == " ") || (sMask.charAt(i) == ".")) //Se for true utiliza elementos especiais aumenta a máscara if (bolMask) { sCod += sMask.charAt(i); mskLen++; //Caso false mostra o sValue(o q foi digitado) } else { sCod += sValue.charAt(nCount); nCount++; } i++; } objForm[strField].value = sCod; if (nTecla != { // backspace if (sMask.charAt(i-1) == "9") { // apenas números... return ((nTecla > 47) && (nTecla < 58)); } // números de 0 a 9
else { // qualquer caracter...
return true;
}
} else {
return true;
}
}