비즈니스 어플리케이션을 개발하는 경우에는 대부분의 회사에서 Office 제품에서 Excel을 많이 사용하기 때문에 Excel의 데이터를 Grid 로 로드해서 보여달라고 하는 경우가 많다.
Office 2010 부터는 OPENXML 기준이기 때문에 해당 Library를 활용해서 Office Interop 처리를 하는 것이 가능하지만 관련된 라이브러리를 사용하는 것도, 구성하는 것도, 다양한 처리를 하는 것도 항상 시간이 필요한 것이 때문에 대부분은 쉽게 OLEDB Provider를 이용해서 처리하게 된다.
이번 프로젝트에서도 OLEDB Provider를 이용해서 Excel의 특정 Sheet를 DataTable로 로드하고 이를 Grid에 바인딩해 주는 방식으로 기본 라이브러리를 구성하였다.
오늘 들어온 새로운 문제점은 대 부분의 Excel은 잘 처리되는데 특정 Excel 에서는 특정 Cell의 데이터가 누락된다는 것이다.
검증을 해 보니, Excel의 Column 에 숫자형과 문자형이 같이 사용된 경우로 DataTable로 로드했을 때 숫자 데이터는 살아있고, 문자 데이터는 누락되어 없는 증상이었다.
유추해 보건데, OLEDB Provider가 Excel의 데이터 형식을 검증하는 과정에서 숫자형을 기준으로 잡았다가 문자 데이터가 들어오니, 데이터 변환을 시도했을 것이고, 변환이 실패하니 DBNull 처리를 한 것 같아서 여러 가지 자료를 검색해 보았다.
검색한 결과는 OLEDB Provider에 접속 문자열 (ConnectionString)을 구성할 때 데이터 형식을 유추할 수 있도록 기준을 설정해 주면 되는 것이었다. 물론 이런 설정을 해 준다고 해서 모든 문제점이 다 해결되는 것이라고는 할 수 없지만, 오늘 발생한 문제점은 해결이 되었다.
[ 실제 적용한 샘플 코드 ]
public static DataTable ImportMyDataTableFromExcel(string filePath)
{
DataTable dt = new DataTable();
string fullPath = Path.GetFullPath(filePath);
string connString =
“Provider=Microsoft.Jet.OLEDB.4.0;” +
“Data Source=”" + fullPath + “”;" +
“Extended Properties=“Excel 8.0;HDR=No;IMEX=1;””;
string sql = @“SELECT * FROM [sheet1$]”;
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString)) {
dataAdapter.Fill(dt);
}
dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);
return dt;
}
private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt) {
DataRow firstRow = dt.Rows[0];
for (int i = 0; i < dt.Columns.Count; i++) {
dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
}
dt.Rows.RemoveAt(0);
return dt;
}
위의 코드를 기준으로 테스트를 해 보면 Excel의 특정 컬럼에 데이터가 섞여있는 경우에는 누락되지 않고 문자열로 처리되어 나오는 것을 확인해 볼 수 있다.
관련된 확장 속성들은 다음과 같다.
- IMEX - Excel 컬럼의 데이터 형식의 섞여 있는 경우가 있으면 누락되는 것을 방지하기 위해서 1 값을 지정하는 것이 좋다. 빠지면 숫자형 컬럼에 문자가 있으면 문자 데이터는 누락된다. (IMEX=1)
- TypeGuessRows - 데이터 형식을 유추하는데 사용할 Row 값을 지정한다. HDR=NO 라고 했으면 첫번째 행의 데이터를 기준으로 데이터 형식을 유추하여 처리하게 된다. (TypeGuessRows=0)
- ImportMixedTypes - IMEX와 동일한 형식이지만 혼합된 컬럼에 대해서 강제로 형식을 지정할 수 있다. (ImportMixedType=TEXT)
좀 더 많은 상황들과 확장 속성들이 존재하겠지만.. 오늘은 여기까지.. ㅠㅠ
와... 정말 감사합니다. 우연히 찾게 되었습니다. ㅜㅠ 시간 벌었어요~~
답글삭제