Unity3D 读取和写入Excel文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

using UnityEngine;

public class BaseTable : MonoBehaviour
{
    public string TableName;
    public string ExcelPath;

    public virtual void Init() { }
}

[System.Serializable]
public class TableDataPath
{
    public string Path;
    public string TableName;
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;

public class ItemTable : BaseTable
{
    public List<ItemElement> DataList = new List<ItemElement>();

    [HideInInspector]
    public Dictionary<int, ItemElement> dict = new Dictionary<int, ItemElement>();

    public override void Init()
    {
        base.Init();

        foreach (var v in DataList)
        {
            if (dict.ContainsKey(v.ID))
            {
                Debug.LogError("Table Init error with same Id: " + v.ID);
                continue;
            }
            dict.Add(v.ID, v);
        }
    }
}

public enum ItemType
{
    Invalid = 0,
    Coin = 1,
    Crystal = 2,
    Equipment = 3,
}

[Serializable]
public class ItemElement
{
    public int ID;
    public string Name;
    public bool QuickUse;
    public ItemType ItemType;
}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEditor;
using System.IO;
using OfficeOpenXml;
using System;
using System.Reflection;

[CustomEditor(typeof(BaseTable), true)]
public class BaseTableEditor : Editor
{
    public override void OnInspectorGUI()
    {
        if (GUILayout.Button("Load Excel"))
        {
            LoadExcel();
        }
        base.OnInspectorGUI();
    }

    void LoadExcel()
    {
        var currentTarget = (BaseTable)serializedObject.targetObject;
        string filePath = Application.dataPath + "/Excel/" + currentTarget.ExcelPath + ".xlsx";
        Debug.Log("filePath: " + filePath);
        var fileInfo = new FileInfo(filePath);
        using (var package = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet ws = package.Workbook.Worksheets[currentTarget.TableName];
            Type tableType = currentTarget.GetType();
            FieldInfo listField = tableType.GetField("DataList");
            Type listType = listField.FieldType;
            Type elementType = listField.FieldType.GetGenericArguments()[0];
            listType.GetMethod("Clear").Invoke(listField.GetValue(currentTarget), new object[] { });

            var clientColDic = new Dictionary<int, string>();
            int colNum = GetColumnsCount(ws);
            int rowNum = GetRowsCount(ws);
            for (int i = 1; i <= colNum; i++)
            {
                Debug.Assert(ws.Cells[2, i].Value != null, "null Row:2 Col:" + i);
                if (ws.Cells[2, i].Value.ToString() != "Server")
                {
                    clientColDic.Add(i, ws.Cells[1, i].Value.ToString());
                }
            }
            Debug.Log("colNum: " + colNum + " rowNum: " + rowNum);

            for (int i = 3; i <= rowNum; i++)
            {
                object subData = Activator.CreateInstance(elementType, null);
                for (int j = 1; j <= colNum; j++)
                {
                    if (clientColDic.ContainsKey(j))
                    {
                        if (ws.Cells[i, j].Value == null || string.IsNullOrEmpty(ws.Cells[i, j].Value.ToString()))
                        {
                            Debug.LogWarning("Row " + i + " Column " + j + " is Null");
                            continue;
                        }
                        string val = ws.Cells[i, j].Value.ToString();
                        FieldInfo fi = elementType.GetField(clientColDic[j]);
                        if (fi == null)
                        {
                            Debug.LogWarning("Can not find FieldInfo " + clientColDic[j]);
                            continue;
                        }

                        if (fi.FieldType.Equals(typeof(int)))
                        {
                            Debug.Log("Property: " + fi + " int value: " + val);
                            fi.SetValue(subData, Convert.ToInt32(val));
                        }
                        else if (fi.FieldType.Equals(typeof(string)))
                        {
                            Debug.Log("Property: " + fi + " string value: " + val);
                            fi.SetValue(subData, val);
                        }
                        else if (fi.FieldType.Equals(typeof(bool)))
                        {
                            Debug.Log("Property: " + fi + " bool value: " + val);
                            fi.SetValue(subData, Convert.ToBoolean(int.Parse(val)));
                        }
                        else if (fi.FieldType.Equals(typeof(float)))
                        {
                            Debug.Log("Property: " + fi + " float value: " + val);
                            fi.SetValue(subData, Convert.ToSingle(val));
                        }
                        else if (fi.FieldType.IsEnum)
                        {
                            Debug.Log("Property: " + fi + " enum value: " + val);
                            fi.SetValue(subData, Convert.ToInt32(val));
                        }
                        else
                        {
                            Debug.LogWarning("Property: " + fi + " null value: " + val);
                        }
                    }
                }
                listType.GetMethod("Add").Invoke(listField.GetValue(currentTarget), new object[] { subData });
            }

            SaveAs_Txt(filePath, ws, rowNum, colNum);
        }

        AssetDatabase.SaveAssets();
        Debug.Log("done");
    }

    void SaveAs_Txt(string filePath, ExcelWorksheet ws, int rowNum, int colNum)
    {
        if (!filePath.EndsWith(".xlsx"))
        {
            Debug.LogError("filePath is wrong");
            return;
        }
        string targetFilePath = filePath.Replace(".xlsx", ".txt");

        using (TextWriter tw = new StreamWriter(targetFilePath, false, System.Text.Encoding.UTF8))
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder(10000);
            for (int i = 1; i <= rowNum; i++)
            {
                for (int j = 1; j <= colNum; j++)
                {
                    sb.Append(ws.Cells[i,j].Value.ToString());
                    if (j != colNum)
                    {
                        sb.Append("\t");
                    }
                }
                sb.Append("\r\n");
            }
            tw.Write(sb);
        }
    }

    void CombineExcel(TableDataPath resultDataPath, List<TableDataPath> dataPathList)
    {
        if (dataPathList.Count <= 0) return;

        var firstDataPath = dataPathList[0];
        var firstFileInfo = new FileInfo(firstDataPath.Path);
        using (var firstPackage = new ExcelPackage(firstFileInfo))
        {
            var firstSheet = firstPackage.Workbook.Worksheets[firstDataPath.TableName];
            int colNum = GetColumnsCount(firstSheet);
            int currentRow = GetRowsCount(firstSheet);

            for (int i = 1; i < dataPathList.Count; i++)
            {
                var data = dataPathList[i];
                var fileInfo = new FileInfo(data.Path);
                using (var package = new ExcelPackage(fileInfo))
                {
                    ExcelWorksheet workSheet = package.Workbook.Worksheets[data.TableName];
                    int rows = GetRowsCount(workSheet);
                    if (rows > 2)
                    {
                        var source = workSheet.Cells[3, 1, rows, colNum];
                        var target = firstSheet.Cells[currentRow + 1, 1, currentRow + rows, colNum];
                        source.Copy(target);
                        currentRow += rows;
                    }
                }
            }

            var fi = new FileInfo(resultDataPath.Path);
            if (fi.Exists)
            {
                File.SetAttributes(resultDataPath.Path, FileAttributes.Normal);
            }
            using (var package = new ExcelPackage(fi))
            {
                package.Workbook.Worksheets.Delete(resultDataPath.TableName);
                var workSheet = package.Workbook.Worksheets.Add(resultDataPath.TableName, firstSheet);
                int rowCount = GetRowsCount(workSheet);
                int colCount = GetColumnsCount(workSheet);

                for (int r = 1; r <= rowCount; r++)
                {
                    for (int c = 1; c <= colCount; c++)
                    {
                        workSheet.Cells[r, c].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        workSheet.Cells[r, c].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    }
                }

                package.Workbook.Worksheets.MoveToStart(resultDataPath.TableName);
                workSheet.View.TabSelected = true;
                workSheet.Calculate();
                workSheet.Cells.AutoFitColumns(0);
                package.Save();
            }
        }
    }

    int GetColumnsCount(ExcelWorksheet ws)
    {
        int count = 0;
        for (; count < ws.Dimension.Columns; count++)
        {
            object obj = ws.Cells[1, count + 1].Value;
            if (obj == null || obj.ToString() == string.Empty)
                return count;
        }
        return count;
    }

    int GetRowsCount(ExcelWorksheet ws)
    {
        int count = 0;
        for (; count < ws.Dimension.Rows; count++)
        {
            object obj = ws.Cells[count + 1, 1].Value;
            if (obj == null || obj.ToString() == string.Empty)
                return count;
        }
        return count;
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
using OfficeOpenXml;

public class ExcelTool : MonoBehaviour
{
	void Start ()
    {
        string filePath = @"D:\" + "Ite.xlsx";
        string sheetName = "Sheet1";
        GenerateExcel(filePath, sheetName);
        ReadExcel(filePath, sheetName);
	}

    private void ReadExcel(string filePath, string sheetName)
    {
        var fi = new FileInfo(filePath);

        using (var package = new ExcelPackage(fi))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
            for (int i = 2; i <= worksheet.Dimension.End.Row; i++)
            {
                Debug.Log(worksheet.Cells[i, 1].Value + "  " + worksheet.Cells[i, 2].Value +
                    "  " + worksheet.Cells[i, 3].Value);
            }
        }
    }

    private void GenerateExcel(string filePath, string sheetName)
    {
        var fi = new FileInfo(filePath);
        if (fi.Exists)
        {
            fi.Delete();
            fi = new FileInfo(filePath);
        }
        
        using (var package = new ExcelPackage(fi))
        {
            // Add a new worksheet to the empty workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);

            // Add the headers
            var headerRow = new List<object[]>()
            {
                new string[] { "ID", "Product", "Price" }
            };
            string headerRange = "A1:" + char.ConvertFromUtf32(headerRow.Count + 64) + "1";
            worksheet.Cells[headerRange].LoadFromArrays(headerRow);

            // Add some items
            worksheet.Cells[2, 1].Value = 1001;
            worksheet.Cells[2, 2].Value = "Hammer";
            worksheet.Cells[2, 3].Value = 4.6;

            worksheet.Cells[3, 1].Value = 1001;
            worksheet.Cells[3, 2].Value = "Hammer";
            worksheet.Cells[3, 3].Value = 4.6;

            //worksheet.Cells["E2:E4"].Formula = "C2*D2";

            // set style
            for (int r = 1; r <= worksheet.Dimension.Rows; r++)
            {
                for (int c = 1; c <= headerRow[0].Length; c++)
                {
                    worksheet.Cells[r, c].Style.HorizontalAlignment = 
                        OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    worksheet.Cells[r, c].Style.VerticalAlignment = 
                        OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                }
            }

            worksheet.Calculate();
            worksheet.Cells.AutoFitColumns(0);
            package.Save();
        }

        Debug.Log("generateExcel done");
    }
}