Unity to Excel 建立班表 (固定格式)

因由Unity 發佈後，無法建立Excle原因，主要是Dll少檔案

Excel 全部相關DLL下載

提取码：0xso

備用載點

---------------------- Excel 班表格式 ------------------------------

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using System.Data;
using Excel;
using OfficeOpenXml;
using System;

public class CreateRoster_Excel
{
    private int _year, _month;
    private string _companyName, _unit;
   
    public CreateRoster_Excel(int year, int month, string companyName, string unit)
    {
        this._year = year;
        this._month = month;
        this._companyName = companyName;
        this._unit = unit;
    }

    // 建立資料位置
    private string folderPath = @"D:\班表\";
    // 檔案名稱
    private string fileName = "班表";
    // 檔案路徑
    private string filePath;                                             

    // Excel 固定格式
    int companyNameYear = 1;                                      // 年
    int positionNumber = 2;                                       // 職位
    int dayExcelNuber = 3;                                        // 天

    // excel 組件
    ExcelPackage excelPackage;
    ExcelWorksheet workSheets;

    // 每年每月類別
    QueryMonthAndDays queryMonthAndDays;

    // 檔案類別
    FileInfo fileInfo;

    public void test()
    {
        createExcelFile();
        excel_Fixed();
    }

    // excel 固定格式建立
    private void excel_Fixed()
    {
        queryMonthAndDays = new QueryMonthAndDays(_year, _month, 1);

        excelPackage = new ExcelPackage(fileInfo);
        workSheets = excelPackage.Workbook.Worksheets.Add(queryMonthAndDays.getData()[0].month + "月");

        // 寫入公司名稱
        workSheets.Cells[companyNameYear, 1].Value = queryMonthAndDays.getData()[0].year + "年" + queryMonthAndDays.getData()[0].month + "月 " + _companyName + " (" + "單位: " + _unit + ")";
        workSheets.Cells[companyNameYear, 1, 1, queryMonthAndDays.getData().Count + 3].Merge = true;

        // 寫入單位
        workSheets.Cells[positionNumber, 1].Value = _unit;
        workSheets.Cells[positionNumber, 1, positionNumber, queryMonthAndDays.getData().Count + 3].Merge = true;
        // 表格上色
        tableColor(positionNumber, 1, System.Drawing.Color.Yellow);

        // 寫入年月與公司名稱
        foreach (var dayweekData in queryMonthAndDays.getData())
        {
            workSheets.Cells[dayExcelNuber, 1].Value = "員工";
            // 天與週次
            workSheets.Cells[dayExcelNuber, Int32.Parse(dayweekData.day) + 1].Value = dayweekData.week + dayweekData.day;

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

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

        excelStyle(1, 1, 3);
        excelPackage.Save();
    }

    // excel 樣式
    void excelStyle(int fromRow, int fromCol, int toRow)
    {
        // 字形
        workSheets.Cells.Style.Font.Name = "標楷體";
        // 文字大小
        workSheets.Cells.Style.Font.Size = 16;
        // 水平置中
        workSheets.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
        // 垂直中
        workSheets.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
        // 表格框線
        workSheets.Cells[fromRow, fromCol, toRow, queryMonthAndDays.getData().Count + 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;
        workSheets.Cells[fromRow, fromCol, toRow, queryMonthAndDays.getData().Count + 3].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        workSheets.Cells[fromRow, fromCol, toRow, queryMonthAndDays.getData().Count + 3].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        workSheets.Cells[fromRow, fromCol, toRow, queryMonthAndDays.getData().Count + 3].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
    }

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

    // 建立檔案
    private void createExcelFile()
    {
        // 建立資料夾
        if (!Directory.Exists(folderPath + @"\" + _unit))
            Directory.CreateDirectory(folderPath + @"\" + _unit);

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

    }

    // 檔案是否存在
    bool fileExist()
    {
        filePath = folderPath + @"\" + _unit + @"\" + _year.ToString() + "年" + _month.ToString() + "月" + ".xlsx";
        fileInfo = new FileInfo(filePath);

        bool fileExist = fileInfo.Exists;

        return fileExist;
    }
 }

---------------------------以下每年每月時間抓取---------------------------------

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


// List 管理
public class Data_data
{
    public string year { get; set; }        // 年
    public string month { get; set; }       // 月
    public string day { get; set; }         // 日
    public string week { get; set; }        // 週期

    public Data_data(string year, string month, string day, string week)
    {
        this.year = year;
        this.month = month;
        this.day = day;
        this.week = week;
    }
}

public class QueryMonthAndDays
{
    int _year;      // 年
    int _month;     // 月
    int _day;       // 日

    DateTime startdate;

    // 建構子
    public QueryMonthAndDays(int year, int month, int day)
    {
        this._year = year;
        this._month = month;
        this._day = day;

        startdate = new DateTime(_year, _month, _day);
    }

    // 天數
    public int DayNumber()
    {
        int dayNumber = 0;
        for(int x=1; x <= DateTime.DaysInMonth(startdate.Year, startdate.Month); x++)
        {
            dayNumber = x;
        }

        return dayNumber;
    }

    // 執行
    public List<Data_data> getData()
    {
        List<Data_data> data = new List<Data_data>();
        
        for (int x = 1; x <= DateTime.DaysInMonth(startdate.Year, startdate.Month); x++)
        {
            startdate = new DateTime(startdate.Year, startdate.Month, x);

            data.Add(new Data_data(vidToRepublic(startdate.Year).ToString(), startdate.Month.ToString(), x.ToString(),
                weekEngToChin(startdate.DayOfWeek.ToString())));      
        }

        return data;
    } 

    // 西元轉換民國 (公式)
    int vidToRepublic(int dayofYear)
    {
        int vidNumber = 1911; // 公式

        int sum = dayofYear - vidNumber;

        return sum;           
    }

    // 英文轉中文(星期)
    string weekEngToChin(string dayOfWeek)
    {
        string chinWeek = "";

        if (dayOfWeek.ToString().Equals("Monday"))
            chinWeek = "一";
        if (dayOfWeek.ToString().Equals("Tuesday"))
            chinWeek = "二";
        if (dayOfWeek.ToString().Equals("Wednesday"))
            chinWeek = "三";
        if (dayOfWeek.ToString().Equals("Thursday"))
            chinWeek = "四";
        if (dayOfWeek.ToString().Equals("Friday"))
            chinWeek = "五";
        if (dayOfWeek.ToString().Equals("Saturday"))
            chinWeek = "六";
        if (dayOfWeek.ToString().Equals("Sunday"))
            chinWeek = "日";

        return chinWeek;
    }
}

------------------------- 圖片顯示 --------------------

結果顯示: Excel 建立班表格式