今天分享一个用于将Excel表格的数据序列化为bytes和json文件，以及提供一个数据管理的C#脚本的窗口程序小工具，脚本中用反射进行反序列化（所以性能不是特别好，有待优化），界面如下：
处理后得到以下示例文件：
项目地址：https://gitee.com/fanguounicorn/excel-handler.git
ExcelHandler.cs
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Newtonsoft.Json;
using OfficeOpenXml;
namespace ExcelHandler
{
public partial class ExcelHandler : Form
{
public ExcelHandler()
{
InitializeComponent();
}
public string offlinePath = "Path.txt";
/// <summary>
/// 获取上一次路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Init(object sender, EventArgs e)
{
if (File.Exists(offlinePath))
{
string[] paths = File.ReadAllLines(offlinePath);
if(paths.Length < 3)
{
return;
}
ExcelInputPath.Text = paths[0];
BytesOutputPath.Text = paths[1];
CSOutputPath.Text = paths[2];
}
}
/// <summary>
/// 存储本次路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Close(object sender, FormClosedEventArgs e)
{
using (StreamWriter writer = new StreamWriter(offlinePath))
{
writer.WriteLine(ExcelInputPath.Text);
writer.WriteLine(BytesOutputPath.Text);
writer.WriteLine(CSOutputPath.Text);
writer.Close();
}
}
/// <summary>
/// 选择Excel读取路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectExcelPath(object sender, EventArgs e)
{
ExcelInputPath.Text = OpenDirectoryPath();
}
/// <summary>
/// 选择Bytes保存路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectBytesPath(object sender, EventArgs e)
{
BytesOutputPath.Text = OpenDirectoryPath();
}
/// <summary>
/// 选择Csharp保存路径
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectCSPath(object sender, EventArgs e)
{
CSOutputPath.Text = OpenDirectoryPath();
}
/// <summary>
/// 打开文件管理器选择路径
/// </summary>
/// <returns></returns>
public string OpenDirectoryPath()
{
// 创建一个 FolderBrowserDialog 对象
FolderBrowserDialog folderBrowserDialog = new FolderBrowserDialog();
// 设置对话框的属性
folderBrowserDialog.Description = "请选择文件夹"; // 设置对话框描述信息
folderBrowserDialog.RootFolder = Environment.SpecialFolder.Desktop; // 设置根文件夹
// 显示文件夹对话框
DialogResult result = folderBrowserDialog.ShowDialog();
// 处理对话框返回的结果
if (result == DialogResult.OK)
{
// 获取所选文件的路径
return folderBrowserDialog.SelectedPath;
}
return null;
}
/// <summary>
/// 更新处理结果
/// </summary>
/// <param name="text"></param>
public void UpdateProgress(string text)
{
SolvingProgress.AppendText(text + Environment.NewLine);
// 滚动到最后一行
SolvingProgress.SelectionStart = SolvingProgress.TextLength;
SolvingProgress.ScrollToCaret();
}
/// <summary>
/// 清空结果
/// </summary>
public void ClearProgress()
{
SolvingProgress.Clear();
}
private bool isSolving = false;
private void StartSolve(object sender, EventArgs e)
{
if (isSolving)
{
//如果正在处理则返回
return;
}
ClearProgress();
string excelPath = ExcelInputPath.Text;
string bytesPath = BytesOutputPath.Text;
string csPath = CSOutputPath.Text;
if(string.IsNullOrEmpty(excelPath) || Directory.Exists(excelPath) == false)
{
MessageBox.Show("请输入有效的Excel路径！");
return;
}
if (string.IsNullOrEmpty(bytesPath))
{
MessageBox.Show("请输入Bytes路径！");
return;
}
if (string.IsNullOrEmpty(csPath))
{
MessageBox.Show("请输入CS路径！");
return;
}
int solveCount = 0;
isSolving = true;
SolvingTip.Text = "正在处理，请稍后...";
string[] paths = Directory.GetFiles(excelPath, "*.xlsx", SearchOption.AllDirectories);
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
foreach (string path in paths)
{
try
{
using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
{
int sheetCount = package.Workbook.Worksheets.Count;
for(int i = 0; i < sheetCount; i++)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[i];
string sheetName = worksheet.Name;
int rowCount = worksheet.Dimension.Rows;
int colCount = worksheet.Dimension.Columns;
if(rowCount <= 3)
{
UpdateProgress(sheetName + "表格数据有误，路径：" + path);
continue;
}
UpdateProgress("正在处理" + sheetName + "表格，路径：" + path);
List<List<string>> datas = new List<List<string>>();
for(int r = 4; r <= rowCount; r++)
{
List<string> data = new List<string>();
for(int c = 1; c <= colCount; c++)
{
data.Add(worksheet.Cells[r, c].Text);
}
datas.Add(data);
}
//创建Json和Bytes数据文件
if(Directory.Exists(bytesPath) == false)
{
Directory.CreateDirectory(bytesPath);
}
string json = JsonConvert.SerializeObject(datas);
if (CreateJson.Checked)
{
string json_path = bytesPath + "/" + sheetName + "Data.json";
json_path = json_path.Replace("\\", "/");
File.WriteAllText(json_path, json);
}
byte[] bytes = Encoding.UTF8.GetBytes(json);
string bytes_path = bytesPath + "/" + sheetName + "Data.bytes";
bytes_path = bytes_path.Replace("\\", "/");
string asset_path = bytes_path.Substring(bytes_path.LastIndexOf("/Assets") + 7);
File.WriteAllBytes(bytes_path, bytes);
//创建CS数据管理脚本
if (Directory.Exists(csPath) == false)
{
Directory.CreateDirectory(csPath);
}
string className = sheetName + "DataTable";
string dataName = sheetName + "Data";
string cs_path = csPath + "/" + className + ".cs";
using(StreamWriter writer = new StreamWriter(cs_path))
{
writer.WriteLine("using System;");
writer.WriteLine("using System.IO;");
writer.WriteLine("using System.Text;");
writer.WriteLine("using System.Reflection;");
writer.WriteLine("using System.Collections;");
writer.WriteLine("using System.Collections.Generic;");
writer.WriteLine("using UnityEngine;");
writer.WriteLine("using Newtonsoft.Json;");
writer.WriteLine();
writer.WriteLine("// 数据对象");
writer.WriteLine($"public class {dataName}");
writer.WriteLine("{");
for (int col = 1; col <= colCount; col++)
{
string propertyName = worksheet.Cells[1, col].Text;
string propertyType = worksheet.Cells[2, col].Text;
if (worksheet.Cells[3, col].Value != null)
{
writer.WriteLine(" /// <summary>");
writer.WriteLine($" /// {worksheet.Cells[3, col].Text}");
writer.WriteLine(" /// <summary>");
}
writer.WriteLine($" public {propertyType} {propertyName}" + " { get; set;}");
}
writer.WriteLine("}");
writer.WriteLine();
writer.WriteLine($"public class {className} : Singleton<{className}>");
writer.WriteLine("{");
writer.WriteLine(" /// <summary>");
writer.WriteLine(" /// 数据列表");
writer.WriteLine(" /// <summary>");
writer.WriteLine($" public List<{dataName}> Rows;");
writer.WriteLine();
writer.WriteLine(" /// <summary>");
writer.WriteLine(" /// 数据文件路径");
writer.WriteLine(" /// <summary>");
writer.WriteLine($" private string dataPath = Application.dataPath + @\"{asset_path}\";");
writer.WriteLine();
writer.WriteLine(" /// <summary>");
writer.WriteLine(" /// 数据初始化");
writer.WriteLine(" /// <summary>");
writer.WriteLine(" public void Init()");
writer.WriteLine(" {");
writer.WriteLine($" Rows = new List<{dataName}>();");
writer.WriteLine(" string json = Encoding.UTF8.GetString(File.ReadAllBytes(dataPath));");
writer.WriteLine(" List<List<string>> datas = (List<List<string>>)JsonConvert.DeserializeObject(json, typeof(List<List<string>>));");
writer.WriteLine($" Type dataType = typeof({dataName});");
writer.WriteLine(" PropertyInfo[] properties = dataType.GetProperties();");
writer.WriteLine(" foreach(List<string> data in datas)");
writer.WriteLine(" {");
writer.WriteLine($" {dataName} row= new {dataName}();");
writer.WriteLine(" for(int i = 0; i < properties.Length; i++)");
writer.WriteLine(" {");
writer.WriteLine(" string propertyName = properties[i].Name;");
writer.WriteLine(" string propertyValue = data[i];");
writer.WriteLine(" PropertyInfo property = dataType.GetProperty(propertyName);");
writer.WriteLine(" try");
writer.WriteLine(" {");
writer.WriteLine(" object value = Convert.ChangeType(propertyValue, property.PropertyType);");
writer.WriteLine(" // 设置属性值");
writer.WriteLine(" property.SetValue(row, value);");
writer.WriteLine(" }");
writer.WriteLine(" catch (Exception ex)");
writer.WriteLine(" {");
writer.WriteLine(" // 转换失败，输出错误信息或者处理错误");
writer.WriteLine(" Debug.LogError($\"Failed to convert {propertyValue} to {property.PropertyType} for property {propertyName}: {ex.Message}\");");
writer.WriteLine(" }");
writer.WriteLine(" }");
writer.WriteLine(" Rows.Add(row);");
writer.WriteLine(" }");
writer.WriteLine(" }");
writer.WriteLine();
writer.WriteLine(" /// <summary>");
writer.WriteLine(" /// 获取数据");
writer.WriteLine(" /// <summary>");
writer.WriteLine($" public {dataName} GetData(int id)");
writer.WriteLine(" {");
writer.WriteLine(" int index = BinarySearch(id);");
writer.WriteLine(" if (index != -1)");
writer.WriteLine(" {");
writer.WriteLine(" return Rows[index];");
writer.WriteLine(" }");
writer.WriteLine(" else");
writer.WriteLine(" {");
writer.WriteLine(" // 未找到对应数据");
writer.WriteLine(" return null;");
writer.WriteLine(" }");
writer.WriteLine(" }");
writer.WriteLine();
writer.WriteLine(" /// <summary>");
writer.WriteLine(" /// 二分查找数据");
writer.WriteLine(" /// <summary>");
writer.WriteLine(" private int BinarySearch(int id)");
writer.WriteLine(" {");
writer.WriteLine(" int left = 0;");
writer.WriteLine(" int right = Rows.Count - 1;");
writer.WriteLine();
writer.WriteLine(" while (left <= right)");
writer.WriteLine(" {");
writer.WriteLine(" int mid = left + (right - left) / 2;");
writer.WriteLine(" int currentID = Rows[mid].ID;");
writer.WriteLine();
writer.WriteLine(" if (currentID == id)");
writer.WriteLine(" {");
writer.WriteLine(" return mid; // 找到了目标值");
writer.WriteLine(" }");
writer.WriteLine(" else if (currentID < id)");
writer.WriteLine(" {");
writer.WriteLine(" left = mid + 1; // 目标值在右半部分");
writer.WriteLine(" }");
writer.WriteLine(" else");
writer.WriteLine(" {");
writer.WriteLine(" right = mid - 1; // 目标值在左半部分");
writer.WriteLine(" }");
writer.WriteLine(" }");
writer.WriteLine();
writer.WriteLine(" return -1; // 没有找到目标值");
writer.WriteLine(" }");
writer.WriteLine("}");
solveCount++;
writer.Close();
}
}
}
}
catch(Exception ex)
{
UpdateProgress("处理异常：" + ex.Message);
return;
}
}
UpdateProgress("处理结束，本次共处理：" + solveCount + "个文件");
SolvingTip.Text = "处理完成";
isSolving = false;
MessageBox.Show("处理成功！");
}
}
}
1073
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
