Automatic ListView Grouping

Lets say you have lots of data your listview. Now you want to Group This data According to a Perticular Subitems.

For Example:

Suppose i have some books data in my ListView.
this listview items contains Author name and Books Title.

And there are 2000 Books in list view.

Now i want to group the data in listview according to the Authors.

Now lets say there are 50 Unique Authors , meaning we will have to create 50 Groups in listview.

this seem hectic, and i dont know if there is any inbuilt function to automatically group this items, but i have created mine To automatically do the above.

Hope it becomes usefull to someone.

Code:

public void GroupListView(ListView lstV, int SubItemIndex)
        {
            bool flag = true;

            foreach (ListViewItem l in lstV.Items)
            {
                string strmyGroupname = l.SubItems[SubItemIndex].Text;

                foreach (ListViewGroup lvg in lstV.Groups)
                {
                    if (lvg.Name == strmyGroupname)
                    {
                        l.Group = lvg;
                        flag = false;
                    }
                }

                if (flag == true)
                {
                    ListViewGroup lstGrp = new ListViewGroup(strmyGroupname, strmyGroupname);
                    lstV.Groups.Add(lstGrp);
                    l.Group = lstGrp;
                }

                flag = true;


            }
        }

How To Use The Code:

Lets say the author's sub item's index is 1 and listview name is LstBooks

then call the function like:

GroupListView(LstBooks,1);

Vb.net Version:

Public Sub GroupListView(ByVal lstV As ListView, ByVal SubItemIndex As Int16)
        Dim flag As Boolean = True
        For Each l As ListViewItem In lstV.Items

            Dim strmyGroupname As String = l.SubItems(SubItemIndex).Text

            For Each lvg As ListViewGroup In lstV.Groups

                If lvg.Name = strmyGroupname Then
                    l.Group = lvg
                    flag = False
                End If

            Next

            If flag = True Then
                Dim lstGrp As New ListViewGroup(strmyGroupname, strmyGroupname)
                lstV.Groups.Add(lstGrp)
                l.Group = lstGrp
            End If

            flag = True

        Next
    End Sub

Output:

Auto Increment Invoice Number For Vb.net

Let's say you have some invoice numbers which contains Alphabets as well as numeric number.
And you want to increment it one by one.

For Example:

if Invoice number is "AZ99999999" then Next Invoice Number will be "BA00000001"

Notice here that , the invoice number's lenght is 10 Characters out of which first 2 are Alphabets and the rest (8) are Numeric. invoice number can be of any digit with any combination of numerics and alphabets.

The function can be changed to your need very easily. but here i will demonstate for the above example.

Function:

Public Function IncrementInvoice(ByVal strInvoiceNumber As String) As String

        If strInvoiceNumber.Length <> 10 Then
            Return "Error"
        End If

        Dim strAlphaPart(1) As Char
        strAlphaPart(0) = strInvoiceNumber(0)
        strAlphaPart(1) = strInvoiceNumber(1)

        Dim IntPart As Int64
        IntPart = strInvoiceNumber.Substring(2, 8)


        If IntPart = 99999999 Then
            If strAlphaPart(1) = "Z" Then
                strAlphaPart(0) = Chr(Asc(strAlphaPart(0)) + 1)
                strAlphaPart(1) = "A"

                IntPart = 1

                Return strAlphaPart(0) & strAlphaPart(1) & IntPart.ToString.PadLeft(8, "0")
            Else
                strAlphaPart(1) = Chr(Asc(strAlphaPart(1)) + 1)
            End If

        Else
            IntPart += 1
            Return strAlphaPart(0) & strAlphaPart(1) & IntPart.ToString.PadLeft(8, "0")
        End If

    End Function

Output:

'outputs example:
strTemp = IncrementInvoice("AA99999998") 'Output will be: "AA99999999"

strTemp = IncrementInvoice("AA00000005") 'Output will be: "AA00000006"

strTemp = IncrementInvoice("AZ00000007") 'Output will be: "AZ00000008"

strTemp = IncrementInvoice("AZ99999999") 'Output will be: "BA00000001"

Dataset To Excel Export Function

The Function:

Public Sub ExportDatasetToExcel(ByVal ds As DataSet, ByVal strExcelFile As String)

        Dim conn As New OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", strExcelFile))
        conn.Open()

        Dim strTableQ(ds.Tables.Count) As String

        Dim i As Integer = 0

        'making table query
        For i = 0 To ds.Tables.Count - 1

            strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

            Dim j As Integer = 0
            For j = 0 To ds.Tables(i).Columns.Count - 1
                Dim dCol As DataColumn
                dCol = ds.Tables(i).Columns(j)
                strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
            Next
            strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
            strTableQ(i) &= ")"

            Dim cmd As New OleDbCommand(strTableQ(i), conn)
            cmd.ExecuteNonQuery()

        Next

        'making insert query
        Dim strInsertQ(ds.Tables.Count - 1) As String
        For i = 0 To ds.Tables.Count - 1
            strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
            Next
            strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
            strInsertQ(i) &= ")"
        Next

        'Now inserting data
        For i = 0 To ds.Tables.Count - 1
            For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                Dim cmd As New OleDbCommand(strInsertQ(i), conn)
                For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                    cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
                Next
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()

            Next
        Next
    End Sub

Let's say i have my.xls in D drive. i want to export a Dataset Name dsFinal to this excel file.
Use:

ExportDatasetToExcel(dsFinal, "d:\\my.xls")