Unity 使用 EPPlus 读写 Excel

Unity 版本： 2020.3.36f1c1

打包平台：PC（EXE）

需要两种 DLL复制到 Plugins 文件夹底下

1. Excel的
   分别为：EPPlus.dll、Excel.dll、ICSharpCode.SharpZipLib.dll
   下载地址
2. 18dll的地址
   找到自己版本的Unity安装目录的 Editor 文件夹下的路径 Editor\Data\MonoBleedingEdge\lib\mono\unityjit
   

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

public class ExcelManager : Singleton<ExcelManager>
{
    /// <summary>
    /// 写入的路径
    /// </summary>
    private string PersistentExcel;

    /// <summary>
    /// 创建 Excel
    /// </summary>
    /// <param name="fileName"> Excel 的名字</param>
    public void CreatExcel(string fileName)
    {
        PersistentExcel = PersistentFilePath(fileName);

        //文件地址
        FileInfo newFile = new FileInfo(PersistentExcel);

        if (newFile.Exists) return;

        newFile = new FileInfo(PersistentExcel);

        //数据操作
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //初次创建增加数据操作（重点在于这条操作语句不同）
            ExcelWorksheet worksheet;

            worksheet = package.Workbook.Worksheets.Add(fileName);
            // 添加对应列名
            worksheet.Cells[1, 1].Value = "计数";
            worksheet.Cells[1, 2].Value = "题型";
            worksheet.Cells[1, 3].Value = "题型编号";
            worksheet.Cells[1, 4].Value = "题目";
            worksheet.Cells[1, 5].Value = "选项A";
            worksheet.Cells[1, 6].Value = "选项B";
            worksheet.Cells[1, 7].Value = "选项C";
            worksheet.Cells[1, 8].Value = "选项D";
            worksheet.Cells[1, 9].Value = "答案";
            worksheet.Cells[1, 10].Value = "解析";

            //保存
            package.Save();

            WriteExcelForCreat("1000道题库");
        }
    }

    /// <summary>
    /// 添加数据
    /// </summary>
    /// <param name="fileURL">添加的数据的路径</param>
    public void ChangeExcel_Add(string fileURL)
    {
        WriteExcel(fileURL);
    }

    /// <summary>
    /// 删除 Excel 中的某一行
    /// </summary>
    /// <param name="row"></param>
    public void ChangeExcel_Remove(int row)
    {
        //文件地址
        FileInfo newFile = new FileInfo(PersistentExcel);
        //数据操作
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
            worksheet.DeleteRow(row);

            //保存
            package.Save();
        }
    }

    public DataTable ReadingExcel()
    {
        return ReadingExcel(PersistentExcel);
    }

    public DataTable ReadingExcel(string fileURL)
    {
        // 加载文件
        FileStream fileStream = File.Open(fileURL, FileMode.Open, FileAccess.Read);
        // 读取Excel
        IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
        // 储存数据
        DataSet result = excelDataReader.AsDataSet();

        fileStream.Close();

        return result.Tables[0];
    }

    /// <summary>
    /// 第一次创建题库的时候写入的1000道题
    /// </summary>
    private void WriteExcelForCreat(string fileName)
    {
        string fistWrite = StreamingAssetsPath(fileName);
        WriteExcel(fistWrite);
    }

    /// <summary>
    /// 写入数据
    /// </summary>
    /// <param name="readingWriteDataURL">写入时,需要把哪些数据写入,就是哪些数据的路径</param>
    private void WriteExcel(string readingWriteDataURL)
    {
        //文件地址
        FileInfo newFile = new FileInfo(PersistentExcel);
        //数据操作
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            // 没写入之前总共的行数
            int totalRow = ReadingExcel(PersistentExcel).Rows.Count;

            // 读取需要写入的数据
            DataTable dataTable = ReadingExcel(readingWriteDataURL);
            // 需要写入的列数
            int needingWriteColumns = dataTable.Columns.Count;
            // 需要写入的行数
            int needingWriteRows = dataTable.Rows.Count;

            //增加数据操作（重点在于这条操作语句与初次创建添加数据不同）
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

            for (int row = 1; row < needingWriteRows; row++)
            {
                for (int col = 0; col < needingWriteColumns; col++)
                {
                    // 在标格中新加入数据时, 新加入的行为: totalRow + row ;第一列的索引为:col + 1,表格中没有第 0 列 
                    worksheet.Cells[totalRow + row - 1, col + 1].Value = dataTable.Rows[row][col].ToString();
                }
            }
            //保存
            package.Save();
        }
    }

    /// <summary>
    /// 持久性路径
    /// </summary>
    private string PersistentFilePath(string fileName)
    {
        return Path.Combine(Application.persistentDataPath, $"{fileName}.xlsx");
    }

    /// <summary>
    /// 第一次加载 Excel 时的路径
    /// </summary>
    private string StreamingAssetsPath(string fileName)
    {
        return Path.Combine(Application.streamingAssetsPath, $"Template\\{fileName}.xlsx");
    }

}