[C#]서버로 업로드 한 엑셀 파일을 액세스 데이터베이스 엔진으로 읽어서 처리하기
C#으로 엑셀 파일(xlsx)을 읽어서 처리하기 위해서는 서버에 "Microsoft Access Database Engine 2010" 재배포 가능 패키지 설치가 필요합니다.
https://www.microsoft.com/ko-kr/download/details.aspx?id=13255
예제 코드들은 레이저(Razor) C# 코드입니다.
ASP.NET에서는 약간씩 구현상에 차이점이 있을 수 있습니다.
엑셀 파일 읽기
먼저 엑셀 파일을 데이터베이스 테이블처럼 다루기 위해서는 액세스 데이터베이스 엔진을 사용해 엑셀 파일에 연결해야 합니다.
기본 연결 문자열은 다음과 같습니다. 실제로는 줄 바꿈 없는 하나의 문자열입니다.
맨 끝의 "Extended Properties" 속성은 여러 개의 속성을 세미콜론(;)으로 구분해서 표현하기 때문에 큰따옴표로 한번 더 감싸야합니다.
{0}, {1}, {2}, {3}, {4} 5개의 템플릿 문자열 대체 값은 엑셀 파일 확장자(엑셀 파일 종류 및 버전)에 따라 다르게 적용됩니다.
string excelConn = "Provider=Microsoft.{0}.OLEDB.{1};Data Source=\"{2}\";
Mode=ReadWrite|Share Deny None;Extended Properties=\"{3};
IMEX=1;HDR={4};TypeGuessRows=0;ImportMixedTypes=Text\"";연결 문자열의 템플릿 변수를 엑셀 파일 확장자에 따라 적당한 것으로 대체해서 연결 문자열을 완성합니다.
strFileExtension 변수는 파일 확장자 문자열입니다.
맨 끝의 isHeaderIncluded 변수는 엑셀 파일에 헤더 행이 있는지를 표시하는 불리언입니다.
CSV 파일은 액세스 데이터베이스 엔진으로 읽을 수 있지만, 정렬 제한이 있기 때문에 다른 엑셀 확장자와는 달리 몇몇 가지 예외 처리를 해야 합니다.
따라서 bCSV = true;처럼 CSV 파일인지를 구분하는 불리언을 사용해 구분 처리를 해주는 것이 뒤쪽에서 엑셀 파일을 처리하는 과정이 단순화돼서 좋습니다.
string strSaveFullPath = strPath + strFile; // 엑셀 파일 전체 경로
switch (strFileExtension)
{
case ".xls":
case ".xlt":
strConn = string.Format(excelConn, "ACE", "12.0", strSaveFullPath, "Excel 8.0", isHeaderIncluded ? "YES" : "NO");
break;
case ".xlsx":
strConn = string.Format(excelConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0 Xml", isHeaderIncluded ? "YES" : "NO");
break;
case ".xlsm":
strConn = string.Format(excelConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0 Macro", isHeaderIncluded ? "YES" : "NO");
break;
case ".xlsb":
strConn = string.Format(excelConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0", isHeaderIncluded ? "YES" : "NO");
break;
case ".csv":
bCSV = true;//뒤에서 CSV 파일 구분을 위해 사용하는 플래그
//CSV 파일은 파일명을 제외한 경로만 넣어야 함.
strConn = string.Format(excelConn, "ACE", "12.0", strPath, "Text", isHeaderIncluded ? "YES;FMT=Delimited(,)" : "NO;FMT=Delimited(,)");
break;
case ".html":
case ".htm":
strConn = string.Format(excelConn, "ACE", "12.0", strSaveFullPath, "HTML Import", isHeaderIncluded ? "YES;FMT=Delimited(,)" : "NO;FMT=Delimited(,)");
break;
default:
Response.Redirect("/Err?c=2");
break;
}최종적으로는 strConn 문자열 변수에 엑셀 연결문이 들어가게 됩니다.
주의해야 할 점이 있습니다.
CSV 파일은 파일명이 빠진 경로(strPath)만 넣어야 합니다.
CSV파일은 시트에서 데이터를 가져오는 쿼리문에서 실제 파일명을 명시합니다. 중요합니다!
첫 번째 시트의 데이터를 로딩
앞서 설명한 대로 CSV 파일은 시트 이름이 아니라 strFile 변수에 저장된 엑셀 파일 이름을 지정합니다.
CSV 파일은 시트가 1개밖에 없는 개념이기 때문이기도 하고, 시트 이름 자체가 없기 때문에 파일명으로 시트명을 대신하는 방식으로 액세스 데이터베이스 엔진이 동작합니다.
OleDbConnection XLSConn = new OleDbConnection(strConn);
try
{
XLSConn.Open();
//구현의 용이성을 위해 첫 번째 시트를 무조건 가져오는 방식으로 구현
//시트이름 결정 - 첫번째 시트 이름을 엑셀 파일에서 가져옴
DataTable dtSchema = XLSConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
sheetName = dtSchema.Rows[0].Field<string>("TABLE_NAME");
switch (strFileExtension)
{
case ".xls":
case ".xlt":
case ".xlsx":
case ".xlsb":
case ".xlsm":
sheetName = sheetName.Replace("'", "") + (header_row + 1).ToString() + ":1000";
//SQLordering 변수는 테이블의 정렬 쿼리를 담은 변수 - "order by username desc"
strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere + SQLordering, sheetName);
break;
case ".csv":
strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere, strFile);
break;
case ".html":
case ".htm":
strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere, sheetName);
break;
}
OleDbCommand cmd = new OleDbCommand(strQuery, XLSConn);
OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet("CSVFile");
try
{
adpt.Fill(ds); // dataset에 데이터 로딩
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
//ds를 통해 첫 번째 시트 데이터를 담은 테이블에 접근해서 엑셀 데이터 처리를 함
//처리 완료 후 반드시 엑셀 파일을 닫아야 함.
if (XLSConn != null){ XLSConn.Close(); }
}
catch (Exception ex)//엑셀파일 오픈 예외 처리
{
Response.Write(ex.Message);
if (XLSConn != null){ XLSConn.Close(); }
}이제 시트 데이터를 데이터베이스 테이블처럼 접근해서 쿼리를 하거나 레코드, 칼럼 단위의 조작 등을 할 수 있습니다.
연결한 엑셀 파일은 데이터셋 객체(앞의 예제에서는 ds 변수로 데이터셋을 받음)를 사용해 접근합니다.
첫 번째 시트는 "ds.Tables [0]"이 되고, 레코드는 "ds.Tables [0]. Rows [37]"과 같이 접근합니다.
칼럼도 마찬가지로 "ds.Tables[0].Columns[colName]" 로 컬럼 이름을 사용해서 접근할 수 있습니다.
위 예제에서 SQLordering 변수는 테이블의 정렬 순서를 담고 있는 정렬 쿼리문입니다.
표준 쿼리 문의 정렬 문과 같아서 "order by orderdate desc"와 같이 작성합니다.
CSV는 쿼리문에 정렬 쿼리문을 넣을 수 없습니다.(지원 안 함)
대신 데이터셋으로 데이터를 모두 로딩한 후 데이터셋의 뷰에 정렬 순서를 지정해서 정렬된 데이터를 얻을 수 있습니다.
앞서 예에서 "bCSV"와 같은 CSV 파일 여부를 확인하는 불리언을 정의했는데 그 불리언을 이용해서 다음처럼 CSV 파일에서 읽은 데이터셋을 정렬할 수 있습니다.
쿼리 문의 "order by"는 필요가 없고 정렬 칼럼 이름과 방향(asc/desc)만 "Sort" 속성에 문자열로 넘겨주면 됩니다.
//csv만 테이블 정렬 정보를 뷰에 줌
if (bCSV) { ds.Tables[0].DefaultView.Sort = "orderdate desc"; }테이블 헤더 행 삭제
테이블에서 헤더 행을 없애고 데이터 레코드만 남기고 싶으면 다음처럼 헤더 행 레코드 개수만큼을 맨 위에서부터 삭제하면 됩니다.
//header_row는 헤더 행 개수를 담은 변수
for (int dsloop = 0; dsloop < header_row; dsloop++)
{
ds.Tables[0].Rows[0].Delete();
}
ds.AcceptChanges();//반드시 커밋해야 dstaset에 적용됨반드시 AcceptChanges() 메서드로 커밋(변경 사항 반영)을 해야 레코드 삭제한 내용이 테이블에 반영됩니다.
Rows []의 인덱스가 항상 0인 것에 주의해야 합니다.
첫 행이 삭제되면 두 번째 행이 첫 행이 되기 때문에 항상 첫 번째 행을 삭제하는 식으로 루프를 돌아야 합니다.
칼럼 추가와 삭제
테이블에서 칼럼들의 계산 값을 새 칼럼에 추가하거나, 컬럼의 타입을 캐스팅(Casting) 해서 새 컬럼에 넣는 것과 같은 작업은 생각보다 빈번하게 발생합니다.
액세스 데이터베이스 엔진이 엑셀 데이터를 읽으면서 데이터 타입을 결정하는 방식이 다소 원시적이다 보니 항상 원하는 데이터 타입으로 엑셀 파일을 읽는 것은 아니기 때문에 타입 캐스팅을 해서 실제 데이터베이스에 최종 가공된 데이터를 넣어야 할 때가 많이 있습니다.
새 칼럼에 원하는 데이터를 집어넣을 때는
- 새 칼럼(객체)을 생성해서 엑셀 시트 맨 끝에 부착
- 가공된 최종 데이터를 만들어서 새 칼럼에 넣음
과 같이 단순한 작업이면 되지만
새 칼럼에 원하는 데이터를 넣은 후 원래 컬럼을 삭제하고, 새로 만든 컬럼을 구 컬럼 위치로 인덱스를 바꾸는 것은 조금 주의를 해야 합니다.
- 삭제할 구 칼럼의 컬럼 인덱스를 얻어서 보관한 후
- 새 칼럼(객체)을 생성해서 엑셀 시트 맨 끝에 부착
- 가공된 최종 데이터를 만들어서 새 칼럼에 넣음
- 구 칼럼을 삭제
- 새 칼럼의 인덱스를 보관해둔 구 컬럼 인덱스로 변환
하는 순서로 작업을 해야 합니다.
먼저 구 칼럼을 삭제해버리면 삭제한 컬럼 이후의 컬럼들의 인덱스 값이 하나씩 줄어들면서 새 컬럼의 인덱스 값도 달라지기 때문에 신경쓸 것들이 늘어나게 됩니다.
새 컬럼을 만들어서 가공된 데이터를 넣은 후 삭제한 구 컬럼 위치로 새 컬럼을 옮기는 방법은 다음과 같습니다.
string columnName = "p_option";
int oldColumnIndex = 1;
//새 컬럼 객체 만들어서
using (DataColumn dc = new DataColumn(columnName+"_new", typeof(String)))
{
// 새 컬럼 부착
ds.Tables[0].Columns.Add(dc);
// 문자열로 타입 캐스팅해서 새 컬럼으로 전체 레코드의 값 복사
//p_option_new로 컬럼 이름 - columnName 이 있음.
foreach (DataRow dr in ds.Tables[0].Rows){
dr[dc.ColumnName] = Convert.ChangeType(dr[oldColumnIndex], typeof(String));
}
// 반드시 구컬럼 삭제하고 위치 변경해야 함. 컬럼 갯수가 늘어나기 때문에 엑셀 환경 값까지 조정해야 하는 문제가 생김
// 구 컬럼 삭제
ds.Tables[0].Columns.RemoveAt(oldColumnIndex); // 컬럼 이름으로 변경할 때는 .Remove(column_name)
// 새 컬럼 위치 변경
dc.SetOrdinal(oldColumnIndex);
}새 컬럼의 순서를 SetOrdinal() 메서드로 변경하지 않은 경우, 단순히 컬럼을 추가한 후의 새 컬럼 인덱스는 "dc.Ordinal"로 얻을 수 있습니다.