C# 에서 엑셀(Xlsx) 저장

Visual Studio 의 솔루션 탐색기에서 우클릭

Nuget 패키지 관리 -> 찾아보기 -> EPPlus -> 설치

---

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;

namespace XlsxSaveProgram
{
    class Program
    {
        static void Main(string[] args)
        {
            // 저장파일명
            string xlsxfile = string.Format("Test{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));

            // 기존파일있을경우 삭제
            FileInfo excelFile = new FileInfo(xlsxfile);
            if (excelFile.Exists) { excelFile.Delete(); }

            // 조회기간
            DateTime date1 = DateTime.Now.AddDays(-7);
            DateTime date2 = DateTime.Now.AddDays(-1);

            // 시트가 여러개일 경우
            string[] sheets = new string[] { "시트1", "시트2", "시트3" };
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            // 패키지가 이용가능하다면
            using (ExcelPackage excel = new ExcelPackage())
            {
                // 시트 개수만큼 반복
                for (var i = 0; i < sheets.Length; i++)
                {
                    // 시트 추가
                    excel.Workbook.Worksheets.Add(sheets[i]);
                    // 상단 고정내용 처리
                    List<object[]> dataRow = new List<object[]>()
                    {
                        new string[] { sheets[i] },
                        new string[] { string.Format("{0} ~ {1}", date1.ToString("yyyy.MM.dd"), date2.ToString("yyyy.MM.dd")) },
                        new string[] { "컬럼1","컬럼2","컬럼3","컬럼4","컬럼5","컬럼6" }
                    };

                    // 몇줄인지 cnt에저장
                    decimal cnt = 0;
                    dataRow.Add(new object[] { "필드1_1", "필드2_1", "필드3_1", "필드4_1", "필드5_1", "필드6_1" });
                    dataRow.Add(new object[] { "필드1_2", "필드2_2", "필드3_2", "필드4_2", "필드5_2", "필드6_2" });
                    // 2줄만 추가했으므로
                    cnt = 2;
                    
                    string headerRange = "A1:" + Char.ConvertFromUtf32(dataRow[0].Length + 64) + "1";

                    // 시트선택
                    var worksheet = excel.Workbook.Worksheets[sheets[i]];

                    // 워크시트에 DataRow 를 불러옴
                    worksheet.Cells[headerRange].LoadFromArrays(dataRow);

                    // 스타일 지정
                    worksheet.Column(1).Width = 18;
                    worksheet.Column(2).Width = 26;
                    worksheet.Column(3).Width = 26;
                    worksheet.Column(4).Width = 16;
                    worksheet.Column(5).Width = 16;
                    worksheet.Column(6).Width = 21;

                    worksheet.Column(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Column(2).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Column(3).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Column(4).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Column(5).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    worksheet.Column(6).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

                    worksheet.Column(1).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
                    worksheet.Column(2).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
                    worksheet.Column(3).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
                    worksheet.Column(4).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
                    worksheet.Column(5).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
                    worksheet.Column(6).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));

                    worksheet.Row(1).Style.Font.Bold = true;
                    worksheet.Row(1).Style.Font.Size = 16;
                    worksheet.Row(1).Height = 30;

                    worksheet.Row(2).Style.Font.Size = 12;
                    worksheet.Row(2).Height = 24;

                    worksheet.Row(3).Style.Font.Bold = true;
                    worksheet.Row(3).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    worksheet.Row(3).Style.Font.Size = 10;
                    worksheet.Row(3).Height = 18;

                    using (ExcelRange range = worksheet.Cells[string.Format("A3:F{0}", (cnt + 3).ToString())])
                    {
                        range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        range.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                        range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                    }
                    using (ExcelRange range = worksheet.Cells[string.Format("B3:C{0}", (cnt + 3).ToString())])
                    {
                        range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                    using (ExcelRange range = worksheet.Cells[string.Format("E3:E{0}", (cnt + 3).ToString())])
                    {
                        range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                    for (var x = 0; x < cnt + 3; x++)
                    {
                        worksheet.Row(x + 3).Height = 18;
                    }
                }
                // 비밀번호 pwd
                //excel.Encryption.Password = "pwd";
                // 내용을 엑셀파일로 저장
                excel.SaveAs(excelFile);
                // 저장위치 출력
                Console.WriteLine("SavePath : {0}", excelFile);
            }
        }
    }
}