private static byte[] GetStringBytes(WDBSheet sheet, out Dictionary <string, int> strOffsetDic)
{
strOffsetDic = new Dictionary <string, int>();
strOffsetDic.Add("", 0);
MemoryStream stream = new MemoryStream();
ByteWriter.WriteString(stream, "");
for (int i = 0; i < sheet.LineCount; ++i)
{
WDBLine line = sheet.GetLineAtIndex(i);
for (int j = 0; j < sheet.FieldCount; ++j)
{
WDBField field = sheet.GetFieldAtIndex(j);
if (field.FieldType == WDBFieldType.String || field.FieldType == WDBFieldType.Address ||
field.FieldType == WDBFieldType.Lua)
{
WDBCell cell = line.GetCellByCol(field.Col);
string cellValue = cell.GetValue(field);
string content = cellValue ?? "";
if (field.FieldType == WDBFieldType.Lua)
{
content = GetLuaFuncContent(content);
}
if (strOffsetDic.ContainsKey(content))
{
continue;
}
strOffsetDic.Add(content, (int)stream.Length);
ByteWriter.WriteString(stream, content);
}
}
}
return(stream.ToArray());
}
public static byte[] WriteToNDB(WDBSheet sheet)
{
NDBHeader header = new NDBHeader();
header.fieldCount = sheet.FieldCount;
header.lineCount = sheet.LineCount;
int structSize = MarshalUtility.GetStructSize(typeof(NDBHeader));
header.fieldOffset = structSize;
byte[] fieldBytes = GetFieldBytes(sheet, out var lineSize);
header.lineSize = lineSize;
header.lineOffset = structSize + fieldBytes.Length;
byte[] strBytes = GetStringBytes(sheet, out var strOffsetDic);
byte[] lineBytes = GetLineBytes(sheet, strOffsetDic);
header.stringOffset = structSize + fieldBytes.Length + lineBytes.Length;
MemoryStream stream = new MemoryStream();
byte[] headerBytes = MarshalUtility.StructToByte(header, structSize);
stream.Write(headerBytes, 0, headerBytes.Length);
stream.Write(fieldBytes, 0, fieldBytes.Length);
stream.Write(lineBytes, 0, lineBytes.Length);
stream.Write(strBytes, 0, strBytes.Length);
return(stream.ToArray());;
}
private static void ReadLineFromSheet(WDBSheet sheetData, ISheet sheet)
{
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_START_READ_LINE));
int firstRowNum = readerExcelStyle.RowStartIndex + readerExcelStyle.FieldRowCount;
int lastRowNum = sheet.LastRowNum;
int firstColNum = sheet.GetRow(readerExcelStyle.RowStartIndex).FirstCellNum;
int lastColNum = sheet.GetRow(readerExcelStyle.RowStartIndex).LastCellNum;
bool isStart = false;
for (int r = firstRowNum; r < lastRowNum; ++r)
{
IRow row = sheet.GetRow(r);
if (row == null)
{
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_LINE_EMPTY, r));
continue;
}
string cellValue = GetCellStringValue(row.GetCell(firstColNum));
if (string.IsNullOrEmpty(cellValue))
{
if (!isStart)
{
continue;
}
}
else
{
if (!isStart && cellValue == readerExcelStyle.LineStartFlag)
{
isStart = true;
}
else if (isStart && cellValue == readerExcelStyle.LineEndFlag)
{
isStart = false;
break;
}
}
WDBLine line = sheetData.AddLine(r);
for (int c = firstColNum + 1; c < lastColNum; c++)
{
WDBField field = sheetData.GetFieldAtCol(c);
if (field == null)
{
continue;
}
ICell valueCell = row.GetCell(c);
line.AddCell(c, GetCellStringValue(valueCell));
}
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_CREATE_LINE, line));
}
logHandler?.Invoke(LogType.Info, LogMessage.INFO_END_READ_LINE);
}
public static void WriteToNDBFile(string outputDirPath, WDBSheet sheet)
{
byte[] dataBytes = WriteToNDB(sheet);
if (dataBytes != null && dataBytes.Length > 0)
{
string filePath = $"{outputDirPath}/{sheet.Name}{NDBConst.NDB_FILE_EXTERSION}";
File.WriteAllBytes(filePath, dataBytes);
}
}
private static string WriteToLua(WDBSheet sheet, string templateContent, string[] assemblyNames, EntryConfig config)
{
StringContextContainer context = new StringContextContainer();
context.Add("__sheet__", sheet);
string content = TemplateEngine.Generate(context, templateContent, assemblyNames, config);
context.Remove("__sheet__");
return(content);
}
private static WDBSheet ReadFromSheet(ISheet sheet)
{
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_START_READ_SHEET, sheet.SheetName));
IRow firstRow = sheet.GetRow(readerExcelStyle.RowStartIndex);
if (firstRow == null)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_ROW_START_EMTPY, readerExcelStyle.RowStartIndex));
return(null);
}
ICell firstCell = firstRow.GetCell(readerExcelStyle.ColumnStartIndex);
if (firstCell == null)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_COLUMN_START_EMPTY, readerExcelStyle.ColumnStartIndex));
return(null);
}
string flagContent = GetCellStringValue(firstCell);
if (string.IsNullOrEmpty(flagContent) || flagContent != readerExcelStyle.MarkFlag)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_MRAK_FLAG, readerExcelStyle.MarkFlag));
return(null);
}
int firstRowNum = readerExcelStyle.RowStartIndex;
int lastRowNum = sheet.LastRowNum;
int firstColNum = sheet.GetRow(firstRowNum).FirstCellNum;
int lastColNum = sheet.GetRow(firstRowNum).LastCellNum;
int rowCount = lastRowNum - firstRowNum + 1;
int colCount = lastColNum - firstColNum + 1;
if (rowCount < readerExcelStyle.FieldRowCount)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_ROW_LESS, rowCount, readerExcelStyle.FieldRowCount));
return(null);
}
if (colCount < readerExcelStyle.ColumnMinCount)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_COL_LESS, colCount, readerExcelStyle.ColumnMinCount));
return(null);
}
WDBSheet sheetData = new WDBSheet(sheet.SheetName);
ReadFieldFromSheet(sheetData, sheet);
ReadLineFromSheet(sheetData, sheet);
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_END_READ_SHEET, sheet.SheetName));
return(sheetData);
}
public static void WriteToLuaFile(WDBSheet sheet, string filePath, string templateContent, EntryConfig config = null)
{
string content = WriteToLua(sheet, templateContent, config);
if (string.IsNullOrEmpty(content))
{
throw new Exception("");
}
File.WriteAllText(filePath, content);
}
private static byte[] GetFieldBytes(WDBSheet sheet, out int lineSize)
{
NDBField[] fields = GetFields(sheet);
lineSize = GetLineSize(fields);
MemoryStream stream = new MemoryStream();
foreach (var field in fields)
{
NDBByteUtility.WriteField(stream, field);
}
return(stream.ToArray());
}
public static string WriteToLua(WDBSheet sheet, TargetPlatformType platformType)
{
if (sheet.FieldCount == 0 || sheet.RowCount == 0)
{
return(string.Empty);
}
WDBFieldPlatform platform = platformType == TargetPlatformType.Client ? WDBFieldPlatform.Client : WDBFieldPlatform.Server;
StringBuilder builder = new StringBuilder();
int indent = 0;
builder.AppendLine($"local {sheet.Name} = {{");
for (int r = 0; r < sheet.RowCount; r++)
{
WDBRow row = sheet.GetRowAtIndex(r);
WDBField keyField = sheet.GetFieldAtIndex(0);
WDBCell keyCell = row.GetCellByIndex(0);
indent++;
string keyStr = keyCell.GetContent(keyField);
builder.AppendLine($"{GetIndent(indent)}[{keyStr}] = {{");
for (int f = 0; f < sheet.FieldCount; f++)
{
WDBField field = sheet.GetFieldAtIndex(f);
if (string.IsNullOrEmpty(field.Name))
{
continue;
}
if (field.FieldPlatform != WDBFieldPlatform.All && field.FieldPlatform != platform)
{
continue;
}
indent++;
WDBCell cell = row.GetCellByIndex(f);
AppendValueLine(builder, indent, field, cell);
indent--;
}
builder.AppendLine($"{GetIndent(indent)}}},");
indent--;
}
builder.AppendLine("}");
builder.AppendLine($"return {sheet.Name}");
return(builder.ToString());
}
private static NDBField[] GetFields(WDBSheet sheet)
{
List <NDBField> fields = new List <NDBField>();
for (int i = 0; i < sheet.FieldCount; ++i)
{
WDBField wdbField = sheet.GetFieldAtIndex(i);
NDBField ndbField = new NDBField()
{
FieldType = GetFieldType(wdbField.FieldType),
Name = wdbField.Name,
};
fields.Add(ndbField);
}
return(fields.ToArray());
}
public static string WriteToJson(WDBSheet sheet, TargetPlatformType platformType)
{
if (sheet.FieldCount == 0 || sheet.RowCount == 0)
{
return(string.Empty);
}
WDBFieldPlatform platform = platformType == TargetPlatformType.Client ? WDBFieldPlatform.Client : WDBFieldPlatform.Server;
JObject sheetObject = new JObject();
for (int r = 0; r < sheet.RowCount; r++)
{
WDBRow row = sheet.GetRowAtIndex(r);
JObject rowObject = new JObject();
for (int f = 0; f < sheet.FieldCount; f++)
{
WDBField field = sheet.GetFieldAtIndex(f);
if (string.IsNullOrEmpty(field.Name))
{
continue;
}
if (field.FieldPlatform != WDBFieldPlatform.All && field.FieldPlatform != platform)
{
continue;
}
object value = GetValue(field, row.GetCellByIndex(f));
rowObject.Add(field.Name, JToken.FromObject(value));
if (f == 0)
{
sheetObject.Add(value.ToString(), rowObject);
}
}
}
return(sheetObject.ToString(Formatting.Indented));
}
private static void ReadFieldFromSheet(WDBSheet sheetData, ISheet sheet)
{
logHandler?.Invoke(LogType.Info, LogMessage.INFO_START_READ_FIELD);
MethodInfo createFieldMI = typeof(WDBUtility).GetMethod("CreateField", BindingFlags.Public | BindingFlags.Static);
int firstRowNum = readerExcelStyle.RowStartIndex;
int lastRowNum = firstRowNum + readerExcelStyle.FieldRowCount;
int firstColNum = sheet.GetRow(firstRowNum).FirstCellNum;
int lastColNum = sheet.GetRow(firstRowNum).LastCellNum;
for (int c = firstColNum + 1; c < lastColNum; ++c)
{
object[] datas = new object[readerExcelStyle.FieldRowCount + 1];
datas[0] = c;
for (int r = firstRowNum; r < lastRowNum; ++r)
{
IRow row = sheet.GetRow(r);
string cellValue = null;
if (row != null)
{
cellValue = GetCellStringValue(row.GetCell(c));
}
datas[r - firstRowNum + 1] = cellValue;
}
WDBField field = (WDBField)createFieldMI.Invoke(null, datas);
WDBFieldPlatform fieldPlatform = field.FieldPlatform;
if (fieldPlatform != WDBFieldPlatform.All && readerExcelStyle.TargetPlatform != WDBFieldPlatform.All && fieldPlatform != readerExcelStyle.TargetPlatform)
{
continue;
}
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_CREATE_FIELD, field));
sheetData.AddField(field);
}
logHandler?.Invoke(LogType.Info, LogMessage.INFO_END_READ_FIELD);
}
private static WDBSheet ReadFromSheet(ISheet sheet)
{
sm_LogHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_START_READ_SHEET, sheet.SheetName));
int firstRowNum = sheet.FirstRowNum;
int lastRowNum = sheet.LastRowNum;
while (true)
{
if (lastRowNum - firstRowNum + 1 <= WDBConst.MinRowCount)
{
break;
}
IRow row = sheet.GetRow(firstRowNum);
int firstColumnNum = row.FirstCellNum;
int lastColumnNum = row.LastCellNum;
if (lastColumnNum - firstColumnNum + 1 <= WDBConst.MinColumnCount)
{
firstRowNum++;
continue;
}
for (int c = firstColumnNum; c <= lastColumnNum; c++)
{
ICell cell = row.GetCell(c);
string cellValue = GetCellValue(cell);
if (string.IsNullOrEmpty(cellValue) || cellValue != sm_Style.MarkFlag)
{
continue;
}
else
{
sm_Style.RowStartIndex = firstRowNum;
sm_Style.RowEndIndex = lastRowNum;
sm_Style.ColumnStartIndex = c;
for (int rc = lastColumnNum; rc >= c; rc--)
{
ICell rCell = row.GetCell(rc);
string rCellValue = GetCellValue(rCell);
if (string.IsNullOrEmpty(rCellValue))
{
continue;
}
sm_Style.ColumnEndIndex = rc;
break;
}
break;
}
}
firstRowNum++;
}
if (sm_Style.RowCount <= WDBConst.MinRowCount)
{
sm_LogHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_ROW_LESS, lastRowNum - firstRowNum + 1, WDBConst.MinRowCount));
return(null);
}
if (sm_Style.ColumnCount <= WDBConst.MinColumnCount)
{
sm_LogHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_SHEET_COL_LESS, sm_Style.ColumnCount, WDBConst.MinColumnCount));
return(null);
}
WDBField[] fields = ReadFieldFromSheet(sheet);
if (fields == null || fields.Length == 0)
{
return(null);
}
WDBRow[] rows = ReadRowFromSheet(sheet, fields);
if (rows == null || rows.Length == 0)
{
return(null);
}
WDBSheet sheetData = new WDBSheet(sheet.SheetName);
sheetData.AddFields(fields);
sheetData.AddRows(rows);
sm_LogHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_END_READ_SHEET, sheet.SheetName));
return(sheetData);
}
public static string WriteToLua(WDBSheet sheet, string templateContent, EntryConfig config = null)
{
string[] assemblyNames = new string[] { typeof(WDBSheet).Assembly.Location };
return(WriteToLua(sheet, templateContent, assemblyNames, config ?? EntryConfig.Default));
}
private static byte[] GetLineBytes(WDBSheet sheet, Dictionary <string, int> strOffsetDic)
{
MemoryStream stream = new MemoryStream();
for (int i = 0; i < sheet.LineCount; ++i)
{
WDBLine line = sheet.GetLineAtIndex(i);
for (int j = 0; j < sheet.FieldCount; ++j)
{
WDBField field = sheet.GetFieldAtIndex(j);
WDBCell cell = line.GetCellByCol(field.Col);
string cellValue = cell.GetValue(field);
if (field.FieldType == WDBFieldType.String || field.FieldType == WDBFieldType.Address ||
field.FieldType == WDBFieldType.Lua)
{
string content = cellValue ?? "";
if (field.FieldType == WDBFieldType.Lua)
{
content = GetLuaFuncContent(content);
}
if (!strOffsetDic.TryGetValue(content, out var offset))
{
throw new Exception();
}
ByteWriter.WriteInt(stream, offset);
}
else if (field.FieldType == WDBFieldType.Bool)
{
if (string.IsNullOrEmpty(cellValue) || !bool.TryParse(cellValue, out var result))
{
ByteWriter.WriteBool(stream, false);
}
else
{
ByteWriter.WriteBool(stream, result);
}
}
else if (field.FieldType == WDBFieldType.Int || field.FieldType == WDBFieldType.Text)
{
if (string.IsNullOrEmpty(cellValue) || !int.TryParse(cellValue, out var result))
{
ByteWriter.WriteInt(stream, 0);
}
else
{
ByteWriter.WriteInt(stream, result);
}
}
else if (field.FieldType == WDBFieldType.Id || field.FieldType == WDBFieldType.Ref)
{
if (string.IsNullOrEmpty(cellValue) || !int.TryParse(cellValue, out var result))
{
ByteWriter.WriteInt(stream, -1);
}
else
{
ByteWriter.WriteInt(stream, result);
}
}
else if (field.FieldType == WDBFieldType.Long)
{
if (string.IsNullOrEmpty(cellValue) || !long.TryParse(cellValue, out var result))
{
ByteWriter.WriteLong(stream, 0);
}
else
{
ByteWriter.WriteLong(stream, result);
}
}
else if (field.FieldType == WDBFieldType.Float)
{
if (string.IsNullOrEmpty(cellValue) || !float.TryParse(cellValue, out var result))
{
ByteWriter.WriteFloat(stream, 0);
}
else
{
ByteWriter.WriteFloat(stream, result);
}
}
else
{
throw new Exception();
}
}
}
return(stream.ToArray());
}
public static WDBSheet[] ReadFromFile(string excelPath, WDBExcelStyle readerStyle = null)
{
readerExcelStyle = readerStyle;
if (readerExcelStyle == null)
{
readerExcelStyle = WDBExcelStyle.DefaultStyle;
}
if (!IsExcel(excelPath))
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_FILE_NOT_EXCEL, excelPath));
return(null);
}
List <WDBSheet> sheetList = new List <WDBSheet>();
string ext = Path.GetExtension(excelPath).ToLower();
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
IWorkbook workbook = null;
if (ext == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else
{
workbook = new HSSFWorkbook(fs);
}
if (workbook == null || workbook.NumberOfSheets == 0)
{
logHandler?.Invoke(LogType.Error, string.Format(LogMessage.ERROR_WORKBOOK_EMPTY, excelPath));
return(null);
}
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_START_READ_WORKBOOK, excelPath));
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
string sheetName = sheet.SheetName;
if (string.IsNullOrEmpty(sheetName))
{
logHandler?.Invoke(LogType.Warning, LogMessage.WARN_SHEET_NAME_EMPTY);
continue;
}
if (sheetName.StartsWith("#"))
{
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_SHEET_IGNORE, sheetName));
continue;
}
WDBSheet wdbSheet = ReadFromSheet(sheet);
sheetList.Add(wdbSheet);
}
logHandler?.Invoke(LogType.Info, string.Format(LogMessage.INFO_END_READ_WORKBOOK, excelPath));
}
}
catch (Exception e)
{
logHandler?.Invoke(LogType.Error, e.Message);
}
readerExcelStyle = null;
return(sheetList.ToArray());
}
private static bool VerifySheet(WDBSheet sheet)
{
List <string> errors = (List <string>)context.Get(WDBContextIENames.CONTEXT_ERRORS_NAME);
if (string.IsNullOrEmpty(sheet.Name))
{
errors.Add(WDBVerifyConst.VERIFY_SHEET_NAME_EMPTY_ERR);
return(false);
}
if (!Regex.IsMatch(sheet.Name, WDBVerifyConst.VERIFY_SHEET_NAME_REGEX))
{
errors.Add(string.Format(WDBVerifyConst.VERIFY_SHEET_NAME_REGEX_ERR, sheet.Name));
return(false);
}
if (sheet.FieldCount == 0)
{
errors.Add(string.Format(WDBVerifyConst.VERIFY_SHEET_NO_FIELD_ERR, sheet.Name));
return(false);
}
if (sheet.LineCount == 0)
{
errors.Add(string.Format(WDBVerifyConst.VERIFY_SHEET_NO_ROW_ERR, sheet.Name));
return(false);
}
bool result = true;
for (int i = 0; i < sheet.FieldCount; ++i)
{
WDBField field = sheet.GetFieldAtIndex(i);
WDBFieldVerify fieldVerify = new WDBFieldVerify()
{
FieldType = field.FieldType,
FieldPlatform = field.FieldPlatform,
ValueValidations = field.ValueValidations,
};
fieldVerifyDic.Add(field, fieldVerify);
if (!VerifyField(field))
{
result = false;
}
}
if (!result)
{
return(false);
}
context.Add(WDBContextIENames.CONTEXT_SHEET_NAME, sheet);
for (int i = 0; i < sheet.LineCount; ++i)
{
WDBLine line = sheet.GetLineAtIndex(i);
if (line.CellCount != sheet.FieldCount)
{
result = false;
errors.Add(string.Format(WDBVerifyConst.VERIFY_SHEET_FIELD_ROW_ERR, sheet.FieldCount, line.Row));
}
}
if (result)
{
for (int i = 0; i < sheet.FieldCount; ++i)
{
WDBField field = sheet.GetFieldAtIndex(i);
WDBFieldVerify fieldVerify = fieldVerifyDic[field];
context.Add(WDBContextIENames.CONTEXT_FIELD_NAME, field);
context.Add(WDBContextIENames.CONTEXT_FIELD_VERIFY_NAME, fieldVerify);
for (int j = 0; j < sheet.LineCount; ++j)
{
WDBLine line = sheet.GetLineAtIndex(j);
WDBCell cell = line.GetCellByIndex(i);
context.Add(WDBContextIENames.CONTEXT_LINE_NAME, line);
if (cell.Col != field.Col)
{
result = false;
errors.Add(string.Format(WDBVerifyConst.VERIFY_CELL_COL_NOTSAME_ERR, cell.Row, cell.Col));
}
else if (fieldVerify.ValueValidations != null && fieldVerify.ValueValidations.Length > 0)
{
context.Add(WDBContextIENames.CONTEXT_CELL_NAME, cell);
foreach (var cellValidation in fieldVerify.ValueValidations)
{
context.InjectTo(cellValidation);
if (!cellValidation.Verify())
{
result = false;
}
}
context.Remove(WDBContextIENames.CONTEXT_CELL_NAME);
}
context.Remove(WDBContextIENames.CONTEXT_LINE_NAME);
}
context.Remove(WDBContextIENames.CONTEXT_FIELD_NAME);
context.Remove(WDBContextIENames.CONTEXT_FIELD_VERIFY_NAME);
}
}
context.Remove(WDBContextIENames.CONTEXT_SHEET_NAME);
return(result);
}
