닷넷 프로그램을 개발할 때 엑셀을 불러와서 DataTable에 채우는 작업은 정말 많이 하는 작업입니다.
이렇게 가져온 DataTable을 다시 가공을 하거나 GridView 컨트롤에 바인딩해서 화면에 보여주기도 합니다.
인터넷을 검색해 보면 .NET에서 엑셀을 처리하는 방법이 정말 다양하게 많이 나와 있습니다.
.NET에서 Excel을 처리하기 쉽도록 만들어 놓은 라이브러리도 많이 나와 있습니다. 기본적인 기능을 제공하는 무료 라이브러리(API)도 있고, 파워풀한 기능을 제공하는 유료 라이브러리(API)도 많이 찾아 볼 수 있습니다. 빠른 처리 속도가 필요하거나 다양한 처리 방법이 필요한 경우에는 유료 라이브러리(API)를 검토해 보시는 것도 좋아 보입니다.
이번에 소개하는 포스팅 글은 무료로 사용할 수 있는 NPOI 라이브러리(API)를 사용하여 엑셀 통합 문서의 여러 시트를 DataTable로 한번에 가져오는 방법에 대해 알아보겠습니다.
Apache POI Java API의 .NET 버전인 NPOI는 xls, doc 및 ppt 파일을 읽거나 쓸 수 있는 오픈 소스 API입니다.
https://www.nuget.org/packages/NPOI/이나 https://github.com/nissl-lab/npoi 에서 라이브러리(DLL)이나 소스를 구할 수 있습니다.
C# 엑셀 문서의 여러 시트를 DataTable로 한번에 가져오는 방법(NPOI 참조)
▶ 아래 화면은 엑셀 시트 화면(위)과 C# 프로그램에서 DataTable에 엑셀 시트 내용을 가져온 화면(아래) 입니다.
▼ 아래는 엑셀 시트 화면입니다.
▼ 아래는 엑셀 시트를 DataTable로 가져온 화면입니다.
C#에서 NPOI를 사용하여 엑셀 파일을 DataTable로 가져오는 예제 소스
1. C# 프로그램에서 NPOI를 사용하기 위해서는 라이브러리 참조가 필요합니다.
2. NPOI.dll, NPOI.OOXML.dll, NPOI.OpenXml4Net.dll, NPOI.OpenXmlFormats.dll 이렇게 4개의 dll 파일을 참조해 주면 됩니다.
3. 참조 DLL의 속성이나 메서드를 편하게 사용하기 위해 using 문에 추가해 줍니다.
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
– 참고로 XSSF는 xlsx 파일을 사용할 때 필요하고, HSSF는 xls 파일을 사용할 때 필요합니다.
▼ 아래는 실제 C# 프로그램 소스입니다.
|
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
private ISheet sheet; //전역변수 선언
private IRow row;
private void btnImportExcel_Click(object sender, EventArgs e)
{
string strFilename = “”;
OpenFileDialog dlgFile = new OpenFileDialog();
dlgFile.Filter = “Xls files (*.xls)|*.xls|Xlsx files (*.xlsx)|*.xlsx|All files (*.*)|*.*”;
dlgFile.FilterIndex = 2;
DialogResult dlgResult = dlgFile.ShowDialog();
if (dlgResult == DialogResult.OK)
{
strFilename = Path.GetFullPath(dlgFile.FileName);
}
if (strFilename == “”)
{
MessageBox.Show(“엑셀 파일을 선택해 주세요.”);
return;
}
// 엑셀의 시트 인덱스(첫번째 시트=0, 두번째=1,…)
int sheetNum = 0;
// DataTable에 엑셀 시트 채움
DataTable dtExcelData = ImportExcel(strFilename, sheetNum);
//엑셀 시트의 컬럼수와 DataTable의 컬럼수를 체크해서 이상여부 확인
if (dtExcelData.Columns.Count != 4)
{
MessageBox.Show(“올바른 시트의 컬럼수는 4개 입니다.”);
return;
}
foreach (DataRow dr in dtExcelData.Rows)
{
if (dr[0].ToString() != “”) // 첫번째 컬럼(열)이 공백인 아닌 경우. (무결성 체크)
{
string column1 = dr[0].ToString().Trim(); //컬럼(열)1
string column2 = dr[1].ToString().Trim(); //컬럼(열)2
string column3 = dr[2].ToString().Trim(); //컬럼(열)3
string column4 = dr[3].ToString().Trim(); //컬럼(열)4
}
}
}
private DataTable ImportExcel(string strFilename, int sheetNum)
{
FileStream str = new FileStream(strFilename, FileMode.Open, FileAccess.Read);
//엑셀의 확장명이 xls일때와 xlsx일때 구분
if (strFilename.Substring(strFilename.LastIndexOf(‘.’)).ToLower() == “.xlsx”)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(str);
sheet = xssfworkbook.GetSheetAt(sheetNum);
}
else
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(str);
sheet = hssfworkbook.GetSheetAt(sheetNum);
}
str.Close();
str.Dispose();
DataTable dt = new DataTable();
IRow headerRow = sheet.GetRow(0); //첫번째 Row을 헤더 Row로
IEnumerator rows = sheet.GetRowEnumerator();
int colCount = headerRow.LastCellNum; //컬럼(열)의 갯수
int rowCount = sheet.LastRowNum; //행(Row)의 갯수- 참고용
for (int c = 0; c < colCount; c++) //컬럼(열)의 갯수만큼 DataTable의 Columns 생성
dt.Columns.Add(headerRow.GetCell(c).ToString()); //헤더 Row를 사용하여 Columns 생성
rowCount = 0;
while (rows.MoveNext())
{
if (rowCount > 0) //첫번째 Row는 헤더 Row이므로 DataTable에 행(Row)을 추가하지 않음
{
DataRow dr = dt.NewRow();
if (strFilename.Substring(strFilename.LastIndexOf(‘.’)).ToLower() == “.xlsx”)
row = (XSSFRow)rows.Current; //xlsx인 경우
else
row = (HSSFRow)rows.Current; //xls인 경우
for (int i = 0; i < colCount; i++) //Row의 Column을 채우는 과정
{
ICell cell = row.GetCell(i);
if (cell != null)
dr[i] = cell.ToString();
}
dt.Rows.Add(dr);
}
rowCount += 1;
}
return dt;
}
|
C# 소스 프로그램에 대한 추가 설명
– 엑셀 파일의 확장명이 .xlsx일 때와 .xls일 때 처리 약간의 구문 차이가 있습니다.(중요)
– 엑셀의 시트 인덱스는 0부터 시작합니다. 첫 번째 시트=0, 두 번째 시트=1, ….
시트의 인덱스 번호만 바꿔가면서 한번에 여러 시트를 DataTable로 가져올 수 있습니다.
– 예제 소스에서는 첫 번째 행을 헤더(제목) 행으로 보고, DataTable에 첫 번째 행은 추가되지 않도록 처리했습니다.
– 소스에 포함된 주석을 참고하시면 쉽게 이해할 수 있습니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
이 글이 도움이 되었기를 바랍니다. ^-^