Hi,

I need to import data from an Excelsheet into my DNN 4.8.0 version page, but with no luck so far.

I created this "Public Sub Open_ExcelSheet()" to get the job done:

Dim ExcelPath As String = "D:\Temp\webwinkel.xls"

The code in the "Public sub" part is:

=====================================================
//Start

        Public Sub Open_ExcelSheet()
            Try
                Dim ExcelConnection As System.Data.OleDb.OleDbConnection
                Dim ExcelPath As String = "D:\Temp\webwinkel.xls"

                'Fetch Data from Excel
                Dim DtSet As System.Data.DataSet
                Dim ExcelCommand As OleDbDataAdapter
                ExcelConnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & ExcelPath & " '; " & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
                ExcelConnection.Open()
                ' Select the data from Sheet of the workbook.
                ExcelCommand = New OleDbDataAdapter("select * from [Blad1$]", ExcelConnection)
                DtSet = New System.Data.DataSet("Blad1")
                ExcelCommand.Fill(DtSet, "Blad1")
                grdExcelGrid.DataSource = DtSet
                grdExcelGrid.DataBind()
                ExcelConnection.Close()
            Catch exc As Exception    'Module failed to load
                ProcessModuleLoadException(Me, exc)
            End Try
        End Sub
//end
=====================================================

This sub is called from Page Load to populate the excel data into the datagrid on the mainpage.

The Excel sheet version is Excel 2002.

I think there are two possible sources of problems:

1st: The Excelsheet is not opened, or the worksheet is not recognized (How can I test for that?)

2nd: The dataset is not populated, so the datagrid remains empty. (How can I test for that?)

Thanks,

Jos 

OK, here's the solution:

I created a tracelog routine to check whatever happens during the process. This tracking is rather straight foreward, just put your references in the variabeles. (You can ommit this routine and its references if you don't want it....)

Create an Excelfile with a number of columns, No header (see the HDR=No in the connection string, otherwise put HDR=Yes instead if you do have columns) Put the Excelfile in the directory and fill the "ExcelPath" in the Excel reader routine with the path and Excelname as a reference to the Excelfile.

TraceLog: put this part somewhere into your "Event Handlers"region...

        Public Sub TraceLog(ByVal strModule As String, ByVal strItem As String, ByVal strDetail As String, ByVal strResult As String)
            If boolDoTraceLog Then
                Dim strDateTime As DateTime = Now()
                intLogRecord = intLogRecord + 1
                If Not IO.File.Exists(strTraceLog) Then
                    Dim writer As IO.StreamWriter = IO.File.CreateText()
                    writer.WriteLine("Log#: " & intLogRecord & " Created: " & strDateTime & "; Module: " & strModule & "; Item: " & strItem & "; Detail: " & strDetail & "; Result: " & strResult)
                    writer.Close()
                Else
                    Dim writer As IO.StreamWriter = IO.File.AppendText(strTraceLog)
                    writer.WriteLine("Log#: " & intLogRecord & " Append: " & strDateTime & "; Module: " & strModule & "; Item: " & strItem & "; Detail: " & strDetail & "; Result: " & strResult)
                    writer.Close()
                End If
            End If
        End Sub

1st: The controls region:
        Protected WithEvents grdExcelGrid As System.Web.UI.WebControls.DataGrid
        Public dsArticlePrizes As DataSet

        'Start TraceLog variabeles
        Dim boolDoTraceLog As Boolean = True 'Switch logging on or off
        Dim strTraceLog As String = "D:\Temp\TraceLog.txt" 'Put your tracelog somewhere on your system
        Dim intLogRecord As Integer = 0 'Tracelog counter
        Dim strModule As String = "Your Module Name.vb" 'Modulename to track
        Dim strItem As String = ""
        Dim strDetail As String = ""
        Dim strResult As String = ""
        'End TraceLog variabeles

2nd: the Private Sub Page_Load part...
                '================  Start TraceLog  ================
                strItem = "PageLoad"
                strDetail = "Get Exceldata; "
                strResult = "Start..."
                TraceLog(strModule, strItem, strDetail, strResult)
                '=================  End TraceLog  =================

                txtBuildID.Text = "2008.01.25 - 00.00.07"
                Open_ExcelSheet()
                '================
                grdExcelGrid.DataSource = dsArticlePrizes
                grdExcelGrid.DataBind()
                '================

                'Start TraceLog
                strItem = "PageLoad"
                strDetail = "Get Exceldata; "
                strResult = "End..."
                TraceLog(strModule, strItem, strDetail, strResult)
                'End TraceLog

3rd: call the Excelreader:
        Public Sub Open_ExcelSheet()
            Try
                Dim ExcelConnection As OleDbConnection
                Dim ExcelPath As String = "D:\Temp\webwinkel.xls"

                'Fetch Data from Excel
                Dim ExcelAdapter As OleDbDataAdapter
                ExcelConnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & ExcelPath & " '; " & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")

                ExcelConnection.Open()

                '================  Start TraceLog  ================
                strItem = "Open Excel"
                strDetail = ExcelPath
                strResult = "OK"
                TraceLog(strModule, strItem, strDetail, strResult)
                '=================  End TraceLog  =================

                ' Select the data from Sheet of the workbook.
                ExcelAdapter = New OleDbDataAdapter("select * from [Blad1$]", ExcelConnection)
                dsArticlePrizes = New DataSet
                txtTrace.Text = Convert.ToString(ExcelAdapter.Fill(dsArticlePrizes))

                '================  Start TraceLog  ================
                Dim intRecords As Integer = ExcelAdapter.Fill(dsArticlePrizes)
                strItem = "Get Excel data"
                strDetail = ExcelPath
                strResult = "## of records: " & intRecords
                TraceLog(strModule, strItem, strDetail, strResult)
                '=================  End TraceLog  =================

                ExcelConnection.Close()

                '================  Start TraceLog  ================
                strItem = "Close Excel"
                strDetail = ExcelPath
                strResult = "OK"
                TraceLog(strModule, strItem, strDetail, strResult)
                '=================  End TraceLog  =================

            Catch exc As Exception    'Module failed to load
                ProcessModuleLoadException(Me, exc)
            End Try
        End Sub

4rd: the datagrid in the user interface:
Start Test Excel connection.<br />
<br />
Build: &nbsp;
<asp:TextBox ID="txtBuildID" runat="server" Width="144px"></asp:TextBox>
&nbsp;&nbsp; Trace # records:&nbsp;
<asp:TextBox ID="txtTrace" runat="server" Width="144px"></asp:TextBox>
&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;<br />
<br />
<asp:DataGrid ID="grdExcelGrid" runat="server" AutoGenerateColumns="true" BorderWidth="3px"
    CellPadding="4" EnableViewState="False" ><Columns>

</Columns>
</asp:DataGrid><br />
Eind test Excel connection<br />

The Excelsheet "Webwinkel.xls" contans 4 columns, no columnnames: (Whisky type, Name, Purchase prize, Selling prize)

BLEND ANTIQUARY 12 Y  070 40% 23,23 25,23
BLEND ANTIQUARY 21 Y 070 43% 67,8 69,8
BLEND BAILIE NICOL JARVIE   070 40% 20,74 22,74
BLEND BALLANTINES LTR 40% 21,36 23,36
BLEND BELL'S 8 Y   070 40% 15,26 17,26
BLEND BELL'S 8 Y  GOLDEN JUBILEE DECANTER 60,59 62,59
BLEND BELL'S 8 Y  X-MAS 2002 CERAMIC BELL 63,91 65,91
BLEND BEN NEVIS SUPREME   070 40% 15,26 17,26
BLEND BEN NEVIS 40 Y  BLENDED AT BIRTH 253,6 255,6
BLEND BLACK & WHITE   LTR 43% 18,82 20,82
BLEND BLACK BOTTLE   070 40% 19,82 21,82
BLEND BLACK BOTTLE 10 Y  070 40% 24,89 26,89
BLEND BUCHANAN  DE LUXE  12 Y    LTR 43% 38,74 40,74
BLEND CAMPBELTOWN LOCH   21,9 23,9
BLEND CAMPBELTOWN LOCH 21 Y  070 40% 41,07 43,07
BLEND CHIVAS REVOLVE  075 40% 57,58 59,58
BLEND CHIVAS REGAL  LEGEND   075 43% 52,75 54,75

Success!

Jos Richters