LinkGame/LuaDemo/Assets/plugins/module_excel/Script/Excel_Streaming/CreateExcel.cs

using OfficeOpenXml;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using Excel;
using System.Reflection;
using System;
using UnityEngine.UI;
using UnityEditor;
using System.Data;
/// <summary>
/// 创建Excel表
/// </summary>
public class CreateExcel : MonoBehaviour
{
    public Text mtext;
    List<string> mtestList = new List<string>();
    string outPutDir;
    string savePutDir;
    const string SheetName = "monster";
    void Awake()
    {
        outPutDir = Application.streamingAssetsPath + "\\" + "Excel/MyExcel.xlsx";
        savePutDir = Application.streamingAssetsPath + "\\" + "Excel/MyExcel.xlsx";

    }
    // Use this for initialization
    void Start()
    {

        ExcelType2WriteExcel();//写入数据
        LoadExcel();//读取数据

        WriteExcel(mtestList,savePutDir);//写入
        ReadExcel(outPutDir);//读取
    }

    public void ExcelType2WriteExcel()
    {
        string[] head = new string[5] { "id", "name", "level", "hp", "mp" };
        string[] data1 = new string[5] { "1001", "哥布林", "50", "1000", "1000" };
        string[] data2 = new string[5] { "1002", "猫妖", "70", "7000", "7000" };
        string[] data3 = new string[5] { "1003", "牛头怪", "100", "100000", "100000" };
        string[] data4 = new string[5] { "1004", "精英僵尸", "200", "200000", "200000" };
        List<string[]> data = new List<string[]>();
        data.Add(head);
        data.Add(data1);
        data.Add(data2);
        data.Add(data3);
        data.Add(data4);
        CreateExcels(savePutDir, SheetName, data);
    }
    void LoadExcel()
    {
        List<string[]> data = ReadExcelFuncTwo(outPutDir, SheetName);

        if (data == null || data.Count == 0) return;

        string allData = "";
        for (int i = 0; i < data.Count; i++)
        {
            string rowData = "";
            for (int k = 0; k < data[i].Length; k++)
            {
                rowData += data[i][k] + "  ";
            }
            allData += rowData + "\n";
        }
        //   print(allData);
        mtext.text = allData;
    }

    #region --ExcelFashion 1----
    /// <summary>
    ///返回数据的集合 
    ///数据的格式为 每一行为一条数据
    ///例：赵一|党员|1年|赵一.png| 
    /// </summary>
    /// <returns></returns>
    public List<string> ReadExcel(string moutPutDr)
    {
        // StreamingAssets目录下的  党员信息.xlsx文件的路径：Application.streamingAssetsPath + "/党员信息.xlsx" 
        FileStream fileStream = File.Open(moutPutDr, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
        // 表格数据全部读取到result里
        DataSet result = excelDataReader.AsDataSet();

        // 获取表格有多少列 
        int columns = result.Tables[0].Columns.Count;
        // 获取表格有多少行 
        int rows = result.Tables[0].Rows.Count;
        // 根据行列依次打印表格中的每个数据 

        List<string> excelDta = new List<string>();
        string value;
        string all;
        //第一行为表头，不读取
        for (int i = 1; i < rows; i++)
        {
            value = null;
            all = null;
            for (int j = 0; j < columns; j++)
            {
                // 获取表格中指定行指定列的数据 
                value = result.Tables[0].Rows[i][j].ToString();
                if (value == "")
                {
                    continue;
                }
                all = all + value + "|";
            }
            if (all != null)
            {
                print(all);
                excelDta.Add(all);
            }
        }
        return excelDta;
    }

    /// <summary>
    /// list内容格式
    /// 赵一|党员|1年|赵一.png| 
    /// </summary>
    /// <param name="newList"></param>
    public void WriteExcel(List<string> newList, string savePutDir)
    {
        //自定义excel的路径

        //string path = Application.streamingAssetsPath + "/党员信息.xlsx";
        // print(Application.dataPath);

        FileInfo newFile = new FileInfo(savePutDir);

        if (newFile.Exists)
        {
            //创建一个新的excel文件

            newFile.Delete();

            newFile = new FileInfo(savePutDir);
        }

        //通过ExcelPackage打开文件
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            //在excel空文件添加新sheet

            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("message");
            //添加列名

            worksheet.Cells[1, 1].Value = "姓名";

            worksheet.Cells[1, 2].Value = "职务";

            worksheet.Cells[1, 3].Value = "党龄";

            worksheet.Cells[1, 4].Value = "图片名";

            for (int i = 0; i < newList.Count; i++)
            {
                string[] messages = newList[i].Split('|'); //赵一|党员|1年|赵一.png| 
                string itemName = messages[0];
                string itemWork = messages[1];
                string itemYear = messages[2];
                string imageName = messages[3];
                //添加一行数据

                int num = i + 2;

                worksheet.Cells["A" + num].Value = itemName;

                worksheet.Cells["B" + num].Value = itemWork;

                worksheet.Cells["C" + num].Value = itemYear;

                worksheet.Cells["D" + num].Value = imageName;
            }

            //保存excel
            package.Save();
            print("重写完成");
        }
    }
    #endregion

    #region --ExcelFashion 2----
    /// <summary>
    /// 保存数据到新的Excel表中
    ///数据的格式为 每一行为一条数据
    /// </summary>
    void CreateExcels(string excelpath, string sheetname, List<string[]> data)
    {
        if (data == null || data.Count == 0) return;
        FileInfo fileInfo = new FileInfo(excelpath);
        if (fileInfo.Exists)
        {
            //创建一个新的excel文件

            fileInfo.Delete();

            fileInfo = new FileInfo(excelpath);
        }
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetname);
            for (int i = 0; i < data.Count; i++)
            {
                for (int k = 0; k < data[i].Length; k++)
                {
                    worksheet.Cells[i + 1, k + 1].Value = data[i][k];
                }
            }
            package.Save();
        }
        Debug.Log("导出Excel成功");
    }
    /// <summary>
    /// 读取Excel表
    /// </summary>
    /// <param name="excelpath">表路径</param>
    /// <param name="sheetname">sheet名称</param>
    public static List<string[]> ReadExcelFuncTwo(string excelpath, string sheetname)
    {
        FileStream fileStream = File.Open(excelpath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        ExcelPackage pck = new ExcelPackage(new MemoryStream(), fileStream);
        ExcelWorkbook ewb = pck.Workbook;
        ExcelWorksheet ew = ewb.Worksheets[sheetname];
        int columns = ew.Dimension.End.Column;//获取列数   
        int rows = ew.Dimension.End.Row;//获取行数

        List<string[]> result = new List<string[]>();

        //第一行为表头，不读取        
        for (int i = 1; i <= rows; i++)
        {
            string[] rowData = new string[columns];
            for (int j = 1; j <= columns; j++)
            {
                // 获取表格中指定行指定列的数据 
                rowData[j - 1] = ew.Cells[i, j].Value.ToString();
            }
            result.Add(rowData);
        }
        return result;
    }
    #endregion
}