첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
유용한 소스 코드가 있으면 icodebroker@naver.com으로 보내주시면 감사합니다.
블로그 자료는 자유롭게 사용하세요.

■ DataGrid 클래스 : 엑셀 파일 내보내기

------------------------------------------------------------------------------------------------------------------------


TestProject.zip


ExcelExportHelper.cs

 

 

using System;

using System.Collections.Generic;

using System.Reflection;

 

namespace TestProject

{

    /// <summary>

    /// 엑셀 내보내기 헬퍼

    /// </summary>

    /// <typeparam name="TEntity">개체 타입</typeparam>

    /// <typeparam name="TList">목록 타입</typeparam>

    public class ExcelExportHelper<TEntity, TList> where TEntity : class where TList : List<TEntity>

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Field

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region Field

 

        /// <summary>

        /// 리스트

        /// </summary>

        public List<TEntity> List;

 

        #endregion

 

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region Field

 

        /// <summary>

        /// 응용 프로그램

        /// </summary>

        private Microsoft.Office.Interop.Excel.Application application = null;

 

        /// <summary>

        /// 워크북들

        /// </summary>

        private Microsoft.Office.Interop.Excel.Workbooks workbooks = null;

 

        /// <summary>

        /// 워크북

        /// </summary>

        private Microsoft.Office.Interop.Excel._Workbook workbook = null;

 

        /// <summary>

        /// 시트들

        /// </summary>

        private Microsoft.Office.Interop.Excel.Sheets sheets = null;

 

        /// <summary>

        /// 워크시트

        /// </summary>

        Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

 

        /// <summary>

        /// 범위

        /// </summary>

        private Microsoft.Office.Interop.Excel.Range range = null;

 

        /// <summary>

        /// 폰트

        /// </summary>

        private Microsoft.Office.Interop.Excel.Font font = null;

 

        /// <summary>

        /// 부가적인 값

        /// </summary>

        private object optionalValue = Missing.Value;

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 생성하기 - Generate()

 

        /// <summary>

        /// 생성하기

        /// </summary>

        public void Generate()

        {

            try

            {

                if(List != null)

                {

                    if(List.Count != 0)

                    {

                        CreateInstance();

 

                        FillSheet();

 

                        ShowEXCEL();

                    }

                }

            }

            finally

            {

                ReleaseInstance(this.worksheet  );

                ReleaseInstance(this.sheets     );

                ReleaseInstance(this.workbook   );

                ReleaseInstance(this.workbooks  );

                ReleaseInstance(this.application);

            }

        }

 

        #endregion

 

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region 인스턴스 생성하기 - CreateInstance()

 

        /// <summary>

        /// 인스턴스 생성하기

        /// </summary>

        private void CreateInstance()

        {

            this.application = new Microsoft.Office.Interop.Excel.Application();

 

            this.workbooks = (Microsoft.Office.Interop.Excel.Workbooks)this.application.Workbooks;

 

            this.workbook = (Microsoft.Office.Interop.Excel._Workbook)(this.workbooks.Add(this.optionalValue));

 

            this.sheets = (Microsoft.Office.Interop.Excel.Sheets)this.workbook.Worksheets;

 

            this.worksheet = this.sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

        }

 

        #endregion

        #region 행들 추가하기 - AddRows(startRange, rowCount, columnCount, values)

 

        /// <summary>

        /// 행들 추가하기

        /// </summary>

        /// <param name="startRange">시작 범위</param>

        /// <param name="rowCount">행 수</param>

        /// <param name="columnCount">열 수</param>

        /// <param name="values">값들</param>

        private void AddRows(string startRange, int rowCount, int columnCount, object values)

        {

            this.range = this.worksheet.get_Range(startRange, this.optionalValue);

 

            this.range = this.range.get_Resize(rowCount, columnCount);

 

            this.range.set_Value(this.optionalValue, values);

        }       

 

        #endregion

        #region 헤더 스타일 설정하기 - SetHeaderStyle()

 

        /// <summary>

        /// 헤더 스타일 설정하기

        /// </summary>

        private void SetHeaderStyle()

        {

            this.font = this.range.Font;

 

            this.font.Bold = true;

        }

 

        #endregion

        #region 헤더 생성하기 - CreateHeader()

 

        /// <summary>

        /// 헤더 생성하기

        /// </summary>

        /// <returns>헤더 목록</returns>

        private object[] CreateHeader()

        {

            PropertyInfo[] propertyInfoArray = typeof(TEntity).GetProperties();

 

            List<object> headerList = new List<object>();

 

            for(int i = 0; i < propertyInfoArray.Length; i++)

            {

                headerList.Add(propertyInfoArray[i].Name);

            }

 

            object[] headerArray = headerList.ToArray();

 

            AddRows("A1", 1, headerArray.Length, headerArray);

 

            SetHeaderStyle();

 

            return headerArray;

        }

 

        #endregion

        #region 컬럼 너비 자동 맞추기 - AutoFitColumnWidth(startRange, rowCount, columnCount)

 

        /// <summary>

        /// 컬럼 너비 자동 맞추기

        /// </summary>

        /// <param name="startRange">시작 범위</param>

        /// <param name="rowCount">행 수</param>

        /// <param name="columnCount">열 수</param>

        private void AutoFitColumnWidth(string startRange, int rowCount, int columnCount)

        {

            this.range = this.worksheet.get_Range(startRange, this.optionalValue);

 

            this.range = this.range.get_Resize(rowCount, columnCount);

 

            this.range.Columns.AutoFit();

        }

 

        #endregion

        #region 데이타 작성하기 - WriteData(headerArray)

 

        /// <summary>

        /// 데이타 작성하기

        /// </summary>

        /// <param name="headerArray">헤더 배열</param>

        private void WriteData(object[] headerArray)

        {

            object[,] dataArray = new object[List.Count, headerArray.Length];

 

            for(int row = 0; row < List.Count; row++)

            {

                TEntity entity = List[row];

 

                for(int column = 0; column < headerArray.Length; column++)

                {

                    object value = typeof(TEntity).InvokeMember

                    (

                        headerArray[column].ToString(),

                        BindingFlags.GetProperty,

                        null,

                        entity,

                        null

                    );

 

                    dataArray[row, column] = (value == null) ? "" : value.ToString();

                }

            }

 

            AddRows("A2", List.Count, headerArray.Length, dataArray);

 

            AutoFitColumnWidth("A1", List.Count + 1, headerArray.Length);

        }

 

        #endregion

        #region 시트 채우기 - FillSheet()

 

        /// <summary>

        /// 시트 채우기

        /// </summary>

        private void FillSheet()

        {

            object[] headerArray = CreateHeader();

 

            WriteData(headerArray);

        }

 

        #endregion

        #region 엑셀 보여주기 - ShowEXCEL()

 

        /// <summary>

        /// 엑셀 보여주기

        /// </summary>

        private void ShowEXCEL()

        {

            this.application.Visible = true;

        }

 

        #endregion

        #region 인스턴스 해제하기 - ReleaseInstance(instance)

 

        /// <summary>

        /// 인스턴스 해제하기

        /// </summary>

        /// <param name="instance">인스턴스</param>

        private void ReleaseInstance(object instance)

        {

            try

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(instance);

 

                instance = null;

            }

            catch(Exception exception)

            {

                instance = null;

 

                throw exception;

            }

            finally

            {

                GC.Collect();

            }

        }

 

        #endregion

    }

}

 

 

MainWindow.xaml

 

 

<Window x:Class="TestProject.MainWindow"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:toolkit="clr-namespace:Microsoft.Windows.Controls;assembly=WPFToolkit"

    Width="800"

    Height="600"

    Title="DataGrid 클래스 : 엑셀 파일 내보내기"

    FontFamily="나눔고딕코딩"

    FontSize="16">

    <Border Margin="5">

        <Grid>

            <Grid.RowDefinitions>

                <RowDefinition Height="30" />

                <RowDefinition Height="5"   />

                <RowDefinition Height="50*" />

                <RowDefinition Height="5"   />

                <RowDefinition Height="30" />

                <RowDefinition Height="5"   />

                <RowDefinition Height="50*" />

            </Grid.RowDefinitions>

            <Button Grid.Row="0"

                HorizontalAlignment="Right"

                Width="100"

                Content="내보내기"

                Click="exportEmployeeButton_Click" />

            <toolkit:DataGrid Name="employeeDataGrid" Grid.Row="2"

                AutoGenerateColumns="False"

                SelectionMode="Single"

                IsReadOnly="True">

                <toolkit:DataGrid.Columns>

                    <toolkit:DataGridTextColumn

                        Header="ID"

                        Width="100"

                        Binding="{Binding Path=ID}" />

                    <toolkit:DataGridTextColumn

                        Header="성명"

                        Width="200"

                        Binding="{Binding Path=Name}" />

                    <toolkit:DataGridTextColumn

                        Header="직위"

                        Width="*"

                        Binding="{Binding Path=Designation}" />

                </toolkit:DataGrid.Columns>

            </toolkit:DataGrid>

            <Button Grid.Row="4"

                HorizontalAlignment="Right"

                Width="100"

                Content="내보내기"

                Click="exportBookButton_Click" />

            <toolkit:DataGrid Name="bookDataGrid" Grid.Row="6"

                AutoGenerateColumns="False"

                SelectionMode="Single"

                IsReadOnly="True">

                <toolkit:DataGrid.Columns>

                    <toolkit:DataGridTextColumn

                        Header="ISBN"

                        Width="100"

                        Binding="{Binding Path=ISBN}" />

                    <toolkit:DataGridTextColumn

                        Header="제목"

                        Width="200"

                        Binding="{Binding Path=Title}" />

                    <toolkit:DataGridTextColumn

                        Header="저자"

                        Width="*"

                        Binding="{Binding Path=Author}" />

                </toolkit:DataGrid.Columns>

            </toolkit:DataGrid>

        </Grid>

    </Border>

</Window>

 

 

MainWindow.xaml.cs

 

 

using System.ComponentModel;

using System.Windows;

using System.Windows.Data;

 

namespace TestProject

{

    /// <summary>

    /// 메인 윈도우

    /// </summary>

    public partial class MainWindow : Window

    {

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

        ////////////////////////////////////////////////////////////////////////////////////////// Public

 

        #region 생성자 - MainWindow()

 

        /// <summary>

        /// 생성자

        /// </summary>

        public MainWindow()

        {

            InitializeComponent();

 

            #region 직원 리스트를 생성한다.

 

            EmployeeList employeeList = new EmployeeList();

 

            Employee employee1 = new Employee();

 

            employee1.ID          = 52590;

            employee1.Name        = "Sathish";

            employee1.Designation = "Developer";            

 

            employeeList.Add(employee1);

 

            Employee employee2 = new Employee();

 

            employee2.ID          = 52592;

            employee2.Name        = "Karthick";

            employee2.Designation = "Developer";            

 

            employeeList.Add(employee2);

 

            Employee employee3 = new Employee();

 

            employee3.ID          = 52593;

            employee3.Name        = "Raja";

            employee3.Designation = "Manager";            

 

            employeeList.Add(employee3);

 

            Employee employee4 = new Employee();

 

            employee4.ID          = 12778;

            employee4.Name        = "Sumesh";

            employee4.Designation = "Project Lead";            

 

            employeeList.Add(employee4);

 

            Employee employee5 = new Employee();

 

            employee5.ID          = 12590;

            employee5.Name        = "Srini";

            employee5.Designation = "Project Lead";            

 

            employeeList.Add(employee5);

            

            #endregion

 

            this.employeeDataGrid.ItemsSource = employeeList;

 

            #region 책 리스트을 생성한다.

 

            BookList bookList = new BookList();

 

            Book book1 = new Book();

 

            book1.ISBN   = 582912;

            book1.Title  = "C#";

            book1.Author = "James";

 

            bookList.Add(book1);

 

            Book book2 = new Book();

 

            book2.ISBN   = 174290;

            book2.Title  = "WPF";

            book2.Author = "Smith";

 

            bookList.Add(book2);

 

            Book book3 = new Book();

 

            book3.ISBN   = 095177;

            book3.Title  = ".NET";

            book3.Author = "Robert";

 

            bookList.Add(book3);

 

            Book book4 = new Book();

 

            book4.ISBN   = 112275;

            book4.Title  = "Java";

            book4.Author = "Steve";

 

            bookList.Add(book4);

 

            Book book5 = new Book();

 

            book5.ISBN   = 998721;

            book5.Title  = "COBOL";

            book5.Author = "John";

 

            bookList.Add(book5);

 

            #endregion

 

            this.bookDataGrid.ItemsSource = bookList;

        }         

 

        #endregion

 

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method

        ////////////////////////////////////////////////////////////////////////////////////////// Private

 

        #region 직원 내보내기 버튼 클릭시 처리하기 - exportEmployeeButton_Click(sender, e)

 

        /// <summary>

        /// 직원 내보내기 버튼 클릭시 처리하기

        /// </summary>

        /// <param name="sender">이벤트 발생자</param>

        /// <param name="e">이벤트 인자</param>

        private void exportEmployeeButton_Click(object sender, RoutedEventArgs e)

        {

            ExcelExportHelper<Employee, EmployeeList> helper = new ExcelExportHelper<Employee, EmployeeList>();

 

            helper.List = employeeDataGrid.ItemsSource as EmployeeList;

 

            helper.Generate();

        }

 

        #endregion

        #region 책 내보내기 버튼 클릭시 처리하기 - exportBookButton_Click(sender, e)

 

        /// <summary>

        /// 책 내보내기 버튼 클릭시 처리하기

        /// </summary>

        /// <param name="sender">이벤트 발생자</param>

        /// <param name="e">이벤트 인자</param>

        private void exportBookButton_Click(object sender, RoutedEventArgs e)

        {

            ExcelExportHelper<Book, BookList> helper = new ExcelExportHelper<Book, BookList>();

 

            ICollectionView collectionView = CollectionViewSource.GetDefaultView(this.bookDataGrid.ItemsSource);

 

            helper.List = collectionView.SourceCollection as BookList;

 

            helper.Generate();

        }

 

        #endregion

    }

}

 

------------------------------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요