Click here to Skip to main content
11,931,584 members (63,465 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.

Posted 23-Jul-12 1:27am
Rate this: bad
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();

PS.. ClosedXml only supports .xlsx!
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 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
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.

Plz help me.
bluesathish 16-Jan-13 5:10am
My source code was:
Private Sub DataTableToXLSX(ByVal dt As DataTable)
Dim wb As New XLWorkbook()
wb.Worksheets.Add(dt, dt.TableName.ToString)
wb.SaveAs("E:\" + dt.TableName.ToString() + "xlsx")
Catch ex As Exception
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.
Rate this: bad
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
            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
                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) = _
                    xlSh.Cells(1, j + 1).Font.Bold = True
                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()
                '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"
                    xlObject.Caption = a_sFileTitle
                End If
                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."
                    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
                    If xlWB IsNot Nothing Then
                        xlWB.Close(Nothing, Nothing, Nothing)
                    End If
                    If rg IsNot Nothing Then
                    End If
                    If xlSh IsNot Nothing Then
                    End If
                    If xlWB IsNot Nothing Then
                    End If
                    If xlObject IsNot Nothing Then
                    End If
                End Try
                xlSh = Nothing
                xlWB = Nothing
                xlObject = Nothing
                ' force final cleanup!
            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 | :)
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\\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:
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" 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 :)

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
Web02 | 2.8.151126.1 | Last Updated 1 Mar 2013
Copyright © CodeProject, 1999-2015
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