C#에서 OLEDB를 이용한 엑셀(EXCEL)파일 읽기 및 쓰기
OleExcelReader
엑셀파일을 읽는 클래스이다.
앞서 글에서도 언급을 했지만 OleDB를 이용한 방법은 다른 것보다 굉장한 속도를 자랑한다..^^;
단순히 읽거나 쓰거나 하는 거라면...이 방법을 사용하기 바란다.
다른 DB프로바이더와 마찬가지로 연결개체를 만들고(Connection) 만들어진 연결로 열고(Open) 데이터를 조작(Excute~)하고 마지막으로 닫는 (Close) 순서로 진행된다.
하나씩 살펴 보도록 하겠다.
먼저 Connection을 살펴 보자
261 private string ExcelConnection()
262 {
263 return
264 @"Provider=Microsoft.Jet.OLEDB.4.0;" +
265 @"Data Source=" + this.fileName + ";" +
266 @"Extended Properties=" + Convert.ToChar(34).ToString() +
267 @"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).ToString();
268 }
기존의 sql 프로바이더와 비슷한 구조이다.
Provider가 Jet.OLEDB 라는거 빼면..비슷한 구조..^^;
Data Source는 출력할 파일의 이름을 선택하면 되겠다.
247 private string ExcelConnectionOptions()
248 {
249 string strOpts = "Imex=" + this.imex + ";";
250 if (this.hasHeaders)
251 {
252 strOpts += "HDR=Yes;";
253 }
254 else
255 {
256 strOpts += "HDR=No;";
257 }
258 return strOpts;
259 }
추가로 헤더의 유무를 선택 할 수 있으며 IMEX 값을 선택 할 수 있다.
IMEX라는 속성은 중요하다..^^;
예전에 데브피아 팁게시판에 올려 놓은 링크를 보게 되면..^^;
http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=18&MAEULNo=8&no=986&ref=986
MSDN에 의하면 이 설명은 아래와 같다.
NOTE: Setting IMEX=1 tells the driver to use Import! mode. In this state, the registry setting Import!MixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT! mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.
The possible settings of IMEX are:
이 클래스는 Reader이므로 imex의 기본값은 1이 되겠다.
만약 Writer라면 ..당연히 0이 될것이다.
그 외의 속성은 첨부된 소스 코드를 참고 하도록 한다.
이제 생성된 ConnectionString으로 connection 개체를 만들고 Open 하도록 한다.
289 this.con = new OleDbConnection(ExcelConnection());
290 this.con.Open();
이제 Select 할 Command 개체를 만들어 보겠다.
313 private bool SetSheetQuerySelect()
314 {
315 try
316 {
317 if (this.con == null)
318 {
319 throw new Exception("Connection is unassigned or closed.");
320 }
321
322 if (this.sheetName.Length == 0)
323 {
324 throw new Exception("Sheetname was not assigned.");
325 }
326
327 string cmd = "";
328 for (int i = 0; i < this.sheetName.Length; i++)
329 {
330 cmd += @"SELECT * FROM [" + this.sheetName[i] + "$" + this.SheetRange[i] + "]" + System.Environment.NewLine;
331 }
332 this.selectCmd = new OleDbCommand(cmd, con);
333 return true;
334 }
335 catch (Exception ex)
336 {
337 throw ex;
338 }
339 }
330 라인에 주목하자.
SQL에서 사용하는 쿼리와 같은 형식의 SELECT 문이다.
해당 시트의 영역에서 컬럼들을 SELECT 한다는 의미가 되겠다.
이러한 쿼리로 332라인의 OleCommand 개체를 만들었다.
이제 쿼리를 실행만 하면 될것이다.
382 //Fill table
383 OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
384 this.selectCmd.CommandTimeout = 200;
385 oleAdapter.SelectCommand = selectCmd;
386 DataTable dt = new DataTable(strTableName);
387 oleAdapter.FillSchema(dt, SchemaType.Source);
388 oleAdapter.Fill(dt);
389 if (this.hasHeaders == false) //헤더가 없으면 임의로 컬럼명을 생성한다.
390 {
391 if (this.sheetRange[0].IndexOf(":") > 0)
392 {
393 string FirstCol = this.sheetRange[0].Substring(0, this.sheetRange[0].IndexOf(":") - 1);
394 int intCol = this.ColNumber(FirstCol);
395 for (int intI = 0; intI < dt.Columns.Count; intI++)
396 {
397 dt.Columns[intI].Caption = ColName(intCol + intI);
398 }
399 }
400 }
401 this.SetPrimaryKey(dt);
위 코드는 GetDataTable() 메서드의 일부이다.
OleDbDataAdpater 개체를 만들어서 388 라인처럼 Fill 하게 되면 dt 라는 데이터 테이블에 SELECT 한 데이터가 채워지게 된다.
구조는..간단하지 않은가????
이제 사용 사례를 살펴 보자..
1 OleExcelReader reader = new OleExcelReader();
2 reader.Filename = filePath;
3 reader.HasHeaders = true;
4 reader.KeepConnectionOpen = false;
5 reader.PkColIndex = 0;
6 string[] sheetNames = reader.GetExcelSheetNames();
7 if (sheetNames.Length < 1)
8 {
9 exceptionMessage = "적어도 한개 이상의 Sheet가 있어야 합니다.";
10 returnDataSet = null;
11 return false;
12 }
13 else
14 {
15 reader.SheetName = new string[] { "List", "Staphylococcus", "E.coli", "Salmonella", "Shigella" };
16 returnDataSet = reader.GetDataSet();
17
18 if (returnDataSet.Tables.Count != 5)
19 {
20 exceptionMessage = "시트의 수가 맞지 않습니다.";
21 return false;
22 }
23 }
6라인의 GetExcelSheetNames()라는 메서드로 해당 엑셀파일의 시트 이름의 배열을 얻을 수 있다.
이 배열로 해당 엑셀 파일이 몇개의 시트를 가지고 있는지 판단 할 수 있게 된다.
15라인을 보게 되면 SheetName 이라는 속성에 출력할 시트의 이름들의 배열을 할당 하는 것을 볼 수 있다.
그리고 나서 16라인의 GetDataSet() 메서드로 엑셀로 부터 쿼리해온 5개의 시트를 하나의 데이터셋으로 Fill 하는 작업을 할수 있다.
그외의 자잘한 작업이 있긴 하지만..
알고보면 아주..간단한 작업이 되겠다.
제공되는 샘플 소스는..꽤..지저분하다..
내가 처음부터 만든것도 아닐 뿐더러...불필요한 메서드 들도 꽤 있다...ㅡ.ㅡ;;;
혹시나 필요한 부분만 발췌해서 사용하고자 한다면.. 조금만 분석에 시간을 들여 보길..권한다.
다음은 OleExcelWriter에 대해서 간략히 알아 보자.
OleExcelWriter
ㅋ..내가 미쳤나 보다..ㅡ.ㅡ;
예제 코드를 만들고야 말았다..
이번 강좌는..이 예제 코드 중심으로 진행을 할 것이다..@.@
그러므로..예제 프로그램을 다운 받아 실행 해보면서 보는게...이해가 빠를 것이다...라고 생각된다.^^;
이 클래스의 사용 순서는 이렇다.
- 파일을 연다. (Open)
- 컬럼을 생성한다.
- 시트를 생성한다.
- 각 시트별 데이터를 인서트 한다. -> 인서트 할때 마다 이벤트를 발생시킨다.
- 파일을 닫는다.
앞서 살펴 보았던 Reader와 비슷하다.
18 public delegate void RowCount(object sender, RowCountEventArgs e);
19 public event RowCount OnRowCount;
하나의 row가 인서트 될때 마다 이벤트를 일으켜 클라이언트에게 알려 주기 위해 델리게이트와 이벤트를 만들었다.
RowCountEventArgs 클래스는 다음과 같이 정의 된다.
11 public class RowCountEventArgs : System.EventArgs
12 {
13 private int rowCount = 0;
14 private string tableName = "";
15
16 ///
17 /// Initializes a new instance of the
18 ///
19 /// The row count.
20 public RowCountEventArgs(int rowCount)
21 {
22 this.rowCount = rowCount;
23 }
24
25 ///
26 /// Initializes a new instance of the
27 ///
28 /// The row count.
29 /// Name of the table.
30 public RowCountEventArgs(int rowCount, string tableName)
31 {
32 this.rowCount = rowCount;
33 this.tableName = tableName;
34 }
35
36 ///
37 /// Gets the name of the table.
38 ///
39 ///
40 public string TableName
41 {
42 get
43 {
44 return this.tableName;
45 }
46 }
47
48 ///
49 /// Gets the row count.
50 ///
51 ///
52 public int RowCount
53 {
54 get
55 {
56 return this.rowCount;
57 }
58 }
59 }
tableName과 rowCount 라는 필드를 만든 이유는 필요에 따라서 몇번째 테이블의 몇번째 row인지 보여줄 필요가 있겠다 싶어서 였다.
하나의 시트 라면 tableName은 필요 없는...게 되겠다. 생성자를 선택해서 사용하면 될것이다.
ConnectionString은 앞서 설명한 OleExcelReader와 같다. 단지 앞에서 언급한 대로 IMEX 속성의 값만 다를 뿐이다..기본값인 0을 유지 하기 바란다..
145 public void Open()
146 {
147 try
148 {
149 if (con != null)
150 {
151 if (con.State == ConnectionState.Open)
152 {
153 con.Close();
154 }
155 con = null;
156 }
157
158 if (System.IO.File.Exists(this.fileName))
159 {
160 System.IO.File.Delete(this.fileName);
161 //throw new Exception("파일이 이미 존재 합니다.");
162 }
163 con = new OleDbConnection(ExcelConnection());
164 con.Open();
165 }
166 catch (Exception ex)
167 {
168 throw ex;
169 }
170 }
161라인의 파일의 존재 여부는 상황에 따라서..달라질듯 하지만..일단은 기존 파일은 삭제를 하는 방향으로 하였다.
본 예제처럼 SaveFileDialog 컨트롤을 사용하게 된다면..별 상관 없는 문제이지만 이 컨트롤을 사용하지 않거나 웹에서 사용하거나 하는 경우에는..문제가 생길 것이다..
웹에서 사용하는 경우에는 이 때문에..파일명을 시간으로 정해주는 것도 좋다..혹은 유일성을 보장하는 GUID 값이라든지...
두번째로는 컬럼을 생성하는 부분이다.
41 string[] colType = new string[3];
42
43 colType[0] = "int";
44 colType[1] = "varchar(16)";
45 colType[2] = "datetime";
Form1.cs 파일에서 보여지는 위 예처럼 코드로 직접 이렇게 컬럼의 형식을 입력해야 하는 이유는..C#의 형식과 SQL의 형식을 매칭 시킬수가 없기 때문이다.ㅡ.ㅠ;
int형과 datetime 형의 경우는 매칭을 시킬수가 있다고 친다지만..44라인의 varchar(16)은 c#의 string형을 변환 한 것이다..
이 때문에 만약 컬럼수가 많은 엑셀 파일의 경우에는...코드가 좀 많아진다는 단점이 있겠다..ㅡ.ㅠ;
string 형은 여러가지 sql 타입으로 변환 할 수 있다..varchar(사이즈), text 등...
그렇기에 이런식으로 string[] 로 타입을 순서대로..가진 List
이 속성에 있는 string[] 은 하나의 시트에 들어 있는 컬럼의 배열을 뜻한다.
그러므로 시트가 여러개 있는 엑셀파일이라면 그 시트의 수가 곧 ColTypeList.Count 가 되겠다.
세번째로 이제 시트를 생성할 차례이다.
시트는 SQL 쿼리문의 CREATE TABLE 로 생성한다.
197 public void CreateSheet()
198 {
199 for (int i = 0; i < this.curDs.Tables.Count; i++)
200 {
201 this.Command("CREATE TABLE [" + this.curDs.Tables[i].TableName + "] (" + this.CreatColName(this.curDs.Tables[i], i) + ")");
202 }
203 }
위 구문은 CREATE TABLE ["시트명"] ( '컬럼명', '컬럼명',......) 이라는 쿼리문을 만들어 주어 쿼리(Command메서드)를 하는 메서드이다.
문맥으로 보아 CreateColName 이라는 메서드는 ( '컬럼명', '컬럼명',......) 라는 문자열을 만들어 주는 메서드라는 것을 짐작해 볼 수 있다.
네번째로 각 시트별 데이터를 인서트 할 차례이다.
250 public void Binding()
251 {
252 foreach (System.Data.DataTable dt in this.curDs.Tables)
253 {
254 string col = "";
255 string resultCol = "";
256 foreach (DataColumn c in dt.Columns)
257 {
258 col += "[" + c.ColumnName + "],";
259 }
260 if (col.EndsWith(","))
261 {
262 resultCol = col.Substring(0, col.Length - 1);
263 }
264
265 for (int i = 0; i < dt.Rows.Count; i++)
266 {
267 this.Command(@"Insert Into [" + dt.TableName + "] (" + resultCol + ") values (" + this.CreateRowForInsert(dt, i) + ")");
268 this.OnRowCount(this, new RowCountEventArgs(++this.rowCount));
269 }
270 }
271 }
252 라인의 foreach문을 보면 각각의 시트(DataTable)마다 인서트 하겠다는 것을 알 수 있다.
256~263라인은 인서트 할 컬럼을 만드는 것이다.
267라인에 보면 INSERT INTO [테이블명] (컬럼명, 컬럼명, ....) VALUES (값, 값, ....) 형태의 인서트 구문을 만들어 쿼리 하는 것을 알 수 있다.
마지막으로 268 라인을 보면 이 강좌의 처음에 보여 주었던 이벤트를 발생 시키는 것을 볼 수 있다.
이 이벤트의 매개변수로는 ++rowCount를 보낸다. 즉 하나의 라인을 인서트 한 후에 그 인서트한 행의 Count를 매개변수로 던지는 것이다.
그렇게 되면 클라이언트에서는 이 이벤트를 받아서 현재 몇번째의 데이터가 인서트 되어 있는지 알 수 있을 것이다.
자..이제 예제를 살펴 보도록 하겠다.(Form1.cs)
예제에는 실제로 progressbar와 label을 만들어서 현재 인서트 진행 상황을 보여주게끔 만들었다.
아직 event와 delegate에 익숙하지 않은 분들도 약간의 참고가 될 수 있으리라 생각된다.
16 private delegate void CurrentRowEventHandler(int rowIndex);
17 private CurrentRowEventHandler rowCount;
18 private DataSet ds = new DataSet();
16 라인을 보면 CurrentRowEventHandler 라는 델리게이트를 만들었고 17 라인에 이 델리게이트 형의 rowCount 라는 개체를 만들었다.
이 델리게이트는 OnRowCount 라는 이벤트를 받아서 그 때마다 UI 스레드에게 CallBack 하여 Label 컨트롤과 ProgressBar컨트롤을 작동시키기위해 필요한 존재이다.
그리고 18행은 예제에서 사용될 DataSet이다.
65 private void Form1_Load(object sender, EventArgs e)
66 {
67 this.rowCount = this.CallBackRowCount;
68 this.MakeDataSet();
69 }
Form 이 Load가 되면 rowCount 라는 델리게이트는 CallBackRowCount 라는 델리게이트메서드에 연결을 시킨다.
그리고 DataSet에 데이터를 채운다..
DataSet에는 2개의 테이블이 있으며 각각 int 형, string 형, datetime 형의 컬럼을 가지고 있으며 데이터는 500개씩 이다.
DataSet에 데이터를 채우는 내용은..예제를 참고 하도록 한다..
자 이제 버튼을 클릭하게 되면...스레드를 만들고 스레드를 시작한다.
이 스레드는 MakeExcelFile 이라는 메서드를 호출한다.
이제 이 MakeExcelFile 이라는 메서드를 살펴 보자.
34 private void MakeExcelFile()
35 {
36 OleExcelWriter excel = new OleExcelWriter();
37 excel.OnRowCount += new OleExcelWriter.RowCount(excel_OnRowCount);
38
39 excel.FileName = this.saveFileDialog.FileName;
40 excel.HasHeader = true;
41 List<string[]> colList = new List<string[]>(2);
42 string[] colType = new string[3];
43
44 colType[0] = "int";
45 colType[1] = "varchar(16)";
46 colType[2] = "datetime";
47
48 colList.Add(colType);
49 colList.Add(colType);
50
51 excel.ColTypeList = colList;
52 excel.CurDs = this.ds;
53 excel.KeepConnectionOpen = true;
54 excel.CreateSheet();
55 excel.Binding();
56 excel.Dispose();
57 excel.Close();
58 }
36라인에서 OleExcelWriter의 개체를 생성하고 37라인에서 이벤트를 등록 하였다.
40~51 라인은 컬럼을 생성하여 ColTypeList 속성에 대입시키는 코드이다. 앞에서 설명 했었다.
52 라인은 DataSet을 CurDs 라는 속성에 대입한다. 그리고 54 라인에서 시트를 생성하고 55라인에서 데이터를 인서트 하며, 56,57 라인에서 Close 하게 된다.
excel_OnRowCount 메서드를 살펴 보자면.
60 void excel_OnRowCount(object sender, RowCountEventArgs e)
61 {
62 this.Invoke(this.rowCount, e.RowCount);
63 }
단순히 Invoke 만 했다.
Invoke 의 첫번째 매개변수는 rowCount 라는 델리게이트이며, 두번째 인자는 RowCountEventArgs.RowCount 이다.
이 델리게이트를 호출(Invoke) 하게 되면 Form1_Load 이벤트 메서드에서 설정한 CallBackRowCount 메서드가 실행된다.
71 private void CallBackRowCount(int rowIndex)
72 {
73 this.lblMessage.Text = String.Format("현재 {0}번째의 데이터를 쓰고 있습니다.", rowIndex.ToString());
74 this.progressBar.PerformStep();
75 Thread.Sleep(10);
76 }
매개변수로 받은 rowIndex를 단지 label 컨트롤에 대입하고 progressbar를 performstep 하는 UI 컨트롤에 대한 설정 밖에 없다.
제2 스레드에서 제1 스레드로 Invoke 하게 된 결과이다
75라인은...너무 빨라서 진행상태를 보기에 어렵기에..살짝 텀을 준것 뿐이다...
이로서 간략하지만 OLEDB를 이용한 엑셀 핸들링을 마치도록 하겠다
다음 시간(??)엔 jakarta.POI를 이용한 쓰기 방법을 알아 보자...^^;
Jakarta.POI 를 이용한 Excel Writing
자 이제 마지막 시간이다..^^;
http://jakarta.apache.org/poi/
위 URL을 참조 하면 jakarta.poi 에 대해서 정보를 얻을 수 있다.
이 어셈블리를 사용하기 위해서는 jakarta.poi.dll이 필요하며 웹의 경우 서버에 J#재배포패키지가 설치되어야 한다.
그리고 JSharplib.dll 파일을 참조 추가 하여야 한다.
자 이제 준비가 되었으니 사용법을 알아 보자.
먼저 Enum.cs 파일을 보게 되면 셀과 폰트에서 사용되는 열거형들이 정의되어 있다.
주석은 없지만...ㅡ.ㅡ;;;
외부에 노출되는 타입은 Enum.cs 에 정의되어 있는 열거형들과 ExcelHandler class, ExcelCellStyle class 이다.
ExcelCellSettingBase class는 internal로 정의되어 내부에서만 사용되는 클래스이다.
ExcelHandler 클래스는 엑셀파일을 생성하기위한 기본 클래스이다.
ExcelCellStyle 클래스는 선택된 시트의 셀 영역에 적용할 스타일에 대한 클래스가 되겠다. 이르자면 폰트에 관련된 속성같은 것 말이다.
이 어셈블리의 구조를 먼저 설명을 해야 할 것 같다.
ExcelHandler 클래스는 내부적으로 List
즉 List
이제 이 DataSet의 구조를 살펴 보자.
이 DataSet에는 최소 1개 이상의 DataTable을 가진다.
이 DataTable은 하나의 데이터영역이다.
이르자면..타이틀영역, 빈공간, 데이터 넣는 곳 등등,,,
하나의 시트에 바인딩되는 데이터의 형태는 반드시 DataTable형태가 되어야 하며 이 DataTable들은 여러가지 형태로 시트에 표현이 될 수 있다.
샘플을 보게 되면 2개의 시트가 있는데 첫번째 시트의 경우에는 DataTable이 3개가 있다. 뒷 부분에서 설명하도록 하겠다.
그리고 시트의 설정에 대한 부분도 포함되어 있다. Split이라든지 Group 같은 기능들이 있다.
ExcelCellStyle 클래스는 데이터와 상관없이 셀의 스타일을 정의한다.
이 클래스는 테두리의 선 및 색상정의, 폰트의 종류,색상,크기등의 정의 등등의 속성이 있다.
이 클래스로 만들어진 스타일 인스턴스는 ExcelHandler.SetCellStyle()메서드로 시트에 임베디드된다.
이제 ExcelCellStyle 클래스부터 살펴 보겠다.
위 클래스 다이어그램을 보는 바와 같이 ExcelCellStyle 클래스는 모두 필드와 속성으로만 정의되어 있으며 내부 클래스인 Font 클래스를 가지고 있다.
이 클래스는 FontStyle이라는 속성으로 사용된다.
Align, Rotation, CellColor, Border, Font를 설정할 수 있다.
이제 핵심 클래스인 ExcelHandler 클래스를 살펴보자.
11 using org.apache.poi.hssf.util;
12 using org.apache.poi.hssf.usermodel;
이 어셈블리를 사용하기 위해서는 위처럼 2개의 네임스페이스를 등록하여야 한다.
48 public delegate void RowCount(object sender, RowCountEventArgs e);
49 public event RowCount OnRowCount;
앞서 OleExcelWriter에서 설명한 바와 같이 OnRowCount 라는 이벤트와 델리게이트를 생성하였다.
앞서 설명을 하였기 때문에 패스..
public ExcelHandler();
public ExcelHandler(List<string> sheetName);
public ExcelHandler(string fullName);
public ExcelHandler(string fullName, List<string> sheetName);
public ExcelHandler(string path, string fileName);
public ExcelHandler(string path, string fileName, List<string> sheetName);
생성자는 위와 같이 정의 되어 있다.
2개 이상의 시트가 있거나 시트의 이름을 정해주고자 할 때는 sheetName 매개변수를 이용하도록 한다. 주의사항은 한글이름은 지원이되지 않는다..ㅡ.ㅠ;
789 ///
790 /// 개체 생성
791 ///
792 private void Create()
793 {
794 this.wb = new HSSFWorkbook();
795 foreach (string sheet in this.sheetName)
796 {
797 this.wb.createSheet(sheet);
798 }
799 }
생성자에서는 위의 Create()메서드를 호출하며 이 메서드에서는 마치 Automation과 마찬가지로 워크북을 생성한다음에 sheetName 리스트에서 할당된 갯수만큼 시트를 만든다.
이제 시트를 만들었다면 Data를 입력해야할 차례이다..다음 메서드를 살펴보자.
506 public void InsertTable(int sheetIndex, bool hasTitle, DataTable dt)
507 {
508 if (this.dsList.Count == sheetIndex)
509 {
510 DataSet ds = new DataSet();
511 this.dsList.Add(ds);
512 }
513 this.dsList[sheetIndex].Tables.Add(dt);
514
515 if (this.hasTitleList.Count == sheetIndex)
516 {
517 StringCollection sc = new StringCollection();
518 this.hasTitleList.Add(sc);
519 }
520 this.hasTitleList[sheetIndex].Add(hasTitle.ToString());
521 }
매개변수를 보자면.
- sheetIndex - 데이터를 넣을 시트의 인덱스
- hasTitle - 데이터테이블의 경우 해당 컬럼의 이름을 데이터의 상단에 렌더 할수 있다. 이 제목의 사용 여부이다.
- dt - 시트에 입력될 데이터테이블
처음에 구조를 설명하였듯이...
dsList 라는 제너릭리스트는 하나의 워크북을 의미하고 그 안에 있는 ds 라는 데이터셋은 하나의 시트를 의미하며 그 안에 입력되는 dt는 하나의 데이터영역이다.
hasTitleList는 hasTitle의 bool 값을 저장하고 있는 문자열컬렉션(StringCollection)의 제너릭리스트이다.
dsList와 hasTitleList의 인덱스는 정확히..매칭된다.
530 public void InsertBlankRow(int sheetIndex, int rowCount)
531 {
532 DataTable dt = new DataTable();
533 dt.Columns.Add(new DataColumn());
534 for (int i = 0; i < rowCount; i++)
535 {
536 DataRow dr = dt.NewRow();
537 dr[0] = "";
538 dt.Rows.Add(dr);
539 }
540 this.InsertTable(sheetIndex, false, dt);
541 }
InsertBlackRow() 메서드는 해당 시트에 빈 공간(row)을 삽입하는 메서드이다.
몇 row의 빈칸을 삽입할지 결정하여 호출 하면 540라인에서 보듯이 InsertTable메서드를 호출하며 해당 DataSet(Sheet)에 값이 없는 (스키마만 있는) DataTable을 삽입하게 된다.
이제 데이터를 바인딩 하기 전에 바딩딩 할때 적용되로 스타일을 세팅 하도록 한다.
public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, CellType type);
public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle);
public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle, CellType type);
스타일을 세팅하기위한 메서드는 SetCellStyle 메서드인데 3가지로 overload되어 있다.
매개변수를 살펴 보자.
- sheetIndex - 스타일을 적용할 시트의 인덱스
- startColumnIndex - 스타일을 적용할 영역의 시작 컬럼 인덱스
- endColumnIndex - 스타일을 적용할 영역의 끝 컬럼 인덱스
- startRowIndex - 스타일을 적용할 영역의 시작 로우 인덱스
- endRowIndex - 스타일을 적용할 영역의 끝 로우 인덱스
- cellStyle - ExcelCellStyle의 개체참조
- type - 셀의 타입, 현재 정의된 타입은 string, int, datetime 형 3가지이다.
301 public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle, CellType type)
302 {
303 if (this.wb == null)
304 {
305 throw new Exception("워크북이 생성되지 않았습니다.");
306 }
307 else
308 {
309 if (this.wb.getSheetAt(sheetIndex) == null)
310 {
311 throw new Exception(sheetIndex + "인덱스에 시트가 생성되지 않았습니다.");
312 }
313 else
314 {
315 HSSFCellStyle style = this.wb.createCellStyle();
316 this.AttachStyle(ref style, cellStyle, type);
317 this.settingList.Add(new ExcelCellSettingBase(sheetIndex, startColumnIndex, endColumnIndex, startRowIndex, endRowIndex, style));
318 }
319 }
320 }
위 메서드를 살펴 보자.
먼저 HSSFCellStyle 형의 셀의 스타일 개체를 생성한다.
그리고 ExcelCellStyle 개채의 값을 style 개체에 attatch를 하고 ExcelCellSettingBase 형의 개체를 생성하고 settingList 라는 제너릭리스트에 Add 한다.
ExcelCellSettingBase 클래스는 스타일이 정의된 영역에 대한 정보를 가지고 있다. 그러므로 settingList 라는 제너릭 리스트는 이 스타일이 정의된 영역에 대한 정보를 가진 컬렉션이다.
이제 바인딩을 해보자.
548 public void DataBinding()
549 {
550 if (this.dsList.Count == 0)
551 {
552 throw new Exception("데이터가 없습니다.");
553 }
554 else
555 {
556 //하나의 DataSet이 하나의 시트
557 for (int j = 0; j < dsList.Count; j++)
558 {
559 this.sheetIndex = j;
560 int totalRowCountInSheet = this.SumOfTableRowsCount(j);
561
562 int rowCount = 0;
563
564 //데이터 갯수와 헤더의 갯수(있을거라고 가정)가 65536을 넘을 수 없다.
565 //헤더는 각각의 테이블당 한개의 row를 차지하므로 한개의 시트에 속하는 테이블의 갯수와 동일하다.
566 //하나의 시트의 Row가 65535를 넘게 되면 오류...
567 totalRowCountInSheet += dsList[j].Tables.Count;
568
569 if (totalRowCountInSheet > 65535)
570 {
571 throw new ArgumentOutOfRangeException("데이터의 수가 65535건이 넘었습니다.");
572 }
573 else
574 {
575 for (int i = 0; i < dsList[j].Tables.Count; i++)
576 {
577 if (Convert.ToBoolean(this.hasTitleList[j][i]))
578 {
579 this.CreateTitle(j, dsList[j].Tables[i].Columns, rowCount);
580 rowCount++;
581 }
582 this.DataBinding(dsList[j].Tables[i], rowCount);
583 rowCount += dsList[j].Tables[i].Rows.Count;
584 }
585 }
586 }
587 }
588 }
지금까지 정의 된 값을 토대로 하여 데이터를 바인딩 한다.
560라인의 SumOfTableRowCount()메서드는 해당 시트에 바인딩 될 총 row의 합계이다.
이를 조사하는 이유는..
엑셀의 경우 하나의 시트에 65536행까지만 허용되기 때문이다.
그러나 Excel 2007의 경우에는 1백만행*65536컬럼 까지 지원한다고 한다..
579라인에 보면 CreateTitle()메서드가 보이는데 제목을 가진 DataTable이라면(577라인의 조건절) 제목을 시트에 쓴다.
그리고 582라인의 DataBinding 메서드에서 실제로 시트에 쓴다.
먼저 CreateTitle()메서드를 살펴보자.
745 HSSFSheet sheet = this.wb.getSheetAt(sheetIndex);
746 HSSFRow row = sheet.createRow(rowCount);
747 for (short i = 0; i < dcc.Count; i++)
748 {
749 if (!dcc[i].ColumnName.StartsWith("Column"))
750 {
751 HSSFCell cell = row.createCell(i);
752 cell.setEncoding(1);
753 this.currentRowIndex = rowCount;
754 this.currentColumnIndex = i;
755 ExcelCellSettingBase cellStyle = this.settingList.Find(FindStyle);
756 if (cellStyle != null)
757 {
758 HSSFCellStyle cs = cellStyle.CellStyle;
759 cell.setCellStyle(cs);
760 }
761 cell.setCellValue(dcc[i].ColumnName);
762 }
763 }
발췌했다.
745라인에서 sheet개체를 얻고 746라인에서 createRow()메서드로 row를 만들었다.
749라인의 조건절은 자동생성된 컬럼을 걸르기 위함이다. 자동으로 생성된 컬럼은 의미가 없기 때문이다 . 자동생성된 컬럼의 이름패턴은 Column1, Column2..... 처럼 생성된다.
755라인에 주목하자.
제너릭리스트의 Find()메서드를 사용하였다. 닷넷 2.0에서 새로나온 개념이다..^^;
이 Find 메서드는 settingList 라는 제너릭 리스트에서 해당 조건에 맞는 첫번째 아이템을 반환한다.
806 private bool FindStyle(ExcelCellSettingBase setting)
807 {
808 if (setting.SheetIndex == this.sheetIndex && setting.StartRowIndex <= this.currentRowIndex && setting.EndRowIndex >= this.currentRowIndex && setting.StartColumnIndex <= this.currentColumnIndex && setting.EndColumnIndex >= this.currentColumnIndex)
809 {
810 return true;
811 }
812 else
813 {
814 return false;
815 }
816 }
FindStyle 메서드를 살펴 보면 앞서 정의했던 settingList 제너릭 리스트에서 현재 Cell의 영역이 포함되어 있는 첫번째 ExcelCellSettingBase 개체를 반환한다.
음..한번도 안써보신 분이라면..약간 헤깔릴 수도....
어쨋든 해당되는 부분을 찾았으면 759라인처럼 셀에 스타일을 정의한 후에 761라인 처럼 값을 입력한다.
이제 582라인의 DataBinding 메서드를 살펴 보자.
620 HSSFCellStyle cs = cellStyle.CellStyle;
621 c.setCellStyle(cs);
622 object o = dt.Rows[rowIndexInCurrentTable][currentColumnIndex];
623 if (o.ToString() != "")
624 {
625 switch ((CellType)cs.getDataFormat())
626 {
627 case CellType.CELL_TYPE_NUMERIC:
628 c.setCellValue(Convert.ToDouble(o));
629 break;
630 case CellType.CELL_TYPE_DATETIME:
631 c.setCellValue(new java.util.Date(Convert.ToDateTime(o).ToLongTimeString().Trim()));
632 break;
633 case CellType.CELL_TYPE_STRING:
634 c.setCellValue(o.ToString().Trim());
635 break;
636 }
637 }
638 else
639 {
640 c.setCellValue("");
641 }
역시 발췌했다.
CreateTitle() 메서드와 마찬가지로 Find 메서드로 해당되는 ExcelCellSettingBase 개체를 반환하여 적용한다.
그리고 추가로 작업을 하나 더 하는데..
바로 셀의 타입을 결정하는 부분이다.
셀의 타입 역시 HSSFCellStyle 개체에 포함되어 있는 부분이다.
그렇기에 셀의 형식에 맞게끔 데이터를 형변환해서 입력해주어야 한다.
그 부분이 switch ~case 구문이다.
631라인의 경우에는 datetime형식을 표현하는 방식이 닷넷과 자바가 달라서..저런 방법을 취할수 밖에 없었다...쿨럭. 그리고 마지막으로 이벤트를 발생 시킨다.
655 this.OnRowCount(this, new RowCountEventArgs(rowIndexInCurrentTable + 1, dt.TableName));
지금까지 작업을 하였다면 하나의 워크북에 시트와 데이터가 모두 만들어 졌을 것이다.
이제 약간의 추가 작업을 선택적으로 해주면 되겠다.
추가적인 기능은
public void ColumnWidth(int sheetIndex, short columnIndex, short width);
public void GroupColumn(int sheetIndex, short startColumn, short endColumn);
public void GroupRow(int sheetIndex, int startRow, int endRow);
public void Merge(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex);
public void RowHeight(int sheetIndex, int startRowIndex, int endRowIndex, int height);
public void Split(int sheetIndex, int colSplit, int rowSplit);
위와 같다.
- ColumnWidth - 컬럼의 폭을 결정한다, 모든 단위는 px 단위이다.
- GroupColumn - 컬럼을 그룹화한다.
- GroupRow - 로우를 그룹화한다.
- Merge - 정의된 셀영역을 합친다.
- RowHeight - 로우의 높이를 결정한다.
- Split - 문서를 스플릿한다.
주의 할 것은 이 메서드들은 반드시 바인딩 후에 호출하여야 한다.는 것이다.
이제 마지막 단계이다.
이렇게 워크북을 만들었다면..이제 출력을 해야 할것이다.
출력은 2가지가 지원한다.
하나는 파일 출력이며 하나는 스트림출력이다.
664 public void OutPut()
665 {
666 string fPath = this.fullName;
667 if (fPath == "")
668 {
669 fPath = this.path + this.fileName;
670 }
671 using (Stream fileOut = new FileStream(fPath, FileMode.Create))
672 {
673 this.wb.write(fileOut);
674 }
675 }
OutPut()메서드는 파일 출력이다. 673라인의 write() 메서드로 정의된 파일명으로 파일이 생성된다.
윈도우즈 응용프로그램에서 사용하기 적합하다.
681 public byte[] OutPutToWeb()
682 {
683 Stream fileOut = new MemoryStream();
684 this.wb.write(fileOut);
685 byte[] buffer = ((MemoryStream)fileOut).GetBuffer();
686 fileOut.Close();
687 return buffer;
688 }
이 메서드는 byte[] 배열형으로 출력한다.
웹의 경우 서버에 파일을 만들필요가 없기에 스트림으로 내려 주는 방식이 좋다. 샘플 예제는 웹방식으로 적용해 보았다.
지금까지...어셈블리에 대해서 알아 보았다.
이제 샘플코드를 보고..어떻게 사용하는지 알아보자.
23 List<string> sheetNames = new List<string>(2);
24 sheetNames.Add("sheet1");
25 sheetNames.Add("sheet2");
26
27 ExcelHandler handler = new ExcelHandler(sheetNames);
28 handler.OnRowCount += new ExcelHandler.RowCount(handler_OnRowCount);
29
30 handler.InsertTable(0, false, this.MakeTitle());
31 handler.InsertBlankRow(0, 3);
32 handler.InsertTable(0, true, this.MakeDataTable("테스트"));
33 handler.InsertTable(1, false, this.MakeTitle());
34 handler.InsertTable(1, true, this.MakeDataTable("테스트"));
35
36 handler.SetCellStyle(0, 0, 0, 0, 0, this.MakeCellStyle1());
37 handler.SetCellStyle(0, 0, 0, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_NUMERIC);
38 handler.SetCellStyle(0, 1, 1, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_STRING);
39 handler.SetCellStyle(0, 2, 2, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_DATETIME);
40
41 handler.DataBinding();
42
43 handler.RowHeight(0, 0, 0, 100);
44 handler.ColumnWidth(0, 0, 300);
45 handler.Merge(0, 0, 2, 0, 0);
46 handler.Split(0, 0, 5);
47 handler.GroupRow(0, 5, 10);
48
49 //파일 다운로드
50 this.Response.Clear();
51 this.Response.AddHeader("Content-Disposition", "attachment; filename=exceltest.xls");
52 this.Response.ContentType = "application/vnd.ms-excel ";
53 this.Response.BinaryWrite(handler.OutPutToWeb());
54 this.Response.End();
22~25 라인에서 시트의 이름을 생성했다. 다시 말하지만 한글은 지원하지 않는다.
27라인에서 생성자를 만들고
28라인에서 이벤트를 등록하였다.(웹에서는 사실 이 이벤트가 소용없으나 선언된것이기 때문에 정의하지 않으면 예외를 던진다.)
30~34라인에서 DataTable을 각각의 시트에 입력하였다. 첫번째 시트에는 공란도 입력하였다.
36~39 라인에서 스타일과 타입을 정의 하였다. 영역이 중복되어도 상관없지만..먼저 정의된것만 적용된다.^^; 테스트를 위하여 각각 숫자형, 문자형, 일시형을 정의해 보았다.
그리고 41라인 처럼 바인딩을 한다.
43~47라인에는 시트의 설정을 변경해 보았다. 어떻게 출력이 될까 하고 예측해보자...
50~54 라인에서는 웹상에서 출력하는 방법을 보여준 예이다.
주목 할 것은 바로 53라인이다.
Response.BinaryWriter() 메서드로 출력하는 것을 확인하기 바란다.
이제 샘플코드를 실행해서 엑셀파일을 다운 받아 보면..아래와 같은 결과가 나오겠다.
그리고 ..
마지막으로 스타일을 정의 하는 부분은 같이 업로드된 파일 처럼 코드스니핏을 이용하면 편하다.
혹시 이용법을 모르는 사람을 위해..
첨부된 excelStyle.snippet 파일을 C:\Documents and Settings\사용자이름\My Documents\Visual Studio 2005\Code Snippets\Visual C#\My Code Snippets 경로에 붙여 넣기를 한다.
그리고 VS2005를 재시동한다.
이제 화면에서 excel이라고 치면 인텔리센스가 뜬다...그게 코드 스니핏이다.
[출처] C#에서 OLEDB를 이용한 엑셀(EXCEL)파일 읽기 및 쓰기|작성자 이카루스
'Programming > C#' 카테고리의 다른 글
| Excel Data Reader - Read Excel files in .NET (0) | 2013.06.28 |
|---|---|
| Excel 2007,2003 OLEDB 연결 문자열 (0) | 2012.12.16 |
| var 키워드 (0) | 2012.12.09 |
| Loaded(로드완료) 이벤트 (0) | 2012.07.16 |
| 윈폼기반 프로그래밍을 할때 Invoke() 이쁘게쓰기! (0) | 2012.06.10 |