Keskustelu: Ohjelmointikysymykset: VB.NET, VBA: [VB 2008] Excel 2007 + datagridviewer

Sam76 [24.03.2009 11:04:42]

Moro taas.

Olen tässä yrittänyt siirtää Excel-taulukkoa datagridvieweriin mutta se ei nyt onnistu ihan niinkuin pitäisi. Eli onko yleensäkään mahdollista tuoda taulukkoa siten, että siinä olevat funktiot toimisivat myös datagridvieverissä? Alla olevalla koodilla tuotu taulukko avautuu oikein mutta siinä olevat funktiot eivät toimi esim summa.

Dim xlDataSetti As New DataSet
Dim yhteysmerkkijono As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source= C:\taulu1.xls; " + "Extended Properties=""Excel 8.0;"""
Dim xlDataAdapteri As New OleDb.OleDbDataAdapter( _
"SELECT * FROM [Taul1$]", yhteysmerkkijono)
xlDataAdapteri.Fill(xlDataSetti, "Taul1")
DataGridView1.DataSource = xlDataSetti.Tables("Taul1")

Toisaalta voiko datagridviemeriin suoraan määrittää soluille funktiota? Eli esimerkiksi että rivin viimeiseen sarakkeeseen laskettaisiin summa.

Ja vielä sellainen, että voinko tuoda samaan datagridiin tietoa sekä accesista että excelistä, siten että ne molemmat olisivat samaan aikaan näkyvissä? Tämän hetkisillä yrityksillä, ensimmäisenä haettu tieto häviää aina alta pois.

Jos joku heittäisi vähän valoa tunneliin, niin pääsisin täältä ehkä joskus poiskin:)

neau33 [24.03.2009 15:26:18]

Heippa Sam76!

Kaavojen tuonti Excelistä suoraan DataGridView-objektiin ei onnistu varsinkaan OleDb-yhteyden avulla, mutta sensijaan voisit päivittää DataGridView-tauluun tekemäsi muutokset takaisin Exceliin käyttämällä hyväksesi esim. Microsoft Office 2003/2007 PIAs pakettin Microsoft.Office.Interop.Excel-assemblyä...
Elikäs helpointa on tehdä niin, että estät DataGridView-taulussa MultiSelection mahdollisuuden ja asetat Boolean-vivun jolla estätä solujen arvojen muutokset jos DataGridView1.CurrentRow.Index = DataGridview.Rows.Count - 1 jne...
Office 2003 Update: Redistributable Primary Interop Assemblies
2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

tässä pikku PIAs-esimerkki, jonka pohjalta voit halutessasi alkaa virittelemään...

'Väännetty SharpDevelp 3.0:lla
Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel

Public Partial Class MainForm: Inherits Form

   Public Sub New()
      Me.InitializeComponent()
   End Sub

   Sub Button1Click(sender As Object, e As EventArgs)
      DisableButtons
      GetFromExcelUsingPIAs
      EnableButtons
   End Sub

   Sub Button2Click(sender As Object, e As EventArgs)
      DisableButtons
      WriteToExcelUsingPIAs
      EnableButtons
   End Sub

   Sub GetFromExcelUsingPIAs()

      Dim fullPath As String = "C:\Työkirja1.xls"

        If Dir(fullPath) = "" Then
           MessageBox.Show("Tiedostoa " + fullPath + " ei löydy!")
           fullPath = Nothing: Exit Sub
        End If

      Dim xlApp As Excel.Application = _
      CType(CreateObject("Excel.Application"), Excel.Application)

      Dim xlBook As Excel.Workbook  = _
      CType(xlApp.Workbooks.Open(fullPath), Excel.Workbook)

      Dim xlSheet As Excel.WorkSheet = _
      CType(xlBook.Worksheets(1), Excel.Worksheet)

      xlSheet.Application.Visible = False

        If xlSheet Is Nothing Then
          fullPath = Nothing
          xlApp = Nothing: xlBook = Nothing
          xlSheet = Nothing: KillExcelProcess
          MessageBox.Show( _
          "Tiedonsiirrossa tapahtui odottamaton virhe!")
          Exit Sub
        Else
          Dim xlDataSet As DataSet = New DataSet()
          Dim xlTable As DataTable = _
          xlDataSet.Tables.Add(xlSheet.Name)
          Dim i As Integer = 0: Dim j As Integer = 0
          xlBook.Sheets(1).Activate
            Dim CurrentRangeColumns As Integer = _
            xlBook.ActiveSheet.Cells.SpecialCells( _
            Excel.XlCellType.xlCellTypeLastCell).Column

         Dim CurrentRangeRows As Integer = _
         xlBook.ActiveSheet.Cells.SpecialCells( _
         Excel.XlCellType.xlCellTypeLastCell).Row

          For i = 1 To CurrentRangeColumns
             Dim xlColumn As DataColumn = New DataColumn()
             xlColumn.DataType = Type.GetType("System.Object")
             Dim xlColumnName() As String = _
             xlSheet.Cells(1, i).Address.Split("$")
             xlColumn.ColumnName = xlColumnName(1)
              xlTable.Columns.Add(xlColumn)
              xlColumnName = Nothing
              xlColumn = Nothing
         Next i

         For i = 1 To CurrentRangeRows
            Dim xlRow As DataRow = xlTable.NewRow()
            For j = 1 To CurrentRangeColumns
                  xlRow(j - 1) = xlSheet.Cells(i, j).Value
            Next j
            xlTable.Rows.Add(xlRow)
            xlRow = Nothing
         Next i: i = Nothing

         DataGRidView1.DataSource = xlDataSet.Tables(xlSheet.Name)

         CurrentRangeRows = Nothing: CurrentRangeColumns = Nothing
         xlSheet = Nothing: xlBook = Nothing
         xlApp = Nothing: fullPath = Nothing
         KillExcelProcess
      End If

   End Sub

   Sub WriteToExcelUsingPIAs()

      Dim fullPath As String = "C:\Työkirja1.xls"

      If Dir(fullPath) = "" Then
         MsgBox("Tiedostoa " + fullPath + " ei löydy.")
         Exit Sub
      End If

      Dim xlApp As Excel.Application = _
      CType(CreateObject("Excel.Application"), Excel.Application)

      Dim xlBook As Excel.Workbook  = _
      CType(xlApp.Workbooks.Open(fullPath), Excel.Workbook)

       Dim xlSheet As Excel.WorkSheet = _
       CType(xlBook.Worksheets(1), Excel.Worksheet)

       xlSheet.Application.Visible = False

       If xlSheet Is Nothing Then
          xlApp = Nothing: xlBook = Nothing
          xlSheet = Nothing: KillExcelProcess
       End If

       DataGridView1.ClearSelection
       Dim changed As Boolean = False
       Dim i As Integer = 0

       For i = 0 To DataGridView1.RowCount -1
          Dim j As Integer = 0
          DataGridView1.Rows(i).Selected = True
          For j = 0 To DataGridView1.ColumnCount - 1
             If DataGridView1.SelectedCells.Item(j).Value _
             <> xlSheet.Cells(i + 1, j + 1).Value Then
                xlSheet.Cells(i + 1, j + 1).Value = _
                DataGridView1.SelectedCells.Item(j).Value
                If changed = False Then
                   changed = True
                End If
             End If
          Next j: j = Nothing
          DataGridView1.Rows(i).Selected = False
       Next i: i = Nothing

       If changed Then
          xlApp.DisplayAlerts = False
          xlBook.SaveAs(fullPath)
          xlApp.DisplayAlerts = True
       End If

       changed = Nothing: fullPath = Nothing
       xlApp = Nothing: xlBook = Nothing
       xlSheet = Nothing: KillExcelProcess

    End Sub

    Sub KillExcelProcess()

      Dim xlProcess As Process() = _
      Process.GetProcessesByName("Excel")

      If Not xlProcess Is Nothing Then
         Dim i As Integer = 0
         For i = xlProcess.GetLowerBound(0) _
         To xlProcess.GetUpperBound(0)
            Try
               xlProcess(i).Kill()
            Catch ex As Exception
            End try
         Next i: i = Nothing
        End If

        xlProcess = Nothing

   End Sub

   Sub DisableButtons()
      Button1.Enabled = False
      Button2.Enabled = False
   End Sub

   Sub EnableButtons()
      Button1.Enabled = True
      Button2.Enabled = True
   End Sub

   Sub MainFormFormClosing(sender As Object, _
   e As FormClosingEventArgs)
      Me.Dispose
   End Sub

   Sub MainFormFormClosed(sender As Object, _
   e As FormClosedEventArgs)
      End
   End Sub

End Class

PS. palataan vaikka hieman myöhemmin tuhon Excelistä & Accessista samaan DataGridView-tauluun tuomiseen...

Sam76 [24.03.2009 17:29:13]

Kiitos Nea! Näistä ohjeista oli/on apua.

pa. arvasinkin että sinulta tulee tähän vastaus. Edellisten viestien perusteella olet aikamoinen ässä näitten office-systeemien(kin) kanssa:)

neau33 [25.03.2009 19:02:59]

Heippa Sam76!

tässä AccessExcelMix -> DataGridView esimerkki...

Imppaa IsOffice.dll täältä, pura paketti projektin bin\debug-kansioon ja lisää projektiin referenssi...
Jos haluat rekisteröidä assemblyn GAC:een niin pura paketti esim. C:\Windows\Microsoft.Net\Framework\v2.0.50727 -hakemistoon, copy/pasteta lihavoitu pätkä
CD C:\Windows\Microsoft.Net\Framework\v2.0.50727 | gacutil -i IsOffice.dll | ngen install IsOffice.dll
avaa komentokehote, klikkaa ikkunassa hiiren oikealla, valise liitä & painele Enter-näppäintä...

asennuksen purku:
CD C:\Windows\Microsoft.Net\Framework\v2.0.50727 | ngen uninstall IsOffice | gacutil -u IsOffice...

Mikäli tökkii niin etsi Windowsin Findillä gacutil.exe & ngen.exe...jos löytyy lisää polku/polut Windowsin Path ympäristömuuttujaan...jos ei löydy imppaa Netistä

'Väännetty SharpDevelop 3.0:lla
'Formille:
'2 nappia & DataGridView-kontrolli

Imports IsOffice
Imports System.Data
Imports Access = Microsoft.Office.Interop.Access
Imports Excel = Microsoft.Office.Interop.Excel

Public Partial Class MainForm

   Const mso_path As Integer = 0
   Const mso_exe As Integer = 1
   Const mso_ver As Integer = 2

   Const first As Integer = 1
   Const second As Integer = 2
   Const both As Integer = 3

   Shared WithEvents AccApp As Access.Application
   Shared WithEvents xlApp As Excel.Application
   Private MixTable As New DataTable
   Private bindingSource1 As New BindingSource()
   Private ver As String = Nothing
   Private dbPath As String = Nothing
   Private xlPath As String = Nothing

   Public Sub New()
      Me.InitializeComponent()
   End Sub

   Sub MainFormLoad(sender As Object, e As EventArgs)

      DisableButtons(both)
        If Not msOffice.GetReg("Access") Is Nothing Then
           dbPath  = "C:\Tietokanta.mdb"
           If Dir(dbPath) = "" Then
              MessageBox.Show( _
              "Tiedostoa " + dbPath + " ei löydy")
              dbPath = Nothing: Me.Close
           End If
           ver = _
         msOffice.GetReg("Access")(mso_ver).Replace(".0","")
      Else
          MessageBox.Show ( _
          "Microsoft Officea tai sen osaa Access ei ole asennettu")
          Me.Close
       End If

      If Not msOffice.GetReg("Excel") Is Nothing Then
         xlPath = "C:\Työkirja1.xls"
           If Dir(xlPath) = "" Then
              MessageBox.Show( _
              "Tiedostoa " + xlPath + " ei löydy")
              xlPath = Nothing: Me.Close
           End If
      Else
         MessageBox.Show ( _
         "Microsoft Officen osaa Excel ei ole asennettu")
      End If
      EnableButtons(first)
   End Sub

   Sub Button1Click(sender As Object, e As EventArgs)
      If dataGridView1.Rows.Count > 0 Then
         MixTable = Nothing
         MixTable = New DataTable
      End If
      DisableButtons(both)
      GetFromAccess
      EnableButtons(second)

   End Sub

   Sub Button2Click(sender As Object, e As EventArgs)
      DisableButtons(both)
      GetFromExcel
      EnableButtons(first)
   End Sub

   Sub EnableButtons(ByVal enable As Integer)
      Select Case enable
         Case first
            Me.button1.Enabled = True
         Case second
            Me.button2.Enabled = True
         Case both
            Me.button1.Enabled = True
            Me.button2.Enabled = True
      End Select
   End Sub

   Sub DisableButtons(ByVal disable As Integer)
      Select Case disable
         Case first
            Me.button1.Enabled = False
         Case second
            Me.button2.Enabled = False
         Case both
            Me.button1.Enabled = False
            Me.button2.Enabled = False
      End Select
   End Sub

   Sub GetFromAccess()

      AccApp = CType(CreateObject( _
      "Access.Application." & ver), Access.Application)
      AccApp.Visible = False
      AccApp.OpenCurrentDatabase(dbPath)

      Dim ColumnsCreated As Boolean = False
      Dim db As dao.Database = AccApp.CurrentDb
      Dim rs As dao.Recordset = _
      db.OpenRecordset("Taulu1")
      rs.MoveFirst

      Do While Not Rs.EOF
         Dim cnt As Integer = -1
         Dim drow As DataRow = MixTable.NewRow()
         For Each fld As dao.Field In rs.Fields
            With fld
               If Not ColumnsCreated Then
                  Dim dcol As New DataColumn
                  dcol.ColumnName = .Name
                  dcol.DataType = Type.GetType("System.Object")
                  dcol.AllowDBNull = True
                  MixTable.Columns.Add(dcol)

                  If MixTable.Columns.Count _
                  = rs.Fields.Count Then
                     ColumnsCreated = True
                  End If
                  dcol = Nothing
               End If
               cnt += 1
               drow(cnt) = .Value
            End With
         Next
         MixTable.Rows.Add(drow)
         drow = Nothing: cnt = Nothing
         rs.MoveNext
      Loop

      ColumnsCreated = Nothing
      rs.Close: rs = Nothing
      db.Close: db = Nothing
      KillApplicationProcess("Msaccess")
      AccApp = Nothing
      bindingSource1.DataSource = MixTable
      dataGridView1.DataSource =  bindingSource1

   End Sub

   Sub GetFromExcel()

      xlApp  = _
      CType(CreateObject("Excel.Application"), Excel.Application)
      Dim xlBook As Excel.Workbook  = _
      CType(xlApp.Workbooks.Open(xlPath), Excel.Workbook)

      Dim xlSheet As Excel.WorkSheet = _
      CType(xlBook.Worksheets(1), Excel.Worksheet)

      xlSheet.Application.Visible = False

      If xlSheet Is Nothing Then
         xlApp = Nothing: xlBook = Nothing
         xlSheet = Nothing: KillApplicationProcess("Excel")
         Exit Sub
      End If


      xlBook.Sheets(1).Activate
      Dim CurrentRangeRows As Integer = _
      xlBook.ActiveSheet.UsedRange.Cells.SpecialCells( _
      Excel.XlCellType.xlCellTypeLastCell).Row

      For i As Integer = 1 To CurrentRangeRows
         Dim drow As DataRow = MixTable.NewRow()
         For j As Integer = 0 To MixTable.Columns.Count -1
            drow(j) = xlSheet.Cells(i, j + 1).Value
         Next j
         MixTable.Rows.Add(drow)
         drow = Nothing
      Next i

      CurrentRangeRows = Nothing
      xlSheet = Nothing: xlBook = Nothing
      xlApp = Nothing
      KillApplicationProcess("Excel")

   End Sub

   Sub KillApplicationProcess(Byval AppName As String)

      Dim AppProcess As Process() = _
      Process.GetProcessesByName(AppName)

      If Not AppProcess Is Nothing Then
         Dim i As Integer = 0
         For i = AppProcess.GetLowerBound(0) _
         To AppProcess.GetUpperBound(0)
            Try
               AppProcess(i).Kill()
            Catch ex As Exception
            End try
         Next i: i = Nothing
        End If

        AppProcess = Nothing

   End Sub

   Sub MainFormFormClosing(sender As Object, _
    e As FormClosingEventArgs)
      Me.Dispose
   End Sub


   Sub MainFormFormClosed(sender As Object, _
   e As FormClosedEventArgs)
      End
   End Sub

End Class

Sam76 [25.03.2009 20:15:01]

Kiitos taas Nea. Täytyy vaan sanoa, että olet kyllä aikamoinen :)

Vastaus

Aihe on jo aika vanha, joten et voi enää vastata siihen.

Kirjautuminen

Haku

Tehtävät