VSTO - Guia básico de sobrevivência VB .NET e Excel

.NET - Guia básico de sobrevivência VB .NET e Excel

Este artigo foi concebido para cobrir os conceitos básicos necessários para controlar os programas do Microsoft Office quando automatizados a partir de uma aplicação VB.NET.

Os exemplos utilizados aqui envolvem o Microsoft Excel, mas os princípios são os mesmos quando você precisar automatizar outros programas MS Office como o Access ou MS Word.

Você pode usar o VBA- Visual Basic for Applications para automatizar as aplicações Office e eu tenho publicado alguns artigos sobre esse assunto na Seção VBA do Site, mas utilizar os recursos da plataforma .NET trás algumas vantagens:

A principal vantagem para o desenvolvedor MS Office usar a plataforma .NET é a tipagem forte que ela oferece. Tanto a linguagem C# como a VB.NET impõe uma tipagem forte e desencoraja o uso do tipo de dados variant e de objetos sem tipagem;
Da mesma forma, o uso da late-binding (ligação tardia) não é incentivado, embora seja nativamente habilitado pelo VB .NET (a linguagem C# também pode executar a ligação tardia, mas faz isso através de código Reflection); Ao impor a tipagem forte e desencorajar o uso da ligação tardia, muitos mais erros podem ser capturados em tempo de compilação, em vez de em tempo de execução, quando muitas vezes é tarde demais;
A Memória gerenciada (coleta de lixo) proporcionada pela plataforma .NET é outra razão fundamental pela qual um programador deve considerar o uso da plataforma .NET na automação do Office, via VB .NET ou C#, embora no caso de automatizar aplicativos do Microsoft Office, isso pode realmente nos levar a situações mais complexas;
A plataforma .NET também suporta os conceitos de programação avançadas, tais como herança, delegados, genéricos, classes de coleção e código de reflexão. (Embora talvez essa não seja uma necessidade imediata do típico desenvolvedor do MS Office, estas características tornam-se de maior valor com o aumento da complexidade das aplicações e com o avança na habilidade do programador.)

O primeiro passo para automatizar um programa do Microsoft Office com VB.NET é referenciar a Biblioteca do projeto necessária em seu projeto VB.NET.

Aqui nós estaremos automatizando o Microsoft Excel, de maneira que teremos que adicionar uma referência à biblioteca de objetos do Microsoft Excel. O procedimento para fazer isso é quase igual ao que era feito no velho e bom VB 6.0.

Então vamos começar...

Lembrando que você precisa ter os seguinte recursos instalados:

Visual Basic 2010 Express Edition ou o Visual Studio 2010
Microsoft Excel 2007

1- Configurando o VB .NET para trabalhar com o Microsoft Excel em uma aplicação Windows Forms

Vamos criar uma aplicação VB .NET com as seguinte funcionalidades:

que referência uma pasta de trabalho (arquivo) Excel criando assim um objeto Excel na aplicação VB .NET;
que abre um arquivo Excel já existente;

Abra o Visual Basic 2010 Express Edition e no menu File clique em New Project;

Selecione o template Windows Forms Application e informe nome VBNET_Excel e clique no botão OK;

A partir da ToolBox inclua no formulário form1.vb 3 controles Buttons (btnCriar, btnAbrir e btnCancelar) conforme o leiaute abaixo:

No menu Project clique em Add Reference;

Na janela Add Reference selecione a guia COM e marque o item Microsoft Excel 12.0 Object Library e clique no botão OK;

A seguir selecione o formulário form1.vb na janela Solution Explorer e clique no ícone View Code;

A seguir inclua no início do formulário a declaração : Imports Excel = Microsoft.Office.Interop.Excel

Os tipos e membros do namespace Microsoft.Office.Interop.Excel fornecem suporte para interoperabilidade entre o modelo de objeto do Microsoft Excel 2010 e aplicações gerenciadas que automatizam o Excel 2010.

A seguir, logo após a declaração do formulário vamos definir dois objetos Excel: Application e WorkBook;

Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook

O objeto Application é usado para referenciar uma pasta de trabalho Excel (arquivo .xlxs);
Um WoorkBook ou uma pasta de trabalho é o arquivo de MS Excel no qual você entra e armazenar os seus dados relacionados;
Workbooks é uma coleção de todos os objetos Workbook;
Uma worksheet ou planilha (também conhecida como spreadsheet) é um conjunto de células em uma "folha" única onde você realmente mantem e manipula os seus dados no Excel;
Worksheets é uma coleção de objetos Worksheet;
Cada pasta de trabalho pode conter muitas planilhas.(É muito parecido com um livro. Um livro contém uma história inteira. Cada página do livro contém uma parte da história.) Muitas vezes um livro tem um tema único, como um orçamento pessoal. Cada planilha teria, então, uma parte do tema, tal como o orçamento discriminado por mês;

Dessa forma temos que :

o objeto Workbook representa uma pasta de trabalho ou arquivo Excel;
o objeto Worksheet representa uma planilha;
o objeto Sheet representa uma planilha ou gráfico;
o objeto Range representa um intervalo de células;
o objeto Range tem uma propriedade Cells que representa uma célula da planilha;
A propriedade Cells carrega um ou dois índices como seus parâmetros. Ex: Cells(índice) ou Cells(linha, coluna);

Vamos agora incluir o código no evento Click de cada botão de de comando de forma que quando o usuário clicar em um botão o código seja executado:

1- Código do botão Criar - Cria uma instância do Excel na aplicação VB .NET

 Private Sub btnCriar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriar.Click
        'Inclui um Novo Workbook
        xlWorkBook = xlApp.Workbooks.Add
        'Exibe o Excel
        xlApp.Visible = True
    End Sub

Ao clicar no botão teremos a criação e exibição do arquivo Excel - Pasta1.xlxs:

2- Código do botão Abrir - Abre um arquivo Excel (WorkBook) existente na aplicação VB .NET

 Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbrir.Click
        'Abre um Workbook existente. (Ajuste o caminho para o seu arquivo Excel)
        xlWorkBook = xlApp.Workbooks.Open("C:\dados\Categorias.xlsx")

        'Exibe o Excel
        xlApp.Visible = True
    End Sub

Ao clicar no botão a abertura do arquivo Excel - Categorias.xlxs:

3- Código do botão Fechar - Encerra a aplicação VB .NET

    Private Sub btnCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelar.Click
        Me.Close()
    End Sub

Neste momento não iremos nos preocupar com o fechamento da aplicação Excel. Abordaremos esse assunto mais adiante.

Inserindo dados na planilha Excel

Vamos agora mostrar como inserir dados no arquivo Excel criado no evento Click do botão Criar.

Então que tal incluir nas colunas A e B da planilha Excel o texto abaixo que representa uma tabela de despesa mensal ?

Mês	Despesa
Janeiro	1000,00
Fevereiro	1500,00
Março	1200,00
Abril	1100,00
Maio	1400,00

Como vamos inserir texto na planilha temos que definir um objeto WorkSheet que representa uma planilha. Para isso inclua a linha de código abaixo (destacada em azul) na declaração dos objetos Excel:

'Define os objetos Excel
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

A seguir teremos que ajustar o código do evento Click do botão Criar conforme abaixo:

Private Sub btnCriar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriar.Click
        'Inclui um Novo Workbook
        xlWorkBook = xlApp.Workbooks.Add
        'Exibe o Excel
        xlApp.Visible = True

        'Define a planiliha na qual desejamos inserir o texto
        xlWorkSheet = xlWorkBook.Sheets("Plan1")

        With xlWorkSheet
            'Inclui o texto diretamente nas células
            .Range("A1").Value = "Mês"
            .Range("A2").Value = "Janeiro"
            .Range("A3").Value = "Fevereiro"
            .Range("A4").Value = "Março"
            .Range("A5").Value = "Abril"
            .Range("A6").Value = "Maio"

            .Range("B1").Value = "Despesa"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
            .Range("B6").Value = "1400.00"
        End With
    End Sub

O resultado o obtido pela execução do código é a abertura da planilha conforme a figura abaixo:

Inserindo Fórmulas na planilha Excel

Vamos agora realizar duas operações de cálculo na planilha criada no item anterior.

Vamos calcular o valor médio das despesas e escrever o resultado na linha 7 e a seguir calcular o valor total das despesas inserindo-o na linha 8 da planilha.

Para isso temos que ajustar novamente o código do evento Click do botão Criar conforme abaixo:

    Private Sub btnCriar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriar.Click
        'Inclui um Novo Workbook
        xlWorkBook = xlApp.Workbooks.Add
        'Exibe o Excel
        xlApp.Visible = True

        'Define a planiliha na qual desejamos inserir o texto
        xlWorkSheet = xlWorkBook.Sheets("Plan1")

        With xlWorkSheet
            'Inclui o texto diretamente nas células
            .Range("A1").Value = "Mês"
            .Range("A2").Value = "Janeiro"
            .Range("A3").Value = "Fevereiro"
            .Range("A4").Value = "Março"
            .Range("A5").Value = "Abril"
            .Range("A6").Value = "Maio"

            .Range("B1").Value = "Despesa"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
            .Range("B6").Value = "1400.00"

            'Define o título para despesa média e despesa total
            .Range("A6").Value = "Despesa Total"
            .Range("A7").Value = "Despesa Média"

            'Insere as fórmulas para o cálculo
            .Range("B7").Formula = "=Sum(B2:B6)"
            .Range("B8").Formula = "=Average(B2:B6)"
        End With
    End Sub

O resultado obtido é mostrado na figura abaixo:

Formatando texto na planilha Excel

Vamos agora realizar a formatação dos dados inseridos para torná-los mais apresentável.

Vamos então realizar as seguintes formatações:

Ajustar o tamanho das Colunas;
Sombrear dos títulos (mês, o dinheiro gasto, as despesas totais e despesas médias) para Preto;
Alterar a cor da fonte do título para Azul;
Formatar o montante a ser exibido como $ ####,##;
Criar Bordas nas células;

Abaixo temos o código em azul que foi incluído para realizar as tarefas acima:

Private Sub btnCriar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriar.Click
        'Inclui um Novo Workbook
        xlWorkBook = xlApp.Workbooks.Add
        'Exibe o Excel
        xlApp.Visible = True

        'Define a planiliha na qual desejamos inserir o texto
        xlWorkSheet = xlWorkBook.Sheets("Plan1")

        With xlWorkSheet
            'Inclui o texto diretamente nas células
            .Range("A1").Value = "Mês"
            .Range("A2").Value = "Janeiro"
            .Range("A3").Value = "Fevereiro"
            .Range("A4").Value = "Março"
            .Range("A5").Value = "Abril"
            .Range("A6").Value = "Maio"

            .Range("B1").Value = "Despesa"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
            .Range("B6").Value = "1400.00"

            'Define o título para despesa média e despesa total
            .Range("A7").Value = "Despesa Total"
            .Range("A8").Value = "Despesa Média"

            'Insere as fórmulas para o cálculo
            .Range("B7").Formula = "=Sum(B2:B6)"
            .Range("B8").Formula = "=Average(B2:B6)"

            'Sombrear os títulos da planilha
            With .Range("A1:B1")
                .Interior.ColorIndex = 1 'Cor de fundo da célula preto
                With .Font
                    .ColorIndex = 2 'Cor da Fonte Branco
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With
            'Sombrear os títulos das despesasa totais e media
            With .Range("A7:A8")
                .Interior.ColorIndex = 1 'Cor de fundo da célula preto
                With .Font
                    .ColorIndex = 4 'Cor da Fonte Amarelo
                    .Size = 8
                    .Name = "Verdana"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            'Definir o formato numérico
            .Range("B2:B8").NumberFormat = "$#,##0.00"

            'Criar as bordas
            With .Range("A1:B8")
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
            End With

            'Auto ajustar o texto nas colunas 
            .Columns("A:B").EntireColumn.AutoFit()
        End With
    End Sub

A execução do projeto cria a planilha conforme exibida na figura abaixo:

Criando uma tabela na planilha Excel

Agora vamos criar uma tabela na planilha Excel.

Para isso vamos incluir um novo botão de controle no formulário form1.vb com nome btnCriarTabela e texto - Criar Tabela;

A seguir inclua o código abaixo no evento Click deste botão de comando:

  Private Sub btnCriarTabela_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriarTabela.Click
        'Inclui um Novo Workbook
        xlWorkBook = xlApp.Workbooks.Add
        'Exibe o Excel
        xlApp.Visible = True

        'Define a planiliha na qual desejamos inserir o texto
        xlWorkSheet = xlWorkBook.Sheets("Plan1")

        With xlWorkSheet
            'Inclui o texto diretamente nas células
            .Range("A1").Value = "Mês"
            .Range("A2").Value = "Janeiro"
            .Range("A3").Value = "Fevereiro"
            .Range("A4").Value = "Março"
            .Range("A5").Value = "Abril"
            .Range("A6").Value = "Maio"

            .Range("B1").Value = "Despesa"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
            .Range("B6").Value = "1400.00"

            'Define o título para despesa média e despesa total
            .Range("A7").Value = "Despesa Total"
            .Range("A8").Value = "Despesa Média"

            'Insere as fórmulas para o cálculo
            .Range("B7").Formula = "=Sum(B2:B6)"
            .Range("B8").Formula = "=Average(B2:B6)"

            'Altera o intervalo em um formato tabular
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$8"), , Excel.XlYesNoGuess.xlYes).Name = "Tabela1"

            'Formata a tabela: define o estilo 
            .ListObjects("Tabela1").TableStyle = "TableStyleDark10"

            'Formata o total o total e a média das despesas
            With .Range("A7:A8")
                .Interior.ColorIndex = 1  'cor de fundo Preta
                With .Font
                    .ColorIndex = 2       'Cor de fonte Branca
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            'Auto ajustar o texto nas colunas 
            .Columns("A:B").EntireColumn.AutoFit()
        End With
    End Sub

O controle ListObject exibe os dados em uma série de linhas e colunas.

O resultado obtido será a planilha exibida na imagem abaixo:

Pegue o projeto completo aqui: VBNET_Excel.zip

Joã 4:13 Replicou-lhe Jesus: Todo o que beber desta água tornará a ter sede;

Joã 4:14 mas aquele que beber da água que eu lhe der nunca terá sede; pelo contrário, a água que eu lhe der se fará nele uma fonte de água que jorre para a vida eterna.

Referências:

José Carlos Macoratti

Excel e VB - Importando e Exportando dados - Macoratti.net

C# - Criando um Arquivo Excel - Macoratti.net

ASP.NET - Lendo planilhas do Excel - Macoratti.net

C# - Lendo, Incluindo e Atualizado dados de uma planilha Excel

Excel - Extração automática de dados - Macoratti.net

VBA - Criando um projeto no Excel - Macoratti.net

ASP .NET - Exportando Excel para GridView e ... - Macoratti.net

SQL - Gravando os dados de uma planilha Excel no ... - Macoratti.net