Click here to Skip to main content
12,405,932 members (43,221 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB MS-Excel VB.NET DataTable , +
Dear All,
I'm having one datatable, i want to export this datatable's values to .xls file(Excel file) without referring the excel assemblies because i didn't install MS Excel in my pc but instead i've Open office software to process xls files.
So anyone give me the idea how to export my Datatable to .xls file format.

Regards,
BlueSathish
Posted 23-Jul-12 0:27am
Updated 12-Jul-16 6:04am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I can recommend ClosedXml. It's open source and easy to use..

You download it from codeplex and add reference in your project.

var wb = new XLWorkbook();
wb.Worksheets.Add(yourDataTable);
wb.SaveAs("myExcelFile.xlsx");

PS.. ClosedXml only supports .xlsx!
  Permalink  
v2
Comments
bluesathish 23-Jul-12 6:43am
   
Hi AlluvialDeposit,
thanks for your reply, but i want to export it into .xls file only and also i dont want to add any external component like this ClosedXml, Is there any possibility to do this by vb.net codings?
barryblessing 7-Nov-13 20:56pm
   
Sorry if this is too late an answer, but use ClosedXML to export to .XLSX format. Then use Excel 2003 (or higher) or OpenOffice Spreadsheet to save the .XLSX file to .XLS format. The ClosedXML library is very effective.
AlluvialDeposit 23-Jul-12 6:47am
   
without external component.... good luck. I don't think you want to even try to do that in xls-format.. You need a 3rd party library, but that does not mean you will have to install MS Office on your server.
bluesathish 23-Jul-12 6:55am
   
ok But i've Open Office software, how can i use its assemblies to export?, we usually doing using Microsoft.Office.Interop, in Open office do we've similar assemblies to do the same?
AlluvialDeposit 23-Jul-12 7:00am
   
http://blog.nkadesign.com/2008/net-working-with-openoffice-3/
bluesathish 23-Jul-12 7:25am
   
thanks allu, i'll try this.
Espen Harlinn 24-Jul-12 4:41am
   
5'ed! hmm, seems somebody is making an effort at downvoting you - probably a bad hair day, or something :-D
AlluvialDeposit 24-Jul-12 4:42am
   
Thanks for voting me up again :)
bluesathish 16-Jan-13 5:09am
   
hi alluvial, I've tried your idea by using closedxml concept, but i got the following error
System.TypeInitializationException was caught
Message=The type initializer for 'ClosedXML.Excel.XLWorkbook' threw an exception.
Source=ClosedXML
TypeName=ClosedXML.Excel.XLWorkbook

Plz help me.
bluesathish 16-Jan-13 5:10am
   
My source code was:
Private Sub DataTableToXLSX(ByVal dt As DataTable)
Try
Dim wb As New XLWorkbook()
wb.Worksheets.Add(dt, dt.TableName.ToString)
wb.SaveAs("E:\" + dt.TableName.ToString() + "xlsx")
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
bluesathish 16-Jan-13 7:05am
   
I've solved the above error with myself, for this above error we've to add another dll file DocumentFormat.OpenXml.dll into our solution.
Thanks much alluvialdeposit.
Member 8345599 21-Jun-16 11:48am
   
XLWorkbook is not defined. What should I do?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Imports Microsoft
Imports Microsoft.Office.Interop

and add reference dll "Microsoft.Office.Interop.Excel"

function to export data is given below

''' <summary>
    ''' Write Excel file as given file name with given data.
    ''' </summary>
    ''' <param name="a_sFilename">full file name for create excel file.</param>
    ''' <param name="a_sData">data in dataset to be fill in excel shhet.</param>
    ''' <param name="a_sFileTitle">Title of Excel file.</param>
    ''' <param name="a_sErrorMessage">output parameter contains error message if error occurrs.</param>
    ''' <returns>bool</returns>
    Public Function ExportToExcel(ByVal a_sFilename As String, ByVal a_sData As DataSet, ByVal a_sFileTitle As String, ByRef a_sErrorMessage As String) As Boolean
        a_sErrorMessage = String.Empty
        Dim bRetVal As Boolean = False
        Dim dsDataSet As DataSet = Nothing
        Try
            dsDataSet = a_sData
 
            Dim xlObject As Excel.Application = Nothing
            Dim xlWB As Excel.Workbook = Nothing
            Dim xlSh As Excel.Worksheet = Nothing
            Dim rg As Excel.Range = Nothing
            Try
                xlObject = New Excel.Application()
                xlObject.AlertBeforeOverwriting = False
                xlObject.DisplayAlerts = False
 
                ''This Adds a new woorkbook, you could open the workbook from file also
                xlWB = xlObject.Workbooks.Add(Type.Missing)
                xlWB.SaveAs(a_sFilename, 56, Missing.Value, Missing.Value, Missing.Value, Missing.Value, _
                Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
 
                xlSh = DirectCast(xlObject.ActiveWorkbook.ActiveSheet, Excel.Worksheet)
 
                'Dim sUpperRange As String = "A1"
                'Dim sLastCol As String = "AQ"
                'Dim sLowerRange As String = sLastCol + (dsDataSet.Tables(0).Rows.Count + 1).ToString()

                For j = 0 To dsDataSet.Tables(0).Columns.Count - 1
                    xlSh.Cells(1, j + 1) = _
                        dsDataSet.Tables(0).Columns(j).ToString()
                    xlSh.Cells(1, j + 1).Font.Bold = True
                Next
 
                For i = 1 To dsDataSet.Tables(0).Rows.Count
                    For j = 0 To dsDataSet.Tables(0).Columns.Count - 1
                        xlSh.Cells(i + 1, j + 1) = _
                            dsDataSet.Tables(0).Rows(i - 1)(j).ToString()
                    Next
                Next
                xlSh.Columns.AutoFit()
                'rg = xlSh.Range(sUpperRange, sLowerRange)
                'rg.Value2 = GetData(dsDataSet.Tables(0))

                'xlSh.Range("A1", sLastCol & "1").Font.Bold = True
                'xlSh.Range("A1", sLastCol & "1").HorizontalAlignment = XlHAlign.xlHAlignCenter
                'xlSh.Range(sUpperRange, sLowerRange).EntireColumn.AutoFit()
               
                If String.IsNullOrEmpty(a_sFileTitle) Then
                    xlObject.Caption = "untitled"
                Else
                    xlObject.Caption = a_sFileTitle
                End If
 
                xlWB.Save()
                bRetVal = True
            Catch ex As System.Runtime.InteropServices.COMException
                If ex.ErrorCode = -2147221164 Then
                    a_sErrorMessage = "Error in export: Please install Microsoft Office (Excel) to use the Export to Excel feature."
                ElseIf ex.ErrorCode = -2146827284 Then
                    a_sErrorMessage = "Error in export: Excel allows only 65,536 maximum rows in a sheet."
                Else
                    a_sErrorMessage = (("Error in export: " & ex.Message) + Environment.NewLine & " Error: ") + ex.ErrorCode
                End If
            Catch ex As Exception
                a_sErrorMessage = "Error in export: " & ex.Message
            Finally
                Try
                    If xlWB IsNot Nothing Then
                        xlWB.Close(Nothing, Nothing, Nothing)
                    End If
                    xlObject.Workbooks.Close()
                    xlObject.Quit()
                    If rg IsNot Nothing Then
                        Marshal.ReleaseComObject(rg)
                    End If
                    If xlSh IsNot Nothing Then
                        Marshal.ReleaseComObject(xlSh)
                    End If
                    If xlWB IsNot Nothing Then
                        Marshal.ReleaseComObject(xlWB)
                    End If
                    If xlObject IsNot Nothing Then
                        Marshal.ReleaseComObject(xlObject)
                    End If
 
                Catch
                End Try
                xlSh = Nothing
                xlWB = Nothing
                xlObject = Nothing
                ' force final cleanup!
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End Try
        Catch ex As Exception
            a_sErrorMessage = "Error in export: " & ex.Message
        End Try
 
        Return bRetVal
    End Function

try this code

Happy Coding!
Smile | :)
  Permalink  
v2
Comments
AlluvialDeposit 23-Jul-12 7:01am
   
this code require Office installed on server.
bluesathish 23-Jul-12 7:06am
   
yes, its not meet my needs.
bluesathish 23-Jul-12 7:05am
   
Hi Aarti, Thanks for your reply,i don't have the excel in my pc. (read question clearly before you posting your solutions) Can you give me the idea to use by open office assemblies?
aarti meswania 23-Jul-12 7:08am
   
I am not sure. but just try to add reference dll "Microsoft.Office.Interop.Excel" in your project if it's successfully added,
then If you do not have office installed in your pc still you will able to write data in excel file.
but you will just not open that file directly.
bluesathish 23-Jul-12 7:11am
   
its successfully added to my project, but it needs Microsoft Excel COM reference. I got runtime errors.
aarti meswania 23-Jul-12 7:12am
   
which error you got?
bluesathish 23-Jul-12 7:18am
   
This is the error i've got while runtime
"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154."
aarti meswania 23-Jul-12 7:29am
   
have you added "Microsoft Excel 12.0 Object Library"?
path is C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
bluesathish 23-Jul-12 7:48am
   
This Microsoft.Office.Interop.Excel path will be available only if we install Msoffice, otherwise we don't have the path like this.
aarti meswania 23-Jul-12 8:00am
   
okay I have also learnt something new today,
I have told already I am not sure about,
I will definitely try this in free time slot.
thanks for sharing experience
if you get anything new regarding this topic then please share
and sorry I couldn't help you
bluesathish 16-Jan-13 7:09am
   
hi aarti, alluvialdeposit's solution was almost meet my needs. Thanks for your sharing too.
Espen Harlinn 24-Jul-12 4:42am
   
Hmm, countered the downvote ...
aarti meswania 24-Jul-12 5:22am
   
what you mean to say?
Espen Harlinn 24-Jul-12 5:36am
   
That two people have voted 1 for your effort, I do not think it deserved that.
Personally I would solve this along the lines of AlluvialDeposits' suggestion, or use EPPlus: http://epplus.codeplex.com/
aarti meswania 24-Jul-12 5:59am
   
I don't know who had down-voted answer,
It's not so important but main thing is to get a solution of problem.
conclusion of thread was "without installing MS-office on pc, can not read-write excel files using vb.net" I haven't personally faced this situation.
but I will like to experience it, I will also try your & AlluvialDeposits' suggestions
thanks for up-voting my answer. :) good day!
shaikh Khaja Peer 15-Jan-13 12:14pm
   
thanks fine post
Aarti Meswania 16-Jan-13 0:36am
   
welcome dear! :)
Glad to hear this post has been helpful to you! :)
Sarvesvara (BVKS) Dasa 22-May-13 5:34am
   
Nice one. Will try to use this.
Aarti Meswania 22-May-13 6:00am
   
thanks for reading :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Public Function GridExport(ByVal DGV As DataGridView) As Boolean
    If DGV.RowCount > 0 Then
        Dim filename As String = Application.StartupPath & "\Export_" + Format(Now, "hhmmss").ToString + ".xls"
        DGV.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        DGV.SelectAll()
        IO.File.WriteAllText(filename, DGV.GetClipboardContent().GetText.TrimEnd)
        DGV.ClearSelection()
        Process.Start(filename)
        Return True
    Else
        Return False
    End If
End Function
  Permalink  
Comments
Richard MacCutchan 12-Jul-16 12:11pm
   
This question was posted 4 years ago. Please do not resurrect dead questions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 12 Jul 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100