DataSet을 엑셀파일로 만드는 함수 (DataSet to Excel)
/// <summary> /// 데이터셋 엑셀 저장 /// </summary> /// <param name="PathName">파일이 저장될 경로</param> /// <param name="ds">저장될 데이터 셋</param> public void ExportDataSetToExcel(string PathName, ref DataSet ds) { Excel.Application App = new Excel.Application(); Excel.Workbook wBook; Excel.Worksheet wSheet; Excel.Range wRange; Excel.Border wBorder; int col = 0; try { App.UserControl = true; wBook = (Excel.Workbook)(App.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)); //현재 활성중인 sheet for (int i = 0; i <= ds.Tables.Count - 1; i++)//테이블 갯수 만큼 Sheet 추가 { wRange = null; wSheet = (Excel.Worksheet)wBook.Sheets[i+1]; wRange = wSheet.get_Range("A3", Missing.Value); wRange = wRange.get_Resize(ds.Tables[i].Rows.Count+1, col); wBorder = wRange.Borders[Excel.XlBordersIndex.xlEdgeRight]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; wBorder = wRange.Borders[Excel.XlBordersIndex.xlEdgeTop]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; wBorder = wRange.Borders[Excel.XlBordersIndex.xlInsideVertical]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; wBorder = wRange.Borders[Excel.XlBordersIndex.xlEdgeBottom]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; wBorder = wRange.Borders[Excel.XlBordersIndex.xlEdgeLeft]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; wBorder = wRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal]; wBorder.LineStyle = Excel.XlLineStyle.xlContinuous; wBorder.ColorIndex = 1; if (i != ds.Tables.Count-1) al.RemoveRange(0, col+1); col = 0; if(i < ds.Tables.Count-1) wBook.Sheets.Add(Type.Missing, wSheet, Type.Missing, Type.Missing); //테이블이 남아있으면 시트추가 } wSheet = (Excel.Worksheet)wBook.Sheets[1]; wSheet.Activate(); App.DisplayAlerts = false; DevExpress.Utils.WaitDialogForm dlg; dlg = new DevExpress.Utils.WaitDialogForm("엑셀로 저장중입니다..."); try { wBook.SaveAs(PathName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing); } finally { dlg.Close(); MessageBox.Show("저장하였습니다", "확인", MessageBoxButtons.OK); } wBook.Close(false, Type.Missing, Type.Missing); } catch(Exception ee) { MessageBox.Show(ee.ToString()); } finally { App.Quit(); if (App != null) { Process[] pProcess; pProcess = System.Diagnostics.Process.GetProcessesByName("Excel"); pProcess[0].Kill(); } } }