Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 1:27am
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 at 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 at 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 at 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 at 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 at 23-Jul-12 7:00am
   
http://blog.nkadesign.com/2008/net-working-with-openoffice-3/
bluesathish at 23-Jul-12 7:25am
   
thanks allu, i'll try this.
Espen Harlinn at 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 at 24-Jul-12 4:42am
   
Thanks for voting me up again :)
bluesathish at 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 at 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 at 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
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 at 23-Jul-12 7:01am
   
this code require Office installed on server.
bluesathish at 23-Jul-12 7:06am
   
yes, its not meet my needs.
bluesathish at 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 at 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 at 23-Jul-12 7:11am
   
its successfully added to my project, but it needs Microsoft Excel COM reference. I got runtime errors.
aarti meswania at 23-Jul-12 7:12am
   
which error you got?
bluesathish at 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 at 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 at 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 at 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 at 16-Jan-13 7:09am
   
hi aarti, alluvialdeposit's solution was almost meet my needs. Thanks for your sharing too.
Espen Harlinn at 24-Jul-12 4:42am
   
Hmm, countered the downvote ...
aarti meswania at 24-Jul-12 5:22am
   
what you mean to say?
Espen Harlinn at 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 at 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 at 15-Jan-13 12:14pm
   
thanks fine post
Aarti Meswania at 16-Jan-13 0:36am
   
welcome dear! :)
Glad to hear this post has been helpful to you! :)
Sarvesvara (BVKS) Dasa at 22-May-13 5:34am
   
Nice one. Will try to use this.
Aarti Meswania at 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
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,476
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,917


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 1 Mar 2013
Copyright © CodeProject, 1999-2014
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