c# - 读取 csv 文件并计算值之间的差异以制作饼图

Question

我制作了一份工作申请，以记录人们用他们的时间在做什么。（即可用、忙碌、现场、返回现场、快速休息、午休）。

发生的情况是，当用户单击一个按钮时，它会将他们的用户名写入一个文件，然后是一个逗号，然后是小时、分钟和秒的时间。例如：

John,Available,10:10:26
Dave,Available,10:15:40
Steve,Available,10:44:14
John,Busy,12:13:28
Steve,QuickBreak,12:15:25
John,Available,12:18:03
Dave,Busy,12:18:03

我已经弄清楚如何只显示用户的状态更新，然后用这个填充到标签中：

private void btnShowUser_Click(object sender, EventArgs e)
        {
            string SelectedUser = lstSelectUser.GetItemText(lstSelectUser.SelectedItem);
            List<string> resLines = new List<string>();
            var lines = File.ReadLines(Filepath);
            foreach (var line in lines)
            {
                var res = line.Split(new char[] { ',' });
                //or name to search 
                if (res[0] == SelectedUser)
                {
                    resLines.Add(res[1] + " " + res[2] + " " + res[3]);
                }
            }
            //to get the output  
            foreach (var line in resLines)
            {
                lblUserData.Text += (line) + System.Environment.NewLine;
            }
        }

如果可能的话，我想做的是计算每个用户花费的时间差，然后使用它在图表中填充饼图，以便它可以显示用户花费的饼图中每个段的大小在每个状态。所以在我的代码中，我能否以某种方式计算用户在每个状态中花费的总时间并在此创建一个饼图？我有一个想法，即读取每一行，然后检查状态并计算出每个状态的变化，然后将其添加到变量中以填充饼图，但无法弄清楚......任何帮助将不胜感激！谢谢。

标签： c#csvpie-chart

Answer 1

这可以通过在给定状态下汇总每个员工的持续时间来实现，最好是 CSV 文件具有列标题，然后可以使用Schema.iniODBC text driver并使用最小可重现示例查询数据：

样本数据.csv：

Name,Status,Time
John,Available,10:10:26
Dave,Available,10:15:40
Steve,Available,10:44:14
John,Busy,12:13:28
Steve,QuickBreak,12:15:25
John,Available,12:18:03
Dave,Busy,12:18:03
Steve,Busy,12:28:16
John,Offline,17:00:00
Dave,Offline,17:00:00
Steve,Offline,17:00:00

架构.ini：

[sampledata.csv]
Format=CSVDelimited
ColNameHeader=True
Col1 = "Name" Text
Col2 = "Status" Text
Col3 = "TimeStamp" DateTime

员工类和状态枚举：

class Employee
{
    public static List<Employee> Employees { get; private set; } = new List<Employee>();
    public string Name { get; }
    public StatusEnum Status { get; }
    public DateTime TimeStamp { get; }
    public TimeSpan Duration { get; private set; }
            

    public Employee(string name, StatusEnum status, DateTime time)
    {
        this.Name = name;
        this.Status = status;
        this.TimeStamp = time;
        Employees.Add(this);
    }

    public static Dictionary<string, double> GetEmployeeStatusSummary(Employee employee)
    {
        var dict = new Dictionary<string, double>();
        foreach (var emp in Employees.Where(e => e.Name == employee.Name))
        {
            if (dict.ContainsKey(emp.Status.ToString()))
            {
                dict[emp.Status.ToString()] += emp.Duration.TotalMinutes;
            }
            else
            {
                dict.Add(emp.Status.ToString(), emp.Duration.TotalMinutes);
            }
        }
        return dict;
    }

    public static void UpdateAllEmployeesDuration()
    {
        Employees = Employees.OrderBy(e => e.Name).ThenBy(e => e.TimeStamp).ToList();
        TimeSpan duration;
        for (int i = 0; i < Employees.Count - 1; i++)
        {
            if (Employees[i].Name == Employees[i + 1].Name)
            {
                duration = Employees[i + 1].TimeStamp - Employees[i].TimeStamp;
                Employees[i].Duration = duration;
            }
        }
    }

    override public string ToString()
    {
        return this.Name;
    }
}

enum StatusEnum
{
    Available,
    Busy,
    QuickBreak,
    Break,
    Offline
}

然后Form使用饼图，刷新按钮触发流程 --> 查询 CSV 文件，使用员工姓名更新下拉列表并清除图表，如果 CSV 文件经常更新并且用户希望查看新数据，这很有用。该ComboBox事件SelectedIndexChanged将更新图表以显示所选员工的数据。

private void btn_Refresh_Click(object sender, EventArgs e)
{
    var csvPath = Path.GetDirectoryName(Application.ExecutablePath);
    var connStr = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Extensions=asc,csv,tab,txt; Dbq=";
    var dt = new DataTable();

    using (var conn = new OdbcConnection(connStr + csvPath))
    using (var odba = new OdbcDataAdapter("SELECT * FROM sampledata.csv", conn))
    {
        odba.Fill(dt);
    }

    foreach (DataRow row in dt.Rows)
    {
        var name = row[nameof(Employee.Name)].ToString();
        Enum.TryParse(row[nameof(Employee.Status)].ToString(), out StatusEnum status);
        var duration = (DateTime)row[nameof(Employee.TimeStamp)];
        new Employee(name, status, duration);
    }
    Employee.UpdateAllEmployeesDuration();

    this.chart_Pie.Series.Clear();
    this.cbx_Names.DataSource = Employee.Employees.GroupBy(emp => emp.Name)
                                                    .Select(g => g.First())
                                                    .ToList();
}

private void cbx_Names_SelectedIndexChanged(object sender, EventArgs e)
{
    var employee = (Employee)cbx_Names.SelectedItem;
            
    if (this.chart_Pie.Series.IsUniqueName(employee.Name) == false)
    {
        foreach (var series in this.chart_Pie.Series)
        {
            if (series.Name.Equals(employee.Name, StringComparison.OrdinalIgnoreCase))
            {
                series.Enabled = true;
            }
            else
            {
                series.Enabled = false;
            }
        }
    }
    else
    {
        foreach (var ser in this.chart_Pie.Series)
        {
            ser.Enabled = false;
        }
        var points = Employee.GetEmployeeStatusSummary(employee);
        var series = new Series(employee.Name);
        series.ChartType = SeriesChartType.Pie;
        this.chart_Pie.Series.Add(series);
        this.chart_Pie.Series[employee.Name].Points.DataBindXY(points.Keys, points.Values);
    }
}

最终结果：