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);
}
}
}