ExcelUploadHepler.cs 3.4 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Diagnostics;
  4. using System.Linq;
  5. using System.Text;
  6. using System.Text.RegularExpressions;
  7. using FMSAdmin.Data;
  8. using OfficeOpenXml;
  9. namespace FMSAdmin.Helpers {
  10. public class ExcelUploadHepler {
  11. Dictionary<string, int> columns;
  12. public ExcelUploadHepler() {
  13. columns = new Dictionary<string, int>();
  14. }
  15. public void GenerateColumns(ExcelWorksheet obj, int startIndex, int columnCount) {
  16. try {
  17. for (int i = startIndex; i <= columnCount; i++) {
  18. columns.Add(obj.Cells[obj.Dimension.Start.Row, i]?.Value.ToString(), i);
  19. }
  20. } catch (System.Exception) {
  21. throw new Exception("엑셀 형식이 일치하지 않습니다.");
  22. }
  23. }
  24. public int GetColIndex(ExcelWorksheet obj, string columnName) {
  25. if (!columns.ContainsKey(columnName)) {
  26. throw new Exception($"엑셀 형식이 일치하지 않습니다.({columnName})");
  27. }
  28. var columnIndex = columns[columnName];
  29. return columnIndex;
  30. }
  31. public string GetValue(ExcelWorksheet obj, string columnName, int rowIndex, bool required = false) {
  32. var columnIndex = GetColIndex(obj, columnName);
  33. var value = obj.Cells[rowIndex, columnIndex]?.Value?.ToString();
  34. if (required && value == null) throw new Exception($"{columnName}은(는) 필수값입니다. ({rowIndex - 1}번 행)");
  35. return value;
  36. }
  37. public bool IsRowEmpty(ExcelWorksheet obj, int rowIndex, int columnCount) {
  38. for (int i = 1; i <= columnCount; i++) {
  39. if (obj.Cells[rowIndex, i].Value != null) {
  40. return false;
  41. }
  42. }
  43. return true;
  44. }
  45. public void NumericCheck(string value, string columnName, string rowName) {
  46. if (!Util.IsNullOrEmpty(value) && !int.TryParse(value, out int n))
  47. throw new Exception($"{columnName}은(는) 숫자만 입력 가능합니다. {rowName}");
  48. }
  49. public void FloatCheck(string value, string columnName, string rowName) {
  50. if (!Util.IsNullOrEmpty(value) && !float.TryParse(value, out float n))
  51. throw new Exception($"{columnName}은(는) 숫자(또는 소수)만 입력 가능합니다. {rowName}");
  52. }
  53. public string DateTimeCheck(string value, string columnName, string rowName) {
  54. if (!Util.IsNullOrEmpty(value)) {
  55. if (value.Length != 8) throw new Exception($"{columnName}을(를) 확인해주세요.[20200101] {rowName}");
  56. value = value.Substring(0, 4) + "/" + value.Substring(4, 2) + "/" + value.Substring(6, 2);
  57. if (!DateTime.TryParse(value, out DateTime d))
  58. throw new Exception($"{columnName}을(를) 확인해주세요.[20200101] {rowName}");
  59. }
  60. return value;
  61. }
  62. public void EntityIdCheck(int? value, string columnName, string rowName) {
  63. if (value == null || value == 0) throw new Exception($"{columnName}을(를) 확인해주세요. {rowName}");
  64. }
  65. public void EntityIdCheck(string value, string columnName, string rowName) {
  66. if (Util.IsNullOrEmpty(value)) throw new Exception($"{columnName}을(를) 확인해주세요. {rowName}");
  67. }
  68. }
  69. }