在VS2019 MVC应用中用Chart.js实现按日期的考勤打卡时间图表

我正在使用Visual Studio 2019开发MVC应用，想要渲染一个以考勤日期为X轴、打卡时间为Y轴的员工考勤图表，已获取该员工的相关数据列表。当前使用Chart.js v3.7.1，但尝试后无法实现需求，请求帮助。

原Action代码 #

[HttpGet]
public ActionResult BarChart()
{
    try
    {
        List<object> iData = new List<object>();
        DataTable dt = new DataTable();
        using (OracleConnection con = new OracleConnection(AppSetting.OraConnectionString()))
        {
            string str = @"SELECT to_char(cast(EAE_DATE as date),'dd-MON-yy') AS EAE_DATE ,to_char(cast(IN_TIME as date),'hh24:mi:ss') AS IN_TIME,to_char(cast(OUT_TIME as date),'hh24:mi:ss') AS OUT_TIME FROM EMPLOYEE_DAILY_ATTENDANCE_EXL WHERE EAE_EM_ID = 904 AND EAE_YEAR=2022 AND EAE_MONTH=11 ORDER BY EAE_DATE";

            OracleDataAdapter da = new OracleDataAdapter(str, con);
            da.Fill(dt);
            foreach (DataColumn dc in dt.Columns)
            {
                List<object> x = new List<object>();
                x = (from DataRow drr in dt.Rows select drr[dc.ColumnName]).ToList();
                iData.Add(x);
            }
            ViewBag.Date_List = iData[0];
            ViewBag.InTime_List = iData[1];
            ViewBag.OutTime_List = iData[2];

        }

        return View();
    }
    catch (Exception)
    {
        throw;
    }
}

原View代码 #

@{
    ViewBag.Title = "BarChart";
}
<div style="text-align: center">
    <canvas id="barcanvas"></canvas>
</div>

<meta name="viewport" content="width=device-width" />
<title>Attendance Punctuality</title>
@* I have installed Chart.js from ManageNugetPackages so not using the cdn *@
   
<script>
    var DayDate =@Html.Raw(Json.Encode(ViewBag.Date_List));
    var InTime =@Html.Raw(Json.Encode(ViewBag.InTime_List));
    var OutTime =@Html.Raw(Json.Encode(ViewBag.OutTime_List));
    var ChartData =
        { labels: DayDate,
          datasets: [{
                            label: 'In Time',
                            backgroundColor: "#aad2ed",
                            borderWidth: 2,
                            data: InTime,
                            borderColor: '#0062ff',
                            fill: false,
                        },
                        {
                            label: 'Out Time',
                            backgroundColor: "#f7bd83",
                            borderWidth: 2,
                            data: OutTime,
                            borderColor: '#F78511',
                            fill: false,
                      }]
        };
            window.onload = function () {
                var ctx1 = document.getElementById("barcanvas").getContext("2d");
                window.myBar = new Chart(ctx1,
                    { type: 'line',       //line,pie,bar,doughnut
                        data: ChartData,
                        showDatapoints: true,
                        legend: { position: 'bottom' },
                        options:
                            {
                                title: { display: true, text: "Attendance Trial Graph"},
                                responsive: true,
                                maintainAspectRatio: true
                            }
                    });
            }
</script>

解决方案：修改后的代码及说明 #

1. 优化后端Action代码 #

[HttpGet]
public ActionResult BarChart()
{
    try
    {
        List<object> dateList = new List<object>();
        List<object> inTimeList = new List<object>();
        List<object> outTimeList = new List<object>();
        
        using (OracleConnection con = new OracleConnection(AppSetting.OraConnectionString()))
        {
            string str = @"SELECT EAE_DATE, IN_TIME, OUT_TIME 
                          FROM EMPLOYEE_DAILY_ATTENDANCE_EXL 
                          WHERE EAE_EM_ID = 904 AND EAE_YEAR=2022 AND EAE_MONTH=11 
                          ORDER BY EAE_DATE";

            OracleDataAdapter da = new OracleDataAdapter(str, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            
            foreach (DataRow row in dt.Rows)
            {
                // 将考勤日期转为标准格式字符串
                DateTime eaeDate = Convert.ToDateTime(row["EAE_DATE"]);
                dateList.Add(eaeDate.ToString("yyyy-MM-dd"));
                
                // 拼接考勤日期与打卡时间，生成完整DateTime对象并转为ISO格式
                DateTime inTime = DateTime.ParseExact(row["IN_TIME"].ToString(), "HH:mm:ss", null);
                DateTime fullInTime = new DateTime(eaeDate.Year, eaeDate.Month, eaeDate.Day, inTime.Hour, inTime.Minute, inTime.Second);
                inTimeList.Add(fullInTime.ToString("o"));
                
                DateTime outTime = DateTime.ParseExact(row["OUT_TIME"].ToString(), "HH:mm:ss", null);
                DateTime fullOutTime = new DateTime(eaeDate.Year, eaeDate.Month, eaeDate.Day, outTime.Hour, outTime.Minute, outTime.Second);
                outTimeList.Add(fullOutTime.ToString("o"));
            }
            
            ViewBag.Date_List = dateList;
            ViewBag.InTime_List = inTimeList;
            ViewBag.OutTime_List = outTimeList;
        }

        return View();
    }
    catch (Exception)
    {
        throw;
    }
}

2. 修改前端View代码 #

@{
    ViewBag.Title = "BarChart";
}
<div style="text-align: center; height: 500px;">
    <canvas id="attendanceChart"></canvas>
</div>

<meta name="viewport" content="width=device-width" />
<title>Attendance Punctuality</title>

<script src="~/Scripts/chart.min.js"></script>
<script>
    const dayDates = @Html.Raw(Json.Encode(ViewBag.Date_List));
    const inTimes = @Html.Raw(Json.Encode(ViewBag.InTime_List)).map(time => new Date(time));
    const outTimes = @Html.Raw(Json.Encode(ViewBag.OutTime_List)).map(time => new Date(time));

    const ctx = document.getElementById('attendanceChart').getContext('2d');
    new Chart(ctx, {
        type: 'scatter', // 散点图更适合日期-时间维度展示，需连线可改为'line'
        data: {
            datasets: [
                {
                    label: '上班打卡',
                    data: dayDates.map((date, index) => ({
                        x: new Date(date),
                        y: inTimes[index]
                    })),
                    backgroundColor: '#0062ff',
                    pointRadius: 6
                },
                {
                    label: '下班打卡',
                    data: dayDates.map((date, index) => ({
                        x: new Date(date),
                        y: outTimes[index]
                    })),
                    backgroundColor: '#F78511',
                    pointRadius: 6
                }
            ]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                title: {
                    display: true,
                    text: '员工考勤时间图表'
                },
                legend: {
                    position: 'bottom'
                }
            },
            scales: {
                x: {
                    type: 'time',
                    time: {
                        unit: 'day',
                        displayFormats: {
                            day: 'dd-MMM-yy'
                        }
                    },
                    title: {
                        display: true,
                        text: '考勤日期'
                    }
                },
                y: {
                    type: 'time',
                    time: {
                        unit: 'hour',
                        displayFormats: {
                            hour: 'HH:mm'
                        }
                    },
                    title: {
                        display: true,
                        text: '打卡时间'
                    }
                }
            }
        }
    });
</script>

关键修改说明 #

• 后端数据处理：不再将日期、时间转为纯字符串，而是拼接成完整DateTime对象后转为ISO格式，确保前端能解析为标准时间对象。
• 前端图表配置：将X、Y轴均设置为time类型，指定时间单位与显示格式；使用散点图（或折线图）展示日期与打卡时间的对应关系，数据格式符合Chart.js时间轴要求。
• 细节优化：调整图表容器高度，明确引入Chart.js脚本路径，简化数据处理逻辑。

内容的提问来源于stack exchange，提问作者Farhan