C#을 이용한 웹 애플리케이션 개발 시 Excel 파일을 읽어 데이터베이스에 일괄 등록하는 기능은 매우 빈번하게 요구됩니다. C#에서 Excel을 다루는 방법은 크게 네 가지 정도로 요약할 수 있습니다.
- Excel Interop: Microsoft Office 구성 요소를 직접 호출합니다. 강력한 기능을 제공하지만, 서버에 Excel이 설치되어 있어야 하며 프로세스 관리 및 성능 오버헤드가 커서 웹 환경에는 적합하지 않습니다.
- OpenXML: .xlsx 형식의 파일을 조작하는 표준 라이브러리입니다. 별도의 설치가 필요 없지만, 구현이 다소 복잡하고 구형(.xls) 파일 지원이 제한적입니다.
- NPOI: Office 설치 없이 사용할 수 있는 오픈 소스 라이브러리입니다. 파일 제어 성능이 우수하지만 데이터베이스 형식의 접근 방식은 아닙니다.
- OleDb: Excel을 하나의 데이터베이스 소스로 간주하여 SQL 쿼리로 데이터를 읽고 쓸 수 있는 방식입니다. 설정이 간편하고 속도가 빨라 데이터 마이그레이션에 자주 쓰입니다.
이 중 OleDb를 사용하여 Excel 데이터를 다루는 핵심 방법과 ASP.NET MVC 환경에서 대량 업로드를 구현하는 과정을 살펴보겠습니다.
1. OleDb 연결 문자열 구성
Excel 파일에 연결하기 위해서는 파일 확장자에 맞는 프로바이더 설정이 중요합니다.
// .xls (Excel 97-2003)
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\data.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
// .xlsx (Excel 2007 이상)
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\data.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
- HDR=Yes: 첫 번째 행을 컬럼명으로 인식합니다.
- IMEX=1: 혼합 데이터 형식이 있을 때 텍스트로 읽어 들여 데이터 손실을 방지합니다.
2. OleDb를 활용한 데이터 제어
데이터 읽기
Excel 시트 이름을 테이블명처럼 사용하여 데이터를 조회할 수 있습니다.
public void ReadExcelData(string filePath)
{
string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
adapter.Fill(ds, "ExcelData");
foreach (DataRow row in ds.Tables["ExcelData"].Rows)
{
Console.WriteLine(row["UserName"].ToString());
}
}
}
}
새 파일 생성 및 데이터 삽입
데이터베이스에 테이블을 생성하고 데이터를 삽입하는 문법과 유사합니다.
public void CreateAndInsertExcel(string targetPath)
{
string connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={targetPath};Extended Properties='Excel 12.0;HDR=Yes;'";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
// 테이블(시트) 생성
cmd.CommandText = "CREATE TABLE MemberInfo ([MemberID] VarChar, [MemberName] VarChar)";
cmd.ExecuteNonQuery();
// 데이터 삽입
cmd.CommandText = "INSERT INTO MemberInfo(MemberID, MemberName) VALUES('M001', '홍길동')";
cmd.ExecuteNonQuery();
}
}
}
3. MVC 웹 환경에서의 일괄 업로드 구현
프론트엔드: AJAX를 이용한 파일 전송
파일 데이터를
FormData 객체에 담아 비동기 전송합니다.
<div class="upload-container">
<input type="file" id="excelFile" name="excelFile" />
<button type="button" id="btnUpload">데이터 업로드</button>
</div>
<script>
$("#btnUpload").click(function () {
var fileInput = document.getElementById("excelFile");
if (fileInput.files.length === 0) return;
var formData = new FormData();
formData.append("uploadedFile", fileInput.files[0]);
$.ajax({
url: "/Data/ProcessExcel",
type: "POST",
data: formData,
contentType: false, // multipart/form-data 처리를 위해 필수
processData: false, // 데이터를 쿼리 문자열로 변환하지 않음
success: function (res) {
alert("처리 완료: " + res);
}
});
});
</script>
백엔드: 파일 처리 및 DB 연동
서버에 업로드된 파일을 임시 저장한 후 OleDb로 읽어 비즈니스 로직에 따라 DB에 저장합니다.
[HttpPost]
public JsonResult ProcessExcel()
{
try
{
HttpPostedFileBase file = Request.Files["uploadedFile"];
if (file == null || file.ContentLength == 0) return Json("파일이 없습니다.");
string extension = Path.GetExtension(file.FileName).ToLower();
if (extension != ".xls" && extension != ".xlsx") return Json("지원하지 않는 형식입니다.");
// 서버 내 임시 경로 설정
string tempPath = Server.MapPath("~/App_Data/Uploads/");
if (!Directory.Exists(tempPath)) Directory.CreateDirectory(tempPath);
string fullPath = Path.Combine(tempPath, Guid.NewGuid().ToString() + extension);
file.SaveAs(fullPath);
// OleDb 연결 설정
string provider = (extension == ".xls")
? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"
: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
using (OleDbConnection conn = new OleDbConnection(provider))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand($"SELECT * FROM [{sheetName}]", conn);
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
// 여기서 서비스 레이어나 리포지토리를 호출하여 DB 저장 로직 수행
// Example: _memberService.Register(row["ID"].ToString(), row["Name"].ToString());
}
}
}
return Json("성공적으로 데이터를 가져왔습니다.");
}
catch (Exception ex)
{
return Json("오류 발생: " + ex.Message);
}
}
주의사항
시스템에서
Microsoft.ACE.OLEDB.12.0 프로바이더를 인식하지 못할 경우 "로컬 컴퓨터에 등록되지 않았습니다"라는 오류가 발생할 수 있습니다. 이 경우
Microsoft Access Database Engine을 서버 운영체제 비트(x86/x64)에 맞춰 설치해야 합니다. 또한, IIS 프로젝트 설정에서 애플리케이션 풀의 '32비트 응용 프로그램 허용' 옵션이 프로바이더와 일치하는지 확인이 필요합니다.