Unity 建立班表 @ Xauxas 筆記

又嘗試用Unity 做其他事情，最近看到班表這個有趣的事情，之前有貼過固定班格式與如何讀取日期的文章。

想要挑戰一下，完美的功能，如: 有日期、公司、選擇班別，班表內容是否超過當日的營業時間與休假跟加班計算....等功能。

本人比較懶，因此很少調整Unity 設計畫面，大致上都以程式碼部分直接調整，以下所使用的方式由Interface方式來呈現。

-----以下成功畫面-----

------------接下來就是程式碼部份了------------

ExcelClass 腳本 (C#) :

using System.Collections;
using System.Collections.Generic;
using UnityEngine;

public abstract class ExcelClass
{
    public abstract class ClassInterface
    { 
        public abstract class Preset
        {
            public abstract void classFixed(int year, int month, string companyName, string unit, int staffNumber);
            public abstract void excelSave(int staffNumber);
        }
        
        public abstract class Formula
        {
            public abstract void createClass(int year, int month, string companyName, string unit, int staffNumber, int operAtingHours,
                bool dayShift, bool night, bool bigNight);
            public abstract void excelSave(int satffNumber);
        }
    }
}

PresetClass 腳本

using System.IO;
using OfficeOpenXml;
using System;
using System.Collections.Generic;

public class PresetClass : ExcelClass.ClassInterface.Preset
{
    // 建立資料位置
    private string folderPath = @"D:\班表\";
    private string filePath;

    // 檔案類別
    FileInfo fileInfo;
    Calendar.GetCalender getCalender = new GetCalender();

    // excel 組件
    public static ExcelPackage EXCEL_PACKAGE;
    public static ExcelWorksheet WORKSHEET;

    // Excel 固定格式 (年、職位、天、資料寫入縱)
    int companyNameYear = 1;                                        
    int positionNumber = 2;                                        
    int dayExcelNuber = 3;                                           
    int dataExcelNum = 4;

    public static List<Data_date> GET_Data = new List<Data_date>();

    public override void classFixed(int year, int month, string companyName, string unit, int staffNumber)
    {
        GET_Data.Clear();
        GET_Data = getCalender.calendarData(year, month, 1);

        if (!Directory.Exists(folderPath + @"\" + unit))
            Directory.CreateDirectory(folderPath + @"\" + unit);

        // 判斷檔案是否存在
        if (fileExist(unit, year, month))
        {
            fileInfo.Delete();
            fileInfo = new FileInfo(filePath);
        }


        EXCEL_PACKAGE = new ExcelPackage(fileInfo);
        WORKSHEET = EXCEL_PACKAGE.Workbook.Worksheets.Add(getCalender.calendarData(year, month, 1)[0].month + "月");

        // 寫入公司名稱
        WORKSHEET.Cells[companyNameYear, 1].Value = GET_Data[0].year + "年" + GET_Data[0].month + "月 " + companyName + " (" + "單位: " + unit + ")";
        WORKSHEET.Cells[companyNameYear, 1, 1, GET_Data.Count + 3].Merge = true;

        // 寫入部分(單位)
        WORKSHEET.Cells[positionNumber, 1].Value = unit;
        WORKSHEET.Cells[positionNumber, 1, positionNumber, GET_Data.Count + 3].Merge = true;
        // 表格上色
        TABLE_COLOR(positionNumber, 1, System.Drawing.Color.Yellow);

        foreach (var dayweekData in GET_Data)
        {
            WORKSHEET.Cells[dayExcelNuber, 1].Value = "員工";
            // 天與週次
            WORKSHEET.Cells[dayExcelNuber, Int32.Parse(dayweekData.day) + 1].Value = dayweekData.week + dayweekData.day;

            //　休假
            WORKSHEET.Cells[dayExcelNuber, Int32.Parse(dayweekData.day) + 2].Value = "休假";
            // 表格上色
            TABLE_COLOR(dayExcelNuber, GET_Data.Count + 2, System.Drawing.Color.Red);

            //加班
            WORKSHEET.Cells[dayExcelNuber, Int32.Parse(dayweekData.day) + 3].Value = "加班";

            // 輸入員工姓名
            for (int x = 0; x < staffNumber; x++)
                WORKSHEET.Cells[dataExcelNum + x, 1].Value = "輸入姓名";

            // 自動調整距離
            WORKSHEET.Cells[WORKSHEET.Dimension.Address].AutoFitColumns();
        }
    }

    // excel save
    public override void excelSave(int staffNumber)
    {
        EXCEL_STYLE(1, 1, staffNumber + 3, GET_Data.Count + 3);
        EXCEL_PACKAGE.Save();
    }

    // 判斷檔案
    bool fileExist(string unit, int year, int month)
    {
        filePath = folderPath + @"\" + unit + @"\" + year.ToString() + "年" + month.ToString() + "月" + ".xlsx";
        fileInfo = new FileInfo(filePath);

        bool fileExist = fileInfo.Exists;

        return fileExist;
    }

    // 表格顏色
    public static void TABLE_COLOR(int fromRow, int fromCol, System.Drawing.Color color)
    {
        WORKSHEET.Cells[fromRow, fromCol].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        WORKSHEET.Cells[fromRow, fromCol].Style.Fill.BackgroundColor.SetColor(color);
    }

    public static void EXCEL_STYLE(int fromRow, int fromCol, int toRow, int toCol)
    {
        // 字形
        WORKSHEET.Cells.Style.Font.Name = "標楷體";
        // 文字大小
        WORKSHEET.Cells.Style.Font.Size = 14;
        // 水平置中
        WORKSHEET.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
        // 垂直中
        WORKSHEET.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
        // 表格框線
        WORKSHEET.Cells[fromRow, fromCol, toRow, toCol].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
        WORKSHEET.Cells[fromRow, fromCol, toRow, toCol].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
        WORKSHEET.Cells[fromRow, fromCol, toRow, toCol].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
        WORKSHEET.Cells[fromRow, fromCol, toRow, toCol].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
    }
}

FormulaClass 腳本

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;

public class FormulaClass : ExcelClass.ClassInterface.Formula
{
    // 檔案類別
    FileInfo fileInfo;

    // Excel 固定格式 (資料寫入縱)
    int dataExcelNum = 4;

    ExcelClass.ClassInterface.Preset presetClass = new PresetClass();

    public override void createClass(int year, int month, string companyName, string unit, int staffNumber, int operAtingHours,
        bool dayShift, bool night, bool bigNight)
    {
        presetClass.classFixed(year, month, companyName, unit, staffNumber);

        int checkExcelNum = dataExcelNum + staffNumber;

        int classStateNum = 0;

        string className1 = "D";
        string className2 = "E";
        string className3 = "N";

        int dayShiftAdd = 0;
        int nightAdd = 0;
        int bigNightAdd = 0;

        // 營業時數
        PresetClass.WORKSHEET.Cells[checkExcelNum + 1, 1].Value = "營業時間 ( " + operAtingHours + "小時 )";
        PresetClass.TABLE_COLOR(checkExcelNum + 1, 1, System.Drawing.Color.Red);
        PresetClass.WORKSHEET.Cells[checkExcelNum + 1, 1].AutoFitColumns(50);

        // 狀態1: 3班都選擇(三班制)
        if (dayShift == true && night == true && bigNight == true)
        {
            // 白班
            for (int x = 2; x <= 6; x++)
            {
                classState(checkExcelNum, x, toggleMessage(dayShift, className1 + classTable(dayShiftAdd)), dayShiftAdd);
                dayShiftAdd += 1;
            }

            // 小夜班(晚班)
            for (int x = 7; x <= 11; x++)
            {
                classState(checkExcelNum, x, toggleMessage(night, className2 + classTable(nightAdd)), nightAdd);
                nightAdd += 1;
            }

            // 大夜班(夜班)
            for (int x = 12; x <= 16; x++)
            {
                classState(checkExcelNum, x, toggleMessage(bigNight, className3 + classTable(bigNightAdd)), bigNightAdd);
                bigNightAdd += 1;
            }

            classStateNum = 1;
        }

        // 狀態2: 2班都選擇 - 白班，晚班(二班制)
        if (dayShift == true && bigNight == true && night != true)
        {
            // 白班
            for (int x = 2; x <= 6; x++)
            {
                classState(checkExcelNum, x, toggleMessage(dayShift, className1 + classTable(dayShiftAdd)), dayShiftAdd);
                dayShiftAdd += 1;
            }

            // 大夜班(夜班)
            for (int x = 7; x <= 11; x++)
            {
                classState(checkExcelNum, x, toggleMessage(bigNight, className3 + classTable(bigNightAdd)), bigNightAdd);
                bigNightAdd += 1;
            }

            classStateNum = 2;
        }

        if (dayShift == true && night != true && bigNight != true)
        {
            // 白班
            for (int x = 2; x <= 6; x++)
            {
                classState(checkExcelNum, x, toggleMessage(dayShift, className1 + classTable(dayShiftAdd)), dayShiftAdd);
                dayShiftAdd += 1;
            }

            classStateNum = 3;
        }

        // 加班公式
        overtimeFormula(checkExcelNum, classStateNum, staffNumber, operAtingHours);
    }

    public override void excelSave(int satffNumber)
    {
        PresetClass.EXCEL_STYLE(1, 1, satffNumber + 3, PresetClass.GET_Data.Count + 3);
        PresetClass.EXCEL_PACKAGE.Save();
    }

    // 加班公式
    void overtimeFormula(int checkExcelNum, int classStateNum, int staffNumber, int operAtingHours)
    {
        // 橫向
        for (int x = 0; x < staffNumber; x++)
        {
            // 加班
            PresetClass.WORKSHEET.Cells[dataExcelNum + x, PresetClass.GET_Data.Count + 3].Formula = formulaHorizontal(1, dataExcelNum + x, 2);
            PresetClass.WORKSHEET.Cells[dataExcelNum + x, PresetClass.GET_Data.Count + 2].Formula = formulaHorizontal(2, dataExcelNum + x, 2);
        }

        for (int x = 0; x < PresetClass.GET_Data.Count; x++)
        {
            // 3班
            if (classStateNum == 1)
            {
                // 檢查營業時間
                PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2].Formula = formula_vertical_openBusiness(checkExcelNum, x + 2, classStateNum);
                var cont = PresetClass.WORKSHEET.ConditionalFormatting.AddGreaterThan(PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2]);
                cont.Style.Font.Color.Color = System.Drawing.Color.Red;
                cont.Formula = operAtingHours.ToString();

                int dayShiftTable = 0;
                int nightTable = 0;
                int bigNightTable = 0;

                // D班含加班
                for (int i = 2; i <= 6; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"D"
                        + classTable(dayShiftTable) + "\"", "\"D" + classTable(dayShiftTable) + "\"", staffNumber);

                    dayShiftTable += 1;
                }

                // E班含加班
                for (int i = 7; i <= 11; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"E"
                        + classTable(nightTable) + "\"", "\"E" + classTable(nightTable) + "\"", staffNumber);

                    nightTable += 1;
                }

                // N班含加班
                for (int i = 12; i <= 16; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"N"
                        + classTable(bigNightTable) + "\"", "\"N" + classTable(bigNightTable) + "\"", staffNumber);

                    bigNightTable += 1;
                }

                // 畫線
                PresetClass.EXCEL_STYLE(checkExcelNum + 1, 1, checkExcelNum + 16, PresetClass.GET_Data.Count + 1);
            }

            // 2班
            if (classStateNum == 2)
            {
                // 檢查營業時間
                PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2].Formula = formula_vertical_openBusiness(checkExcelNum, x + 2, classStateNum);
                var cont = PresetClass.WORKSHEET.ConditionalFormatting.AddGreaterThan(PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2]);
                cont.Style.Font.Color.Color = System.Drawing.Color.Red;
                cont.Formula = operAtingHours.ToString();

                int dayShiftTable = 0;
                int bigNightTable = 0;

                // D班含加班
                for (int i = 2; i <= 6; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"D"
                        + classTable(dayShiftTable) + "\"", "\"D" + classTable(dayShiftTable) + "\"", staffNumber);

                    dayShiftTable += 1;
                }

                // N班含加班
                for (int i = 7; i <= 11; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"N"
                        + classTable(bigNightTable) + "\"", "\"N" + classTable(bigNightTable) + "\"", staffNumber);

                    bigNightTable += 1;
                }

                // 畫線
                PresetClass.EXCEL_STYLE(checkExcelNum + 1, 1, checkExcelNum + 11, PresetClass.GET_Data.Count + 1);
            }

            if (classStateNum == 3)
            {
                // 檢查營業時間
                PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2].Formula = formula_vertical_openBusiness(checkExcelNum, x + 2, classStateNum);
                var cont = PresetClass.WORKSHEET.ConditionalFormatting.AddGreaterThan(PresetClass.WORKSHEET.Cells[checkExcelNum + 1, x + 2]);
                cont.Style.Font.Color.Color = System.Drawing.Color.Red;
                cont.Formula = operAtingHours.ToString();

                int dayShiftTable = 0;

                // D班含加班
                for (int i = 2; i <= 6; i++)
                {
                    PresetClass.WORKSHEET.Cells[checkExcelNum + i, x + 2].Formula = formulaVerticalClassName(dataExcelNum, x + 2, "\"D"
                        + classTable(dayShiftTable) + "\"", "\"D" + classTable(dayShiftTable) + "\"", staffNumber);

                    dayShiftTable += 1;
                }

                // 畫線
                PresetClass.EXCEL_STYLE(checkExcelNum + 1, 1, checkExcelNum + 6, PresetClass.GET_Data.Count + 1);
            }
        }
    }

    // 公式 Excel 縱向(班別名稱)
    string formula_vertical_openBusiness(int rol, int col, int classState)
    {
        string result = "";

        string dayShiftResult = "";
        string nightResult = "";
        string bigNightResult = "";

        int dayShiftNum = 8;
        int nightNum = 8;
        int bigNightNum = 8;

        int dayShiftNum1 = 12;
        int bigNightNum1 = 12;

        // 3班制
        if (classState == 1)
        {
            // 白班
            for (int x = 2; x <= 6; x++)
            {
                dayShiftResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0, " + dayShiftNum + ",0) +";
                dayShiftNum += 1;
            }

            // 小夜(晚班)
            for (int x = 7; x <= 11; x++)
            {
                nightResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0, " + nightNum + ",0) +";
                nightNum += 1;
            }

            // 大夜(夜班)
            for (int x = 12; x <= 16; x++)
            {
                bigNightResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0," + bigNightNum + " ,0) +";
                bigNightNum += 1;
            }

            result = dayShiftResult + nightResult + bigNightResult;
        }

        // 兩班制
        if (classState == 2)
        {
            // 白班
            for (int x = 2; x <= 6; x++)
            {
                dayShiftResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0," + dayShiftNum1 + " ,0) +";
                dayShiftNum1 += 1;
            }

            // 大夜(夜班)
            for (int x = 7; x <= 11; x++)
            {
                bigNightResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0," + bigNightNum1 + " ,0) +";
                bigNightNum1 += 1;
            }

            result = dayShiftResult + bigNightResult;
        }

        // 一班制
        if (classState == 3)
        {

            // 白斑
            for (int x = 2; x <= 6; x++)
            {
                dayShiftResult += "IF(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "<> 0," + dayShiftNum + ",0) +";
                dayShiftNum += 1;
            }

            result = dayShiftResult;

        }

        result = result.Remove(result.LastIndexOf("+"), 1);

        return result;
    }

    // 公式: Excel 縱向(班別名稱)
    string formulaVerticalClassName(int rol, int col, string className, string className2, int staffNumber)
    {
        string result = "";

        for (int x = 0; x < staffNumber; x++)
        {
            string str = "IF(OR(" + PresetClass.WORKSHEET.Cells[rol + x, col] + "=" + className + ", +"
                + PresetClass.WORKSHEET.Cells[rol + x, col] + "=" + className2 + "), 1, 0) + ";
            result += str;
        }

        result = result.Remove(result.LastIndexOf("+"), 1);

        return result;
    }

    // 公式: Excel 橫向
    string formulaHorizontal(int state, int rol, int col)
    {
        string result = "";

        // 加班狀態 
        for (int x = 0; x < PresetClass.GET_Data.Count; x++)
        {
            if (state == 1)
            {
                string str = "IF(OR(" + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"D1\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"E1\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"N1\"), 1) + IF(OR(" + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"D2\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"E2\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"N2\"), 2) + IF(OR("
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"D3\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"E3\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"N3\"), 3) + IF(OR(" + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"D4\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"E4\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "= \"N4\"), 4) +";

                result += str;
            }

            // 假別
            if (state == 2)
            {
                string str = "IF(OR(" + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"休\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"特\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"病\", " + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"公\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"喪\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"婚\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"公病\"," + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"事\","
                    + PresetClass.WORKSHEET.Cells[rol, col + x] + "=\"災\",),1)+";

                result += str;
            }
        }

        result = result.Remove(result.LastIndexOf("+"), 1);

        return result;
    }

    // 班別區分顏色
    void classState(int checkExcelNum, int frequency, string message, int addClassTable)
    {
        PresetClass.WORKSHEET.Cells[checkExcelNum + frequency, 1].Value = message;

        if (addClassTable.Equals(0))
            PresetClass.TABLE_COLOR(checkExcelNum + frequency, 1, System.Drawing.Color.Orange);
        else
            PresetClass.TABLE_COLOR(checkExcelNum + frequency, 1, System.Drawing.Color.Yellow);
    }

    // 加班訊息表示
    string classTable(int table)
    {
        return (table.Equals(0)) ? "" : table.ToString();
    }

    // toggle 布林為訊息
    string toggleMessage(bool toggleBool, string message)
    {
        return (toggleBool == true) ? message : "";
    }
}

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

專案設定 :

※ 匯入Dll 檔案，非Unity內部功能，會導致錯誤，因此要更改。

Unity 2019 LTS - 2020 做法: