C# 엑셀 확장자별로 OLEDB 연결 설정을 구분해 엑셀 데이터 읽기
엑셀 파일 포맷별로 OLEDBConnectiion으로 임포트해서 가져오는 코드입니다.
실제 사용중인 코드이고, 파일 포맷별로 대응되도록 만든 코드입니다.
Microsoft Access Database Engine 2010 재배포 가능 패키지 는 사전에 설치해야 합니다.
엑셀 임포트 설정시 OLEDB는 읽어들이는 데이터의 타입을 추정해서 정하게 됩니다.
최초 8줄을 읽어서 해당 타입으로 추정하거나, 우선 타입을 사용하도록 하거나 하는식으로 지정합니다.
해당 값들은 모두 레지스트리에 설정 값이 지정되어 있으며, 이 값들을 변경해 OLEDB로 엑셀 임포트를 할 때 임의로 타입을 지정해 사용하지 않도록 막을 수 있습니다.
1.
최초 8행을 읽어서 임포트하는 컬럼의 타입을 추정하지 않도록 하려면 다른 글을 참조하시기 바랍니다.
->엑셀 임포트시 셀타입을 미리 추정하지 않고 전체 스캔하도록 설정하기
2.
그밖에 OLEDB 엑셀 임포트시 우선 타입으로 자동 선택하지 않고 무조건 텍스트 타입으로 지정되도록 하려면
레지스트리 편집기에서
64비트 윈도우 - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text
32비트 윈도우 - HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
경로의 필드 값인 ImportMixedTypes 값을 "Majority Type" ->"Texxt" 로 변경합니다.
OLEDB로 확장자에 따라 엑셀 임포트를 하는 코드는 아래와 같습니다.
이대로는 100% 사용하지 못하고, 예를 보여주기 위해 관련된 부분만 잘라낸 것입니다.
엑셀 네이비트 포맷(XLS, XLSX 등) 은 읽는 시점에 데이터 컬럼 정렬값을 줄 수 있습니다.
string fPath = Server.MapPath("~/ExcelFiles/"); string strFileName = Request["excelfilename"]; string strSaveFullPath = fPath + strFileName; string strFileExtension = Request["fextension"]; bool IsHeaderIncluded = false; string tmpConn = "Provider=Microsoft.{0}.OLEDB.{1};Data Source=\"{2}\";Mode=ReadWrite|Share Deny None;Extended Properties=\"{3};IMEX=1;HDR={4};\""; bool bCSV = false;//csv타입 포맷인지 판단. csv, html, htm
if (!string.IsNullOrEmpty(fPath)) { // 확장자에 따라 Connection string을 다르게 처리 switch (strFileExtension) { case ".xls": case ".xlt": strConn = string.Format(tmpConn, "ACE", "12.0", strSaveFullPath, "Excel 8.0", IsHeaderIncluded ? "YES" : "NO"); break;
case ".xlsx": strConn = string.Format(tmpConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0 Xml", IsHeaderIncluded ? "YES" : "NO"); break;
case ".xlsm": strConn = string.Format(tmpConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0 Macro", IsHeaderIncluded ? "YES" : "NO"); break;
case ".xlsb": strConn = string.Format(tmpConn, "ACE", "12.0", strSaveFullPath, "Excel 12.0", IsHeaderIncluded ? "YES" : "NO"); break;
case ".csv": bCSV = true; strConn = string.Format(tmpConn, "ACE", "12.0", fPath, "Text", IsHeaderIncluded ? "YES;FMT=Delimited(,)" : "NO;FMT=Delimited(,)"); break;
case ".html": case ".htm": bCSV = true; strConn = string.Format(tmpConn, "ACE", "12.0", strSaveFullPath, "HTML Import", IsHeaderIncluded ? "YES;FMT=Delimited(,)" : "NO;FMT=Delimited(,)"); break;
default: Response.Redirect("/Err?c=2"); break; } } OleDbConnection XLSConn = new OleDbConnection(strConn); try {
XLSConn.Open();
//시트이름 결정 - 첫번째 시트 이름을 파일에서 가져옴 DataTable dtSchema = XLSConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string 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"; strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere + SQLordering, sheetName); break; case ".csv": strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere, strFileName); break; case ".html": case ".htm": strQuery = string.Format("SELECT * FROM [{0}] " + SQLWhere, sheetName); break; } //여기서 엑셀파일 읽어서 데이터 처리를 하고 XLSConn.Close(); } catch (Exception ex)//엑셀파일 오픈 예외 처리 { if (XLSConn != null) { XLSConn.Close(); Response.Write(ex.Message); } }