Foreword
- In game companies, in order to facilitate the interaction of game data between programs and planners, Excel files are generally used. The program needs to write a program that reads xlsx files. It can export xlsx files to json, xml, cs files, etc. and store them for convenience. read
- This article introduces how to read xlsx files and export them to cs files
- The advantage of exporting cs files is that the game will be automatically loaded as soon as it is run. It is convenient to read through the dictionary, eliminating the trouble of loading and unloading data and data format parsing.
Disadvantages: configuration data is always in the memory, consuming memory, cs files are generally in the size of medium and large games (5-20M);
Why choose cs file?
Configuration data plays a vital role in the game. Almost all functions (loading of pictures, models, sounds and other resources, multi-language implementation, various game guidance, dialogues, tasks, achievements) rely on configuration data, and for a For medium and large games, several detailed model textures may be larger than the memory occupied by the configuration data. Therefore, it has great value and is much easier to read. - Read xlsx files using NPOI plugin
Editor extension
The following codes are placed under an ExportExcel script
The following files are all placed in one file and under the Editor folder
private static string ExcelPath = Application.dataPath.Replace("Assets", "Excel");
private static string IgnoreChar = "#";
private static string exportDirPath = Application.dataPath + "/Scripts/Data/";
private static string templateFile = Application.dataPath + "/Editor/Excel/Template.txt";
[MenuItem("Tools/Export/Quick Export Configuration Table")]
public static void Export()
{<!-- -->
EditorUtility.DisplayProgressBar("Export cs file based on Excel", "Exporting...", 0f);//Create a loading bar
try
{<!-- -->
//Return the paths of all sub-files under the ExcelPath path
string[] tableFullPaths = Directory.GetFiles(ExcelPath, "*", SearchOption.TopDirectoryOnly);
foreach (string path in tableFullPaths)
{<!-- -->
Debug.Log($"Exporting...{<!-- -->path}");
StartExport(path);//Traverse all excel files
}
AssetDatabase.Refresh();//unity re-reads the changed files from the hard disk
Debug.Log("Export completed");
}
catch
{<!-- -->
Debug.LogError("Export failed");
}
finally
{<!-- -->
EditorUtility.ClearProgressBar();Delete loading bar
}
}
Supports two modes: quick export and all export. In the editor menu bar, “Tools/Export/Export all configuration tables”
public static bool isFastExport = true;
[MenuItem("Tools/Export/Export all configuration tables")]
private static void FastExport()
{<!-- -->
isFastExport = false;
Export();
}
Export only modified xlsx files
Generally, the Excel file is placed in the same path as the Assets folder to prevent Unity from reloading resources.
The ExportFile method uses NPOI’s API to read each cell of the xlsx file and stores it in the sheetDict dictionary.
How to export only the modified xlsx file?
- When the total size of the xlsx file is larger than 2-5M, the export time may be 1-2 minutes, which is a waste of time.
Exporting only the modified xlsx takes about 2-5 seconds - Save each file name and the hash value of the file in a txt file, compare the hash value of the file, and re-export if the hash value changes.
public static Dictionary<string, string> hashDict = new Dictionary<string, string>();
public static StringBuilder allHash = new StringBuilder();
public static void StartExport(string[] tableFullPaths)
{<!-- -->
string hashFile = ExcelPath + "/AllFileHash.txt";//The hash value file is in the same directory as the xlsx file
bool isExitHashFile = File.Exists(hashFile); //Is this the first time to export?
if (!isExitHashFile || !isFastExport)//The hash file does not exist or it is not a fast export, export all xlsx files
{<!-- -->
foreach (string tableFileFullPath in tableFullPaths)
{<!-- -->
if (tableFileFullPath.EndsWith(".xls") || tableFileFullPath.EndsWith(".xlsx"))
{<!-- -->
Debug.LogFormat("Start exporting configuration: {0}", Path.GetFileName(tableFileFullPath));
string hash = CalculateFileHash(tableFileFullPath);//Calculate the hash value of the file
int startIndex = tableFileFullPath.IndexOf("\");//E:/unitycode/project name/Excel\Test.xlsx
string tempFile = tableFileFullPath.Substring(startIndex + 1);//Test.xlsx
allHash.Append($"{<!-- -->tempFile}:{<!-- -->hash}\\
");//Test.xlsx:5F6342BC7B0387...
StartPieceExport(tableFileFullPath);//Export all xlsx files
}
}
}
else
{<!-- -->
ReadFileToDict(hashFile);//Read the hash file into a dictionary, file name->hash value
foreach (string tableFileFullPath in tableFullPaths)
{<!-- -->
if (tableFileFullPath.EndsWith(".xls") || tableFileFullPath.EndsWith(".xlsx"))
{<!-- -->
string hash = CalculateFileHash(tableFileFullPath);//Calculate the market value of Hash
int startIndex = tableFileFullPath.IndexOf("\");//E:/unitycode/project name/Excel\Test.xlsx
string tempFile = tableFileFullPath.Substring(startIndex + 1);//Test.xlsx
allHash.Append($"{<!-- -->tempFile}:{<!-- -->hash}\\
");
if (hashDict.ContainsKey(tempFile))//The dictionary has a file name
{<!-- -->
if (hashDict[tempFile] != hash)//The file name exists in the dictionary, and the hash values are not equal
{<!-- -->
StartPieceExport(tableFileFullPath);//The file has changed
Debug.Log("Exported" + tableFileFullPath);
}
else
{<!-- -->
//Exists, the file has not changed
}
}
else//The file name does not exist in the dictionary, the file is a new file
{<!-- -->
StartPieceExport(tableFileFullPath);
//File added
}//The situation where the xlsx file was deleted is not handled, and the corresponding cs file needs to be deleted manually.
}
}
}
SavehashFile(hashFile);//Write allHash to the file
}
Storage calculated hash file
The following are methods related to calculating and storing hash
hash related
public static void ReadFileToDict(string hashFile)
{<!-- -->
string output = File.ReadAllText(hashFile);//Read the specified path file into a string
hashDict = ParseStringToDictionary(output);//Parse the string into a dictionary
}
public static Dictionary<string, string> ParseStringToDictionary(string input)
{<!-- -->
//Dialogue system.xlsx:5F6342BC7B03878CFB...Language system.xlsx:530D69327A0FA9A7A6...
Dictionary<string, string> dictionary = new Dictionary<string, string>();
//Split the string, each line is a string
string[] lines = input.Split(new[] {<!-- --> "\\
", "\r\\
" }, StringSplitOptions.RemoveEmptyEntries);
//Dialogue system.xlsx:5F6342BC7B03878CFB...
//Language system.xlsx:530D69327A0FA9A7A6...
foreach (string line in lines)
{<!-- -->//Dialogue system.xlsx:5F6342BC7B03878CFB...
string[] parts = line.Split(':');//Use: split string
if (parts.Length == 2)
{<!-- -->
string key = parts[0].Trim();//Dialogue system.xlsx
string value = parts[1].Trim();//5F6342BC7B03878CFB...
dictionary[key] = value;
}
}
return dictionary;
}
public static string CalculateFileHash(string filePath)//Calculate the hash value of a file
{<!-- -->
var hash = SHA256.Create();
var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);//Open file stream
byte[] hashByte = hash.ComputeHash(stream);//Calculate the hash value of the file stream
stream.Close();//Close the file stream
return BitConverter.ToString(hashByte).Replace("-", "");//Convert byte array to string, replace-
}
public static void SavehashFile(string hashFile)//Store a string in the file
{<!-- -->
using (StreamWriter sw = new StreamWriter(File.Create(hashFile)))
sw.Write(allHash);
Debug.Log("hashFile:" + hashFile);
}
hash related
Export data
Record xlsx file to two-dimensional array
private static void StartPieceExport(string path)
{<!-- -->
//Each sheet -> two-dimensional cell
Dictionary<string, List<List<string>>> sheetDict = new Dictionary<string, List<List<string>>>();
FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);//Create a file stream to read and write each xlsx file
IWorkbook book = null;
if (path.EndsWith(".xls"))//If it is an xls file, pass in the binary file stream
book = new HSSFWorkbook(stream);
else if (path.EndsWith(".xlsx"))
book = new XSSFWorkbook(path);//Pass in the file path
else
return;
int sheetNum = book.NumberOfSheets;//Get the number of sheets in an excel file
stream.Close();//Close the file stream
//process each sheet
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex + + )
{<!-- -->
string sheetName = book.GetSheetName(sheetIndex);//Get the name of each sheet file
//The name is prefixed with #, the sheet to be ignored is prefixed with sheet, the default name of excel is ignored
if (sheetName.StartsWith(IgnoreChar) || sheetName.StartsWith("Sheet")) {<!-- --> continue; }
ISheet sheet = book.GetSheetAt(sheetIndex);//Get each sheet object
sheet.ForceFormulaRecalculation = true; //Force formula calculation and execute functions supported by excel itself, such as AVERAGE(D2:K5)
int MaxColumn = sheet.GetRow(0).LastCellNum;//Get the number of columns in the first row
List<List<string>> FilterCellInfoList = new List<List<string>>();
//process each row
for (int rowId = 0; rowId <= sheet.LastRowNum; rowId + + )
{<!-- -->
IRow sheetRowInfo = sheet.GetRow(rowId);
if (sheetRowInfo == null) Debug.LogError("Unable to obtain sheetRowInfo data");
var firstCell = sheetRowInfo.GetCell(0);//Get the first column
if (firstCell == null||firstCell.ToString().Contains(IgnoreChar)) {<!-- --> continue; }//The first column of the row is invalid, skip the row
if (firstCell.CellType == CellType.Blank || firstCell.CellType == CellType.Unknown || firstCell.CellType == CellType.Error) {<!-- --> continue; }
List<string> rowList = new List<string>();//Storage cells of each row
//process each cell
for (int columIndex = 0; columIndex < MaxColumn; columIndex + + )
{<!-- -->
ICell cell = sheetRowInfo.GetCell(columIndex);//Get the columIndex cell of the rowId row
if (cell != null & amp; & amp; cell.IsMergedCell)
{<!-- -->//The cell is not empty and can be merged
cell = GetMergeCell(sheet, cell.RowIndex, cell.ColumnIndex);
}
else if (cell == null)
{<!-- -->// Sometimes the index of the merged grid is empty, so just use the index to find the merged grid directly.
cell = GetMergeCell(sheet, rowId, columIndex);
}
//Calculation results, supports logical expressions
if (cell != null & amp; & amp; cell.CellType == CellType.Formula)
{<!-- -->
cell.SetCellType(CellType.String);
rowList.Add(cell.StringCellValue.ToString());//Add cells to this row
}
else if (cell != null)
{<!-- -->
rowList.Add(cell.ToString());
}
else
{<!-- -->
rowList.Add("");
}
}
FilterCellInfoList.Add(rowList);//Add row data to the table
}
sheetDict.Add(sheetName, FilterCellInfoList);//Add the sheet name and corresponding table data to the dictionary
}
foreach (var item in sheetDict)
{<!-- -->
string fileName = item.Key;
string dirPath = exportDirPath;
ParseExcelToCS(item.Value, item.Key, fileName, dirPath);//Filter the recorded table data->cs file
}//item.Value=>sheetName (class name), item.Key=>sheet description
}
GetMergeCell, merge cells
private static ICell GetMergeCell(ISheet sheet, int rowIndex, int colIndex)
{<!-- -->
//Get the total number of merged units
for (int i = 0; i < sheet.NumMergedRegions; i + + )
{<!-- -->
//Get the first merged cell
var cellrange = sheet.GetMergedRegion(i);
//If in cell range
if (colIndex >= cellrange.FirstColumn & amp; & amp; colIndex <= cellrange.LastColumn
& amp; & amp; rowIndex >= cellrange.FirstRow & amp; & amp; rowIndex <= cellrange.LastRow)
{<!-- -->
//Return to the first cell
var row = sheet.GetRow(cellrange.FirstRow);
var mergeCell = row.GetCell(cellrange.FirstColumn);
return mergeCell;
}
}
return null;
}
Write recorded data to cs file
ParseExcelToCS, export the data to a cs file, write it to the file, use template replacement, and pre-write a .txt file
The following is the Template.txt file, Assets/Editor/Excel/Template.txt
Write data into dictionary,
using UnityEngine;
using System.Collections;
using System.Collections.Generic;
public class _DataClassName
{
_DataClassFields
public _DataClassName(_DataClassParameter)
{
_DataClassFun
}
}
public static class _Data_XX
{
public static Dictionary<string, _DataClassName> data = new Dictionary<string, _DataClassName>()
{
_DictContent
};
}
ParseExcelToCS
The rules for writing xlsx files are as follows: 1: remarks, 2: field name, 3: field type. Lines 1, 2, and 3 can be remarks, followed by fields
Modify according to your own habits
static string _DataClassName = "_DataClassName";
static string _DataClassParameter = "_DataClassParameter";
static string _DataClassFun = "_DataClassFun";
static string _Data_XXDict = "_Data_XX";
static string _DictContent = "_DictContent";
static string _Type = "_Type";
static string _DataClassFields = "_DataClassFields";
private static void ParseExcelToCS(List<List<string>> cellInfo, string tableName, string ExportFileName, string ExportPath)
{<!-- -->
//Read template file
TextAsset template = AssetDatabase.LoadAssetAtPath<TextAsset>("Assets/Editor/Excel/Template.txt");
StringBuilder templateStr = new StringBuilder(template.text);
string saveFullPath = exportDirPath + "Data_" + tableName + ".cs";//The path to the exported file
if (File.Exists(saveFullPath))//File exists, delete
File.Delete(saveFullPath);
List<string> oneRowList = cellInfo[0];//Chinese remarks
List<string> twoRowList = cellInfo[1];//Field name
List<string> threeRowList = cellInfo[2];//Field type
string DataClassName = "Data_" + tableName + "Class";
templateStr.Replace(_Data_XXDict, "Data_" + tableName);
templateStr.Replace(_DataClassName, DataClassName);//Data class
StringBuilder dataClassFields = new StringBuilder();
StringBuilder dataClassParameter = new StringBuilder();
StringBuilder dataClassFun = new StringBuilder();
List<int> vaildColumIndex = new List<int>();
for (int i = 0; i < oneRowList.Count; i + + )//Loop the first row
{<!-- -->
if (oneRowList[i].StartsWith(IgnoreChar)) continue;
if (twoRowList[i].StartsWith(IgnoreChar)) continue;
vaildColumIndex.Add(i);//Add the columns that are valid for filtering to an array
//The following is the result to be output in the following part
/// <summary>
///id
/// </summary>
//public int id;
dataClassFields.AppendFormat("\t/// <summary>\r\\
\t/// {0}\r\\
\t/// </summary>\r\ \
", oneRowList[i]);//Write remarks
dataClassFields.AppendFormat("\tpublic {0} {1};\r\\
", threeRowList[i], twoRowList[i]);//public type field name
//public Data_TestClass(int id,string name)
//{<!-- -->
// this.id = id;
// this.name = name;
//}
dataClassParameter.AppendFormat("{0} {1}", threeRowList[i], twoRowList[i]);//Constructor
if (i < oneRowList.Count - 1)
dataClassParameter.Append(",");
dataClassFun.AppendFormat("\t\tthis.{0} = {1};", twoRowList[i], twoRowList[i]);
if (i < oneRowList.Count - 1)
dataClassFun.Append("\r\\
");
}
templateStr.Replace(_DataClassFields, dataClassFields.ToString());
templateStr.Replace(_DataClassParameter, dataClassParameter.ToString());
templateStr.Replace(_DataClassFun, dataClassFun.ToString());
StringBuilder rowData = new StringBuilder();
string_type = null;
for (int i = 3; i < cellInfo.Count; i + + )//Write data starting from line 3
{<!-- -->
List<string> RowInfo = cellInfo[i];
string id = null;
if (threeRowList[0] == "string")//The type is string or int
{<!-- -->
id = """ + RowInfo[0] + """;
}
else
{<!-- -->
id = RowInfo[0];
}
StringBuilder inner = new StringBuilder();
for (int j = 0; j < vaildColumIndex.Count; j + + )//Traverse each row and column
{<!-- -->
int ColumIndex = vaildColumIndex[j];//Extract the valid index, such as ColumIndex is not 123456, but 1345
string cell = RowInfo[ColumIndex];
string FieldName = twoRowList[ColumIndex];
if (ColumIndex == 0)
{<!-- -->
_type = threeRowList[ColumIndex];
}
string FieldType = threeRowList[ColumIndex];
cell = AllToString(cell, FieldType);
inner.Append(cell);
if (j < vaildColumIndex.Count - 1) inner.Append(",");
}
rowData.Append("\t\t{");
rowData.AppendFormat("{0} ,new {1}({2})", id, DataClassName, inner);
rowData.Append("},");
//public static Dictionary<int, Data_TestClass> data = new Dictionary<int, Data_TestClass>()
//{<!-- -->
//{1 ,new Data_TestClass(1,"name")},
//}
if (i < cellInfo.Count - 1) rowData.Append("\r\\
");//The last row does not need to wrap
}
templateStr.Replace(_DictContent, rowData.ToString());
templateStr.Replace(_Type, _type);
using (StreamWriter sw = new StreamWriter(File.Create(saveFullPath)))
{<!-- -->
sw.Write(templateStr.ToString());//Write the last data to the cs file
sw.Flush();
sw.Close();
}
}
AllToString converts excel cells into a format that can be instantiated, such as (1,2,3)=>new Vector3(1,2,3)
private static string AllToString(string cell, string type)
{<!-- -->
StringBuilder result = new StringBuilder();
switch (type)
{<!-- -->
case "int":
if (cell.Length <= 0) return "0";
result.Append(cell);
break;
case "int[]":
if (cell.Length <= 0) return "new int[] { }";
result.Append("new int[] {");
result.Append(cell);
result.Append("}");
break;
case "int[][]":
if (cell.Length <= 0) return "new int[][] { }";
result.Append("new int[][] {");
string[] s = cell.Split(',');
for (int i = 0; i < s.Length; i + + )
{<!-- -->
result.Append("new int[]" + s[i]);
if (i < s.Length - 1)
{<!-- -->
result.Append(",");
}
}
//result.Append(cell);
result.Append("}");
break;
case "string":
if (cell.Length <= 0) return "null";
result.Append(""");
result.Append(cell);
result.Append(""");
break;
case "string[]":// supports "111", "222" and 111;222
if (cell.Length <= 0) return "null";
result.Append("new string[] {");
if (cell.IndexOf(";") < 0 & amp; & amp; cell.IndexOf(""") < 0)
{<!-- -->
result.Append(""");
result.Append(cell);//"aaa"=>new string[]{"aaa"}
result.Append(""");
}
else
{<!-- -->
if (cell.IndexOf(";") > 0)
{<!-- -->
string[] str = cell.Split(';');
for (int i = 0; i < str.Length; i + + )
{<!-- -->
result.Append(""");
result.Append(str[i]);//"aaa;bbb"=>new string[] {"aaa","bbb"}
result.Append(""");
if (i < str.Length)
{<!-- -->
result.Append(",");
}
}
}
else
{<!-- -->
result.Append(cell);//"aaa","bbb"=>new string[] {"aaa","bbb"}
}
}
result.Append("}");
break;
break;
case "float":
if (cell.Length <= 0) return "0f";
result.AppendFormat("{0}f", cell);
break;
case "double":
if (cell.Length <= 0) return "0d";
result.AppendFormat("{0}d", cell);
break;
case "bool":
if (cell.Length <= 0) return "false";
result.Append(cell.ToLower());
break;
case "Vector3":
if (cell.Length <= 0) return "null";
result.AppendFormat("new Vector3{0}", cell);
break;
case "Vector3[]":
StringBuilder sb = new StringBuilder();
if (cell.Length <= 3) return "null";
string[] strings = cell.Split(new char[] {<!-- --> '(', ')' }, System.StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < strings.Length; i + + )
{<!-- -->
if (strings[i].Length <= 1) continue;
sb.Append("new Vector3");
sb.Append("(");
sb.AppendFormat("{0}", strings[i]);
sb.Append(")");
if (i < strings.Length - 1)
sb.Append(",");
}
result.Append("new Vector3[]");
result.Append("{");
result.AppendFormat("{0}", sb);
result.Append("}");
break;
}
return result.ToString();
}
How to use
Data_Test.data,Data_Test.data[id] access
//The following is the export key class
public static class Data_Test
{
public static Dictionary
{
{ 1, new Data_TestClass(1, “name”) }
}
}
Conclusion
This ends the explanation of exporting cs files from Excel files. If you don’t understand the code above or have questions, you can leave a message in the comment area.