Create excel file without Import Microsoft.Office.Interop.Excel

2019-04-22

Question

_{Monday, April 22, 2019 5:01 PM}

I am using a framework in the vb.net base. But I can not make Microsoft.Office.Interop.Excel imports however I needed to create an excel file with datatable data.

Any suggestions on how to create this xls file?

Thanks.

All replies (8)

_{Monday, April 22, 2019 5:13 PM}

Hello,

There is a library, SpreadSheetLight (totally free and no restrictions) which can create a new Excel .xlsx file (does not work for .xls) and import a DataTable with a single method ImportDataTable. The import does no formatting but SpreadSheetLight has methods to perform styling after the import.

Here is an example I did (full source in the link below)

https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2

Public Function ExportDataTable(ByVal table As DataTable) As Boolean 
    Try 
        Using sl As New SLDocument(ExportFileName) 
            Dim startRow As Integer = 1 
            Dim startColumn As Integer = 1 

            ' redundent 
            sl.SelectWorksheet("Sheet1") 

            ' clear cells if this is ran more than once and the row or column count changes 
            sl.ClearCellContent() 

            ' import DataTable with column headers 
            sl.ImportDataTable(startRow, startColumn, table, True) 

            ' set the Date style 
            Dim dateStyle = sl.CreateStyle 
            dateStyle.FormatCode = "mm-dd-yyyy" 

            sl.SetCellStyle(2, table.Columns("MyDate").Ordinal + 1, table.Rows.Count - 1, table.Columns("MyDate").Ordinal + 1, dateStyle) 

            ' set the column header stype 
            Dim headerSyle = sl.CreateStyle 
            headerSyle.Font.FontColor = Color.White 
            headerSyle.Font.Strike = False 
            headerSyle.Fill.SetPattern(DOS.PatternValues.Solid, Color.Green, Color.White) 
            headerSyle.Font.Underline = DOS.UnderlineValues.None 
            headerSyle.Font.Bold = True 
            headerSyle.Font.Italic = False 
            sl.SetCellStyle(1, 1, 1, table.Columns.Count, headerSyle) 

            ' auto-fit the columns 
            sl.AutoFitColumn(1, table.Columns.Count) 

            ' save back to the Excel file - see also sl.SaveAs 
            sl.Save() 

        End Using 
        Return True 
    Catch ex As Exception 
        theException = ex 
        Return False 
    End Try 
End Function

Here is working example that creates the Excel if not present.

Imports System.Data.SqlClient
Public Class Operations
    Inherits BaseSqlServerConnection
    Public Function ReadCustomers() As DataTable

        Dim dtCustomers As New DataTable

        Dim selectStatement =
                "SELECT CustomerIdentifier,CompanyName,ContactName,[Address]," &
                "City,PostalCode, Country FROM Customers;"

        Using cn = New SqlConnection(ConnectionString)
            Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                cmd.CommandText = selectStatement
                cn.Open()
                dtCustomers.Load(cmd.ExecuteReader)
            End Using
        End Using

        Return dtCustomers

    End Function
End Class

Form code

Imports System.IO
Imports SpreadsheetLight
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) _
        Handles Button1.Click

        Dim fileName = Path.Combine(
            AppDomain.CurrentDomain.BaseDirectory, "KarenPayne.xlsx")

        Dim ops As New Operations
        Dim dt = ops.ReadCustomers()

        Using sl As New SLDocument()
            sl.ImportDataTable("A1", dt, True)
            sl.SaveAs(fileName)
        End Using

    End Sub

End Class

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Monday, April 22, 2019 5:44 PM | 1 vote}

You could use OLEDB/Jet driver to create an excel file

Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

_{Monday, April 22, 2019 6:16 PM | 1 vote}

One of the ways is with ADOX/ADODB

For example =>

'Reference Microsoft ADO Ext. 6.0 for DDL and Security
'Reference ADODB

Dim conn As ADODB.Connection = New ADODB.Connection()
Dim cat As ADOX.Catalog = New ADOX.Catalog()
Dim tbl As ADOX.Table = New ADOX.Table()
Dim col As ADOX.Column = New ADOX.Column()
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & "E:\TestExcel1.XLS" & ";Extended Properties=Excel 8.0")
cat.ActiveConnection = conn
tbl.Name = "TestTable1"
col.Name = "TestCol1"
col.Type = ADOX.DataTypeEnum.adVarWChar
tbl.Columns.Append(col)
cat.Tables.Append(tbl)
conn.Close()

_{Tuesday, April 23, 2019 1:17 PM}

Hello,

This is a good solution. The problem is that since I'm working on a framework where I can not use the "imports" command.

I read about de reflection but i dont get a good example for interop.excel

_{Tuesday, April 23, 2019 1:17 PM}

Hello,

This is a good solution. The problem is that since I'm working on a framework where I can not use the "imports" command.

I read about de reflection but i dont get a good example for interop.excel

_{Tuesday, April 23, 2019 1:21 PM}

Hello,

This is a good solution. The problem is that since I'm working on a framework where I can not use the "imports" command.

I read about de reflection but i dont get a good example for interop.excel

Can you indicate why you can't use an Import statement as this is common place to use Import statements in just about every project.

Refection is a last resort option when you have exhausted all other options. So this not a Visual Studio constraint but something with you, your team or team lead.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Tuesday, April 23, 2019 1:38 PM}

The problem is that since I'm working on a framework where I can not use the "imports" command.

Without "Imports", it can be done with P/Invoke, but it is a lot more code for Interfaces declarations.

_{Tuesday, April 23, 2019 5:28 PM}

Here is an example using OLEDB which also makes use of backgroundworker.

Option Strict On
Public Class FormExportToExcel

    Dim SourceDTable As New DataTable With {.TableName = "ExcelExample"}

    Private Sub FormExportToExcel_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Test Data
        With SourceDTable
            For i As Integer = 0 To 10
                .Columns.Add(New DataColumn With {
                             .ColumnName = "StringColumn" & i,
                             .DataType = GetType(String)})
            Next

            .Columns.Add(New DataColumn With {
                             .ColumnName = "DateColumn",
                             .DataType = GetType(DateTime)})

            .Columns.Add(New DataColumn With {
                             .ColumnName = "IntegerColumn",
                             .DataType = GetType(Integer)})

            For i As Integer = 0 To 10000
                Dim NRow As DataRow = SourceDTable.NewRow
                For Each Dcol As DataColumn In SourceDTable.Columns
                    Select Case Dcol.DataType
                        Case GetType(String)
                            NRow(Dcol.ColumnName) = Dcol.ColumnName & "- Value " & i
                        Case GetType(DateTime)
                            NRow(Dcol.ColumnName) = Now
                        Case GetType(Integer)
                            NRow(Dcol.ColumnName) = Now.Millisecond
                    End Select

                Next
                .Rows.Add(NRow)
            Next

        End With

    End Sub

    Private Sub ButtonExportExcel_Click(sender As Object, e As EventArgs) Handles ButtonExportExcel.Click
        Using ExportExcel As New ExportExcel(SourceDTable)
            ExportExcel.ShowDialog()
        End Using
    End Sub



End Class

Public Class ExportExcel
    Inherits Form
    Dim ProgBar As New ProgressBar With {.Name = "ProgBar"}
    Private WithEvents BGW As New ComponentModel.BackgroundWorker With {.WorkerReportsProgress = True}
    Dim TargetPath As String = String.Empty

    Public Sub New(ByVal _DT As DataTable)
        ProgBar.Dock = DockStyle.Fill
        Controls.Add(ProgBar)

        StartPosition = FormStartPosition.Manual
        Location = Cursor.Position
        Height = 75
        Width = 700

        TargetPath = My.Computer.FileSystem.SpecialDirectories.Desktop & "\" & _DT.TableName & ".XLS"
        BGW.RunWorkerAsync(_DT)

    End Sub

    Private Sub BGW_DoWork(sender As Object, e As ComponentModel.DoWorkEventArgs) Handles BGW.DoWork
        Dim _SourceDT As DataTable = DirectCast(e.Argument, DataTable)
        If My.Computer.FileSystem.FileExists(TargetPath) Then My.Computer.FileSystem.DeleteFile(TargetPath)

        Using OleDBConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TargetPath &
                                                   ";Extended Properties=Excel 8.0;")
            OleDBConn.Open()

            Dim ColNames As String = Nothing
            Dim ColParams As String = Nothing
            Dim ColNamesTypes As String = Nothing


            For Each DCol As DataColumn In _SourceDT.Columns
                ColNames &= DCol.ColumnName & ","
                ColParams &= "@" & DCol.ColumnName & ","
                Select Case DCol.DataType
                    Case GetType(String)
                        ColNamesTypes &= DCol.ColumnName & " String,"
                    Case GetType(DateTime)
                        ColNamesTypes &= DCol.ColumnName & " DateTime,"
                    Case GetType(Integer)
                        ColNamesTypes &= DCol.ColumnName & " INTEGER,"
                End Select
            Next

            ColNames = ColNames.Substring(0, ColNames.Length - 1)
            ColParams = ColParams.Substring(0, ColParams.Length - 1)
            ColNamesTypes = ColNamesTypes.Substring(0, ColNamesTypes.Length - 1)


            Using CreateTableCMD As New OleDb.OleDbCommand("CREATE TABLE " & _SourceDT.TableName &
                                                                 "(" & ColNamesTypes & ")", OleDBConn)
                CreateTableCMD.ExecuteNonQuery()
            End Using

            Dim TotalRows As Integer = _SourceDT.Rows.Count
            Dim i As Integer = 1


            For Each Drow As DataRow In _SourceDT.Rows
                Using InsertCMD As New OleDb.OleDbCommand("INSERT INTO " & _SourceDT.TableName & " (" & ColNames & ") VALUES (" &
                                                      ColParams & ")", OleDBConn)
                    For Each Dcol As DataColumn In _SourceDT.Columns
                        Select Case Dcol.DataType
                            Case GetType(String)
                                InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, Drow(Dcol.ColumnName).ToString)
                            Case GetType(DateTime)
                                InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, CType(Drow(Dcol.ColumnName).ToString, DateTime))
                            Case GetType(Integer)
                                InsertCMD.Parameters.AddWithValue("@" & Dcol.ColumnName, CType(Drow(Dcol.ColumnName).ToString, Integer))
                        End Select
                    Next

                    InsertCMD.ExecuteNonQuery()
                    BGW.ReportProgress(CInt(100 * i / TotalRows))
                    i += 1
                End Using
            Next
            OleDBConn.Close()
        End Using
    End Sub

    Private Sub Me_Closed() Handles Me.FormClosed
        Process.Start(TargetPath)
    End Sub

    Private Sub BGW_ProgressChanged(sender As Object, e As ComponentModel.ProgressChangedEventArgs) Handles BGW.ProgressChanged
        If e.ProgressPercentage = 1 Then ProgBar.Show()
        ProgBar.Value = e.ProgressPercentage
        If e.ProgressPercentage = 100 Then ProgBar.Hide()
    End Sub

    Private Sub BGW_RunWorkerCompleted(sender As Object, e As ComponentModel.RunWorkerCompletedEventArgs) Handles BGW.RunWorkerCompleted
        Close()
    End Sub

End Class

Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

שתף באמצעות

Create excel file without Import Microsoft.Office.Interop.Excel

Question

All replies (8)

משאבים נוספים