C# SQLite, Excel 템플릿에 맞춰 입력 후 저장하기

JungWooLee·2022년 9월 22일
0

C#

목록 보기
2/2

진행사항

  1. 유추해낸 DB 를 통하여 쿼리를 수행하여 적절한 값을 들고 오는지 확인한다
  2. 윈폼으로 엑셀 템플릿을 불러와 해당 템플릿 양식에 맞게 엑셀로 저장한다

SQL 쿼리

실무에서 피벗을 과연 사용할까..? 했었지만 😊 응 사용해! 였다

SELECT    B.EmpNm                AS '사원명' 
                    ,A.PaymDt              AS '지급년월일'
                    ,MAX(CASE WHEN xxxx = 1001 THEN A.AMT
                              ELSE 0 END)  AS '기본급'
                    ,MAX(CASE WHEN xxxx = 1002 THEN A.AMT
                              ELSE 0 END)  AS '상여'
                    ,MAX(CASE WHEN xxxx = 1003 THEN A.AMT
                              ELSE 0 END)  AS '제수당'
                    ,MAX(CASE WHEN xxxx = 1004 THEN A.AMT
                              ELSE 0 END)  AS '월차수당'
                    ,MAX(CASE WHEN xxxx = 1005 THEN A.AMT
                              ELSE 0 END)  AS '식대'
                    ,MAX(CASE WHEN xxxx = 1006 THEN A.AMT
                              ELSE 0 END)  AS '자가운전보조금'
                    ,MAX(CASE WHEN xxxx = 1007 THEN A.AMT
                              ELSE 0 END)  AS '야간근로수당'
                    ,MAX(CASE WHEN xxxx = 2001 THEN A.AMT
                              ELSE 0 END)  AS '기타수당'
                    ,C.TaxAmt              AS '과세'
                    ,C.NoTaxAmt  		   AS '비과세'
                    ,C.xAmt                AS '제출비과세'
                    ,C.NoxAmt              AS '미제출비과세'
                    ,C.PTAmt               AS '지급총액'
                    ,C.DAmt                AS '공제금액'
                    ,C.BAmt                AS '차인지급액'
                    ,B.NAmt                AS '국민연금'
                    ,B.LI                  AS '장기요양보험'
                    ,B.Hlt                 AS '건강보험'
                    ,C.Incm                AS '소득세'
                    ,C.Rdx                 AS '지방소득세'
                    ,C.RScTx               AS '농특세'
                    ,MAX(CASE WHEN D.xxxx = 5004 THEN D.AMT
                              ELSE 0 END)  AS '고용보험'
                    ,MAX(CASE WHEN D.xxxx = 5005 THEN D.AMT
                              ELSE 0 END)  AS '학자금상환'
                    
          FROM                             Pt  AS A
          LEFT JOIN                        Empt AS B             --사원 테이블
          ON                               A.Ed = B.Ed
          LEFT JOIN                        Px   AS C        -- 급여집계테이블
          ON                               A.ECd = C.ECd
          AND                              C.Pt = A.Pt
          LEFT JOIN                        DT AS D
          ON                               A.Ed = D.Ed 
          AND                              A.YM = D.YM
          GROUP BY                         A.Pt, A.Ed, D.YM, D.Ed
          HAVING                           A.YM = 202207      -- 당월 변경가능
  • 난해하다.. 필자는 주로 서브쿼리를 통하여 SELECT에 지정하는 방식을 좋아하는데 저번에 봤던 JOIN과 서브쿼리의 성능 속도차이가 두배이상 나는것을 보고 난 뒤 서브쿼리 사용을 지양하고 있다

SQLite 컨트롤러 만들기

자프링에서의 일종의 비즈니스 로직 클래스의 개념과 윈폼에서 컨트롤러의 역할이 비슷하지 않나 싶다

        public string _filePath;
        public SQLiteConnection _conn;
        public string _searchDate, _empCd; // 검색년월, 사원번호
        public Dictionary<int, string> dict; // 수당코드의 이름

        public SqliteSource(string _filePath, string _searchDate, string _empCd)
        {
            // 생성자를 통하여 커넥션 시작
            this._filePath              = _filePath;
            this._searchDate            = _searchDate;
            this._empCd                 = _empCd;
            string _connectionString    = string.Format(@"Data Source={0};Pooling=true;FailIfMissing=false;", _filePath);
            this._conn                  = new SQLiteConnection(_connectionString);
            this._conn.Open();
        }

        

        public SqliteSource(string _filePath, string _searchDate)
        {
            // 생성자를 통하여 커넥션 시작
            this._filePath              = _filePath;
            this._searchDate            = _searchDate;
            string _connectionString    = string.Format(@"Data Source={0};Pooling=true;FailIfMissing=false;", _filePath);
            this._conn                  = new SQLiteConnection(_connectionString);
            this._conn.Open();
        }


        public void close()
        {
            // 사용이 끝난 뒤 커넥션 닫기
            this._conn.Close();
        }

윈폼에서 매번 컨트롤러를 거쳐 커넥션을 열고 닫기를 관리하기 까다롭기에 생성자를 통하여 커넥션을 생성하고 열어주도록한다.

  • 오버로딩의 경우 각각의 경우에 쓰이는 파라미터가 다르기 때문에 지정해주었다
public DataTable toDatatable(string query)
        {
            // 쿼리를 데이터테이블로 변환하는 메서드
            var cmd                     = new SQLiteCommand(query, _conn);
            cmd.ExecuteNonQuery();
            SQLiteDataReader sdr        = cmd.ExecuteReader();
            // list of name in current database
            DataTable _ret              = new DataTable();
            _ret.Load(sdr);
            sdr.Close();

            return _ret;
        }

반복적으로 쓰는 DataTable로 만들어 주는 메서드를 생성, 결합도를 낮춘다

최종으로는 이렇게 쿼리를 통해서 DataTable 로 바로 반환 할 수 있게 만듦. 🤢

엑셀에 작성하기

이렇게 DataTable 추출이 완료되었다면 추출한 테이블을 토대로 양식에 맞게 엑셀에 옮겨주는 작업을 거쳐야 한다.

  1. Nuget 패키지 설치 (마소가 직접 만들었나 보다)

  2. 템플릿 불러오기 - 참조

[TestClass()]
    public class SqliteSourceTests
    {
        private static Excel.Workbook wb = null;
        private static Excel.Application app = null;
        private static Excel.Worksheet ws = null;

        [TestMethod()]
        public void SqliteSourceTest()
        {
            string _filePath = @"D:\tempTest.DB";
            string _sDate = "202204";
            SqliteSource sqliteSource = new SqliteSource(_filePath, _sDate);
            
            //DataTable dt = sqliteSource.getExtraPay();
            string _excelPath = @"{템플릿저장경로}";
            int delim = _excelPath.LastIndexOf('\\');
            string _templateName = _excelPath.Substring(delim + 1, _excelPath.LastIndexOf('.')-(delim+1));
            string _templateDir = _excelPath.Substring(0, delim);
            
            string _excelEndPath = string.Format(@"{0}\{1}\{2}\{4}_{3}"
                                            , _templateDir
                                            , 회사코드
                                            , _sDate
                                            , _templateName
                                            , _sDate);
            string _excelEndDir = _excelEndPath.Substring(0, _excelEndPath.LastIndexOf('\\'));
            app = new Excel.Application();
            wb = app.Workbooks.Open(_excelPath); 
            ws = wb.Worksheets["{템플릿워크시트이름}"] as Excel.Worksheet;

            // 사전 정보 얻기
            DataTable dt = sqliteSource.getCompany();
            // 회사정보의 경우 무조건 보장
            string companyName = dt.Rows[0]["NAME"].ToString();
            string repName = dt.Rows[0]["REPST"].ToString();
            int month = sqliteSource._searchMonth;

            ws.Cells[1][2] = companyName;
            ws.Cells[16][4] = string.Format("{0}년 {1}월", sqliteSource._searchYear, sqliteSource._searchMonth);
            ws.Cells[26][4] = string.Format("* 대표자: {0}", repName);

            if (File.Exists(_excelEndPath)) File.Delete(_excelEndPath);
            DirectoryInfo di = new DirectoryInfo(_excelEndDir);
            if (!di.Exists)
            {
                di.Create();
            }
            wb.SaveAs(_excelEndPath);

            wb.Close();
            app.Quit();

            Debug.WriteLine("엑셀 파일 생성 완료");
        }
}
  • 본격적인 시작에 앞서 테스트 코드를 통하여 동작여부를 확인한다
  • Excel.Application() 을 통하여 애플리케이션을 열고 그안에서 템플릿을 열어온다
  • 참조할 워크시트를 지정하여 ws 로 불러온다

✔ 주의

  • 다음과 같이 엑셀이 주어질 때 프로그램에서 다루던 행과 열이 뒤바뀌어 표현됨을 인지하여야 한다
  • 또한 0,0 에서 시작하던 배열과는 달리 엑셀은 [1,1] 부터 시작된다
  • 즉, 회사명 같은 경우 일반적인 int[][] 배열에서 [1,0] 과 같다고 보면 된다
  • 성공적으로 엑셀 저장 여부를 endPath에서 확인해 볼 수 있다

이슈 관리

  1. 회사마다 수당정책이 다르다 보니 One line query를 통하여 급여 정보를 모두 갖고 올 수 없다
  2. sqlite 의 경우 따로 @Declare 와 같은 variable 할당을 할 수 없어 코드에서 따로 두번의 절차를 거쳐 쿼리를 수행해야한다 (프로시저 사용 X)
  3. 직책정보, 호봉정보 등과 같이 입력되지 않은 란에서 아래와 같은 제약조건 예외가 발생

수당정책을 딕셔너리에 담고 이후에 회사별로 dynamic 하게 변경한 쿼리로 급여정보 추출

public void getExtraPay()
        {
            // 수당코드와 수당이름 매핑 후 저장
            string query = "SELECT DISTINCT B.cD, B.NM FROM PDT AS A LEFT JOIN AT AS B ON A.AD = B.AD ORDER BY A.AD";
            DataTable dt = toDatatable(query);

            dict =  dt.AsEnumerable()
                        .ToDictionary<DataRow, object, object>(row => row.Field<object>(0),
                               row => row.Field<object>(1));
        }
  • 급여테이블을 기준으로 급여테이블에 존재하는 중복되지 않은 수당들을 가져와 수당이름과 수당코드를 딕셔너리 형태로 저장
  • MS-SQL의 경우에는 테이블 값의 타입에 따라 자동으로 매핑되었는데 SQLite 의 경우 불가하여 object type으로 받음

가장 상단의 쿼리를 기준으로 딕셔너리에 있는 키값에 따라 회사별 수당항목을 불러올 수 있습니다

SQLite 에서의 제약조건예외

말그대로 제약조건을 위반한 경우일 수 있는데 제가 마주한 케이스는 null 값을 가지는 칼럼에 대한 제약조건을 걸지 않았다는 점입니다.

실제 sqlite dbms를 통해서 쿼리를 실행하였을 때는 이러한 에러가 없는 것을 보고 null 값 처리를 하지 않았다는 문제를 확인하게 됨

→ 우선 null 값을 가지는 칼럼을 확인하고 해당 필드에 ISNULL()과 같이 NULL 값일 경우 다른 값으로 할당되도록 합니다

  • Sqlite 에서는 IFNULL({찾고자하는 필드}, {NULL일 경우 할당될 값}) 을 사용합니다

Excel 템플릿에서 row 복사하여 사람 수 만큼 붙여넣기

아래와 같은 템플릿이 있다 하였을 때

동적으로 사람 수에 따라 그 길이를 늘려주어야 한다
소스에서 급여 정보를 가져오는 dt = sqliteSource.getPayRoll() 에서 유동적으로 rows.count 의 개수에 따라 총 n-1개 만큼 row 를 늘려준다

기존 템플릿에는 하나의 row 를 남긴 채로 총 사람 수 n 이라 하였을 때, n-1번 반복하면서 row 를 복사하여 붙여 넣어준다

		for (int i = 1; i < dt.Rows.Count; i++) // 초기 템플릿은 하나를 갖고있음. 그렇다면 회사 인원에 따라 진행하기 때문에 1 부터 시작
                {
                    // 초기 늘어날값 지정
                    Excel.Range selectRange = ws.Rows[9 + ":" + 10];
                    selectRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
                    // 아래 붙여넣기
                    Excel.Range dest = ws.Rows[9 + ":" + 10];
                    selectRange.Copy(dest);
                }
  • row [9:10] 에 해당하는 열을 잘라서 n-1 번 아래로 늘려줍니다

여기까지 엑셀 틀은 마무리


Excel 에 DB에서 받아온 값 삽입하기

먼저 시작위치를 정해야 하기 때문에 따로 Point 라는 명칭의 객체를 사용하여 현재 위치를 담아주었다. ( 🤔 int[] , 즉 배열로 선언하여도 되지만 언젠가 쓰일 다른 엑셀 프로젝트 재사용성 측면에서 따로 객체를 두는 것이 이점이 있을 것이라 판단 )

class Point
    {
        public int x { get; set; }
        public int y { get; set; }
        
        public Point(int y, int x)
        {
            this.x = x;
            this.y = y;
        }
    }
  • 게터 세터 설정, 생성자를 통해 값을 주입받는 방식
  • 왜 y,x 순으로 받느냐 하면은.. 기존 배열 방식에 익숙해져있어 이렇게 하지 않으면 헷갈린다

수당 정책에 맞춘 값 할당

우선 각 회사별로 세무관리 서비스에 등록한 수당명이 다르기에 해당하는 이름을 가졌는지 여부를 확인하고 그 값을 부여해야 한다

😒 ??? : 가령 야간수당의 경우 회사별로 야간 수당, 야간근로수당, 야간 근로 수당, 야간
이런식으로 제각각인 경우를 발견하였다
😊 해결 하고자 한다면, 위 경우에는 뽑아온 DataTable 의 컬럼명들 중에 "야간" 을 포함한 컬럼이 있는지 확인하고, 없다면 0을 반환하는 메서드를 따로 만들어 주었다

public int findCol(string[] names, string keyword, DataTable dt, int idx)
        {
            int ret = 0;
            List<string> pos = names.AsEnumerable().Where(name => name.Contains(keyword)).ToList();
            if(pos.Count > 0)
            { // 유망한 경우, 데이터테이블에서 해당 값을 모두 더해준다
                pos.ForEach(key =>
                {
                    ret += Convert.ToInt32(dt.Rows[idx][key]);
                });
            }
            return ret;
        }
  • AsEnumerable 은 Stream 과 비슷하게 쓰이는 것 같다. C# 에는 Linq, 나 쿼리를 통하여 inline 으로 코드 작성하기에 편한듯?
  • names 는 DataTable의 컬럼명을 가지는 배열, idx는 데이터테이블에서 몇번째 row에 해당하는지, keyword 는 각 수당에 맞게 특정할 수 있는 키워드가 되겠다
  • 우선 컬럼명들 중에서 키워드를 갖는 컬럼명을 뽑고, 유망한 값들의 총 합을 리턴한다

수당 값 입력받기

돌고 돌아 드디어 엑셀에 값을 넣을 수 있게 되었다

// 값 채우기
                Point start = new Point(1, 9); // 시작점, A9
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ws.Cells[start.y][start.x]          = i + 1;                                // 순번
                    ws.Cells[start.y + 1][start.x]      = dt.Rows[i]["사원명"].ToString();  // 사원명
                    ws.Cells[start.y + 1][start.x + 1]  = dt.Rows[i]["담당업무"].ToString();  // 담당업무
                    ws.Cells[start.y + 2][start.x]      = dt.Rows[i]["생년월일"].ToString(); // 생년월일
                    ws.Cells[start.y + 2][start.x + 1]  = dt.Rows[i]["입사일"].ToString(); // 입사일
                    ws.Cells[start.y + 3][start.x]      = dt.Rows[i]["호봉"].ToString(); // 호봉
                    ws.Cells[start.y + 4][start.x]      = dt.Rows[i]["근로일수"].ToString(); // 근로일수
                    ws.Cells[start.y + 4][start.x + 1]  = dt.Rows[i]["월근로시간"].ToString(); // 월근로시간
                    ws.Cells[start.y + 5][start.x]      = dt.Rows[i]["기본급"].ToString(); // 기본급
                    ws.Cells[start.y + 6][start.x]      = findCol(names, "직책", dt, i).ToString(); // 직책급
                    ws.Cells[start.y + 6][start.x + 1]  = findCol(names, "직무", dt, i).ToString(); // 직무수당
                    ws.Cells[start.y + 7][start.x]      = findCol(names, "자격", dt, i).ToString(); // 자격수당
                    ws.Cells[start.y + 7][start.x + 1]  = findCol(names, "제수당", dt, i).ToString(); // 제수당
                    ws.Cells[start.y + 8][start.x]      = findCol(names, "상여", dt, i).ToString(); // 상여금
                    ws.Cells[start.y + 8][start.x + 1]  = findCol(names, "명절", dt, i).ToString(); // 명절수당
                    ws.Cells[start.y + 9][start.x]      = findCol(names, "근속", dt, i).ToString(); // 근속수당
                    ws.Cells[start.y + 9][start.x + 1]  = findCol(names, "기타", dt, i).ToString(); // 기타수당
                    ws.Cells[start.y + 10][start.x + 1] = findCol(names, "연장", dt, i).ToString(); // 연장수당
                    ws.Cells[start.y + 11][start.x + 1] = findCol(names, "야간", dt, i).ToString(); // 야간수당
                    ws.Cells[start.y + 12][start.x + 1] = findCol(names, "휴일", dt, i).ToString(); // 휴일수당
                    ws.Cells[start.y + 14][start.x]     = findCol(names, "식대", dt, i).ToString(); // 식대
                    ws.Cells[start.y + 14][start.x + 1] = findCol(names, "자가운전", dt, i).ToString(); // 자가운전

                    ws.Cells[start.y + 16][start.x]     = dt.Rows[i]["국민연금"].ToString(); // 국민연금
                    ws.Cells[start.y + 16][start.x + 1] = dt.Rows[i]["고용보험"].ToString(); // 고용보험
                    start.x += 2;
                }
  • 불필요한 루프를 굳이 돌 필요 없기에 애매한 항목들만 findCol함수를 써주었다

파일 저장하기

if (File.Exists(_excelEndPath+ ".xlsx")) File.Delete(_excelEndPath+".xlsx");
            DirectoryInfo di = new DirectoryInfo(_excelEndDir);
            if (!di.Exists)
            {
                di.Create();
            }
            wb.SaveAs(_excelEndPath);

            wb.Close();
            app.Quit();
            sqliteSource.close();
            Debug.WriteLine("엑셀 파일 생성 완료");

파일이 만약 있다면 삭제해주고, 디렉토리도 없다면 생성해준다

  • 엑셀 app 끄기, 워크북 닫기
  • 만약 해당 파일이 열려있다면 저장이 불가하다

완료!
이제는 모듈화와 리팩토링을 거쳐서 윈폼 유효성 검사등만 남았네요

0개의 댓글