Exportera Access-data till arbetsblad

 

I detta tips demonstreras hur vi exporterar Access-data till en nyskapad arbetsbok i Excel från ett Windows Form.

När vi arbetar med större datamängder är klassisk ADO att föredra då vi kan dumpa data till arbetsblad via metoden CopyFromRecordset i VB.NET. F n saknas denna möjlighet i Excel och med ADO.NET

DataSet arbetar i s k disconnected mode vilket är en fördel jämför med DataReader men om vi vill använda oss av ADO.NET och för större datamängder är DataReader-ansatsen att föredra. Detta då prestandan är till DataReaders fördel.

Ett alternativ till nedanstående ansatser är att skapa XML-filer med data och som läses in i Excel.
 

 Följande "namespaces" måste importeras till projektet:

 

Externa referenser:

Exempel 1: ADO.NET & Datareader:

'© 2004 Alla rättigheter XL-Dennis

'Variabler för databasanslutningen.

Const stDb As String = "e:\Arbetsmaterial\db1.mdb"

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _

                         & "Data Source=" & stDb

Dim stSQL As String = "SELECT * FROM tblSort"

Dim cn As New OleDbConnection(stCon)

Dim cmd As New OleDbCommand(stSQL, cn)

cmd.CommandType = CommandType.Text

cn.Open()

Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

 

'Variabler för Excel och för den skapade arbetsboken.
 Dim xlApp As New Excel.Application

Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _

                                Excel.XlWBATemplate.xlWBATWorksheet)

Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)

Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range("A2")

Dim xlCalc As Excel.XlCalculation

Dim i, j, x, y As Short

 

'Temporär avstängning av Excels kalkylmotor.

With xlApp

      xlCalc = .Calculation

      .Calculation = Excel.XlCalculation.xlCalculationManual

End With

 

'Skriver fältnamnen till arbetsbladet.

j = dr.FieldCount - 1

For i = 0 To j

       xlRange.Offset(-1, i).Value = dr.GetName(i)

Next

 

'Skriver posterna till arbetsbladet.

While (dr.Read())

      For y = 0 To j

            xlRange.Offset(x, y).Value = dr.Item(y)

      Next

      x = x + 1

End While


'Stänger DataReader.

dr.Close()

  

'Gör Excel tillgänglig för användarna.

With xlApp

      .Visible = True

      .UserControl = True

      'Återställer Excels beräkningsstatus.

      .Calculation = xlCalc

End With

 

'Frigör objekten från minnet.
 cmd.Dispose()

cn.Dispose()

dr = Nothing

cmd = Nothing

cn = Nothing

xlRange = Nothing

xlWSheet = Nothing

xlWBook = Nothing

xlApp = Nothing

 

Exempel 2: ADO.NET & Dataset:

 '© 2004 Alla rättigheter XL-Dennis

'Variabler för Excel och den skapade arbetsboken.

Dim xlApp As New Excel.Application

Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _
                                Excel.XlWBATemplate.xlWBATWorksheet)

Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)

Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range("A2")

Dim xlCalc As Excel.XlCalculation

Dim i, x, y As Short

 

'Temporär avstängning av Excels kalkylmotor.

With xlApp

     xlCalc = .Calculation

     .Calculation = Excel.XlCalculation.xlCalculationManual

End With

 

'Variabler för databasanslutningen.

Const stDb As String = "e:\Arbetsmaterial\db1.mdb"

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _

                        & "Data Source=" & stDb

'Hämta all data från tabellen.
'Om önskvärt kan data från flera tabeller erhållas.
 Dim stSQL As String = "SELECT * FROM tblSort"

Dim cn As New OleDbConnection(stCon)

Dim adp As New OleDbDataAdapter(stSQL, cn)

'Användandet av Dataset gör att ADO arbetar i “disconnected mode”.

Dim ds As New DataSet

Dim dt As New DataTable

Dim dc As DataColumn

Dim dr As DataRow

 

'Fyller Dataset.

adp.Fill(ds, "XLData")

 

'Skriver tabellnamnet till arbetsbladet.

xlRange.Value = ds.Tables(0).TableName

 

'Skriver fältnamnen till arbetsbladet.

For Each dc In ds.Tables(0).Columns

      xlRange.Offset(1, i).Value = dc.ColumnName

      i = i + 1

Next

 

'Skriver posterna till arbetsbladet.

For Each dc In ds.Tables(0).Columns

      x = 0

      For Each dr In ds.Tables(0).Rows

           xlRange.Offset(x + 2, y).Value = dr.Item(dc)

           x = x + 1
      Next

      y = y + 1

Next

'Gör Excel tillgänglig för användarna.
With xlApp

     .Visible = True

     .UserControl = True

     .Calculation = xlCalc

End With

 

'Frigör objekten från minnet.

dr = Nothing

dc = Nothing

dt.Dispose()

ds.Dispose()

adp.Dispose()

cn.Dispose()

xlRange = Nothing

xlWSheet = Nothing

xlWBook = Nothing

xlApp = Nothing

 

Example 3: Klassisk ADO & Recordset:
 

'© 2004 Alla rättigheter XL-Dennis


Const stDb As String = "e:\Arbetsmaterial\db1.mdb"

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _

                        & "Data Source=" & stDb

Dim stSQL As String = "SELECT * FROM tblSort"

Dim cnt As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim fld As ADODB.Field

 

'Öppnar databasanslutningen.

cnt.Open(stCon)

 

'Öppnar Recordset.

With rst

    .CursorLocation = CursorLocationEnum.adUseClient

    .Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _

                      ADODB.LockTypeEnum.adLockReadOnly, _

                      ADODB.CommandTypeEnum.adCmdText)

    .ActiveConnection = Nothing

End With

 

'Stänger databasanslutningen.

cnt.Close()

 

'Variabler för Excel och den skapade arbetsboken.

Dim xlApp As New Excel.Application

Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add( _
                                Excel.XlWBATemplate.xlWBATWorksheet)

Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), Excel.Worksheet)

Dim xlRange As Excel.Range = CType(xlWSheet, Excel.Worksheet).Range("A2")

Dim xlCalc As Excel.XlCalculation

Dim i As Short

 

With xlApp

      xlCalc = .