Hi kana250688,
First you need to filter the record based on the DatePicker.
Then, select distinct Ids from dat file records and add the records that does not exists in Dat file based on the DatePicker selected Dates.
Finally, add the non exists records to the filterd records and assign to the DataGridView DataSource property.
Refer below sample and modify as per your requirement and data present in Dat file.
The Dat File
FormDesign
The Form consists of:
DatePicker - For selecting Date.
OpenFileDialogBox - For for filtering the record.
GridView - For displaying the records.
Button - For Reading and filtering the Dat file records.
Namespaces
You need to inherit the following namespaces.
C#
using System.Data;
using System.IO;
using System.Linq;
VB.Net
Imports System.Data
Imports System.IO
Imports System.Linq
Class Property
C#
public class Attendance
{
public string ID { get; set; }
public string DATE { get; set; }
public string TIME { get; set; }
public string FP { get; set; }
public string IN_OUT { get; set; }
public string OTHERS1 { get; set; }
public string OTHERS2 { get; set; }
}
VB.Net
Public Class Attendance
Public Property ID As String
Public Property [DATE] As String
Public Property TIME As String
Public Property FP As String
Public Property IN_OUT As String
Public Property OTHERS1 As String
Public Property OTHERS2 As String
End Class
Code
C#
private void btnFilter_Click(object sender, EventArgs e)
{
DateTime dtStart = Convert.ToDateTime(startDate.Value).Date;
DateTime dtEnd = Convert.ToDateTime(endDate.Value).Date;
// Opening FileDialog to select Dat File.
openFileDialog1.InitialDirectory = @"D:\Files";
openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat";
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
List<Attendance> attendances = new List<Attendance>();
// Reading all the Data from Dat File.
string[] lines = File.ReadAllLines(openFileDialog1.FileName);
for (int i = 0; i <= lines.Length - 1; i++)
{
if (!string.IsNullOrEmpty(lines[i]))
{
string[] data = lines[i].Split('\t');
attendances.Add(new Attendance
{
ID = data[0],
DATE = data[1],
TIME = data[2],
FP = data[3],
IN_OUT = data[4].ToString() == "0" ? "IN" : "OUT",
OTHERS1 = data[5],
OTHERS2 = data[6]
});
}
}
// Filtering based on dates.
List<Attendance> existingAttendances = attendances.Where(x => Convert.ToDateTime(x.DATE) >= dtStart && Convert.ToDateTime(x.DATE) <= dtEnd).ToList();
// Selecting distinct Ids.
string[] ids = attendances.Select(x => x.ID).Distinct().ToArray();
// Selecting distinct Dates.
DateTime[] dates = existingAttendances.Select(x => Convert.ToDateTime(x.DATE)).Distinct().ToArray();
// Adding records that does not exists in Dat file.
for (DateTime dt = dtStart; dt <= dtEnd; dt = dt.AddDays(1))
{
if (!dates.Contains(dt))
{
foreach (string id in ids)
{
existingAttendances.Add(new Attendance
{
ID = id,
DATE = dt.ToString("yyyy-MM-dd"),
IN_OUT = "IN"
});
existingAttendances.Add(new Attendance
{
ID = id,
DATE = dt.ToString("yyyy-MM-dd"),
IN_OUT = "OUT"
});
}
}
}
dataGridView1.DataSource = existingAttendances.OrderBy(x => Convert.ToDateTime(x.DATE)).ToList();
}
}
VB.Net
Private Sub btnFilter_Click(sender As Object, e As EventArgs) Handles btnFilter.Click
Dim dtStart As DateTime = Convert.ToDateTime(startDate.Value).Date
Dim dtEnd As DateTime = Convert.ToDateTime(endDate.Value).Date
' Opening FileDialog to select Dat File.
openFileDialog1.InitialDirectory = "D:\Files"
openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat"
openFileDialog1.RestoreDirectory = True
If openFileDialog1.ShowDialog() = DialogResult.OK Then
Dim attendances As List(Of Attendance) = New List(Of Attendance)()
' Reading all the Data from Dat File.
Dim lines As String() = File.ReadAllLines(openFileDialog1.FileName)
For i As Integer = 0 To lines.Length - 1
If Not String.IsNullOrEmpty(lines(i)) Then
Dim data As String() = lines(i).Split(vbTab)
attendances.Add(New Attendance With {
.ID = data(0),
.DATE = data(1),
.TIME = data(2),
.FP = data(3),
.IN_OUT = If(data(4).ToString() = "0", "IN", "OUT"),
.OTHERS1 = data(5),
.OTHERS2 = data(6)
})
End If
Next
' Filtering based on dates.
Dim existingAttendances As List(Of Attendance) = attendances.Where(Function(x) Convert.ToDateTime(x.DATE) >= dtStart AndAlso Convert.ToDateTime(x.DATE) <= dtEnd).ToList()
' Selecting distinct Ids.
Dim ids As String() = attendances.Select(Function(x) x.ID).Distinct().ToArray()
' Selecting distinct Dates.
Dim dates As DateTime() = existingAttendances.Select(Function(x) Convert.ToDateTime(x.DATE)).Distinct().ToArray()
Dim dt As DateTime = dtStart
' Adding records that does not exists in Dat file.
While dt <= dtEnd
If Not dates.Contains(dt) Then
For Each id As String In ids
existingAttendances.Add(New Attendance With {
.ID = id,
.DATE = dt.ToString("yyyy-MM-dd"),
.IN_OUT = "IN"
})
existingAttendances.Add(New Attendance With {
.ID = id,
.DATE = dt.ToString("yyyy-MM-dd"),
.IN_OUT = "OUT"
})
Next
End If
dt = dt.AddDays(1)
End While
dataGridView1.DataSource = existingAttendances.OrderBy(Function(x) Convert.ToDateTime(x.DATE)).ToList()
End If
End Sub
Screenshot