To create a Excel file in vb.net

To create a Excel sheet and Add values
Add the reference of the Excel object model and then add the below namespace


Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

‘Create the Excel object declaration

' create a excel application object
Dim objExcel As Excel.Application = Nothing
' create a excel workbooks object
Dim objBooks As Excel.Workbooks = Nothing
' create a workbook object
Dim objBook As Excel.Workbook = Nothing
' create a excel sheets object
Dim objSheets As Excel.Sheets = Nothing
' create a excel sheet object
Dim objSheet As Excel.Worksheet = Nothing
' create a excel range object
Dim objRange As Excel.Range = Nothing

' Create a new object of the Excel application object
objExcel = New Excel.Application
objExcel.Visible = False
objExcel.DisplayAlerts = False
' Adding a collection of Workbooks to the Excel object
objBook = CType(objExcel.Workbooks.Add(), Excel.Workbook)

objBooks = objExcel.Workbooks
objSheet = CType(objBooks(1).Sheets.Item(1), Excel.Worksheet)
objSheets = objBook.Worksheets
' Adding multiple worksheets to workbook
objSheets.Add(Count:=6)

' Summary log file sheet
' adding first sheet as summary log file
objBook = objBooks.Item(1)
objSheet = CType(objSheets.Item(1), Excel.Worksheet)
' Assigning the worksheet name and summary log file values
objSheet.Name = “test”
objExcel.Cells(2, 2).Value = “Title”
objExcel.Cells(3, 1).value = “Heading”
objExcel.Cells(4, 1).value = “Content”

objSheet.Range("A2", "Z2").Font.Bold = True
objSheet.Range("A2", "Z2").Font.ColorIndex = 5
objRange = objSheet.CellsWriteDataToExcel(objRange)
objSheet.Range("A2", "Z2").EntireColumn.AutoFit()



' Focus on summary log file sheet
objSheet = objBook.ActiveSheet()
Dim objFirstSheet As Excel.Worksheet
objSheets = objBook.Worksheets
objFirstSheet = CType(objSheets.Item(1), Excel.Worksheet)
objFirstSheet.Activate()

Marshal.ReleaseComObject(objSheet)
Marshal.ReleaseComObject(objSheets)

'Saving the Workbook as a normal workbook format under log location
objBook.SaveAs(“Path of the file”, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, True, False, Excel.XlSaveAsAccessMode.xlNoChange, False, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)




Private Sub WriteDataToExcel(ByVal objCells As Excel.Range)
' get the minutes value
Dim intMin As Integer = Now.Minute
' get the current date value
Dim currentDate As Date = Now.Date
' get the hource
Dim inthr As Integer = Now.Hour
' get the cuirrent time
Dim currentTime As String = inthr.ToString() + ":" + intMin.ToString()

Try

' Assigning values to the excel cells

objCells(3, 2) = strUsername

objCells(4, 2) = currentDate

objCells(5, 2) = currentTime

Catch ex As Exception
objExceptionHandler.ToWrite_ErrorinDB(ex)
objUtilityFunctions.To_WriteAudit("WriteDataToExcel() - CVX_GIL3_DCT_BLLogProcess ", ex.Message)
End Try
End Sub


Attachments

  • To download the Sample excel files creation in vb.net (20382-21352-Sample Code for Data Table to Excel.doc)
  • Comments

    Guest Author: Krunal shaholia25 Sep 2012

    Really a good one.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: