[C#]ExcelPackage를 이용한 Excel 만들기 (Dataset)

ExcelPackage를 사용하여 Dataset로 Excel 만들기.

전체 셀의 옵션 선택도 가능하고 맨 위의 header 부분도 배경화면 및 폰트 적용 가능합니다.

 

using (ExcelPackage pck = new ExcelPackage())
{
	ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(ds.Tables["Table"].TableName);

	Color DeepBlueHexCode = ColorTranslator.FromHtml("#c6efce");

	//가로 길이
	workSheet.Column(1).Width = 30;
	workSheet.Column(2).Width = 13;
	workSheet.Column(3).Width = 20;
	workSheet.Column(4).Width = 20;
	workSheet.Column(5).Width = 15;
	workSheet.Column(6).Width = 15;
	workSheet.Column(7).Width = 125;
	workSheet.Column(8).Width = 8;

	//세로 정렬
	workSheet.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(4).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(5).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(6).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(7).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
	workSheet.Column(8).Style.VerticalAlignment = ExcelVerticalAlignment.Center;

	//가로 정렬
	workSheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(5).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(7).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
	workSheet.Column(8).Style.HorizontalAlignment = ExcelHorizontalAlignment.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.Column(7).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));
	workSheet.Column(8).Style.Font.SetFromFont(new System.Drawing.Font("나눔고딕", 10));

	//Header 부분 스타일 적용
	workSheet.Cells["A1:H1"].Style.Font.Bold = true;
	workSheet.Cells["A1:H1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
	workSheet.Cells["A1:H1"].Style.Fill.BackgroundColor.SetColor(DeepBlueHexCode);

	////테두리 border 적용
	var nBorderRowCount = ds.Tables["Table"].Rows.Count + 1;
	var nRowCount = ds.Tables["Table"].Rows.Count;

	string modelRange = "A1:H" + nBorderRowCount.ToString();

	workSheet.Cells[modelRange].Style.Border.Top.Style = ExcelBorderStyle.Thin;
	workSheet.Cells[modelRange].Style.Border.Left.Style = ExcelBorderStyle.Thin;
	workSheet.Cells[modelRange].Style.Border.Right.Style = ExcelBorderStyle.Thin;
	workSheet.Cells[modelRange].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

	//세팅 완료된 데이터 뿌려주기
	workSheet.Cells["A1"].LoadFromDataTable(ds.Tables["Table"], true);

	//엑셀 파일 저장
	pck.SaveAs(new FileInfo(strRealPath));
}

 

궁굼하신건 댓글로 남겨주세요~