C# 에서 엑셀(Xlsx) 저장

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

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

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120

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