接着上篇Unity导表工具开发(一),界面更改了一下颜色以及加了一个刷新按钮
开始分析代码
应该都能看出来这是基于C#得Winform程序,所有这边用得是C#代码
这边的载入配置和保存配置用的Xml,导出的是Json,
个人认为主要的难点就是将所有的Excel导出为Json和Xml文件的保存和读取,别的应该没什么难得,现在先上Xml得帮助类。
Xml帮助类
public class XmlHelper
{
public static void CreateXml(string excelName, List<string> typeList, List<string> nameList, string xmlName)
{
//创建一个XML文档对象
XmlDocument doc = new XmlDocument();
//声明XML头部信息
XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
//添加进doc对象子节点
doc.AppendChild(dec);
//创建根节点
XmlElement root = doc.CreateElement("xml");
doc.AppendChild(root);
//再创建根节点下的子节点
XmlElement child = doc.CreateElement("excel");
//设置子节点属性
child.SetAttribute("name", excelName);
for (int i = 0; i < typeList.Count; i++)
{
//再创建根节点下的子节点
XmlElement field = doc.CreateElement("field");
//设置子节点属性
field.SetAttribute("name", nameList[i]);
field.SetAttribute("type", typeList[i]);
child.AppendChild(field);
}
//最后把标记添加到根节点的子节点下
root.AppendChild(child);
//doc文档对象保存写入
doc.Save(xmlName);
}
public static void LoadXml(string xmlName)
{
//将XML文件加载进来
XmlDocument doc = new XmlDocument();
doc.Load(xmlName);
//获取根节点
XmlElement root = doc.DocumentElement;
//获取子节点
XmlNodeList pnodes = root.GetElementsByTagName("excel");
//使用foreach循环读出集合
string excelName = ((XmlElement)pnodes[0]).GetAttribute("name");
XmlNodeList fields = root.GetElementsByTagName("field");
foreach (XmlNode field in fields)
{
string type = ((XmlElement)field).GetAttribute("type");
string name = ((XmlElement)field).GetAttribute("name");
}
}
}
这边暂时还没有分析多个Excel表的情况,这个肯定是跑的通的,我测试的代码如下:
private void btn_LoadXml_Click(object sender, EventArgs e)
{
string xmlName = "C:/Users/SUN/Desktop/ensign/info.xml";
try
{
XmlHelper.LoadXml(xmlName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btn_SaveXml_Click(object sender, EventArgs e)
{
string xmlName = "C:/Users/SUN/Desktop/ensign/info.xml";
List<string> typeList = new List<string>() { "int", "string", "string", "int" };
List<string> nameList = new List<string>() { "id", "name", "icon", "quality" };
try
{
XmlHelper.CreateXml("测试用", typeList, nameList, xmlName);
MessageBox.Show("保存成功");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
这是winform程序里面的两个按钮点击事件,也就是最上面界面里的载入配置和保存配置两个按钮了。
打开代码写死的文件路径(实际情况一般不会写死,会默认项目路径)
打开看看:
顺便提一下,我用的这个是Sublime Text,看Json或者Xml文件都很舒服,哈哈
添加Excel
private void btn_AddExcel_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog openFileDialog = new OpenFileDialog(); //显示选择文件对话框
openFileDialog.InitialDirectory = "C:\\";
openFileDialog.Filter = "excel files (*.xlsx)|*.xlsx";
openFileDialog.FilterIndex = 2;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
FileInfo file = new FileInfo(openFileDialog.FileName);
foreach (string item in this.lb_ExcelList.Items)
{
if (item == file.Name)
{
MessageBox.Show("Excel文件已存在");
return;
}
}
fileInfos.Add(file);
this.lb_ExcelList.Items.Add(file.Name); //显示文件路径
this.lb_ExcelList.SelectedIndex = this.lb_ExcelList.Items.Count - 1; //默认选中新添加的
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
解析Excel
private DataTable LoadExcelToDataTable(string filename, string worksheetname)
{
DataTable table;
//连接字符串
//String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
String sConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filename + ";" + "Extended Properties = \"Excel 12.0 Xml;HDR=YES\"";
OleDbConnection myConn = new OleDbConnection(sConnectionString);
string strCom = " SELECT * FROM [" + worksheetname + "$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
table = new DataTable();
myCommand.Fill(table);
myConn.Close();
return table;
}
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application(); //写外面,不然很卡
private void lb_ExcelList_SelectedIndexChanged(object sender, EventArgs e)
{
string worksheetname = string.Empty;
object objMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook myBook = (Microsoft.Office.Interop.Excel.Workbook)oExcel.Workbooks.Open(fileInfos[this.lb_ExcelList.SelectedIndex].FullName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
Microsoft.Office.Interop.Excel.Sheets sheets = myBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
worksheetname = worksheet.Name;//获取worksheet Name
oExcel.Quit();
dgv_JsonSetting.DataSource = null;
DataTable table = LoadExcelToDataTable(fileInfos[this.lb_ExcelList.SelectedIndex].FullName, worksheetname);
DataTable dt = new DataTable();
dt.Columns.Add("Excel表字段");
dt.Columns.Add("Json字段名");
dt.Columns.Add("Json字段类型");
DataRow dr = dt.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dr = dt.NewRow();
dr["Excel表字段"] = table.Columns[i];
dr["Json字段名"] = "";
dr["Json字段类型"] = "string";
dt.Rows.Add(dr);
}
this.dgv_JsonSetting.DataSource = dt;
}
private void dgv_JsonSetting_CellEnter(object sender, DataGridViewCellEventArgs e)
{
if (this.dgv_JsonSetting.Columns[2].HeaderText == "Json字段类型")
{
DataGridViewComboBoxCell cel = new DataGridViewComboBoxCell();
cel.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox;
cel.Items.Add("string");
cel.Items.Add("int");
cel.Items.Add("bool");
cel.Items.Add("short");
cel.Items.Add("long");
this.dgv_JsonSetting.Rows[0].Cells[2] = cel;
}
}
这个解析Excel看着挺复杂的,其实也就是3个步骤,
第一步:得到你要操作的Excel,
第二部:通过微软的Excel操作类取出里面的数据放到DataTable里面,
第三步:将数据显示到界面上,并且保存起来,之后导出Json。
其中为了省事,一般会将字段类型写成选择的,可以确保它不会因为不注意写错了。可能会有人疑问 this.dgv_JsonSetting 这个是什么,这个是Winform的DataGridView控件。运行一下:
this.dgv_JsonSetting就是左边很大框的这个,可以看到Excel表字段,Json字段名是自己填写的,Json字段类型也是自己选择。
目前运行就是这个样子的。
导出Json的代码
还没写哈哈,下一节见咯