Export Dinamic in Excel dintr-un formular .Net


    Ce inseamna asta?

    Un formular care prin anumiti parametri (un Combo conectat la o baza de date si un Buton) poate scrie un rand in Excel. De fiecare data cand se apasa Buton-ul, se adauga o alta linie in functie de valoarea selectata in Combo. 

    Fisierul se salveaza pe zile, daca s-a depasit ziua curenta se creaza un alt document si o ia de la inceput.

   Pentru a nu incarca aplicatia cu mult cod pentru formatarea fisierului Excel, am recurs la varianta de a crea un Template si il apelez din cod. Template-ul se creaza realizand fisierul Excel in formatul dorit si salvat template (.xlt)

Demo

 
  
Code Snippet

Codul este cat de cat comentat, sper sa intelegeti.

  1. Try
  2.             Dim connexiune As New OleDb.OleDbConnection
  3.             ‘conexiunea cu baza de date
  4.             connexiune.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Certificate Distrugere\Certificare distrugere.mdb”
  5.             ‘se declara o comanda
  6.             Dim comand As New OleDb.OleDbCommand()
  7.             Dim reader As OleDb.OleDbDataReader
  8.  
  9.             connexiune.Open()
  10.             comand.CommandText = “SELECT CD_clienti_auto.*, CD_voucher_radiere.*” & _
  11.             “FROM CD_clienti_auto INNER JOIN CD_voucher_radiere ON CD_clienti_auto.NrCrt = CD_voucher_radiere.NrCrt ” & _
  12.             “WHERE CD_clienti_auto.NrCrt =” & cmbnume.SelectedValue & “;”
  13.  
  14.             comand.Connection = connexiune
  15.  
  16.             Dim excel_app As Excel.Application
  17.             ‘Dim wkbook As Excel.Workbook
  18.             ‘ Dim wksheet As Excel.Worksheet
  19.  
  20.  
  21.             ‘se declara linia in excel
  22.             Dim row As Integer
  23.  
  24.  
  25.  
  26.             ‘”D:\Certificate Distrugere\lista.xlt “)
  27.  
  28.             excel_app = CreateObject(“Excel.Application”)
  29.             excel_app.Visible = False
  30.  
  31.             ‘declararea path =ului catre fisierul template din pc
  32.             Dim strfile As String
  33.             strfile = “D:\Certificate Distrugere\Certificate Distrugere\” & Format(Now, “short date”).ToString & “.xls”
  34.  
  35.             ‘Incepe citirea din baza de date
  36.             reader = comand.ExecuteReader
  37.  
  38.             If reader.Read = True Then
  39.  
  40.                 ‘fisierul se salveaza pe zile. Daca trece ziua, se creaza alt fisier
  41.                 ‘verifica faca exista fisierul salvat, se adauga o linie noua
  42.  
  43.                 If File.Exists(strfile) Then
  44.                     ‘se deschide fisierul
  45.                     excel_app.Workbooks.Add(strfile)
  46.                     ‘se face insert in excel
  47.                     ‘MessageBox.Show(reader.Item(“CD_clienti_auto.Numeprenume”) & ”  sau ” & reader.Item(“numeprenume”))
  48.                     With excel_app
  49.                         row = .Range(“A65536″).End(Excel.XlDirection.xlUp).Row
  50.  
  51.                         row = row + 1
  52.                         .Range(“A” & Format$(row)).Select()
  53.                         .ActiveCell.FormulaR1C1 = reader.Item(“CD_clienti_auto.NrCrt”)
  54.                         .Range(“B” & Format$(row)).Select()
  55.                         .ActiveCell.FormulaR1C1 = reader.Item(“Numeprenume”)
  56.                         .Range(“C” & Format$(row)).Select()
  57.                         .ActiveCell.FormulaR1C1 = reader.Item(“cnp”)
  58.                         .Range(“D” & Format$(row)).Select()
  59.                         .ActiveCell.FormulaR1C1 = reader.Item(“telfixmob”)
  60.                         .Range(“E” & Format$(row)).Select()
  61.                         .ActiveCell.FormulaR1C1 = reader.Item(“email”)
  62.                         .Range(“F” & Format$(row)).Select()
  63.                         .ActiveCell.FormulaR1C1 = reader.Item(“categorie”)
  64.                         .Range(“G” & Format$(row)).Select()
  65.                         .ActiveCell.FormulaR1C1 = reader.Item(“marca”)
  66.                         .Range(“H” & Format$(row)).Select()
  67.                         .ActiveCell.FormulaR1C1 = reader.Item(“tip”)
  68.                         .Range(“I” & Format$(row)).Select()
  69.                         .ActiveCell.FormulaR1C1 = reader.Item(“an_fabricatie”)
  70.                         .Range(“J” & Format$(row)).Select()
  71.                         .ActiveCell.FormulaR1C1 = reader.Item(“an_prima_inmatriculare”)
  72.                         .Range(“K” & Format$(row)).Select()
  73.                         .ActiveCell.FormulaR1C1 = reader.Item(“ult_nr_inmatriculare”)
  74.                         .Range(“L” & Format$(row)).Select()
  75.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_identificare”)
  76.                         .Range(“M” & Format$(row)).Select()
  77.                         .ActiveCell.FormulaR1C1 = reader.Item(“serie_motor”)
  78.                         .Range(“N” & Format$(row)).Select()
  79.                         .ActiveCell.FormulaR1C1 = reader.Item(“capacitate_cilindrica”)
  80.                         .Range(“O” & Format$(row)).Select()
  81.                         .ActiveCell.FormulaR1C1 = reader.Item(“greutate”)
  82.                         .Range(“P” & Format$(row)).Select()
  83.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_seria_cert_inmatriculare”)
  84.                         .Range(“Q” & Format$(row)).Select()
  85.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_seria”)
  86.                         .Range(“R” & Format$(row)).Select()
  87.                         .ActiveCell.FormulaR1C1 = reader.Item(“CD_clienti_auto.nrcrt”)
  88.                         .Range(“S” & Format$(row)).Select()
  89.                         .ActiveCell.FormulaR1C1 = reader.Item(“dataprocesverb”)
  90.                         .Range(“T” & Format$(row)).Select()
  91.                         .ActiveCell.FormulaR1C1 = reader.Item(“nremitentatfisc”)
  92.                         .Range(“U” & Format$(row)).Select()
  93.                         .ActiveCell.FormulaR1C1 = reader.Item(“emitentatfisc”)
  94.                         .Range(“V” & Format$(row)).Select()
  95.                         .ActiveCell.FormulaR1C1 = reader.Item(“serie_ticket_valoric”)
  96.                         .Range(“W” & Format$(row)).Select()
  97.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_ticket_valoric”)
  98.                         .Range(“X” & Format$(row)).Select()
  99.                         .ActiveCell.FormulaR1C1 = “Onu Vasile”
  100.                         .Range(“Y” & Format$(row)).Select()
  101.                         .ActiveCell.FormulaR1C1 = Format(Now, “short date”).ToString
  102.  
  103.  
  104.                         ”se opresc alertele, se salveaza
  105.                         .Application.DisplayAlerts = False
  106.                         .ActiveWorkbook.SaveAs(strfile)
  107.  
  108.                     End With
  109.                     ‘MsgBox(“FLAG1″)
  110.  
  111.  
  112.                     ‘daca fisierul nu exista in ziua curenta, se creaza un nou fisier si se adauga o noua linie
  113.                     ‘linia se adauga dintr-un template si se salveaza in locatia specificata
  114.                 Else
  115.                     ‘ Create a new spreadsheet.
  116.                     excel_app.Workbooks.Add(“D:\Certificate Distrugere\lista.xlt”)
  117.                     ‘MessageBox.Show(reader.Item(“Numeprenume”))
  118.  
  119.                     ‘ Insert data into Excel.
  120.                     With excel_app
  121.  
  122.                         row = .Range(“A65536″).End(Excel.XlDirection.xlUp).Row
  123.  
  124.                         ‘.Columns(“A:A”).ColumnWidth = 35
  125.  
  126.                         ‘.Columns(“B:B”).ColumnWidth = 13
  127.                         row = row + 1
  128.                         .Range(“A” & Format$(row)).Select()
  129.                         .ActiveCell.FormulaR1C1 = reader.Item(“CD_clienti_auto.NrCrt”)
  130.                         .Range(“B” & Format$(row)).Select()
  131.                         .ActiveCell.FormulaR1C1 = reader.Item(“Numeprenume”)
  132.                         .Range(“C” & Format$(row)).Select()
  133.                         .ActiveCell.FormulaR1C1 = reader.Item(“cnp”)
  134.                         .Range(“D” & Format$(row)).Select()
  135.                         .ActiveCell.FormulaR1C1 = reader.Item(“telfixmob”)
  136.                         .Range(“E” & Format$(row)).Select()
  137.                         .ActiveCell.FormulaR1C1 = reader.Item(“email”)
  138.                         .Range(“F” & Format$(row)).Select()
  139.                         .ActiveCell.FormulaR1C1 = reader.Item(“categorie”)
  140.                         .Range(“G” & Format$(row)).Select()
  141.                         .ActiveCell.FormulaR1C1 = reader.Item(“marca”)
  142.                         .Range(“H” & Format$(row)).Select()
  143.                         .ActiveCell.FormulaR1C1 = reader.Item(“tip”)
  144.                         .Range(“I” & Format$(row)).Select()
  145.                         .ActiveCell.FormulaR1C1 = reader.Item(“an_fabricatie”)
  146.                         .Range(“J” & Format$(row)).Select()
  147.                         .ActiveCell.FormulaR1C1 = reader.Item(“an_prima_inmatriculare”)
  148.                         .Range(“K” & Format$(row)).Select()
  149.                         .ActiveCell.FormulaR1C1 = reader.Item(“ult_nr_inmatriculare”)
  150.                         .Range(“L” & Format$(row)).Select()
  151.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_identificare”)
  152.                         .Range(“M” & Format$(row)).Select()
  153.                         .ActiveCell.FormulaR1C1 = reader.Item(“serie_motor”)
  154.                         .Range(“N” & Format$(row)).Select()
  155.                         .ActiveCell.FormulaR1C1 = reader.Item(“capacitate_cilindrica”)
  156.                         .Range(“O” & Format$(row)).Select()
  157.                         .ActiveCell.FormulaR1C1 = reader.Item(“greutate”)
  158.                         .Range(“P” & Format$(row)).Select()
  159.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_seria_cert_inmatriculare”)
  160.                         .Range(“Q” & Format$(row)).Select()
  161.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_seria”)
  162.                         .Range(“R” & Format$(row)).Select()
  163.                         .ActiveCell.FormulaR1C1 = reader.Item(“CD_clienti_auto.nrcrt”)
  164.                         .Range(“S” & Format$(row)).Select()
  165.                         .ActiveCell.FormulaR1C1 = reader.Item(“dataprocesverb”)
  166.                         .Range(“T” & Format$(row)).Select()
  167.                         .ActiveCell.FormulaR1C1 = reader.Item(“nremitentatfisc”)
  168.                         .Range(“U” & Format$(row)).Select()
  169.                         .ActiveCell.FormulaR1C1 = reader.Item(“emitentatfisc”)
  170.                         .Range(“V” & Format$(row)).Select()
  171.                         .ActiveCell.FormulaR1C1 = reader.Item(“serie_ticket_valoric”)
  172.                         .Range(“W” & Format$(row)).Select()
  173.                         .ActiveCell.FormulaR1C1 = reader.Item(“nr_ticket_valoric”)
  174.                         .Range(“X” & Format$(row)).Select()
  175.                         .ActiveCell.FormulaR1C1 = “Onu Vasile”
  176.                         .Range(“Y” & Format$(row)).Select()
  177.                         .ActiveCell.FormulaR1C1 = Format(Now, “short date”).ToString
  178.  
  179.                         ‘MsgBox(“FLAG2″)
  180.                         ‘ Save the results.
  181.  
  182.                         ‘se salveaza in path general
  183.                         .ActiveWorkbook.SaveAs(strfile)
  184.                     End With
  185.  
  186.  
  187.                 End If
  188.  
  189.                 ‘ Close the workbook
  190.                 excel_app.ActiveWorkbook.Close()
  191.  
  192.                 ‘ Close Excel.
  193.                 excel_app.Quit()
  194.                 ‘ excel_app = Nothing
  195.  
  196.  
  197.  
  198.             End If
  199.  
  200.             reader.Close()
  201.  
  202.  
  203.             connexiune.Close()
  204.             MessageBox.Show(“S-a realizat cu succes exportul!”, “Success”, MessageBoxButtons.OK, MessageBoxIcon.Information)
  205.  
  206.             ‘MsgBox(“Ok”)
  207.  
  208.         Catch except As Exception
  209.  
  210.             MessageBox.Show(“Probleme la export in Excel. Este ceva important, contacteaza de urgenta Administratorul ” & except.Message, “Eroare”, MessageBoxButtons.OK, MessageBoxIcon.Error)
  211.  
  212.  
  213.         End Try

 

PS: Codul mai poate fi optimizat, dar cand l-am creat eram focusat pe rezultate, nu pe performanta.

Follow

Get every new post delivered to your Inbox.

Join 597 other followers