integrarea visual basic cu excel

Download Integrarea Visual Basic Cu Excel

Post on 06-Apr-2018

222 views

Category:

Documents

1 download

Embed Size (px)

TRANSCRIPT

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    1/14

    Integrarea Visual Basic cu Microsoft Excel

    Punctul de pornire n realizarea acestui articol l constituie problemarealizrii de rapoarte profesionale n formate accesibile diverilor utilizatori.De regul, fiecare mediu de programare a aplicaiilor care se respect

    pune la dispoziia programatorilor componente specifice pentru realizareade rapoarte. Sunt arhicunoscute generatoarele de rapoarte din VisualFoxPro, Magic sau Visual Basic. Fiecare dintre ele are puncte tari i puncteslabe, ns n practic se simt mai ales punctele slabe, determinate deflexibilitatea destul de redus n abordarea unor anumite tipuri deprobleme.

    Visual Basic deine utilitarul "Data Report" pentru realizarea rapoartelor.Utilizarea acestuia este foarte simpl, dar de cele mai multe ori n practic,

    facilitile oferite de "Data Report" sunt insuficiente. Soluia pentru acesteinconveniente o poate reprezenta achiziionarea contra cost a unorcomponente specializate, sau utilizarea altor medii, cum este MicrosoftExcel pentru exportarea rapoartelor n formatul dorit. Dei presupune unvolum ceva mai mare de munc pentru realizarea rapoartelor, aceastsoluie ofer n schimb o mare flexibilitate (s ne gndim numai la graficelei multitudinea de funcii de care dispune Excel-ul i care vor putea fifolosite prin intermediul Visual Basic).

    Utilizarea n Visual Basic a obiectelor ce aparin altor medii necesitstabilirea unei referine la acestea (meniul Project / References). nfereastra care se deschide se bifeaz bibliotecile de obiecte dorite (pentruExcel se bifeaz Microsoft Excelx.x Object Library unde x.x esteversiunea bibliotecii. Dac aceast bibliotec nu apare n list nseamn cnu este instalat Microsoft Excel pe maina local).

    Fig. 1 - Stabilire referin ctre biblioteca de obiecte Microsoft Excel.

    Dup stabilirea referinei la o bibliotec de obiecte se poate folosi opiunea

    Object Browser (meniul View / Object Browser sau tasta F2) pentru avedea membrii acesteia. S vedem n continuare care sunt cele maiimportante clase, precum i atributele i metodele speifice lor.

    1.Clasa Application

    Clasa Application ncorporeaz toate funcionalitile aplicaiei MicrosoftExcel. Instanierea fiecrui obiect din aceast clas va lansa n execuie onou instan a aplicaiei Excel, chiar dac aceasta nu va fi vizibil pentruutilizator. Aceste instane vor rula pn la invocarea metodei quit.

    Este bine de tiut c n momentul opririi aplicaiei, instanele Excelcreatese vor nchide automat doar dac au fost nchise toate documentele

    1

    http://www.agora.ro/images/126/atelier2a.gifhttp://www.agora.ro/images/126/atelier2a.gif
  • 8/3/2019 Integrarea Visual Basic Cu Excel

    2/14

    create sau deschise de acestea. Este recomandat ca la nchidereaaplicaiei s invocm explicit metoda quit pentru toate instanele Excelcreate, altfel riscm s ocupm inutil resursele sistemului cu instane Excelcare nu mai sunt referite de nici o aplicaie.

    Principalele proprieti ale clasei APPLICATIONProprietate Descriere

    ActiveCellReturneaz un obiect de tip RANGE(grup de celule) ce reprezint celulacurent din fereastra activ.

    ActiveSheetReturneaz un obiect de tip SHEET(foaie de lucru) ce reprezint foaiade lucru activ.

    ActiveWindowReturneaz un obiect de tipWINDOW (fereastr) ce reprezintfereastra activ.

    ActiveWorkbookReturneaz un obiect de tipWORKBOOK (registru de lucru) cereprezint registrul activ.

    CellsReturneaz un obiect de tip RANGEce reprezint toate celulele din foaia

    de lucru activ.

    ColumnsReturneaz un obiect de tip RANGEce reprezint toate coloanele dinfoaia de lucru activ.

    Path Returneaz calea spre MicrosoftExcel.

    RangeReturneaz un obiect de tip RANGEce reprezint o celul sau un grup decelule.

    RowsReturneaz un obiect de tip RANGEce reprezint toate rndurile dinfoaia de lucru activ.

    SheetsReturneaz o colecie de tipSHEETS ce reprezint toate foile delucru din registrul activ.

    Windows

    Returneaz o colecie de tipWINDOWS ce reprezint toate

    ferestrele din toate registrele delucru deschise.

    2

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    3/14

    WorkbooksReturneaz o colecie de tipWORKBOOKS ce reprezint toateregistrele de lucru deschise.

    WorksheetsReturneaz o colecie de tipSHEETS ce reprezint toate foile delucru din registrul activ.

    Principalele metode ale clasei APPLICATION

    Metoda Descriere

    CalculateEfectueaz calculele conformformulelor din toate registrelede lucru.

    ExecuteExcel4Macro Execut o macrocomandMicrosoft Excel 4.0.

    Quit nchide aplicaia.

    RunExecut o macrocomandscris n Visual Basic sauMicrosoft Excel 4.0.

    Undo Anuleaz ultima modificareefectuat.

    Exemplu:

    -instanierea unei aplicaii Excel:

    Dim appExc As NEW Excel.Applicationsau

    Dim appExc As NEW Excel.ApplicationSet appExc = NEW Excel.Application

    2.Clasa Workbook

    Clasa Workbookface parte din colecia Workbooks i reprezint registrulde lucru (documentul) Microsoft Excel.

    Principalele metode ale coleciei WORKBOOKS

    Metoda Descriere

    AddCreeaz un registru de lucru nou. Deasemenea, noul registru de lucru va deveni

    registrul activ.Close nchide toate registrele de lucru deschise.

    3

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    4/14

    Open Deschide registrul de lucru specificat.

    O colecie Workbooks conine toate registrele de lucru (obiecteWorkbook) deschise cu o aplicaie Excel (un obiectApplication). Implicit,elementele coleciei Workbooks sunt numerotate de la 1. Astfel, primul

    registru de lucru deschis cu o aplicaie Excel va fi acesat prinApplication.Workbooks(1).

    Urmtorul exemplu va instania o aplicaie Excel i apoi genereaz un nouregistru de lucru prin intermediul acesteia:

    Dim appExl As New Excel.Application

    AppExl.Workbooks.Add

    sauDim appExl As Excel.ApplicationSet appExl = New Excel.ApplicationAppExl.Workbooks.Add

    Urmtorul exemplu va instania o aplicaie Excel i apoi va deschideregistrul de lucru "Registru.xls" prin intermediul acesteia:

    Dim appExl As New Excel.ApplicationAppExl.Workbooks.Open "C:\Registru.xls"

    sau

    Dim appExl As Excel.ApplicationSet appExl = New Excel.ApplicationAppExl.Workbooks.Open "C:\Registru.xls"

    ATENIE:

    Nu trebuie uitat adugarea n rutina de nchidere a aplicaiei iappExl.Quit, altfel la fiecare execuie a aplicaiei va fi deschis o aplicaieExcel ce nu va mai putea fi nchis dect prin intermediul utilitarului TaskManagerdin Windows sau prin repornirea sistemului de operare.

    Pentru a nchide toate registrele de lucru deschise de o aplicaie Excel seapeleaz metoda Close a coleciei Workbooks. Pentru exemplele de maisus, pentru a nchide registrele create sau deschise se va adugaappExl.Workbooks.Close.

    Principalele proprieti ale clasei WORKBOOK

    Proprietate Descriere

    ActiveSheet Foaia de lucru activ din registru de lucru

    4

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    5/14

    specificat.

    Path Calea ctre registrul de lucru

    Saved

    Va fi TRUE" dac registrul de lucru a fost salvat. Pentru a marca ca salvat un

    registru de lucru fr a-l scrie efectiv pedisc trebuie setat aceast proprietate pe"TRUE".

    Worksheets O colecie ce reprezint toate foile delucru din registrul de lucru.

    Principalele metode ale clasei WORKBOOK

    Metoda Descriere

    ActivateActiveaz registrul de lucru. n cazul ncare activ era un alt registru, respectivuldevine automat inactiv.

    Close nchide registrul de lucru.

    NewWindow Creeaz o nou fereastr pentruregistrul de lucru.

    Save

    Salveaz pe disc registrul de lucru.

    Pentru a marca ca salvat un registru delucru fr a-l scrie pe disc trebuie setatpe valoarea "TRUE" proprietatea Saved.Prima dat cnd se salveaz un registrude lucru se folosete metoda SaveAs.

    SaveAs Salveaz pe disc registrul de lucru, nfiierul specificat ca parametru.

    n exemplul urmtor, vom instania o aplicaie Excel, vom crea un nou

    registru de lucru pe care apoi l vom salva i l vom deschide n aceeaiaplicaie. n final vom nchide registrul de lucru i aplicaia.

    Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookSet appExl = New Excel.ApplicationappExl.Workbooks.Add Set wbkBook = appExl.Workbooks(1)wbkBook.SaveAs "C:\Registru.xls"appExl.Workbooks(1).CloseappExl.Workbooks.Open "C:\Registru.xls"Set wbkBook = appExl.Workbooks(1)wbkBook.CloseappExl.Quit

    5

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    6/14

    sau

    Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookSet appExl = New Excel.ApplicationSet wbkBook = appExl.Workbooks.AddwbkBook.SaveAs "C:\Registru.xls"wbkBook.CloseSet wbkBook = appExl.Workbooks.Open("C:\Registru.xls")wbkBook.CloseappExl.Quit

    3.Clasa WorkSheet

    Clasa WorkSheet face parte din colecia WorkSheets i reprezint foaiade lucru. O colecie Worksheets conine toate foile de lucru dintr-unregistru de lucru (workbook).

    Principalele metode ale coleciei WORKSHEETS

    Metoda Descriere

    AddCreeaz o foaie de lucru nou. Aceasta vadeveni foaia de lucru activ. Un registru delucru conine implicit trei foi de lucru.

    CopyCopie o foaie de lucru ntr-o alt poziie nregistrul de lucru.

    Delete terge o foaie de lucru.Move

    Mut o foaie de lucru ntr-o alt poziie nregistrul de lucru.

    Principalele proprieti ale clasei WORKSHEET

    Proprietate Descriere

    Cells

    Returneaz un obiect de tip RANGE ce

    reprezint toate celulele din foaia de lucru.

    ColumnsReturneaz un obiect de tip RANGE cereprezint toate coloanele din foaia delucru.

    Index Numrul de ordine al foii de lucru n cadrulregistrului de lucru.

    Name Denumirea foii de lucru.

    Next

    Returneaz un obiect de tip

    WORKSHEET ce reprezint foaia de lucruurmtoare.

    6

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    7/14

    PreviousReturneaz un obiect de tipWORKSHEET ce reprezint foaia de lucruanterioar.

    RangeReturneaz un obiect de tip RANGE cereprezint o celul sau un grup de celuledin foaia de lucru.

    Rows Returneaz un obiect de tip RANGE cereprezint toate liniile din foaia de lucru.

    Principalele metode ale clasei WORKSHEET

    Metoda Descriere

    Activate Foaia de lucru devine foaia activ dinregistrul de lucru. Foaia de lucru activanterior devine automat inactiv.

    Calculate Efectueaz calculele conform formulelor dintoate celulele din foaia de lucru.

    Copy Copie foaia de lucru ntr-o alt poziie dinregistrul de lucru.

    Delete terge foaia de lucru din cadrul registrului.

    Move Mut foaia de lucru ntr-o alt poziie nregistrul de lucru.

    SaveAs Salveaz modificrile efectuate n foaia delucru.

    Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet'initializez o aplicatie ExcelSet appExl = New Excel.Application'creez un registru de lucruSet wbkBook = appExl.Workbooks.Add'in registrul de lucru wbkBook'creez o noua foaie de lucruSet wshSheet = wbkBook.Worksheets.Add'Denumesc foaia de lucru'De remarcat c noua foaie este adaugat la "nceputulregistrului"wshSheet.Name = "Foaie de proba"'Introduc un text in casuta A1 din'foaia de lucru "Foaie de proba"wshSheet.Range("A1").Value = "Am reusit!"'Salvez modificarile din registrul de lucru'in fisierul "Registru.xls"wbkBook.SaveAs "C:\Registru.xls"'Inchid registrul de lucruwbkBook.Close'Inchid aplicatia Excel

    7

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    8/14

    appExl.Quit

    Pentru a vedea rezultatul exemplului de mai sus se deschide fiierul"C:\Registru.xls" cu aplicaia Microsoft Excel. Dac nu este specificatcalea atunci cnd se salveaz registrul de lucru, fiierul va fi salvat ndirectorul n care se afl aplicaia (proiectul) realizat n Visual Basic. Oalt variant de a vizualiza aplicaia registrul de lucru nou creat o constituieinvocarea comenzii "Shell" din Visual Basic care va primi ca parametricalea ctre executabilul Excel i numele fiierului care se va deschide.

    4.Clasa Range

    Un obiect de tip Range reprezint o celul, o linie, o coloan sau un grupde celule.

    Principalele proprieti ale clasei RANGEProprietate Descriere

    AddressAdresa grupului de celule. Deexemplu adresa csuei dinstnga sus va fi "$A$1".

    BordersToate cele patru borduri pentruun grup de celule.

    Cells

    Celulele ce alctuiesc grupul de

    celule.

    Column

    Numrul primei coloane dingrupul de celule. Pentru coloana

    A" va fi 1, pentru B" va fi 2 etc.

    Columns

    Returneaz un obiect de tipRANGE ce reprezint toatecoloanele din grupul de celule

    specificat.ColumnWidth

    Limea coloanelor din grupul decelule specificat.

    Font Font-ul pentru grupul de celule.

    FormulaFormula de calcul pentru grupulde celule.

    Height nlimea grupului de celule npuncte (1 punct = 1/72 inch).

    HorizontalAlignment Alinierea orizontal a textului.Poate lua ca valoare una din

    8

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    9/14

    urmtoarele constante xlHAlign(xlHAlignCenter,xlHAlignDistributed,xlHAlignJustify, xlHAlignLeft,xlHAlignRight ).

    MergeArea

    Returneaz un obiect de tipRANGE ce va conine ntregulgrup de celule lipite sau grupate(Merged Cells ) din care faceparte celula specificat. Daccelula nu este lipit atunciaceast proprietate va returnadoar celula specificat.

    MergeCellsDac regiunea specificatconine celule lipite atunciaceast proprietate returneazTRUE.

    Next Celula urmtoare din cadrulgrupului.

    NumberFormat Formatul de numr.

    Orientation Orientarea textului in celule.Poate lua valori ntre 90 i 90

    Previous Celula anterioar din cadrulgrupului.

    Row Numrul primei linii din regiuneaspecificat.

    RowHeight nlimea liniilor din regiuneaspecificat, n puncte (1 punct =1/72 inch).

    Rows Returneaz un obiect de tipRANGE ce reprezint toate liniiledin grupul de celule specificat.

    StyleStilul ce se aplic grupului decelule (ex. Normal", "Percent" etc).

    Text Returneaz textul aflat n celulaspecificat.

    Value Valoarea din celula specificat.Dac obiectul RANGE coninemai multe celule atunci

    9

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    10/14

    proprietatea Value va returna unvector (Array) de valori.

    VerticalAlignment

    Alinierea pe vertical a textului.Poate lua ca valoare una dinurmtoarele constante xlVAlign(xlVAlignBottom, xlVAlignCenter,xlVAlignDistributed,xlVAlignJustify, xlVAlignTop).

    WidthLimea celulei (grupului decelule) n puncte (1 punct = 1/72inch).

    Principalele metode ale clasei RANGEMetoda Descriere

    ActivateActiveaz o celul. Aceasta trebuie sfac parte din regiunea selectat (a sevedea i metoda Select).

    AutoFit

    Modific limea coloanelor dinregiunea specificat astfel nct s vievizibil textul din toate celulele. Sintaxa

    pentru aceast metod este:Range(grupcelule).Columns.AutoFit

    BorderAround

    Adaug o bordur grupului de celulespecificat i seteaz forma bordurii("LineStyle"), grosimea ("Width") iculoarea acesteia ("ColorIndex" sau"Color").

    Calculate Efectueaz calculele conformformulelor din regiunea specificat.

    Clear Anuleaz toate proprietile celulelor.

    ClearFormats Anuleaz formatrile pentru celulelespecificate.

    Copy Copie un grup de celule n regiuneaspecificat.

    Cut Taie un grup de celule i eventual lemut n regiunea specificat.

    Delete terge un grup de celule.Insert Adaug noi celule n regiunea

    10

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    11/14

    specificat.

    Merge Lipete celulele specificate.

    Select Selecteaz regiunea specificat.

    Sort

    Ordoneaz valorile din grupul de celule

    specificat.

    UnMerge "Dezlipete" regiunea selectat ncelule individuale.

    n exemplul urmtor avem cheltuielile i ncasrile pe trimestre i pe bazaacestora se vor calcula profitul pe trimestre i totalul cheltuielilor,

    ncasrilor i profitului la sfritul anului, dup formula Profit=ncasri-Cheltuieli. Rezultatul final va arta astfel:

    Situatia financiara pe trimestreTrimestru Cheltuieli Incasari Profit1 190,021,321 LEI 312,321,432 LEI 122,300,111 LEI2 250,121,332 LEI 645,965,342 LEI 395,844,010 LEI3 213,076,089 LEI 823,332,554 LEI 610,256,465 LEI4 345,266,121 LEI 325,266,664 LEI -19,999,457 LEITotal 998,484,863 LEI 2,106,885,992 LEI 1,108,401,129 LEI

    Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet'initializez o aplicatie ExcelSet appExl = New Excel.Application'creez un registru de lucruSet wbkBook = appExl.Workbooks.AddSet wshSheet = wbkBook.Sheets(1)'Denumesc prima foaie de lucruwshSheet.Name = "Situatia financiara"'Lipesc primele 4 celule de pe prima linie (titlultabelului)wshSheet.Range("A1:D1").MergewshSheet.Range("A1").Value = "Situatia financiara petrimestre"'Titlul va fi scris cu caractere ingrosate'si cu font de 12wshSheet.Range("A1:D1").Font.Bold = TruewshSheet.Range("A1:D1").Font.Size = 12'Aliniez titlul la centruwshSheet.Range("A1:D1").HorizontalAlignment =xlHAlignCenter'Casutele ce contin titlul vor fi'inconjurate cu bordura continua, subtirewshSheet.Range("A1:D1").BorderAround xlContinuous, xlThin'Antetele de coloana vor fi aliniate la centru,'scrise cu caractere ingrosate si inclinate(italice),'fiecare celula cu bordura continua,subtirewshSheet.Range("A2:D2").HorizontalAlignment =

    11

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    12/14

    xlHAlignCenterwshSheet.Range("A2:D2").Font.Bold = TruewshSheet.Range("A2:D2").Font.Italic = TruewshSheet.Range("A2").BorderAround xlContinuous, xlThinwshSheet.Range("B2").BorderAround xlContinuous, xlThinwshSheet.Range("C2").BorderAround xlContinuous, xlThinwshSheet.Range("D2").BorderAround xlContinuous, xlThinwshSheet.Range("A2").Value = "Trimestru"wshSheet.Range("B2").Value = "Cheltuieli"wshSheet.Range("C2").Value = "Incasari"wshSheet.Range("D2").Value = "Profit"wshSheet.Range("A3").Value = 1wshSheet.Range("A4").Value = 2wshSheet.Range("A5").Value = 3wshSheet.Range("A6").Value = 4wshSheet.Range("A3:A6").BorderAround xlContinuous, xlThinwshSheet.Range("A7").Value = "Total"wshSheet.Range("A3:A7").HorizontalAlignment =xlHAlignCenterwshSheet.Range("A7:D7").Font.Bold = TruewshSheet.Range("A7").BorderAround xlContinuous, xlThinwshSheet.Range("B7:D7").BorderAround xlContinuous, xlThin'Formatez celulele ce vor contine valorilewshSheet.Range("B3:D7").NumberFormat = "#,##0 ""LEI"""'Introduc formula pentru calculul profitului pe primaliniewshSheet.Range("D3").Formula = "=C3-B3"'Formula din D3 va fi copiat n toate celulele pn la D6'Excel va actualiza automat formulele n funcie de noilecoordonatewshSheet.Range("D3:D6").FillDown 'Formulele pentru total cheltuieli,incasari i profitwshSheet.Range("B7").Formula = "=SUM(B3:B6)"wshSheet.Range("C7").Formula = "=SUM(C3:C6)"wshSheet.Range("D7").Formula = "=C7-B7"'Introduc datele de calculwshSheet.Range("B3").Value = 190021321wshSheet.Range("C3").Value = 312321432wshSheet.Range("B4").Value = 250121332wshSheet.Range("C4").Value = 645965342wshSheet.Range("B5").Value = 213076089wshSheet.Range("C5").Value = 823332554wshSheet.Range("B6").Value = 345266121wshSheet.Range("C6").Value = 325266664'Se efectueaz toate calculele din foaie conformformulelorwshSheet.Calculate'Potrivire automat a limii coloanelorwshSheet.Range("A1:D7").Columns.AutoFit'Bordur ngroat n jurul tabeluluiwshSheet.Range("A1:D7").BorderAround xlContinuous,xlMedium'Salvez modificrile din registrul de lucru'n fisierul "Registru.xls"wbkBook.SaveAs "C:\Registru.xls"'nchid registrul de lucruwbkBook.Close'nchid aplicatia ExcelappExl.Quit

    Pentru a deschide aplicaia Microsoft Excel prin cod, trebuie introdus lafinal comanda:

    12

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    13/14

    Shell "cale_spre_EXCEL nume_fisier_de_deschis"

    Pentru exemplul dat, pe calculatorul autorilor, funcia Shellarat astfel:

    Shell "C:\Program Files\Microsoft Office\Office\Excel.exe C:\Registru.xls".

    Exemplul de mai sus a fost creat doar pentru familiarizarea cu tehnologiaExcel. n practic datele nu vor fi introduse explicit din cod ci, n cele maimulte cazuri vor fi preluate dintr-o baz de date.

    5.Excel, o alternativa pentru Data Report

    S presupunem c lucrm pe o baz de date n Microsoft Access ceconine tabela clieni, cu urmtoarea structur:

    Fig. 2 - Structura tabelei CLIENTI

    n exemplul urmtor, exist un control de tip "Data", care este conectat latabela Clieni. Pe baza acestuia se obine un raport n Excel, n manieraprezentat n secvena ce urmeaz.

    Dim appExl As Excel.ApplicationDim wbkBook As Excel.WorkbookDim wshSheet As Excel.Worksheet

    Dim intRow As Integer

    Dim intCol As IntegerDim i As IntegerSet appExl = New Excel.ApplicationSet wbkBook = appExl.Workbooks.AddSet wshSheet = wbkBook.Sheets(1)With wshSheet

    .Name = "Clienti"

    .Range("A1:F1").Merge

    .Range("A1").Value = "Lista clienti"

    .Range("A1:F1").Font.Size = 12

    .Range("A1:F1").Font.Bold = True

    .Range("A1:F1").HorizontalAlignment = xlHAlignCenter

    .Range("A2").Value = "Cod".Range("B2").Value = "Denumire"

    .Range("C2").Value = "Cod fiscal"

    .Range("D2").Value = "Localitate"

    13

  • 8/3/2019 Integrarea Visual Basic Cu Excel

    14/14