using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Text.RegularExpressions; using FMSAdmin.Data; using OfficeOpenXml; namespace FMSAdmin.Helpers { public class ExcelUploadHepler { Dictionary columns; public ExcelUploadHepler() { columns = new Dictionary(); } public void GenerateColumns(ExcelWorksheet obj, int startIndex, int columnCount) { try { for (int i = startIndex; i <= columnCount; i++) { columns.Add(obj.Cells[obj.Dimension.Start.Row, i]?.Value.ToString(), i); } } catch (System.Exception) { throw new Exception("엑셀 형식이 일치하지 않습니다."); } } public int GetColIndex(ExcelWorksheet obj, string columnName) { if (!columns.ContainsKey(columnName)) { throw new Exception($"엑셀 형식이 일치하지 않습니다.({columnName})"); } var columnIndex = columns[columnName]; return columnIndex; } public string GetValue(ExcelWorksheet obj, string columnName, int rowIndex, bool required = false) { var columnIndex = GetColIndex(obj, columnName); var value = obj.Cells[rowIndex, columnIndex]?.Value?.ToString(); if (required && value == null) throw new Exception($"{columnName}은(는) 필수값입니다. ({rowIndex - 1}번 행)"); return value; } public bool IsRowEmpty(ExcelWorksheet obj, int rowIndex, int columnCount) { for (int i = 1; i <= columnCount; i++) { if (obj.Cells[rowIndex, i].Value != null) { return false; } } return true; } public void NumericCheck(string value, string columnName, string rowName) { if (!Util.IsNullOrEmpty(value) && !int.TryParse(value, out int n)) throw new Exception($"{columnName}은(는) 숫자만 입력 가능합니다. {rowName}"); } public void FloatCheck(string value, string columnName, string rowName) { if (!Util.IsNullOrEmpty(value) && !float.TryParse(value, out float n)) throw new Exception($"{columnName}은(는) 숫자(또는 소수)만 입력 가능합니다. {rowName}"); } public string DateTimeCheck(string value, string columnName, string rowName) { if (!Util.IsNullOrEmpty(value)) { if (value.Length != 8) throw new Exception($"{columnName}을(를) 확인해주세요.[20200101] {rowName}"); value = value.Substring(0, 4) + "/" + value.Substring(4, 2) + "/" + value.Substring(6, 2); if (!DateTime.TryParse(value, out DateTime d)) throw new Exception($"{columnName}을(를) 확인해주세요.[20200101] {rowName}"); } return value; } public void EntityIdCheck(int? value, string columnName, string rowName) { if (value == null || value == 0) throw new Exception($"{columnName}을(를) 확인해주세요. {rowName}"); } public void EntityIdCheck(string value, string columnName, string rowName) { if (Util.IsNullOrEmpty(value)) throw new Exception($"{columnName}을(를) 확인해주세요. {rowName}"); } } }