/// <summary> レコード情報読み込み(.xlsx) </summary>
public static SheetData[] LoadExcelData(string excelFilePath, Settings settings)
{
if (!File.Exists(excelFilePath))
{
return(null);
}
ConsoleUtility.Progress("------ LoadExcelData ------");
var sheets = new List <SheetData>();
using (var excel = new ExcelPackage(new FileInfo(excelFilePath)))
{
foreach (var worksheet in excel.Workbook.Worksheets)
{
if (worksheet.Name == settings.TemplateSheetName)
{
continue;
}
if (settings.IgnoreSheetNames.Contains(worksheet.Name))
{
continue;
}
var fileNameValue = worksheet.GetValue(Constants.FileNameAddress.Y, Constants.FileNameAddress.X);
var fileName = ExcelUtility.ConvertValue <string>(fileNameValue);
var descriptionValue = worksheet.GetValue(Constants.DescriptionAddress.Y, Constants.DescriptionAddress.X);
var description = ExcelUtility.ConvertValue <string>(descriptionValue);
var sheetData = new SheetData()
{
fileName = fileName,
description = description,
sheetName = worksheet.Name,
};
// タイトル文字列の頭が「-」の場合はデータに含めない.
var titleValues = ExcelUtility.GetRowValues(worksheet, Constants.TitleRow).ToArray();
var ignoreColumnList = new List <int>();
for (var i = 0; i < titleValues.Length; i++)
{
var title = ExcelUtility.ConvertValue <string>(titleValues, i);
if (string.IsNullOrEmpty(title))
{
continue;
}
if (title.StartsWith("-"))
{
ignoreColumnList.Add(i + 1);
}
}
var records = new List <RecordData>();
for (var r = Constants.RecordStartRow; r <= worksheet.Dimension.End.Row; r++)
{
var rowValues = ExcelUtility.GetRowValues(worksheet, r).ToArray();
// 開始.
var column = Constants.DataStartColumn - 1;
var behaviorData = new RecordData.Behavior();
if (rowValues.All(x => x == null))
{
continue;
}
// 行動データ.
var successRateValue = ExcelUtility.ConvertValue <float>(rowValues, column).ToString("F4");
behaviorData.successRate = (float)Math.Truncate(Convert.ToSingle(successRateValue) * 1000.0f) / 1000.0f;
column = GetNextColumn(column, ignoreColumnList);
behaviorData.actionType = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
behaviorData.actionParameters = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
behaviorData.targetType = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
behaviorData.targetParameters = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
var conditions = new List <RecordData.Condition>();
while (true)
{
var cellValue = ExcelUtility.ConvertValue <string>(rowValues, column);
if (cellValue == null)
{
break;
}
var condition = new RecordData.Condition();
try
{
if (conditions.Any())
{
var connecter = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
if (string.IsNullOrEmpty(connecter))
{
break;
}
connecter = connecter.Trim();
if (connecter != "|" && connecter != "&")
{
throw new InvalidDataException("connecter support | or &.");
}
condition.connecter = connecter;
}
condition.type = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
condition.parameters = ExcelUtility.ConvertValue <string>(rowValues, column);
column = GetNextColumn(column, ignoreColumnList);
}
catch (Exception e)
{
var errorMessage = string.Format("[{0},{1}] condition data error.\n{2}", r, column, e.Message);
ConsoleUtility.Error(errorMessage);
break;
}
conditions.Add(condition);
}
behaviorData.conditions = conditions.ToArray();
// セル情報.
var cells = new List <ExcelCell>();
for (var c = Constants.DataStartColumn; c < column; c++)
{
var cellData = ExcelCellUtility.Get <ExcelCell>(worksheet, r, c);
if (cellData == null)
{
continue;
}
cellData.address = string.Format("{0},{1}", r, c);
cells.Add(cellData);
}
var recordData = new RecordData()
{
behavior = behaviorData,
cells = cells.Any() ? cells.ToArray() : null,
};
records.Add(recordData);
}
sheetData.records = records.ToArray();
sheets.Add(sheetData);
ConsoleUtility.Task("- {0}", sheetData.sheetName);
}
}
return(sheets.ToArray());
}
/// <summary> レコード情報読み込み(.xlsx) </summary>
public static SheetData[] LoadSheetData(string workspace, Settings settings)
{
var excelFilePath = PathUtility.Combine(workspace, settings.EditExcelFileName);
if (!File.Exists(excelFilePath))
{
return(null);
}
ConsoleUtility.Progress("------ LoadExcelData ------");
var sheets = new List <SheetData>();
using (var excel = new ExcelPackage(new FileInfo(excelFilePath)))
{
foreach (var worksheet in excel.Workbook.Worksheets)
{
if (worksheet.Name == settings.TemplateSheetName)
{
continue;
}
if (settings.IgnoreSheetNames.Contains(worksheet.Name))
{
continue;
}
var sheetEnumNameValue = worksheet.GetValue(Constants.SheetNameAddress.Y, Constants.SheetNameAddress.X);
var sheetEnumName = ExcelUtility.ConvertValue <string>(sheetEnumNameValue);
if (string.IsNullOrEmpty(sheetEnumName))
{
continue;
}
// 既にシートデータがある場合は読み込み.
var existSheetData = LoadExistSheetData(workspace, sheetEnumName, settings);
// データが出力されていない場合は新規Guidを割り当て.
var sheetGuid = existSheetData == null?Guid.NewGuid().ToString("N") : existSheetData.guid;
var sheetData = new SheetData()
{
guid = sheetGuid,
displayName = worksheet.Name,
sheetName = sheetEnumName,
};
var records = new List <RecordData>();
for (var r = Constants.RecordStartRow; r <= worksheet.Dimension.End.Row; r++)
{
var rowValues = ExcelUtility.GetRowValues(worksheet, r).ToArray();
var enumName = ExcelUtility.ConvertValue <string>(rowValues, Constants.EnumNameColumn - 1);
if (string.IsNullOrEmpty(enumName))
{
continue;
}
var recordGuid = string.Empty;
// データが出力されていない場合は新規Guidを割り当て.
if (existSheetData != null && existSheetData.records != null)
{
var existRecordData = existSheetData.records.FirstOrDefault(x => x.enumName == enumName);
if (existRecordData != null)
{
recordGuid = existRecordData.guid;
}
}
if (string.IsNullOrEmpty(recordGuid))
{
recordGuid = Guid.NewGuid().ToString("N");
}
var description = ExcelUtility.ConvertValue <string>(rowValues, Constants.DescriptionColumn - 1);
var record = new RecordData()
{
guid = recordGuid,
enumName = enumName,
description = description,
};
// 言語タイプ数取得.
var textEndColumn = Constants.TextStartColumn;
for (var c = Constants.TextStartColumn; c < rowValues.Length; c++)
{
var textTypeValue = worksheet.GetValue(Constants.TextTypeStartRow, c);
var textTypeName = ExcelUtility.ConvertValue <string>(textTypeValue);
if (string.IsNullOrEmpty(textTypeName))
{
break;
}
textEndColumn++;
}
// テキスト取得.
var texts = new List <string>();
for (var c = Constants.TextStartColumn; c < textEndColumn; c++)
{
var text = ExcelUtility.ConvertValue <string>(rowValues, c - 1);
texts.Add(text);
}
record.texts = texts.ToArray();
// セル情報取得.
var cells = new List <ExcelCell>();
for (var c = Constants.TextStartColumn; c < textEndColumn; c++)
{
var cellData = ExcelCellUtility.Get <ExcelCell>(worksheet, r, c);
if (cellData == null)
{
continue;
}
cellData.address = string.Format("{0},{1}", r, c);
cells.Add(cellData);
}
record.cells = cells.Any() ? cells.ToArray() : null;
records.Add(record);
}
sheetData.records = records.ToArray();
sheets.Add(sheetData);
ConsoleUtility.Task("- {0}", sheetData.displayName);
}
}
return(sheets.ToArray());
}