public CSDefineGenerator(string excelName, ExcelLoader excel, string excludePrefix) { //-- 创建代码字符串 StringBuilder sb = new StringBuilder(); sb.AppendLine("//"); sb.AppendLine("// Auto Generated Code By excel2json"); sb.AppendLine("// https://neil3d.gitee.io/coding/excel2json.html"); sb.AppendLine("// 1. 每个 Sheet 形成一个 Struct 定义, Sheet 的名称作为 Struct 的名称"); sb.AppendLine("// 2. 表格约定:第一行是变量名称,第二行是变量类型"); sb.AppendLine(); sb.AppendFormat("// Generate From {0}.xlsx", excelName); sb.AppendLine(); sb.AppendLine(); for (int i = 0; i < excel.Sheets.Count; i++) { DataTable sheet = excel.Sheets[i]; sb.Append(_exportSheet(sheet, excludePrefix)); } sb.AppendLine(); sb.AppendLine("// End of Auto Generated Code"); mCode = sb.ToString(); }
/// <summary> /// 根据命令行参数,执行Excel数据导出工作 /// </summary> /// <param name="options">命令行参数</param> private static void Run(Options options) { //-- Excel File string excelPath = options.ExcelPath; string excelName = Path.GetFileNameWithoutExtension(options.ExcelPath); //-- Header int header = options.HeaderRows; //-- Encoding Encoding cd = new UTF8Encoding(false); if (options.Encoding != "utf8-nobom") { foreach (EncodingInfo ei in Encoding.GetEncodings()) { Encoding e = ei.GetEncoding(); if (e.HeaderName == options.Encoding) { cd = e; break; } } } //-- Date Format string dateFormat = options.DateFormat; //-- Export path string exportPath; if (options.JsonPath != null && options.JsonPath.Length > 0) { exportPath = options.JsonPath; } else { exportPath = Path.ChangeExtension(excelPath, ".json"); } //-- Load Excel ExcelLoader excel = new ExcelLoader(excelPath, header); //-- export JsonExporter exporter = new JsonExporter(excel, options.Lowcase, options.ExportArray, dateFormat, options.ForceSheetName, header, options.ExcludePrefix, options.CellJson); exporter.SaveToFile(exportPath, cd); //-- 生成C#定义文件 if (options.CSharpPath != null && options.CSharpPath.Length > 0) { CSDefineGenerator generator = new CSDefineGenerator(excelName, excel, options.ExcludePrefix); generator.SaveToFile(options.CSharpPath, cd); } }
/// <summary> /// 构造函数:完成内部数据创建 /// </summary> /// <param name="excel">ExcelLoader Object</param> public JsonExporter(ExcelLoader excel, bool lowcase, bool exportArray, string dateFormat, bool forceSheetName, int headerRows, string excludePrefix, bool cellJson) { mHeaderRows = 4 - 1; List <DataTable> validSheets = new List <DataTable>(); for (int i = 0; i < excel.Sheets.Count; i++) { DataTable sheet = excel.Sheets[i]; // 过滤掉包含特定前缀的表单 string sheetName = sheet.TableName; if (excludePrefix.Length > 0 && sheetName.StartsWith(excludePrefix)) { continue; } if (sheet.Columns.Count > 0 && sheet.Rows.Count > 0 && sheet.Rows[1][0].ToString() == "tid") { validSheets.Add(sheet); } } jsonSettings = new JsonSerializerSettings { DateFormatString = dateFormat, Formatting = Formatting.Indented }; if (!forceSheetName && validSheets.Count == 1 && false) { // single sheet //-- convert to object object sheetValue = convertSheet(validSheets[0], exportArray, lowcase, excludePrefix, cellJson); //-- convert to json string mContext = JsonConvert.SerializeObject(sheetValue, jsonSettings); } else { // mutiple sheet foreach (var sheet in validSheets) { Dictionary <string, object> data = new Dictionary <string, object>(); object sheetValue = convertSheet(sheet, exportArray, lowcase, excludePrefix, cellJson); data.Add("data", sheetValue); mData.Add(sheet.TableName, data); } //-- convert to json string mContext = JsonConvert.SerializeObject(mData, jsonSettings); } }
/// <summary> /// 构造函数:完成内部数据创建 /// </summary> /// <param name="excel">ExcelLoader Object</param> public JsonExporter(ExcelLoader excel, bool lowcase, bool exportArray, string dateFormat, int headerRow) { List <DataTable> validSheets = new List <DataTable>(); headerIndex = headerRow; for (int i = 0; i < excel.Sheets.Count; i++) { DataTable sheet = excel.Sheets[i]; if (sheet.Columns.Count > 0 && sheet.Rows.Count > 0) { validSheets.Add(sheet); } } var jsonSettings = new JsonSerializerSettings { DateFormatString = dateFormat, Formatting = Formatting.Indented }; if (validSheets.Count == 1) // single sheet { rows = validSheets[0].Rows[0]; //-- convert to object object sheetValue = convertSheet(validSheets[0], exportArray, lowcase); //-- convert to json string mContext = JsonConvert.SerializeObject(sheetValue, jsonSettings); } else // mutiple sheet { Dictionary <string, object> data = new Dictionary <string, object>(); for (int i = 0; i < validSheets.Count; i++) { rows = validSheets[i].Rows[0]; object sheetValue = convertSheet(validSheets[i], exportArray, lowcase); data.Add(validSheets[i].TableName, sheetValue); } //foreach (var sheet in validSheets) { // object sheetValue = convertSheet(sheet, exportArray, lowcase); // data.Add(sheet.TableName, sheetValue); //} //-- convert to json string mContext = JsonConvert.SerializeObject(data, jsonSettings); } }
public CSDefineGenerator(string excelName, ExcelLoader excel, string excludePrefix) { //-- 创建代码字符串 StringBuilder sb = new StringBuilder(); //文件头注释 sb.AppendLine("//"); sb.AppendLine("// Auto Generated Code By excel2json"); sb.AppendLine("// https://neil3d.gitee.io/coding/excel2json.html"); sb.AppendLine("// 1. 每个 Sheet 形成一个 Struct 定义, Sheet 的名称作为 Struct 的名称"); sb.AppendLine("// 2. 表格约定:第一行是变量名称,第二行是变量类型"); sb.AppendLine(); sb.AppendFormat("// Generate From {0}.xlsx", excelName); sb.AppendLine(); sb.AppendLine(); //添加using sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine(); sb.AppendLine(); //添加namespace sb.AppendLine("namespace FZYX.GameWorld.Res\r\n{"); string tabPrex = "\t"; for (int i = 0; i < excel.Sheets.Count; i++) { DataTable sheet = excel.Sheets[i]; sb.Append(_exportSheet(sheet, excludePrefix, tabPrex)); } string className = UpperFirstChar(excelName); sb.Append(_exportTableClass(excel.Sheets, className, excludePrefix, tabPrex)); //end of namespace sb.Append('}'); sb.AppendLine(); sb.AppendLine(); sb.AppendLine("// End of Auto Generated Code"); mCode = sb.ToString(); }
/// <summary> /// 构造函数:完成内部数据创建 /// </summary> /// <param name="excel">ExcelLoader Object</param> public JsonExporter(ExcelLoader excel, bool lowcase, bool exportArray, string dateFormat) { List <DataTable> validSheets = new List <DataTable>(); for (int i = 0; i < excel.Sheets.Count; i++) { DataTable sheet = excel.Sheets[i]; if (sheet.Columns.Count > 0 && sheet.Rows.Count > 0) { validSheets.Add(sheet); } } var jsonSettings = new JsonSerializerSettings { DateFormatString = dateFormat, Formatting = Formatting.Indented }; // 当Excel只有一个sheet的时候 if (validSheets.Count == 1) // single sheet //-- convert to object { object sheetValue = convertSheet(validSheets[0], exportArray, lowcase); if (exportArray) { // 遍历所有从Excel读取的结果: foreach (Dictionary <string, object> item in (List <object>)sheetValue) { foreach (object ctn in item.Values) { // 遍历该表内所有的时间 if (ctn is DateTime) { DateTime check_datetime = (DateTime)ctn; Console.WriteLine("--" + ctn.ToString()); // 遍历到的日期是否已经检查 Predicate <DateTime> FindNotSameTime = p => p.Date != check_datetime.Date; List <DateTime> ret = AlreadyCountDate.FindAll(FindNotSameTime); // 若果在已检查列表中未找到对应的日期,则证明是一条新记录 if (ret.Count == 0) { OneDayData oneday_check = new OneDayData(); foreach (object samedaytemp in item.Values) { if (samedaytemp is DateTime) { DateTime sameday = (DateTime)samedaytemp; if (sameday.Date == check_datetime.Date) { if (sameday.Hour <= 12) { oneday_check.GoWorkTime = sameday; } else if (sameday.Hour >= 15) { oneday_check.GoHomeTime = sameday; } } } } } AlreadyCountDate.Add(check_datetime); } } } } //-- convert to json string mContext = JsonConvert.SerializeObject(sheetValue, jsonSettings); } else // mutiple sheet { Dictionary <string, object> data = new Dictionary <string, object>(); foreach (var sheet in validSheets) { object sheetValue = convertSheet(sheet, exportArray, lowcase); data.Add(sheet.TableName, sheetValue); } //-- convert to json string mContext = JsonConvert.SerializeObject(data, jsonSettings); } }
/// <summary> /// 根据命令行参数,执行Excel数据导出工作 /// </summary> /// <param name="options">命令行参数</param> private static void Run(Options options) { //-- Excel File string excelPath = options.excelPath; string excelName = Path.GetFileNameWithoutExtension(options.excelPath); var excelFileInfo = new FileInfo(excelPath); if (!excelFileInfo.Exists) { throw new Exception("Excel文件不存在"); } HashSet <string> exportTags = new HashSet <string>(); exportTags.Add(""); if (options.exportTags != null) { var tags = options.exportTags.Split(','); foreach (var tag in tags) { exportTags.Add(tag); } } //-- Encoding Encoding cd = new UTF8Encoding(false); //-- Load Excel ExcelLoader excel = new ExcelLoader(excelPath); List <ExcelParser.TableInfo> tableInfos = null; //-- export json if (!string.IsNullOrEmpty(options.jsonDir)) { var finalPath = Path.Combine(options.jsonDir, NameFormater.FormatFileName(excelName) + ".json"); var fileInfo = new FileInfo(finalPath); if (!fileInfo.Exists || fileInfo.LastWriteTimeUtc.Ticks < excelFileInfo.LastWriteTimeUtc.Ticks) { if (tableInfos == null) { tableInfos = ExcelParser.ReadSheetData(excel.Sheets[0]); } JsonExporter jsonExporter = new JsonExporter(tableInfos, excelName, exportTags); jsonExporter.SaveToFile(finalPath, cd); } } //-- export xml if (!string.IsNullOrEmpty(options.xmlDir)) { var finalPath = Path.Combine(options.xmlDir, NameFormater.FormatCamelName(excelName, false) + ".xml"); var fileInfo = new FileInfo(finalPath); if (!fileInfo.Exists || fileInfo.LastWriteTimeUtc.Ticks < excelFileInfo.LastWriteTimeUtc.Ticks) { if (tableInfos == null) { tableInfos = ExcelParser.ReadSheetData(excel.Sheets[0]); } XmlExporter xmlExporter = new XmlExporter(tableInfos, excelName, exportTags); xmlExporter.SaveToFile(finalPath, cd); } } //-- export c# if (!string.IsNullOrEmpty(options.csharpDir)) { var finalPath = Path.Combine(options.csharpDir, NameFormater.FormatCamelName(excelName, false) + ".cs"); var fileInfo = new FileInfo(finalPath); if (!fileInfo.Exists || fileInfo.LastWriteTimeUtc.Ticks < excelFileInfo.LastWriteTimeUtc.Ticks) { if (tableInfos == null) { tableInfos = ExcelParser.ReadSheetData(excel.Sheets[0]); } CSharpExporter csharpExporter = new CSharpExporter(tableInfos, excelName, exportTags); csharpExporter.SaveToFile(finalPath, cd); } } }