因由Unity 發佈後，無法建立Excle原因，主要是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 建立班表格式 |
