How to create excel sheet ?

[nokomoli]

Hi guys,

i am facing problem when i need to create an excel file and insert some data inside. Can anyone help me? i am using vs 2005 and excel 2003.

[c0deFr3aK]

You can use this sample. Export from DataGridView to Excel

Imports Excel = Microsoft.Office.Interop.Excel

        Dim excel As Excel.Application
        Dim xWorkBook As Excel.Workbook
        Dim xWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim row As Integer
        Dim col As Integer
        
        excel = New Excel.ApplicationClass
        xWorkBook = excel.Workbooks.Add(misValue)
        xWorkSheet = xWorkBook.Worksheets.Add
        xWorkSheet.Name = "MyWorkSheet"


'Create your connection,sql statements etc. and fill in your DataGridview
'then follow this codes
 For row = 0 To DataGridView1.Rows.Count - 2
            For col = 0 To DataGridView1.Columns.Count - 1
                xWorkSheet.Cells(8, col + 2) = DataGridView1.Columns.Item(col).HeaderText 
                xWorkSheet.Cells(row + 9, col + 2) = DataGridView1(col, row).Value.ToString()
                xWorkSheet.Columns.AutoFit()
            Next
        Next

'That's it. You can change all the variables to fit your project
'Save your Excel File
xWorkSheet.SaveAs("C:\Excel Exported Files\"&"MyExportedFile.xls")
        xWorkBook.Close()
        excel.Quit()
'release all objects
        releaseObject(excel)
        releaseObject(xWorkBook)
        releaseObject(xWorkSheet)

[nokomoli]

Thanks ur reply. i have tried your code that you provided, unfortunately when i copy ur code into my coding page and run it, i get error "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." and stuck at "xWorkSheet.SaveAs("C:\abc.xls")"
I have tried also change the path name to server.mappath("abc.xls"), but still cannot. Can anyone tell me how i solve this problem?

[samir_ibrahim]

Hi guys,

i am facing problem when i need to create an excel file and insert some data inside. Can anyone help me? i am using vs 2005 and excel 2003.

Here the way to do it, and you have to explore by your self.

Dim _Ex = New Excel.Application
_Ex.DisplayAlerts = False ' Or True in case you want to see the alerts

Dim _Wb As Excel.Workbook
_Wb = _Ex.Workbooks.Add()

Dim _Ws As Excel.Worksheet
_Ws = _Wb.ActiveSheet

_Ex.Visible = True

' put some data
_Ws.Range("A1").Select()
_Ex.ActiveCell.FormulaR1C1 = "My Data 1"
' or in single line such as
_Ws.Range("A2").FormulaR1C1 = "My Data 2"
' save the excel sheet
_Wb.SaveAs("c:\myexcel.xls")
' quit the excel
_Ex.Quit()

[Nikita Jetani]

first to add reference into project:
how it possible?????????????
Ans: project(menu) -> add Reference -> com[tab] -> microsoft excel 5.0 object library

now it done....

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object,_
ByVal e As System.EventArgs) Handles Button1.Click

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
xlWorkSheet.Cells(1, 1) = "http://vb.net-informations.com"
xlWorkSheet.SaveAs("C:\vbexcel.xls")

xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("Excel file created , you can find the file c:\")
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

End Class