Datatable to Notepad with value repeat on each page with line break

Amit kumar Yadava 81 Reputation points
2022-12-26T08:32:28.49+00:00

Hello all great minds ! i am new here and need support to write a data in notepad with column header with row value repeat single on header ( as you can say group by)
i write a file but value continues write and in need help to repeat user on top of the page .
My expected result look like.
Page No.1
Item Issued For Customer :102

----------------------------------------------

Id Item Qty Date

----------------------------------------------

101 Pen 10 10/05/2021
101 Copy 10 10/05/2021
101 Glue 1 10/05/2021
101 Cover 10 10/05/2021

----------------------------------------------

Total 4 31

----------------------------------------------

					Page No.2  

Item Issued For Customer :102

-----------------------------------------------

Id Item Qty Date

-----------------------------------------------

102 Bag 1 09/06/2021
102 Pencil Box 2 09/06/2021
102 Cover 5 09/06/2021

----------------------------------------------

Total 3 8

-----------------------------------------------

SubTotal 7 39

My code is :
Using sw As New StreamWriter(OutPutFile)
For I As Integer = 0 To dt.Rows.Count - 1
sw.WriteLine("------------------------------------------------------------")
sw.WriteLine(dt.Rows(I).Item(0).ToString.PadRight(15) & vbTab & dt.Rows(I).Item(1).ToString().PadRight(25) & vbTab & dt.Rows(I).Item(2).ToString().PadRight(10) & vbTab & dt.Rows(I).Item(3).ToString())
Next
END USING
Process.Start(OutPutFile)

Developer technologies | VB
{count} votes

Accepted answer
  1. Jack J Jun 25,296 Reputation points
    2022-12-26T10:32:40.78+00:00

    @Amit kumar Yadava , Welcome to Microsoft Q&A, you could try to group by your datatable and use list of string to write all the lines to txt file.

    Here is a code example you could refer to.

    Dim dt As DataTable = New DataTable()  
        dt.Columns.Add("Id", GetType(Int32))  
        dt.Columns.Add("Item", GetType(String))  
        dt.Columns.Add("Qty", GetType(Int32))  
        dt.Columns.Add("Date", GetType(DateTime))  
        dt.Rows.Add(101, "Pen", 5, "2021-10-05")  
        dt.Rows.Add(102, "Bag", 10, "2021-09-06")  
        dt.Rows.Add(101, "Copy", 2, "2021-01-05")  
        dt.Rows.Add(101, "Glue", 10, "2021-03-05")  
        dt.Rows.Add(102, "Conver", 5, "2021-10-02")  
        dt.Rows.Add(101, "Cover", 10, "2021-10-01")  
        Dim result = dt.AsEnumerable().GroupBy(Function(row) row.Field(Of Int32)("Id"))  
        Dim count As Int32 = 1  
        Dim arrcount As Int32  
        Dim sumaccount As Int32  
        Dim list As New List(Of String)  
        For Each grp In result  
            Dim str As String  
            str = String.Format("                               Page{0}", count)  
            list.Add(str)  
            str = String.Format("Item Issued For Customer :{0}", grp.Key)  
            list.Add(str)  
            list.Add("--------------------------------------------------")  
            str = String.Format("{0} {1} {2} {3}", dt.Columns(0).ColumnName, dt.Columns(1).ColumnName, dt.Columns(2).ColumnName, dt.Columns(3).ColumnName)  
            list.Add(str)  
            list.Add("--------------------------------------------------")  
            Dim arr = grp.Select(Function(row) row.ItemArray)  
            For Each line In arr  
                list.Add(line(0).ToString() + " " + line(1).ToString() + " " + line(2).ToString() + " " + DateTime.Parse(line(3).ToString()).ToShortDateString())  
            Next  
            list.Add("--------------------------------------------------")  
            Dim sum As Int32 = grp.Sum(Function(row) row.Field(Of Int32)("Qty"))  
            Dim final As String  
            final = String.Format("Total {0} {1}", arr.Count, sum)  
            list.Add(final)  
            list.Add("--------------------------------------------------")  
            arrcount = arr.Count + arrcount  
            sumaccount = sum + sumaccount  
            If count = result.Count Then  
                str = String.Format("SubTotal {0} {1}", arrcount, sumaccount)  
                list.Add(str)  
                list.Add("--------------------------------------------------")  
            End If  
    
            count = count + 1  
        Next  
        File.WriteAllLines("test.txt", list)  
    

    Tested result:

    274164-image.png

    Hope my code could help you.

    Best Regards,
    Jack

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.