안녕하세요
코딩연습생입니다
C# Winform에서 엑셀 연동시 데이터를 읽어오거나 쓰기를 할때 Cells를 많이 사용하게 됩니다
저번 포스팅에서도 한번 언급했던 적이 있는데 300건 이후로 속도가 현저히 느려집니다
그래서 속성 설정으로 속도를 개선하는것을 예전에 포스팅 했던 적이 있는데요
https://codingman.tistory.com/101
[C#] 엑셀 Cells 사용시 속도 문제 해결하기
안녕하세요. 코딩연습생입니다. 정말 오랜만에 글을 쓰는거 같습니다.. 회사 프로젝트 진행 때문에 시간을 너무 빼앗겨 버리네요ㅎㅎ 근데 저도 이제 블로거가 다 된거 같습니다 하루에도 몇번�
codingman.tistory.com
근데 위의 방법도 근본적인 속도 문제가 해결 되질 않습니다
정말 미비한 차이만 발생하더라구요
그래서 이번 포스팅에서는 근본적인 속도 문제를 해결 해보려고 합니다
일단 기본적인 엑셀 값 쓰기 방식인데요 이전 포스팅을 참조해 주세요
https://codingman.tistory.com/100
[C#] 엑셀 템플릿파일 불러오기 및 값 넣기
안녕하세요 코딩 연습생입니다 아직도 코로나19로 인해서 기업들 소상인 분들 모두 참 어렵게 지내고 계시는거 같습니다 저 또한 회사원으로 회사가 많이 힘들어 지고 있다고 체감할 정도니깐��
codingman.tistory.com
위의 방식으로 구현을 할 경우 조건이 엑셀의 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분 미만
이런 결과가 테스트 되었습니다
혹시 저 처럼 속도 문제로 머리 썪고 계시다면 한번 적용 해보시기 바랍니다
'코딩정보 > C#' 카테고리의 다른 글
| [C#] FTP를 이용한 파일 다운로드 구현하기!! (0) | 2020.06.16 |
|---|---|
| [DataGridView] 콤보박스 원클릭 리시트 보여주기 (0) | 2020.06.01 |
| [C#]컨트롤이 데이터 바인딩된 경우에는 datagridview의 행 컬렉션에 프로그래밍 방식으로 행을 추가할 수 없습니다 (0) | 2020.05.11 |
| [C#] 'Microsoft.ACE.OLEDB.12.0' 공급자는 로컬 컴퓨터에 등록할 수 없습니다 (3) | 2020.05.07 |
| [C#] 데이터그리드뷰(콤보박스) 값 변경 이벤트 (0) | 2020.05.06 |
