Exporting Data to Excel from a windows forms .Net application

My Blog Readers, please check out: My latest venture. http://loqly.me – a way for you to ask questions and get answers about local businesses around you. iTunes link: http://bit.ly/e5u4jv

Exporting data to excel from a web application is really easy. All you need to do is write out an HTML table and change the content type to excel.

But how do you accomplish the same in a windows forms application ? The most obvious solution is Excel Automation. So, here is an example of how I exported a Datagrid to excel. This example is for a .Net 2.0 winform app. But the same code can be used with minor alterations for .Net 1.1 also. (You will know what to alter when your code doesn’t compile. When it starts compiling, it will start working)

Private Sub ExportGridToExcel()
Dim Excel As Object = CreateObject(“Excel.Application”)

If Excel Is Nothing Then
MsgBox(“It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.”, MsgBoxStyle.Critical)
Return
End If

‘Make Excel visible
Excel.Visible = True

‘Initialize Excel Sheet
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

Add header row to Excel Sheet by copying column headers from the Datagrid

Dim Col As DataGridViewColumn
Dim i As Integer = 1
For Each Col In dgRemovaLog.Columns
.Cells(1, i).Value = Col.HeaderText
i += 1
Next

‘Add data to excel sheet by looping through the rows
‘in the datagrid
i = 2
Dim RowItem As DataGridViewRow
Dim Cell As DataGridViewCell
For Each RowItem In dgRemovaLog.Rows
Dim j As Integer = 1
For Each Cell In RowItem.Cells
.Cells(i, j).Value = Cell.Value
j += 1
Next
i += 1
Next
End With

Excel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing
MsgBox(“Export to Excel Complete”, MsgBoxStyle.Information)
End Sub

Advertisements

52 thoughts on “Exporting Data to Excel from a windows forms .Net application

  1. Hi, Could you tell me why it complain these two lines of code: dgRemovaLog.Columns and dgRemovaLog.Rows

    ” columns, rows are not member of datagrid”? Thanks.

  2. Nerds-central is starting up (still small and young) a moderated email discussion list on this sort of subject. BTW – Nerds-Central is non commercial :-). If anyone is interested you can get to the email list via http://www.nerds-central.com. I have posted ways to do the same stuff as above but using JScript. I have recently been helping out a person (with success) to get it method to work with JScript.net.

  3. I am also receiving the “Columns are not a member of ‘System.Windows.Form.Datagrid'” and I am using .Net 1.1.
    Any advice you can give me?

  4. Great code and really straight forward. Had one issue though – when exporting numbers that are text fields, the leading zeros are lost. How can I set it to keep leading zeros.
    For eg: zipcode 08805 is exported as 8805.

    Adding a single quote (‘), retains the leading zeroes, but I would prefer to not go that route. Any way to set the column text format to a text field
    – Artee

  5. thanks ive overcome the initial problem and infact my excel sheet gets populated thanx to u.but immediately after it throws this exception: Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND)).

    pls help…………….

  6. This code is not working for me. I am using visual studio2005 for a windows application in c#.
    what is the equivalent statement for CreateObject()

  7. Sonia, you don’t need an equivalent of createobject. Createobject works everywhere whether it is a windows app or web app. Only reason createobject may fail is that you don’t have excel installed on your box.

  8. Hi, i was looking for this kind of code, to work on a windows mobile application that I’m working on. I have the data grid fill, I just cant make the data grid to be exported to excel. Any suggestion. Running on VB 2005, .net2.0 windows mobile 5.0
    Appreciate any help.. Thanks..

  9. Hi, Could you tell me why it complain these two lines of code: dgRemovaLog.Columns and dgRemovaLog.Rows

    ” columns, rows are not member of datagrid”?

    I am using .net 1.1 and VS 2003.

    Plz DO give me a feed back.

    Thanks.

  10. I am also having difficulty with dgremovaLog. I am on .NET 2.0 but the name dgremovaLog is not declared. I think there is something missing here, but I am too new to coding to even guess at what it is. It would be helpful to have an alternative and the links provided in previous posts were not helpful to me.

    Thank you.

  11. Hi, all of you this is a working code of exporting datagrid in excel in VS 2003 or .net 1.1

    First add reffrence of excel in your project through project Tab of menu or right clicking the project in solution explorer
    after selecting add reffrence select com tab & select Microsoft excel reffrence
    then

    this is code on click event of button

    Dim Excel As Object = CreateObject(“Excel.Application”)

    If Excel Is Nothing Then
    MsgBox(“It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.”, MsgBoxStyle.Critical)
    Return
    End If

    ‘Make Excel visible
    Excel.Visible = True
    Dim dt As DataTable = DataGrid1.DataSource

    Dim currentRow As New System.Text.StringBuilder

    Dim entireFile As New System.Text.StringBuilder

    ‘Initialize Excel Sheet
    With Excel
    .SheetsInNewWorkbook = 1
    .Workbooks.Add()
    .Worksheets(1).Select()

    Dim c As Long = 1
    For Each dc As DataColumn In dt.Columns
    .Cells(1, c).Value = dc.ColumnName.ToString
    .Cells(1, c).Font.Bold = True
    c += 1
    Next

    Dim i As Long = 2
    For Each dr As DataRow In dt.Rows
    c = 1
    For Each dc As DataColumn In dt.Columns
    .Cells(i, c).Value = dr.Item(dc.ColumnName)
    c += 1
    Next
    i += 1
    Next

    Excel.Visible = True

    End With

    Excel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
    Excel = Nothing
    MsgBox(“Export to Excel Complete”, MsgBoxStyle.Information)

    • Rakesh,
      Thanks a lot . . . today i finished my job bcoz of your code..its a gf8 code …
      Pls can u provide the same code for C#.

  12. Excellent code…really works great. However, I have a question and there seems not to be much on the topic:

    How might one go about APPENDING data to an existing Excel spreadsheet?

    Would you search the cells until you found a null value? Then insert new data? Or is there some kind of Workbook.Rows.Count that I can’t seem to find in the Interop? Your comments would be much appreciated.

    Again, excellent code. Thanks for posting your code and helping everyone out!

  13. Great code thanks for it.
    but the problem is with the date format.
    its not able to convert the dates properly.

    would be helpful if its done too.

  14. What a great find, I’d be interested to know if [kpizzle] ever found out how to append to an exsiting excel spreadsheet. Would really come in handy for future reference.

    I myself am looking for a way to automatically save the spreadsheet with today’s date (not part of the exported data) as part of the filename.

    Great code, works great and thanks also to whomever included the net 1.1 version.

    • One quick thing to try is to not show excel until it is done. (Excel.visible
      = false )

      There are multiple ways of rewriting this code to make it more efficient. But let us try the quick route first. Update your comment if this helps.

  15. I’m trying my hands on Windows applications in Visual C# 2005, its a kind of calculator. I have a windows application form, and tabs in the form to accomodate multiple windows. I want to export required number of labels & textbox from a particular Tab into excel file. Can u help me with a code?

  16. Your code is working fine.and i have used in my project. this method.
    But I Need show dialog box for save or open.
    what can i do?
    Please reply me…
    Thanks
    RAJADURAI M

  17. Thank you very much
    i m tooo much satisfy with the excel code bt now i want to export gridview to the word document
    then what should i do ???

    ple give rpl or hind on my email id
    i wil wait for your replay early

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s