[C#] DataSet을 엑셀파일로 변환

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();
        }
    }
}

댓글 남기기