To Create Excel Sheet and Add Values

To create 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)

PrivateSub WriteDataToExcel(ByVal objCells As Excel.Range)

' get the minutes value

Dim intMin AsInteger = Now.Minute

' get the current date value

Dim currentDate AsDate = Now.Date

' get the hource

Dim inthr AsInteger = Now.Hour

' get the cuirrent time

Dim currentTime AsString = 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)

EndTry

End Sub