close
又嘗試用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 做法:
文章標籤
全站熱搜
留言列表
