none
error when exporting to Excel RRS feed

  • Question

  • Hi

    I have a problem when exporting date from datagrid to Excel.

    The project was built in Windows 7 64-bit , office 2007 in pc
    The following export code works well on the same device with the previous specifications

    Excel is not exported on a different device .

    What's wrong ?

    Note :  I want the code to export to any version of Office 2007,to 2019

    error

    dll

    my code

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
    
            Dim lRow As Long = 0
            Dim row As Long = 0
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer
            Try
                System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
                xlApp = New Excel.Application()
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")
                For i = 0 To DATAG_CLIEN.RowCount - 2
                    For j = 0 To DATAG_CLIEN.ColumnCount - 1
                        For k As Integer = 1 To DATAG_CLIEN.Columns.Count
                            xlWorkSheet.Cells(1, k) = DATAG_CLIEN.Columns(k - 1).HeaderText
                            xlWorkSheet.Cells(i + 2, j + 1) = DATAG_CLIEN(j, i).Value.ToString()
                        Next
                    Next
                Next
                xlWorkBook.Sheets("Sheet2").Delete()
                xlWorkBook.Sheets("Sheet3").Delete()
                xlWorkSheet.DisplayRightToLeft = Not xlWorkSheet.DisplayRightToLeft
                xlWorkSheet.SaveAs("D:\New folder\" & "excel1.xlsx")
                xlWorkBook.Close()
                xlApp.Quit()
    
                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
     
        End Sub
    
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub

    • Edited by monemas Saturday, December 14, 2019 3:36 PM
    Saturday, December 14, 2019 3:35 PM

Answers

  • SpreadSheetLight
    Is it completely free and work in office 2007 to 2019 or 365
    Public Sub ExportDataTableToNewExcelFile(FileName As String, SheetName As String, Table As DataTable, IncludeHeader As Boolean)
    	Using doc As New SLDocument()
    		doc.ImportDataTable(1, 1, Table, IncludeHeader)
    		doc.RenameWorksheet("Sheet1", SheetName)
    
    		doc.SaveAs(FileName)
    	End Using
    End Sub
    
    This is a starter for creating a new file, rename the default sheet, importing data from a DataTable with option to include column names.


    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

    • Marked as answer by monemas Sunday, December 15, 2019 12:41 PM
    Saturday, December 14, 2019 7:17 PM
    Moderator

All replies

  • I would not automate excel.  I would use NPOI or the open xml sdk.  It allows you to create a excel spread sheet without needing excel

    https://github.com/tonyqus/npoi/

    https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk

    Saturday, December 14, 2019 4:09 PM
  • Hello,

    Excel automation is notorious for breaking like this as Excel is fragile if a version of Excel is different from machine to machine and/or operating system.

    When things like this happen it's better to use Open XML which used without a library is extremely difficult to code if you have not done this before.

    How to get around this? Use a free library such as SpreadSheetLight or EPPlus, both are installed via NuGet. Here is an example using EPPlus where dataTable would be the DataTable from the DataGridView (and if not using a DataTable I highly recommend it), ExportFileName is the name of the file including it's path.

    Use Option Infer On

    Dim newFile = New FileInfo(ExportFileName)
    Using package = New ExcelPackage(newFile)
    	Dim workbook = package.Workbook
    	Dim worksheet = workbook.Worksheets.Add("RightToLeft")
    	worksheet.View.RightToLeft = True
    	worksheet.Cells("A1").LoadFromDataTable(dataTable, True)
    	package.Save()
    End Using
    
    SpreadSheetLight is just as easy but does not support easily right to left.


    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

    Saturday, December 14, 2019 4:19 PM
    Moderator
  • i remove this line

     xlWorkSheet.DisplayRightToLeft = Not xlWorkSheet.DisplayRightToLeft
    And still the same defect

    Saturday, December 14, 2019 4:36 PM
  • i remove this line

     xlWorkSheet.DisplayRightToLeft = Not xlWorkSheet.DisplayRightToLeft
    And still the same defect

    Not surprised, have you considered using Ken's or my recommendations?

    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

    Saturday, December 14, 2019 4:56 PM
    Moderator
  • I will do it
    Saturday, December 14, 2019 5:02 PM
  • SpreadSheetLight
    Is it completely free and work in office 2007 to 2019 or 365
    Saturday, December 14, 2019 6:24 PM
  • SpreadSheetLight
    Is it completely free and work in office 2007 to 2019 or 365
    Public Sub ExportDataTableToNewExcelFile(FileName As String, SheetName As String, Table As DataTable, IncludeHeader As Boolean)
    	Using doc As New SLDocument()
    		doc.ImportDataTable(1, 1, Table, IncludeHeader)
    		doc.RenameWorksheet("Sheet1", SheetName)
    
    		doc.SaveAs(FileName)
    	End Using
    End Sub
    
    This is a starter for creating a new file, rename the default sheet, importing data from a DataTable with option to include column names.


    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

    • Marked as answer by monemas Sunday, December 15, 2019 12:41 PM
    Saturday, December 14, 2019 7:17 PM
    Moderator