系列文章目录
Unity工具
文章目录
前言
大家好,我是★心疼你的一切★,不定时更新Unity开发技巧,觉得有用记得一键三连哦。
开发过程中可能会需要json文件,但你只有Excel,所以要转一下,不想转也行
一、首先你要读取Excel文件
第一步需要读取Excel的dll文件
下载地址
如果没有积分可以私聊我邮箱
1-1 Excel文件转成Json、XML、CSV文件
在Editor文件夹里右键新建一个脚本,
1-2 代码如下
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Excel;
using System.Data;
using System.IO;
using Newtonsoft.Json;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using System;
public class ExcelController
{
/// <summary>
/// 表格数据集合
/// </summary>
private DataSet mResultSet;
/// <summary>
/// 读取表数据
/// </summary>
/// <param name="excelFile">Excel file.</param>
public ExcelController(string excelFile)
{
FileStream mStream = File.Open(excelFile, FileMode.Open, FileAccess.Read);
IExcelDataReader mExcelReader = ExcelReaderFactory.CreateOpenXmlReader(mStream);
mResultSet = mExcelReader.AsDataSet();
}
/// <summary>
/// 转换为Json格式文件
/// </summary>
/// <param name="JsonPath">Json文件路径</param>
/// <param name="Header">表头行数</param>
public void ConvertToJson(string JsonPath, Encoding encoding)
{
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
return;
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
//判断数据表内是否存在数据
if (mSheet.Rows.Count < 1)
return;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
//准备一个列表存储整个表的数据
List<Dictionary<string, object>> table = new List<Dictionary<string, object>>();
//读取数据
for (int i = 1; i < rowCount; i++)
{
//准备一个字典存储每一行的数据
Dictionary<string, object> row = new Dictionary<string, object>();
for (int j = 0; j < colCount; j++)
{
//读取第1行数据作为表头字段
string field = mSheet.Rows[0][j].ToString();
//Key-Value对应
row[field] = mSheet.Rows[i][j];
}
//添加到表数据中
table.Add(row);
}
//生成Json字符串
string json = JsonConvert.SerializeObject(table, Newtonsoft.Json.Formatting.Indented);
//写入文件
using (FileStream fileStream = new FileStream(JsonPath, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
{
textWriter.Write(json);
}
}
}
/// <summary>
/// 转换为CSV格式文件
/// </summary>
public void ConvertToCSV(string CSVPath, Encoding encoding)
{
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
return;
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
//判断数据表内是否存在数据
if (mSheet.Rows.Count < 1)
return;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
//创建一个StringBuilder存储数据
StringBuilder stringBuilder = new StringBuilder();
//读取数据
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
//使用","分割每一个数值
stringBuilder.Append(mSheet.Rows[i][j] + ",");
}
//使用换行符分割每一行
stringBuilder.Append("\r\n");
}
//写入文件
using (FileStream fileStream = new FileStream(CSVPath, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
{
textWriter.Write(stringBuilder.ToString());
}
}
}
/// <summary>
/// 转换为Xml格式文件
/// </summary>
public void ConvertToXml(string XmlFile)
{
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
return;
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
//判断数据表内是否存在数据
if (mSheet.Rows.Count < 1)
return;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
//创建一个StringBuilder存储数据
StringBuilder stringBuilder = new StringBuilder();
//创建Xml文件头
stringBuilder.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
stringBuilder.Append("\r\n");
//创建根节点
stringBuilder.Append("<Table>");
stringBuilder.Append("\r\n");
//读取数据
for (int i = 1; i < rowCount; i++)
{
//创建子节点
stringBuilder.Append(" <Row>");
stringBuilder.Append("\r\n");
for (int j = 0; j < colCount; j++)
{
stringBuilder.Append(" <" + mSheet.Rows[0][j].ToString() + ">");
stringBuilder.Append(mSheet.Rows[i][j].ToString());
stringBuilder.Append("</" + mSheet.Rows[0][j].ToString() + ">");
stringBuilder.Append("\r\n");
}
//使用换行符分割每一行
stringBuilder.Append(" </Row>");
stringBuilder.Append("\r\n");
}
//闭合标签
stringBuilder.Append("</Table>");
//写入文件
using (FileStream fileStream = new FileStream(XmlFile, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, Encoding.GetEncoding("utf-8")))
{
textWriter.Write(stringBuilder.ToString());
}
}
}
}
二、工具使用步骤
2-1 工具代码
代码如下(示例):需要一个编辑器工具进行使用
using UnityEngine;
using UnityEditor;
using System.Collections;
using System.Collections.Generic;
using System.Text;
public class ExcelTools : EditorWindow
{
/// <summary>
/// 当前编辑器窗口实例
/// </summary>
private static ExcelTools instance;
/// <summary>
/// Excel文件列表
/// </summary>
private static List<string> excelList;
/// <summary>
/// 项目根路径
/// </summary>
private static string pathRoot;
/// <summary>
/// 滚动窗口初始位置
/// </summary>
private static Vector2 scrollPos;
/// <summary>
/// 输出格式索引
/// </summary>
private static int indexOfFormat = 0;
/// <summary>
/// 输出格式
/// </summary>
private static string[] formatOption = new string[] { "JSON", "CSV", "XML" };
/// <summary>
/// 编码索引
/// </summary>
private static int indexOfEncoding = 0;
/// <summary>
/// 编码选项
/// </summary>
private static string[] encodingOption = new string[] { "UTF-8", "GB2312" };
/// <summary>
/// 是否保留原始文件
/// </summary>
private static bool keepSource = true;
/// <summary>
/// 显示当前窗口
/// </summary>
[MenuItem("Plugins/ExcelTools")]
static void ShowExcelTools()
{
Init();
//加载Excel文件
LoadExcel();
instance.Show();
}
void OnGUI()
{
DrawOptions();
DrawExport();
}
/// <summary>
/// 绘制插件界面配置项
/// </summary>
private void DrawOptions()
{
GUILayout.BeginHorizontal();
EditorGUILayout.LabelField("请选择格式类型:", GUILayout.Width(85));
indexOfFormat = EditorGUILayout.Popup(indexOfFormat, formatOption, GUILayout.Width(125));
GUILayout.EndHorizontal();
GUILayout.BeginHorizontal();
EditorGUILayout.LabelField("请选择编码类型:", GUILayout.Width(85));
indexOfEncoding = EditorGUILayout.Popup(indexOfEncoding, encodingOption, GUILayout.Width(125));
GUILayout.EndHorizontal();
keepSource = GUILayout.Toggle(keepSource, "保留Excel源文件");
}
/// <summary>
/// 绘制插件界面输出项
/// </summary>
private void DrawExport()
{
if (excelList == null) return;
if (excelList.Count < 1)
{
EditorGUILayout.LabelField("目前没有Excel文件被选中哦!");
}
else
{
EditorGUILayout.LabelField("下列项目将被转换为" + formatOption[indexOfFormat] + ":");
GUILayout.BeginVertical();
scrollPos = GUILayout.BeginScrollView(scrollPos, false, true, GUILayout.Height(150));
foreach (string s in excelList)
{
GUILayout.BeginHorizontal();
GUILayout.Toggle(true, s);
GUILayout.EndHorizontal();
}
GUILayout.EndScrollView();
GUILayout.EndVertical();
//输出
if (GUILayout.Button("转换"))
{
Convert();
}
}
}
/// <summary>
/// 转换Excel文件
/// </summary>
private static void Convert()
{
foreach (string assetsPath in excelList)
{
//获取Excel文件的绝对路径
string excelPath = pathRoot + "/" + assetsPath;
//构造Excel工具类
ExcelController excel = new ExcelController(excelPath);
//判断编码类型
Encoding encoding = null;
if (indexOfEncoding == 0)
{
encoding = Encoding.GetEncoding("utf-8");
}
else if (indexOfEncoding == 1)
{
encoding = Encoding.GetEncoding("gb2312");
}
//判断输出类型
string output = "";
if (indexOfFormat == 0)
{
output = excelPath.Replace(".xlsx", ".json");
excel.ConvertToJson(output, encoding);
}
else if (indexOfFormat == 1)
{
output = excelPath.Replace(".xlsx", ".csv");
excel.ConvertToCSV(output, encoding);
}
else if (indexOfFormat == 2)
{
output = excelPath.Replace(".xlsx", ".xml");
excel.ConvertToXml(output);
}
//判断是否保留源文件
if (!keepSource)
{
FileUtil.DeleteFileOrDirectory(excelPath);
}
//刷新本地资源
AssetDatabase.Refresh();
}
//转换完后关闭插件
//这样做是为了解决窗口
//再次点击时路径错误的Bug
instance.Close();
}
/// <summary>
/// 加载Excel
/// </summary>
private static void LoadExcel()
{
if (excelList == null) excelList = new List<string>();
excelList.Clear();
//获取选中的对象
object[] selection = (object[])Selection.objects;
//判断是否有对象被选中
if (selection.Length == 0)
return;
//遍历每一个对象判断不是Excel文件
foreach (Object obj in selection)
{
string objPath = AssetDatabase.GetAssetPath(obj);
if (objPath.EndsWith(".xlsx"))
{
excelList.Add(objPath);
}
}
}
private static void Init()
{
//获取当前实例
instance = EditorWindow.GetWindow<ExcelTools>();
//初始化
pathRoot = Application.dataPath;
//对路径进行处理
pathRoot = pathRoot.Substring(0, pathRoot.LastIndexOf("/"));
excelList = new List<string>();
scrollPos = new Vector2(instance.position.x, instance.position.y + 75);
}
void OnSelectionChange()
{
//当选择发生变化时重绘窗体
Show();
LoadExcel();
Repaint();
}
}
2-2 使用方法第一步点击菜单栏Tools
2-3 点击ExcelTools
2-4 把表格放到StreamingAssets里面
2-5 选中Excel
2-6 点击转换
2-7 查看Json
2-8 查看XML
总结
本文记录原地址:
https://itmonon.blog.csdn.net/article/details/128311260?spm=1001.2014.3001.5502
本文使用了Unity的编辑器拓展,制作了一个简单的Excel文件转XML、JSON、CSV文件小工具。
因为有些平台比如WEBGL不支持IO加载Excel文件,所以将Excel转成CSV文件或者json就很有用了。
不定时更新Unity开发技巧,觉得有用记得一键三连哦。