예제 #1
0
        /// <summary> エクセル情報読み込み </summary>
        public static SheetData[] LoadAllSheetData(string folderPath, Settings settings)
        {
            // シート情報読み込み.

            var extension = settings.GetFileExtension();

            var dataFiles = Directory.EnumerateFiles(folderPath, "*.*", SearchOption.TopDirectoryOnly)
                            .Where(x => Path.GetExtension(x) == extension)
                            .ToArray();

            var sheets = new List <SheetData>();

            if (dataFiles.IsEmpty())
            {
                return(new SheetData[0]);
            }

            ConsoleUtility.Progress("------ LoadSheetData ------");

            foreach (var sheetFile in dataFiles)
            {
                var sheet = LoadSheetData(sheetFile, settings);

                if (sheet != null)
                {
                    ConsoleUtility.Task("- {0}", sheet.sheetName);

                    sheets.Add(sheet);
                }
            }

            return(sheets.ToArray());
        }
예제 #2
0
        private static void Exit(int exitCode, string message = "")
        {
            if (!string.IsNullOrEmpty(message))
            {
                ConsoleUtility.Error(message);
            }

            // 正常終了以外ならコンソールを閉じない.
            if (exitCode != 0)
            {
                Console.ReadLine();
            }

            Environment.Exit(exitCode);
        }
예제 #3
0
        public static void WriteAllSheetData(string excelFilePath, SheetData[] sheetData, Settings settings)
        {
            var excelFileName = Path.GetFileNameWithoutExtension(excelFilePath);

            var directory = Path.GetDirectoryName(excelFilePath);

            var dataFileDirectory = PathUtility.Combine(directory, excelFileName);

            DirectoryUtility.Clean(dataFileDirectory);

            var extension = settings.GetFileExtension();

            if (sheetData.IsEmpty())
            {
                return;
            }

            ConsoleUtility.Progress("------ WriteData ------");

            foreach (var data in sheetData)
            {
                if (string.IsNullOrEmpty(data.sheetName))
                {
                    continue;
                }

                var records = data.records;

                if (records == null || records.IsEmpty())
                {
                    continue;
                }

                // シート情報書き出し.

                if (!string.IsNullOrEmpty(data.fileName))
                {
                    var fileName = data.fileName + extension;

                    var filePath = PathUtility.Combine(dataFileDirectory, fileName);

                    FileSystem.WriteFile(filePath, data, settings.FileFormat);
                }

                ConsoleUtility.Task("- {0}", data.sheetName);
            }
        }
예제 #4
0
        /// <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());
        }
예제 #5
0
        //----- 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();
            }
        }