none
Create excel file without Import Microsoft.Office.Interop.Excel RRS feed

  • Question

  • 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.

    Monday, April 22, 2019 5:01 PM

All replies

  • 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
    profile for Karen Payne on Stack Exchange


    Monday, April 22, 2019 5:13 PM
    Moderator
  • 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 5:44 PM
  • 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()

    Monday, April 22, 2019 6:16 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: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

    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.


    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
    profile for Karen Payne on Stack Exchange

    Tuesday, April 23, 2019 1:21 PM
    Moderator
  • 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 1:38 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


    • Edited by Gtripodi Tuesday, April 23, 2019 7:35 PM
    Tuesday, April 23, 2019 5:28 PM