/// <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());
}
/// <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());
}
//----- params -----
//----- field -----
//----- property -----
//----- method -----
public static void Build(string indexFilePath, IndexData indexData, SheetData[] sheetData, Settings settings)
{
var originExcelPath = Path.GetFullPath(settings.ExcelPath);
var editExcelPath = Path.ChangeExtension(indexFilePath, Constants.ExcelExtension);
ConsoleUtility.Progress("------ Build edit excel file ------");
//------ エディット用にエクセルファイルを複製 ------
if (!File.Exists(originExcelPath))
{
throw new FileNotFoundException(string.Format("{0} is not exists.", originExcelPath));
}
var originXlsxFile = new FileInfo(originExcelPath);
var editXlsxFile = originXlsxFile.CopyTo(editExcelPath, true);
//------ レコード情報を書き込み ------
if (sheetData == null)
{
return;
}
using (var excel = new ExcelPackage(editXlsxFile))
{
var worksheets = excel.Workbook.Worksheets;
// テンプレートシート.
var templateSheet = worksheets.FirstOrDefault(x => x.Name.ToLower() == settings.TemplateSheetName);
if (templateSheet == null)
{
throw new Exception(string.Format("Template worksheet {0} not found.", settings.TemplateSheetName));
}
// シート作成.
foreach (var data in sheetData)
{
if (string.IsNullOrEmpty(data.sheetName))
{
continue;
}
if (worksheets.Any(x => x.Name == data.sheetName))
{
throw new Exception(string.Format("Worksheet create failed. Worksheet {0} already exists", data.sheetName));
}
// テンプレートシートを複製.
var newWorksheet = worksheets.Add(data.sheetName, templateSheet);
// 保護解除.
newWorksheet.Protection.IsProtected = false;
// タブ選択状態解除.
newWorksheet.View.TabSelected = false;
// セルサイズ調整.
newWorksheet.Cells.AutoFitColumns();
// エラー無視.
var excelIgnoredError = newWorksheet.IgnoredErrors.Add(newWorksheet.Dimension);
excelIgnoredError.NumberStoredAsText = true;
}
// シート順番入れ替え.
if (worksheets.Any() && indexData != null)
{
for (var i = indexData.sheetNames.Length - 1; 0 <= i; i--)
{
var sheetName = indexData.sheetNames[i];
if (worksheets.All(x => x.Name != sheetName))
{
continue;
}
worksheets.MoveToStart(sheetName);
}
}
// 先頭のシートをアクティブ化.
var firstWorksheet = worksheets.FirstOrDefault();
if (firstWorksheet != null)
{
firstWorksheet.View.TabSelected = true;
}
// レコード情報設定.
foreach (var data in sheetData)
{
var worksheet = worksheets.FirstOrDefault(x => x.Name == data.sheetName);
if (worksheet == null)
{
ConsoleUtility.Error("Worksheet:{0} not found.", data.sheetName);
continue;
}
var dimension = worksheet.Dimension;
var records = data.records;
if (records == null)
{
continue;
}
worksheet.SetValue(Constants.FileNameAddress.Y, Constants.FileNameAddress.X, data.fileName);
// レコード投入用セルを用意.
for (var i = 0; i < records.Length; i++)
{
var recordRow = Constants.RecordStartRow + i;
// 行追加.
if (worksheet.Cells.End.Row < recordRow)
{
worksheet.InsertRow(recordRow, 1);
}
// セル情報コピー.
for (var column = 1; column < dimension.End.Column; column++)
{
CloneCellFormat(worksheet, Constants.RecordStartRow, recordRow, column);
}
}
// タイトル文字列の頭が「-」の場合は値設定をスキップ.
var titleValues = ExcelUtility.GetRowValues(worksheet, Constants.TitleRow).ToArray();
var ignoreIndexList = 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("-"))
{
ignoreIndexList.Add(i + 1);
}
}
// 値設定.
for (var i = 0; i < records.Length; i++)
{
var row = Constants.RecordStartRow + i;
var column = Constants.DataStartColumn;
var record = records[i];
var successRate = (float)Math.Truncate(Convert.ToSingle(record.behavior.successRate) * 1000.0f) / 1000.0f;
worksheet.SetValue(row, column, successRate.ToString());
column = GetNextColumn(column, ignoreIndexList);
worksheet.SetValue(row, column, record.behavior.actionType);
column = GetNextColumn(column, ignoreIndexList);
worksheet.SetValue(row, column, record.behavior.actionParameters);
column = GetNextColumn(column, ignoreIndexList);
worksheet.SetValue(row, column, record.behavior.targetType);
column = GetNextColumn(column, ignoreIndexList);
worksheet.SetValue(row, column, record.behavior.targetParameters);
column = GetNextColumn(column, ignoreIndexList);
for (var j = 0; j < record.behavior.conditions.Length; j++)
{
var condition = record.behavior.conditions[j];
if (j != 0)
{
worksheet.SetValue(row, column, condition.connecter);
column = GetNextColumn(column, ignoreIndexList);
}
worksheet.SetValue(row, column, condition.type);
column = GetNextColumn(column, ignoreIndexList);
worksheet.SetValue(row, column, condition.parameters);
column = GetNextColumn(column, ignoreIndexList);
}
// セル情報.
if (record.cells != null)
{
foreach (var cellData in record.cells)
{
var address = cellData.address.Split(',');
var rowStr = address.ElementAtOrDefault(0);
var columnStr = address.ElementAtOrDefault(1);
if (string.IsNullOrEmpty(rowStr) || string.IsNullOrEmpty(columnStr))
{
continue;
}
var r = Convert.ToInt32(rowStr);
var c = Convert.ToInt32(columnStr);
ExcelCellUtility.Set(worksheet, r, c, cellData);
}
}
}
// セルサイズを調整.
var maxRow = Constants.RecordStartRow + records.Length + 1;
var celFitRange = worksheet.Cells[1, 1, maxRow, dimension.End.Column];
ExcelUtility.FitColumnSize(worksheet, celFitRange);
ExcelUtility.FitRowSize(worksheet, celFitRange);
// 説明.
if (!string.IsNullOrEmpty(data.description))
{
var r = Constants.DescriptionAddress.Y;
var c = Constants.DescriptionAddress.X;
var cell = worksheet.Cells[r, c];
cell.Style.WrapText = false;
worksheet.SetValue(r, c, data.description);
}
ConsoleUtility.Task("- {0}", data.sheetName);
}
// 保存.
excel.Save();
}
}
