/// <summary>
/// Excel2DataSet
/// </summary>
static public DataSet ExcelToDataSet(string path)
{
DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection();
try
{
//HDR=Yes/NO 表示是否将首行做标题,若设了YES 数据集中自动忽略了第一行。 IMEX 表示是否强制转换为文本
//string sql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";//2003版
string sql = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"";//2007版
conn.ConnectionString = sql;
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//string tableName = dt.Rows[0][2].ToString().Trim();//获得第一张工作区间表名
//OleDbCommand cmd = new OleDbCommand("select * from [" + tableName + "]", conn);//工作表名就是Excel显示区下面的工作区名
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd); //读取Excel表格
da.Fill(ds);
}
catch (Exception ex)
{
Assist.Log(ex.StackTrace);
}
return(ds);
}
private void ForGen(string inPath, string outPath)
{
Assist.ClearDirFiles(outPath);
DirectoryInfo folder = new DirectoryInfo(inPath);
FileSystemInfo[] files = folder.GetFileSystemInfos();
int length = files.Length;
for (int index = 0; index < length; index++)
{
if (files[index] is DirectoryInfo)
{
//this.ForGen(files[index].FullName);
}
else
{
if (files[index].Name.EndsWith(".xlsx"))
{
//DataSet mResultSet = Assist.ExcelToDataSet(files[index].FullName);
FileStream stream = File.Open(files[index].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet mResultSet = excelReader.AsDataSet();
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
{
return;
}
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
string tableName = mSheet.TableName;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
string xmlFileName = tableName + ".xml";
string xmlNodeName = tableName;
XmlDocument xmlDoc = new XmlDocument();;
XmlNode root = xmlDoc.CreateElement("Root");;
xmlDoc.AppendChild(root);
XmlNode parent = xmlDoc.CreateElement("List");
root.AppendChild(parent);
XmlElement node;
for (int i = contentBeginIndex; i < rowCount; i++)//行
{
node = xmlDoc.CreateElement(xmlNodeName);
for (int j = 0; j < colCount; j++)//列
{
string key = mSheet.Rows[titleIndex][j].ToString();
if (key == "")
{
Assist.Log("下标" + "(" + titleIndex + "," + j + ")" + "不能为空");
return;
}
string value = mSheet.Rows[i][j].ToString();
node.SetAttribute(key, value);
parent.AppendChild(node);
}
}
string savePath = Path.Combine(outPath, xmlFileName);
xmlDoc.Save(savePath);
xmlDoc = null;
}
}
}
Assist.Log("Gen OK");
}
public void Gen()
{
string currDir = Environment.CurrentDirectory;
this.m_inPath = Path.Combine(currDir, m_inPath);
Assist.CreateDir(this.m_inPath);
this.m_outPath = Path.Combine(currDir, m_outPath);
Assist.CreateDir(this.m_outPath);
ForGen(m_inPath, m_outPath);
string savePath = Path.Combine(m_outPath, "PBData.bytes");
using (var file = File.Create(savePath))
{
try
{
ProtoBuf.Serializer.Serialize(file, obj);
}
catch (Exception e)
{
Assist.Log(e.ToString());
}
}
Assist.Log("Gen OK");
//FileStream fs = new FileStream(savePath, FileMode.Open, FileAccess.Read);
//int len = (int)fs.Length;
//byte[] array = new byte[len];
//fs.Read(array, 0, len);
//MemoryStream ms = new MemoryStream(array);
//PBData pbData = null;
//try
//{
// pbData = ProtoBuf.Serializer.Deserialize<PBData>(ms);
//}
//catch (Exception e)
//{
// Assist.Log(e.ToString());
//}
//int c = pbData.HeroInfoDic.Count;
//// string s = pbData.HeroInfoDic[]
//foreach (var item in pbData.HeroInfoDic)
//{
// string s = item.Key;
// Assist.Log(s);
// string sda = item.Value.Name;
// Assist.Log(sda);
//}
//foreach (var item in pbData.SkillInfoDic)
//{
// string s = item.Key;
// Assist.Log(s);
// string sda = item.Value.SkillName;
// Assist.Log(sda);
//}
}
/// <summary>
///
/// </summary>
/// <param name="inPath"></param>
/// <param name="outPath"></param>
private void ForGen(string inPath, string outPath)
{
List <string> listClassNames = new List <string>();
Assist.ClearDirFiles(outPath);
DirectoryInfo folder = new DirectoryInfo(inPath);
FileSystemInfo[] files = folder.GetFileSystemInfos();
int length = files.Length;
for (int index = 0; index < length; index++)
{
if (files[index] is DirectoryInfo)
{
//this.ForGen(files[index].FullName);
}
else
{
if (files[index].Name.EndsWith(".xlsx"))
{
//DataSet mResultSet = Assist.ExcelToDataSet(files[index].FullName);
FileStream stream = File.Open(files[index].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet mResultSet = excelReader.AsDataSet();
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
{
return;
}
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
string tableName = mSheet.TableName;
string csName = m_itemHeader + Stringer.FirstLetterUp(tableName);
listClassNames.Add(csName);
CreateCSItemData csItemData = new CreateCSItemData(m_nameSpace, csName, m_outPath);
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
List <string> mainKeyList = new List <string>();
for (int j = 0; j < colCount; j++)//列
{
string key = mSheet.Rows[titleIndex][j].ToString();
if (key == "")
{
Assist.Log("下标" + "(" + titleIndex + "," + j + ")" + "不能为空");
return;
}
string strData = mSheet.Rows[typeIndex][j].ToString();
string[] strs = strData.Split('+');
string keyType = strs[0];
int isMainkey = int.Parse(strs[1]);
string linkTableName = strs[2];
ItemField itemField = new ItemField(key, keyType, MemberAttributes.Private);
csItemData.AddFieldValue(itemField);
ItemProperty itemProperty = new ItemProperty(key);
itemProperty.SetGetName();
itemProperty.SetSetName();
itemProperty.SetComment(mSheet.Rows[summary][j].ToString());
itemProperty.SetValueType(keyType);
itemProperty.SetModifier(MemberAttributes.Public | MemberAttributes.Final);
itemProperty.SetField("ProtoMember", (j + 1).ToString());
csItemData.AddPropertyValue(itemProperty);
//是主键，或者是合成主键的一部分字段
if (isMainkey == 1)
{
mainKeyList.Add(key);
}
//有链接的表
if (linkTableName != "0")
{
linkTableName = m_itemHeader + Stringer.FirstLetterUp(linkTableName);
string subClassname = linkTableName;
ItemMethod mis = new ItemMethod("Get" + subClassname, MemberAttributes.Final | MemberAttributes.Public, new List <string>()
{
"System.String"
});
csItemData.SetComment("获取" + subClassname, mis.Method);
mis.Method.ReturnType = new CodeTypeReference(subClassname);
mis.Method.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("CfgData.Instance.m_PBData." + subClassname + "Dic[inArg0]")));
csItemData.AddMethodValue(mis);
}
}
//最多由3个字段组成主键
string MainKey = "";
if (mainKeyList.Count == 1)
{
MainKey = mainKeyList[0] + ".ToString()";
}
else if (mainKeyList.Count == 2)
{
MainKey = mainKeyList[0] + ".ToString() + \"_\" + this.m_" + mainKeyList[1] + ".ToString()";
}
else if (mainKeyList.Count == 3)
{
MainKey = mainKeyList[0] + ".ToString() + \"_\" + this.m_" + mainKeyList[1] + ".ToString() + \"_\" + this.m_" + mainKeyList[2] + ".ToString()";
}
ItemProperty itemProperty2 = new ItemProperty("MainKey");
itemProperty2.SetGetName(MainKey);
itemProperty2.SetComment("主键");
itemProperty2.SetValueType("string");
itemProperty2.SetModifier(MemberAttributes.Public | MemberAttributes.Final);
csItemData.AddPropertyValue(itemProperty2);
csItemData.Create();
}
}
}
CreatePBData final = new CreatePBData(m_nameSpace, m_PBData, m_outPath);
final.SetValue(listClassNames);
final.Create();
List <string> dyncUsingList = new List <string>();
dyncUsingList.Add("protobuf-net.dll");
dyncUsingList.Add("mscorlib.dll");
List <string> dyncCsFilesName = Assist.GetObjPaths(".cs", m_outPath);
string csDataPoolPath = Path.Combine(Environment.CurrentDirectory, "CfgData.cs");
dyncCsFilesName.Add(csDataPoolPath);
for (int i = 0; i < dyncCsFilesName.Count; i++)
{
dyncCsFilesName[i] = dyncCsFilesName[i].Replace("/", "\\");
}
string dyncDllPath = Path.Combine(m_outPath, "CfgData.dll");
CompilerResults cr = DyncLibTool.CompileCSharpCode(dyncCsFilesName, dyncDllPath, dyncUsingList);
//Assembly assembly = cr.CompiledAssembly;
Assist.Log("Gen OK");
}
private void ForGen(string inPath, string outPath)
{
Assist.ClearDirFiles(outPath);
DirectoryInfo folder = new DirectoryInfo(inPath);
FileSystemInfo[] files = folder.GetFileSystemInfos();
int length = files.Length;
for (int index = 0; index < length; index++)
{
if (files[index] is DirectoryInfo)
{
//this.ForGen(files[index].FullName);
}
else
{
if (files[index].Name.EndsWith(".xlsx"))
{
//DataSet mResultSet = Assist.ExcelToDataSet(files[index].FullName);
FileStream stream = File.Open(files[index].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet mResultSet = excelReader.AsDataSet();
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
{
return;
}
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
string tableName = mSheet.TableName;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
string jsonFileName = tableName + ".json";
//准备一个列表存储整个表的数据
Dictionary <string, Dictionary <string, object> > table = new Dictionary <string, Dictionary <string, object> >();
for (int i = contentBeginIndex; i < rowCount; i++)//行
{
//准备一个字典存储每一行的数据
Dictionary <string, object> row = new Dictionary <string, object>();
string MainKey = "";
for (int j = 0; j < colCount; j++)//列
{
string key = mSheet.Rows[titleIndex][j].ToString();
if (key == "")
{
Assist.Log("下标" + "(" + titleIndex + "," + j + ")" + "不能为空");
return;
}
string strData = mSheet.Rows[typeIndex][j].ToString();
string[] strs = strData.Split('+');
string keyType = strs[0];
int isMainkey = int.Parse(strs[1]);
string linkTableName = strs[2];
string value = mSheet.Rows[i][j].ToString();
switch (keyType)
{
case "int":
row.Add(key, int.Parse(value));
break;
case "string":
row.Add(key, value);
break;
case "float":
row.Add(key, float.Parse(value));
break;
default:
break;
}
//是主键，或者是合成主键的一部分字段
if (isMainkey == 1)
{
MainKey += ("_" + value);
}
}
//添加到表数据中
// table.Add(mSheet.Rows[i][0].ToString(), row);
table.Add(MainKey.Trim('_'), row);
}
//生成Json字符串
string json = JsonConvert.SerializeObject(table, Newtonsoft.Json.Formatting.Indented);
string savePath = Path.Combine(outPath, jsonFileName);
//写入文件
using (FileStream fileStream = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, System.Text.Encoding.UTF8))
{
textWriter.Write(json);
}
}
}
}
}
Assist.Log("Gen OK");
}
private void ForGen(string inPath, string outPath)
{
Assist.ClearDirFiles(outPath);
DirectoryInfo folder = new DirectoryInfo(inPath);
FileSystemInfo[] files = folder.GetFileSystemInfos();
int length = files.Length;
for (int index = 0; index < length; index++)
{
if (files[index] is DirectoryInfo)
{
//this.ForGen(files[index].FullName);
}
else
{
if (files[index].Name.EndsWith(".xlsx"))
{
//DataSet mResultSet = Assist.ExcelToDataSet(files[index].FullName);
FileStream stream = File.Open(files[index].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet mResultSet = excelReader.AsDataSet();
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
{
return;
}
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
string tableName = mSheet.TableName;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
string csvFileName = tableName + ".txt";
string content = "";
for (int i = contentBeginIndex; i < rowCount; i++) //行
{
for (int j = 0; j < colCount; j++) //列
{
content += mSheet.Rows[i][j].ToString();
if (j != colCount - 1)
{
content += ",";
}
}
if (i != rowCount - 1)
{
content += "\n";
}
}
string savePath = Path.Combine(outPath, csvFileName);
//写入文件
using (FileStream fileStream = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, System.Text.Encoding.UTF8))
{
textWriter.Write(content.Trim());
}
}
}
}
}
Assist.Log("Gen OK");
}