MARKUS

using GemBox.Spreadsheet;

using Markus.Service.Interface;

using Microsoft.Win32;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

using System.Windows;

namespace Markus.Service.StationController.Extensions

{

    class DataExport

    {

        public void DataExportData(System.Collections.ObjectModel.ObservableCollection<ConvertItem> FilterConvertSource)

        {

            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.FileName = "Document"; // Default file name

            saveFileDialog.DefaultExt = ".txt"; // Default file extension

            saveFileDialog.Filter = "Csv documents (.Csv)|*.csv|Excel(2017~2019)Worksheets|*.xlsx"; // Filter files by extension

            if (saveFileDialog.ShowDialog() == true)

            {

                if (!string.IsNullOrWhiteSpace(saveFileDialog.FileName))

                {

                    var extension = new System.IO.FileInfo(saveFileDialog.FileName).Extension;

                    if (extension == ".xlsx" || extension == ".csv")

                    {

                        var headers = new List<HeaderMember>

                        {

                            new HeaderMember{HeaderName = "ServiceID", Property = "ServiceID" },

                            new HeaderMember{HeaderName = "ConvertID", Property = "ConvertID" },

                            new HeaderMember{HeaderName = "ProjectNumber", Property = "ProjectNumber" },

                            new HeaderMember{HeaderName = "ConvertState", Property = "ConvertState" },

                            new HeaderMember{HeaderName = "CurrentPageNo", Property = "CurrentPageNo" },

                            new HeaderMember{HeaderName = "TotalPage", Property = "TotalPage" },

                            new HeaderMember{HeaderName = "OriginfilePath", Property = "OriginfilePath" },

                            new HeaderMember{HeaderName = "ConvertPath", Property = "ConvertPath" },

                            new HeaderMember{HeaderName = "CreateTime", Property = "CreateTime" },

                            new HeaderMember{HeaderName = "Exception", Property = "Exception" },

                            new HeaderMember{HeaderName = "ProcessorAffinity", Property = "ProcessorAffinity" },

                            new HeaderMember{HeaderName = "ReConverter", Property = "ReConverter" },

                            new HeaderMember{HeaderName = "UniqueKey", Property = "UniqueKey" }

                        };

                        DataExportExcel(saveFileDialog.FileName, "Hello world", FilterConvertSource, headers);

                        //_dataExport.DataExportExcel(saveFileDialog.FileName, saveFileDialog.FileName.ToString(),  Projectsource, headers);

                        //GemBoxFileSave(obj);

                    }

                }

            }

        }

        public void DataExportExcel<T>(string SaveFile, string SheetName, IEnumerable<T> collections, List<HeaderMember> headerMembers)

        {

            try

            {

                SpreadsheetInfo.SetLicense("EXK0-W4HZ-N518-IMEW");

                // SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");//프리버전은 15줄까지만 가능

                var workbook = new ExcelFile();

                var worksheet = workbook.Worksheets.Add(SheetName);

                // IEnumerable<T> collections를 for문

                for (int rowNumber = 0; rowNumber < collections.Count(); rowNumber++)//1857  양이 너무 많아서 빨리 엑셀출력 누르면 숫자가 감소됨 차분히 기다리면 숫자 늘어남

                {

                    // index에 해당하는 row를 가져온다.

                    object row = collections.ElementAt(rowNumber);//우왕좌왕하게 모든 프로퍼티 있음 생성자 프로퍼티도 있음

                    //row의 properties를 가져옴.

                    var rowPropInfos = row.GetType().GetProperties();//행렬로 보기좋게 프로퍼티 나옴

                    /// 지정된 header에 대해서 프로퍼티에 해당하는 값을 가져와서 cell에 출력

                    for (int i = 0; i < headerMembers.Count(); i++)//13

                    {

                        var prop = rowPropInfos.Where(x => x.Name == headerMembers[i].Property);//prop 0으로 나옴

                        if (headerMembers[i].Property == "CreateTime")

                        {

                            worksheet.Cells[rowNumber + 1, i].Value = prop.First().GetValue(row).ToString();

                        }

                        else if (prop.Count() > 0)

                        {

                            worksheet.Cells[rowNumber + 1, i].Value = prop.First().GetValue(row);

                        }

                    }

                }

                int k = 0;

                foreach (var HeadInfo in headerMembers)

                {

                    worksheet.Cells[0, k].Value = HeadInfo.HeaderName;

                    k++;

                }

                int columnCount = worksheet.CalculateMaxUsedColumns();

                for (int i = 0; i < columnCount; i++)

                {

                    worksheet.Columns[i].AutoFit();

                }

                var header = new CellStyle();//헤더

                header.FillPattern.SetSolid(SpreadsheetColor.FromArgb(0, 100, 220));

                header.Font.Weight = ExcelFont.BoldWeight;

                header.HorizontalAlignment = HorizontalAlignmentStyle.Center;

                header.VerticalAlignment = VerticalAlignmentStyle.Center;

                //worksheet.Cells.GetSubrange("A1:M1").Style = header;

                worksheet.Cells.GetSubrangeAbsolute(0, 0, collections.Count(), headerMembers.Count() - 1).Style = header;

                var style = new CellStyle();

                //style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top | MultipleBorders.Left | MultipleBorders.Bottom, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thick);//바깥쪽 border 두껍게 성공

                style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thick);//바깥쪽 border 두껍게 성공

                style.HorizontalAlignment = HorizontalAlignmentStyle.Center;

                style.VerticalAlignment = VerticalAlignmentStyle.Center;

                style.Font.Color = SpreadsheetColor.FromArgb(75, 60, 50);//글자 색갈

                style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(126, 239, 239));//cell 색갈

                style.Font.Name = "맑은 고딕";

                style.Font.Size = 9 * 20;

                worksheet.Cells.GetSubrangeAbsolute(0, 0, collections.Count(), headerMembers.Count() - 1).Style = style;

                //worksheet.Cells.GetSubrange("A1:M1858").Style = style;

                for (int line = 0; line < collections.Count() - 1; line++) //바깥border과 안쪽 얇은 border 같이 쓰면 안됨

                {

                    //for (int j = 0; j < 14 - 1 - 1; j++)

                    for (int j = 0; j < headerMembers.Count() - 1 - 1; j++)

                    {

                        worksheet.Cells[line + 1, j + 1].Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thin);//안쪽 (1,1) ~ (마지막-1,마지막-1) 가운데 성공

                    }

                }

                for (int line = 0; line < collections.Count(); line++) //바깥border과 안쪽 얇은 border 같이 쓰면 안됨

                {//Projectsource.Count() = 761개 ( 0 ~ 760 )

                    for (int j = 0; j < headerMembers.Count() - 1; j++)

                    {

                        worksheet.Cells[0, j].Style.Borders.SetBorders(MultipleBorders.Right, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thin);//(0,0) ~ (0,마지막-1) 위 성공

                        worksheet.Cells[collections.Count(), j].Style.Borders.SetBorders(MultipleBorders.Right, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thin);//(마지막,0) ~ (마지막,마지막-1) 아래 성공

                    }

                    worksheet.Cells[line, 0].Style.Borders.SetBorders(MultipleBorders.Bottom, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thin);//(0,0) ~ (마지막-1,0) 왼쪽 성공

                    worksheet.Cells[line, headerMembers.Count() - 1].Style.Borders.SetBorders(MultipleBorders.Bottom, SpreadsheetColor.FromArgb(140, 120, 50), LineStyle.Thin);//(0,마지막) ~ (마지막-1,마지막) 오른쪽 성공

                }

                workbook.Save(SaveFile);//파일 열었던 파일로 저장 성공

                MessageBox.Show("성공");

            }

            catch (Exception ex)

            {

                Console.WriteLine("예외 발생!! 메세지: {0}", ex.Message);

            }

        }

    }

}

public class HeaderMember

{

    public string HeaderName { get; set; }

    public string Property { get; set; }

}