[C#] 엑셀 Cells 사용시 속도 문제 해결하기 #2

안녕하세요

 

코딩연습생입니다

 

C# Winform에서 엑셀 연동시 데이터를 읽어오거나 쓰기를 할때 Cells를 많이 사용하게 됩니다

 

저번 포스팅에서도 한번 언급했던 적이 있는데 300건 이후로 속도가 현저히 느려집니다

 

그래서 속성 설정으로 속도를 개선하는것을 예전에 포스팅 했던 적이 있는데요

 

https://codingman.tistory.com/101

[C#] 엑셀 Cells 사용시 속도 문제 해결하기

근데 위의 방법도 근본적인 속도 문제가 해결 되질 않습니다

 

정말 미비한 차이만 발생하더라구요

 

그래서 이번 포스팅에서는 근본적인 속도 문제를 해결 해보려고 합니다

 

일단 기본적인 엑셀 값 쓰기 방식인데요 이전 포스팅을 참조해 주세요

 

https://codingman.tistory.com/100

[C#] 엑셀 템플릿파일 불러오기 및 값 넣기

위의 방식으로 구현을 할 경우 조건이 엑셀의 Row수가 대략 100개 이하일 경우에 사용하세요

 

그 이상 넘어가게 되면 속도가 많이 걸립니다 대략 300~500개의 Row의 값을 쓸 경우 대략 10~15분 가량이 소요

 

됩니다

 

이 문제를 해결하기 위해 고민도 많이 하고 검색도 많이 했는데 뚜렸한 답변이 없더라구요

 

OLEDB를 사용한 방법도 있긴 한데 해당 방법은 쿼리 형식으로 데이터를 읽고/쓰기를 해야 해서

 

특정 Cell 지정이나 이미 생성되어 있는 양식화에 적용하기에는 어렵고 복잡하더라구요

 

그래서 검색 내용한 내용과 기존 방식을 응용해서 구현해 봤습니다

 

일단 소스 코드 부터 보여 드리겠습니다

 

private void hoverGradientButton21_Click(object sender, EventArgs e)
        {
            DataGridView_Change(false);

            string ExcelPath = Environment.CurrentDirectory + @"\Excel\Shipping_sample.xlsx";
            string ExcelEndPath;
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Title = "저장경로 지정하세요";
            saveFileDialog.OverwritePrompt = true;
            saveFileDialog.Filter = "Excel File(*.xlsx)|*.xlsx";

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                ExcelEndPath = saveFileDialog.FileName;

                //파일생성유무 확인(기준 하루 한번 생성이 가능 재생성시 그존 파일 삭제.
                if (File.Exists(ExcelEndPath))
                {
                    File.Delete(ExcelEndPath);
                }

                Excel.Application app = new Excel.Application();
                app.DisplayAlerts = false;
                app.Visible = false;
                app.ScreenUpdating = false;
                app.DisplayStatusBar = false;
                app.EnableEvents = false;

                Excel.Workbooks workbooks = app.Workbooks;
                Excel.Workbook workbook = workbooks.Open(ExcelPath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                Excel.Sheets sheets = workbook.Worksheets;
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
                Excel.Range rng = worksheet.get_Range("A1", "CG3000");


                //Excel.Range totalRange = worksheet.get_Range(worksheet.get_Range("A1"), lastCell);
                object[,] only_data = (object[,])rng.get_Value();
                

                try
                {
                    //엑셀 시트 인덱스 번호는 0,0 부터 시작 하는 것이 아니라 1,1 A1 부터 시작 함. 0,0 으로 시작하면 오류... 
                    //시트에 값 쓰기... 
                    string query = "EXEC SP_SHIP_CUST_PLAN_EXCEL";
                    query += "           'SELECT',";
                    query += "           '" + WORK_FROM_KEY.Text + "',";
                    query += "           ''";
                    SAC_MesDB db = new SAC_MesDB();
                    DataTable dt = db.ExcuteQuery(query);

                    if (dt.Rows.Count > 0)
                    {
                        int row = dt.Rows.Count + 30;
                        int column = rng.Columns.Count;
                        object[,] data = new object[row, column];

                        data = only_data;

                        //해더 복사
                        for (int h = 1; h <= column - 1; h++)
                        {
                            data[1, h] = only_data[1, h];
                            data[2, h] = only_data[2, h];
                            data[3, h] = only_data[3, h];
                            data[4, h] = only_data[4, h];
                            data[5, h] = only_data[5, h];
                            data[6, h] = only_data[6, h];
                        }

                        //고객사 계획 일자 헤더 생성.
                        DateTime Today = Convert.ToDateTime(WORK_FROM_KEY.Text);
                        data[5, 25] = string.Format("{0:M/d}", Today);

                        //총일
                        data[2, 8] = dt.Rows[0]["TOTAL_CNT"].ToString();
                        //지정일수
                        data[2, 9] = dt.Rows[0]["BACK_CNT"].ToString();

                        for (int i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            data[7 + i, 1] = dt.Rows[i]["Cust_Code"].ToString();
                            data[7 + i, 2] = dt.Rows[i]["VEND_NAME"].ToString();
                            data[7 + i, 3] = dt.Rows[i]["Delivery_Code"].ToString();
                            data[7 + i, 4] = dt.Rows[i]["Delivery_Name"].ToString();
                            data[7 + i, 5] = dt.Rows[i]["Part_No"].ToString();
                            data[7 + i, 6] = dt.Rows[i]["Cust_GridNo"].ToString();
                            data[7 + i, 7] = dt.Rows[i]["Part_Name"].ToString();
                            data[7 + i, 8] = dt.Rows[i]["LAST_FINQTY"].ToString();
                            data[7 + i, 9] = dt.Rows[i]["SELL_FINQTY"].ToString();
                            data[7 + i, 10] = dt.Rows[i]["SELL_RESULT"].ToString();
                            data[7 + i, 14] = dt.Rows[i]["AVG_CNT"].ToString();
                            data[7 + i, 15] = dt.Rows[i]["Min_Storage"].ToString();
                            data[7 + i, 16] = dt.Rows[i]["Max_Storage"].ToString();
                            data[7 + i, 17] = dt.Rows[i]["STORAGE_FINQTY"].ToString();
                            data[7 + i, 18] = dt.Rows[i]["WIP_FINQTY"].ToString();
                            data[7 + i, 20] = dt.Rows[i]["Cust_Plan_Qty"].ToString();
                            data[7 + i, 21] = dt.Rows[i]["Add_Cust_Plan_Qty"].ToString();
                            data[7 + i, 22] = dt.Rows[i]["Ship_LotQty"].ToString();
                            data[7 + i, 23] = dt.Rows[i]["Cust_SafeQty"].ToString();
                            data[7 + i, 25] = dt.Rows[i]["Cust_Qty"].ToString();
                            data[7 + i, 72] = dt.Rows[i]["D0"].ToString();
                            data[7 + i, 73] = dt.Rows[i]["D1"].ToString();
                            data[7 + i, 74] = dt.Rows[i]["D2"].ToString();
                            data[7 + i, 75] = dt.Rows[i]["D3"].ToString();
                            data[7 + i, 76] = dt.Rows[i]["D4"].ToString();
                            data[7 + i, 77] = dt.Rows[i]["D5"].ToString();
                            data[7 + i, 78] = dt.Rows[i]["D6"].ToString();
                            data[7 + i, 79] = dt.Rows[i]["D7"].ToString();
                            data[7 + i, 80] = dt.Rows[i]["D8"].ToString();
                            data[7 + i, 81] = dt.Rows[i]["D9"].ToString();
                            data[7 + i, 82] = dt.Rows[i]["D10"].ToString();
                            data[7 + i, 83] = dt.Rows[i]["D11"].ToString();
                            data[7 + i, 84] = dt.Rows[i]["D12"].ToString();
                            data[7 + i, 85] = dt.Rows[i]["D13"].ToString();
                        }

                        rng.Value = data;
                    }

                    SubForms.SplashWnd.SplashClose(this);

                    if (File.Exists(ExcelEndPath)) File.Delete(ExcelEndPath);
                    workbook.SaveAs(ExcelEndPath);

                    MessageBox.Show("엑셀 파일 생성이 완료 되었습니다.");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    workbook.Close(true, null, null);
                    app.Quit();

                    ReleaseExcelObject(app);
                    ReleaseExcelObject(workbook);
                    ReleaseExcelObject(workbooks);
                    ReleaseExcelObject(worksheet);
                    ReleaseExcelObject(sheets);
                }
                finally
                {
                    workbook.Close(true, null, null);
                    app.Quit();

                    ReleaseExcelObject(app);
                    ReleaseExcelObject(workbook);
                    ReleaseExcelObject(workbooks);
                    ReleaseExcelObject(worksheet);
                    ReleaseExcelObject(sheets);
                }
            }
        }
        
private void ReleaseExcelObject(object obj)
        {
            try
            {
                if (obj != null)
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
            }
            catch (Exception ex)
            {
                obj = null;
                throw ex;
            }
            finally
            {
                GC.GetTotalMemory(false);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.GetTotalMemory(true);
            }
        }

 

전체 소스입니다

 

해당 문제 해결 방법은 object[,] 였습니다

 

템플릿화 된 문서를 불러와서 object[,] 배열에 담아주고 object[,] 배열의 값을

 

수정하여 완성된 데이터를 배열에서 만들어 준뒤에 range 함수의 Value를 통해 BulkInsert를 실행해 주는것입니다

 

구문별로 설명을 다시 한면

 

 

위의 소스 코드 영역은 엑셀 파일(템플릿)을 불러와 첫번째 시트 중 A1부터 CG3000까지를 영역 설정을 지정하는

 

구문 입니다

 

선택된 영역의 내용을 only_data라는 object[,] 배열에 담아 줍니다

 

 

only_data의 object[,]배열을 동일한 object 배열인 data의 배열로 복사 합니다

 

이유는 onlu_data를 직접수정하게되면  기존 속도하고 큰 차이가 없어지게 됩니다

 

예를들면 100 = 100으로 복사한 뒤에 10, 50, 60의 수치를 변경한다고 헀을 경우 

 

결국 100을 모두 조회 해야 하기 때문입니다

 

하지만 위의 처럼 오브젝트를 두개를 선언해서 1개의 오브젝는 100

 

나머지 한개의 오브젝트에는 필요한 수지만 10, 50, 60만 지정합니다

 

그럼 데이터를 쓰기 할 경우 10, 50, 60만 조회 하면 되기 때문에 속도가 빨라지겠죠

 

 

data 오브젝트 배열에 필요한 값 만큼만 지정하여 입력해 줍니다

 

직접 Cells를 사용하여 엑셀 문서에 데이터를 조합하는것이 아닌 object[,]의 내용을 조합하는 것 입니다

 

 

다음 조합된 오브젝트 배열을 엑셀 함수인 Range.Value를 사용하여 오브젝트를 바로 넣어주게 되면

 

끝이됩니다

 

속도 차이는 

* 500개 Row 기준

   1) Cells 함수 사용시 10~15분 소요

   2) 1개의 오브젝트 사용시 5~10분 소요

   3) 2개의 오브젝트 사용시 1분 미만

 

이런 결과가 테스트 되었습니다

 

혹시 저 처럼 속도 문제로 머리 썪고 계시다면 한번 적용 해보시기 바랍니다