using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using OfficeOpenXml;
using System.IO;
using Excel;
using System.Data;
using System;
/// <summary>
/// https://blog.****.net/Xz616/article/details/128893023
/// Unity3D操作数据之Excel表操作(创建、读取、写入、修改)
/// 北斗课题 VR 设备统计功能开发——每次程序打开关闭记录对应时间到本地电子表格
/// 列：打开年，月，日，时间，关闭年，月，日，时间
/// </summary>
public class ExcelOperation : MonoBehaviour
{
// [SerializeField]
string strExcelSavePath = "";
[SerializeField]
string strFilename = "北斗VR设备统计.xlsx";
// Start is called before the first frame update
void Start()
{
//strExcelSavePath = Application.streamingAssetsPath;
}
/// <summary>
/// 增
/// Application.dataPath : E:/UnityProject_V2020.3.47f3c1/BDProject/Assets
/// Application.streamingAssetsPath :/UnityProject_V2020.3.47f3c1/BDProject/Assets/StreamingAssets
/// </summary>
public void CreatExcel()
{
//文件地址
FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
//如果文件存在删除重建
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
}
//数据操作//如果只想创建文件不写入表头，数据操作步骤可以省略
using (ExcelPackage package = new ExcelPackage(newFile))
{
//初次创建增加数据操作（重点在于这条操作语句不同）
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");
//添加对应列名
worksheet.Cells[1, 1].Value = "列名1";
worksheet.Cells[1, 2].Value = "列名2";
worksheet.Cells[1, 3].Value = "列名3";
//保存
package.Save();
print("创建成功");
}
}
/// <summary>
/// 存
/// </summary>
public void WriteExcel()
{
//文件地址
FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
//数据操作
using (ExcelPackage package = new ExcelPackage(newFile))
{
//增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
ExcelWorksheet worksheet = package.Workbook.Worksheets["test"];
//添加第二行数据
worksheet.Cells[2, 1].Value = "名称1";
worksheet.Cells[2, 2].Value = "价格1";
worksheet.Cells[2, 3].Value = "销量1";
//添加第三行数据
worksheet.Cells[3, 1].Value = "名称2";
worksheet.Cells[3, 2].Value = "价格2";
worksheet.Cells[4, 3].Value = "销量2";
//保存
package.Save();
print("写入数据成功");
}
}
/// <summary>
/// 已知已有行数，添加一行新数据，供外部调用
/// dt为开始时间
/// </summary>
public void AddStartTimeToExcel()
{
if (!isSaveExcel)
{ //文件地址
FileInfo newFile = new FileInfo(strExcelSavePath + "/" + strFilename);
//数据操作
using (ExcelPackage package = new ExcelPackage(newFile))
{
//增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
ExcelWorksheet worksheet = package.Workbook.Worksheets["设备统计"];
//添加第curRows(原行数当现索引用)行数据 Cells[x,y]//x，y都是从1开始数的
curRows++;
worksheet.Cells[curRows, 1].Value = (curRows - 1).ToString();
worksheet.Cells[curRows, 2].Value = DateTime.Now.Year;
worksheet.Cells[curRows, 3].Value = DateTime.Now.Month;
worksheet.Cells[curRows, 4].Value = DateTime.Now.Day;
worksheet.Cells[curRows, 5].Value = DateTime.Now.ToString("HH:mm:ss");
//保存
package.Save();
isSaveExcel = true;
print("写入开始时间到excel");
}
}
}
/// <summary>
/// 给系统一登录新增的那一行数据的最后一列新增内容——存储结束时间
/// </summary>
public void AddEndTimeToExcel()
{
if (isSaveExcel)
{ //文件地址
FileInfo newFile = new FileInfo(strExcelSavePath + "/" + strFilename);
//数据操作
using (ExcelPackage package = new ExcelPackage(newFile))
{
//增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
ExcelWorksheet worksheet = package.Workbook.Worksheets["设备统计"];
//添加第curRows(原行数当现索引用)行数据 Cells[x,y]//x，y都是从1开始数的
// curRows = curRows++;
// worksheet.Cells[curRows, 1].Value = (curRows - 1).ToString();
worksheet.Cells[curRows, 6].Value = DateTime.Now.Year;
worksheet.Cells[curRows, 7].Value = DateTime.Now.Month;
worksheet.Cells[curRows, 8].Value = DateTime.Now.Day;
worksheet.Cells[curRows, 9].Value = DateTime.Now.ToString("HH:mm:ss");
//保存
package.Save();
isSaveExcel = false;
print("写入结束时间到excel");
}
}
}
/*/// <summary>
/// 删改 from ****
/// </summary>
public void ChangeExcel()
{
//文件地址
FileInfo newFile = new FileInfo(strExcelSavePath + "/test.xlsx");
//数据操作
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["test"];
//追加
worksheet.Cells[4, 1].Value = "名称3";
worksheet.Cells[4, 2].Value = "价格3";
worksheet.Cells[4, 3].Value = "销量3";
//删除某一列（参数：列的序号）
worksheet.DeleteColumn(1);
//删除某一行（参数：行的序号）
worksheet.DeleteRow(1);
//修改（和添加一样）
worksheet.Cells[4, 1].Value = "修改名称";
worksheet.Cells[4, 2].Value = "修改价格";
worksheet.Cells[4, 3].Value = "修改销量";
//保存
package.Save();
print("删改数据成功");
}
}*/
/// <summary>
/// 第一次存开始时间的时候不变此值，存结束时间的时候变
/// </summary>
bool isSaveExcel = false;
/// <summary>
/// 系统刚打开查询表格看看表格中总行数，也就是即将添加的行的索引
/// </summary>
int curRows = 0;
/// <summary>
/// 系统刚打开查询表格看看表格中总行数，也就是即将添加的行的索引
/// </summary>
int curColumns = 0;
/* /// <summary>
/// 查 from ****
/// </summary>
public void ReadExecel()
{
//加载文件
FileStream fileStream = File.Open(strExcelSavePath + "/test.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
DataSet result = excelDataReader.AsDataSet();
// 获取表格有多少列
int columns = result.Tables[0].Columns.Count;
// 获取表格有多少行
int rows = curRows = result.Tables[0].Rows.Count;
Debug.Log("共有：" + columns + "列 " + rows + "行");
//第一行为表头，不读取。没有表头从0开始(获取数据)
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
// 获取表格中指定行指定列的数据
string value = result.Tables[0].Rows[i][j].ToString();
Debug.Log(value);
}
}
fileStream.Close();
print("查询数据成功");
}*/
/// <summary>
/// 读取本地excel,为了填充变量 curRows，获取当前行数
/// </summary>
/// <param name="fileName">含文件后缀：北斗VR设备统计.xlsx</param>
public void ReadExecel()
{
//加载文件
FileStream fileStream = File.Open(strExcelSavePath + "/" + strFilename, FileMode.Open, FileAccess.Read);
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
DataSet result = excelDataReader.AsDataSet();
// 获取表格有多少列
int columns = curColumns = result.Tables[0].Columns.Count;
// 获取表格有多少行
int rows = curRows = result.Tables[0].Rows.Count;
Debug.Log("共有：" + columns + "列 " + rows + "行");
/* //第一行为表头，不读取。没有表头从0开始(获取数据)
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
// 获取表格中指定行指定列的数据
string value = result.Tables[0].Rows[i][j].ToString();
Debug.Log(value);
}
}*/
fileStream.Close();
//print("查询数据成功，行数：" + curRows);
}
// Update is called once per frame
void Update()
{
/*if (Input.GetKeyUp(KeyCode.U))
{
print(Application.streamingAssetsPath);
CreatExcel();
}
if (Input.GetKeyUp(KeyCode.I))
{
WriteExcel();
}*/
/*if (Input.GetKeyUp(KeyCode.O))
{
ReadExecel();
AddStartTimeToExcel();
}
if (Input.GetKeyUp(KeyCode.P))
{
AddEndTimeToExcel();
}*/
}
/// <summary>
/// DontDestroyOnLoad(GameObject);
//让此物体在场景销毁的时候不进行销毁，但是有几点需要注意
//再次返回到此物体所在场景的时候，这个脚本所在的物体也会进行初始化，执行Awake(),Start()方法，所以要设计成单例的：
//注意看注释
/// </summary>
private static ExcelOperation _instance; // 单例
public ExcelOperation GameManagerInstance
{
get { return _instance; }
}
void Awake()
{
if (_instance != null)
{
//这里一定要是销毁this.gameObject
Destroy(this.gameObject);
return;
}
//这句话只执行一次，第二次上面return了
_instance = this;
strExcelSavePath = Application.streamingAssetsPath;
ReadExecel();
AddStartTimeToExcel();
DontDestroyOnLoad(gameObject);
}
private void OnApplicationQuit()
{
AddEndTimeToExcel();
}
}
