[VB.NET] dataGridView to Excel Class

Description
     In this article i will share excel class which can convert the data in the DataGrid to excel, very usefull better than code from scratch. the limitation is the data appear in datagridview same data in excel later, you cannot costumize the block field.


How To

here's the exportExcel Class :

Imports System.Windows.Forms
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Threading
Imports System.Globalization
Imports System.Net

Public Class exportExcel
    Dim oexcel As Excel.Application = New Excel.Application()
    Dim obook As Excel.Workbook
    Dim osheet As Object
    Dim customFormatDate As String = "dd/MM/yyyy"

    Public Sub exportExcel(ByVal dataGrid As DataGridView, ByVal savePath As String, ByRef messageOut As Label)
        Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        oexcel = CreateObject("Excel.Application")
        obook = oexcel.Workbooks.Add
        osheet = obook.Worksheets(1)

        'format Header, row index always start at 1
        For columnIndex = 1 To dataGrid.Columns.Count
            osheet.Range(intToAlphabet(columnIndex) & 1).NumberFormat = "@"
            osheet.Range(intToAlphabet(columnIndex) & 1).Value = dataGrid.Columns(columnIndex - 1).HeaderText
            Application.DoEvents()
            messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex) & " " & "Row:1"
        Next columnIndex

        'format Row cell
        For rowIndex = 0 To dataGrid.Rows.Count - 1 'row start from 2 , because row 1 is header
            For columnIndex = 0 To dataGrid.Columns.Count - 1
                If dataGrid.Columns(columnIndex).ValueType.ToString = GetType(Date).ToString Then
                    'Jika column type = DATE
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = Format(dataGrid.Rows(rowIndex).Cells(columnIndex).Value(), customFormatDate)
                Else
                    'Jika column type = STRING
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).NumberFormat = "@"
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = dataGrid.Rows(rowIndex).Cells(columnIndex).Value()
                End If

                Application.DoEvents()
                messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex + 1) & " " & "Row:" & rowIndex + 2
            Next columnIndex
        Next rowIndex

        oexcel.DisplayAlerts = False

        Try
            obook.SaveAs(savePath, Excel.XlFileFormat.xlOpenXMLWorkbook)
        Catch e As Exception
            MsgBox(e.Message & vbCrLf & "Process cancelled !!", vbCritical, "Abort")
        End Try

        oexcel.Quit()
        killCurrentExcel(oexcel) ' you can find this function in different article
        oexcel = Nothing
    End Sub

    Private Sub killCurrentExcel(ByVal oexcel As Object)
        Dim proc As System.Diagnostics.Process
        Dim intPID As Integer
        Dim intResult As Integer
        Dim iHandle As IntPtr
        Dim strVer As String

        strVer = oexcel.Version
        iHandle = IntPtr.Zero
        If CInt(strVer) > 9 Then
            iHandle = New IntPtr(CType(oexcel.Parent.Hwnd, Integer))
        Else
            iHandle = FindWindow(Nothing, oexcel.Caption)
        End If
        oexcel.Workbooks.Close()
        oexcel.Quit()
        Marshal.ReleaseComObject(oexcel)
        oexcel = Nothing
        intResult = GetWindowThreadProcessId(iHandle, intPID)
        proc = System.Diagnostics.Process.GetProcessById(intPID)
        proc.Kill()
    End Sub

    Private Function intToAlphabet(ByVal iCol As Integer) As String
        Dim iAlpha As Integer
        Dim iRemainder As Integer
        intToAlphabet = Nothing 'init value 

        iAlpha = Int(iCol / 27)
        iRemainder = iCol - (iAlpha * 26)
        If iAlpha > 0 Then
            intToAlphabet = Chr(iAlpha + 64)
        End If
        If iRemainder > 0 Then
            intToAlphabet = intToAlphabet & Chr(iRemainder + 64)
        End If
    End Function
End Class

To Use this class :

1. Add reference excel Microsoft Excel 12.0 Object Library.
2. Use this code :
     Dim objExportExcel As New exportExcel
     objExportExcel.exportExcel(dataGridView, "D:\pathSave", yourlabelobject)
3. and wait until file excel has been created

Read more

[VB.NET] Add button icon / image beside text

How To :

1.  Add 1 button on form
2.  Prepare an image (.png, size 16 x 16), you get the image at iconarchive.com (my favourite).
3.  Right click on button > properties , find Image and import your image (png) to resources.
4.  After the image appear on button, set imageAlign to Middle Center.
5.  Now set textAlign to Middle Right and set textImageRelation to ImageBeforeText.
6.  here's the result :

 

     
however the size of the width is changed, the text and the image stays in the middle.

ENJOY :D

Read more

[VB.NET] All About the use of dataGridView

Description
     This article we will discuss all about dataGridView in VB.NET, This article may be incomplete because i am just human (Above the sky there is another sky). so i need your help if something usefull about dataGridViewthat's not include in this article. you can add by comment below. if very usefull i will include in this article. many thanks :)


OK, Let's start :

1.  i want to make color datagrid, especially column header or row header
-----  enableheadersvisualstyles => FALSE
-----  to edit color column header you can click on property : columnheadersdefaultcellstyle
-----  to edit color row header you can click on property : rowheadersdefaultcellstyle

2. i want to get cell value when row get selected
----- respon the event selected using : SelectionChanged
----- to get row index : dataGridView.CurrentCell.RowIndex
----- to get cell value : dataGridView.Rows(RowIndex).Cells(ColumnIndex).Value()

3. i want every click in dataGridView = click row , not click current cell
----- on property set SelectionMode = fullRowSelect

4. i need to givecell color like white black white black
-----  you can goto here dataGridView Color

5. i need filter record in dataGridView without requery
----- you can use bindingSource, but still need current dataSource that you use. here's the example
        Dim sourceFilter As New BindingSource()
        sourceFilter.DataSource = dataGridView.DataSource 'current data source is exist
        sourceFilter.Filter = "[FIELD for FILTER] like '%" & txtFilter.Text & "%'"
        dataGridView.Refresh()

6. i want change date format in some column
------ dataGridView.Columns(indexcolumn).DefaultCellStyle.Format = "dd/MM/yyyy"

7. i want color code from HTML
----- colortranslator.fromhtml("#fafafa")

8. i want fit header column
----- autosizecolumnmode = fill

9. i want get value of multiple selection
 For Each selectedItem As DataGridViewRow In selectedItems
                'Add code to handle whatever you want for each row
                msgbox(selectedItem.Index) 'row index
 Next

Read more

[VB.NET] CRUD Class ADO.NET

Description

This article i will show you crud using template class that i create. very simple to use and clean code. here's this class :

Public Class database
    Implements IDisposable

    Public Function runningQuery(ByVal querystring As String) As DataTable
        Dim dbcmd, adaptor, datacontent
        Dim dbconn = New OleDbConnection

        dbconn.ConnectionString = "FILE NAME=" & My.Application.Info.DirectoryPath & "\connectionFile.udl"
        dbcmd = New OleDbCommand(querystring, dbconn)
        dbcmd.CommandType = CommandType.Text
        adaptor = New OleDbDataAdapter(dbcmd)
        datacontent = New DataTable
        adaptor.Fill(datacontent)
        dbconn.Close()
        Return datacontent
    End Function

    Public Sub executeQuery(ByVal querystring As String)
        Dim dbcmd
        Dim dbconn = New OleDbConnection
        dbconn.ConnectionString = "FILE NAME=" & My.Application.Info.DirectoryPath & "\connectionFile.udl"
        dbconn.Open()

        'Execute Query :
        ' INSERT : "insert into table (field1,field2) values ('1','yeah')"
        ' Delete : "update table set field1 ='abc' where  field2 = '1' "
        ' Update : "delete from table where field1 = '1' "

        dbcmd = New OleDbCommand(querystring, dbconn)
        dbcmd.CommandType = CommandType.Text
        dbcmd.ExecuteReader()
        dbconn.Close()
    End Sub
End Class

here's how to use this class :

1. Add Class and copy paste class code above, give name the class : database
2. Define and make object from that class :
   Dim objdatabase As New database
3. now you can use the object, INSERT DELETE and UPDATE use executeQuery() function because it's doesn't return any value. SELECT use runningQuery() function and it's always return type data : dataTable. 
4. Example runningQuery()
   Dim objdatabase As New database
   Dim employeeData as dataTable
   employeeData = objdatabase.runningQuery("select * from table where field1='xxx' ")
   ' type dataTable functionality :
   - to check count : employeeData.rows.count
   - to get value field :
          - if string type : employeeData .Rows(index).Field(Of String)("FIELD_NAME")
          - if integer type : employeeData .Rows(index).Field(Of Integer)("FIELD_NAME")
5. After use release the object from memory
      objdatabase.Dispose()
      objdatabase = Nothing
      GC.Collect()

Read more

[VB.NET] Random Number Function within range

Description

Random function is a function that is used to generate a random value.for a programmer is very useful random value that can be used to create a combination of string with random numbers that can produce a value to blur or protect the original value.

Not only that, a random number can be implemented in a variety of applications, for example randomize the order list of songs like winamp, then for animations contained in the application like "count down application" or even to generate some random string for security purpose like in oauth.

but for this case, i just make a random number function not random string !!.


How To

Private Function getRandomRange(ByVal lower As Integer, ByVal upper As Integer)
 Dim randomValue
 Dim objRandom As New Random
 randomValue = objRandom .Next(lower, upper)
 Return randomValue
End Function

to use this function within range just call this function like this :
- i want random number between number 1 - 9

so in VB.NET like this
* msgbox(getRandomRange(1,10)).

the question why 10 ? not 9 ? because in VB.NET upper number (in this case is 10) is not issued only 1 - 9.

Read more