赞
踩
- using System.Collections;
- using System.Collections.Generic;
- using UnityEngine;
- using OfficeOpenXml;
- using System.IO;
- using Excel;
- using System.Data;
- using System;
- /// <summary>
- /// https://blog.csdn.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 csdn
- /// </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 csdn
- /// </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();
- }
- }
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。