개발자.. 너란놈..

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

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

//시트 한가지만 사용할 경우
using (ExcelPackage pck = new ExcelPackage())
{
	ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(ds.Tables["데이터테이블명"].TableName);
	workSheet.Cells["A1"].LoadFromDataTable(ds.Tables["데이터테이블명"], true);

	pck.SaveAs(new FileInfo(strRealPath));
}

//시트 여러개 사용할 경우
private static void DataSetToExcel(DataSet dataSet, string filePath)
{
	using (ExcelPackage pck = new ExcelPackage())
	{
		foreach (DataTable dataTable in dataSet.Tables)
		{
			ExcelWorksheet workSheet = pck.Workbook.Worksheets.Add(dataTable.TableName);
			workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);
		}

		pck.SaveAs(new FileInfo(filePath));
	}
}

//EPPlus를 사용 한 것 입니다.

대략 1만개의 row를 15초 정도로 속도 줄여줍니다. 퍼포먼스 좋습니다.

ds = JsonConvert.DeserializeObject(srtResult);

//strJson = value.vJsonData.ToString(); //Object로 보내기 위한 작업
//여기서 부터 엑셀 데이터 만들기. (Ds)
//ds = JsonConvert.DeserializeObject(value.vJsonData);

Excel.Application ap = new Excel.Application();
Excel.Workbook excelWorkBook = ap.Workbooks.Add();

foreach (DataTable Excel_dt in ds.Tables)
{
	Excel.Worksheet ws = excelWorkBook.Sheets.Add();
	ws.Name = Excel_dt.TableName;

	for(int columnHeaderIndex = 1; columnHeaderIndex <= Excel_dt.Columns.Count; columnHeaderIndex++)
	{
		ws.Cells[1, columnHeaderIndex] = Excel_dt.Columns[columnHeaderIndex -1].ColumnName;
		ws.Cells[1, columnHeaderIndex].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSteelBlue);
	}

	for(int rowIndex = 0; rowIndex < Excel_dt.Rows.Count; rowIndex++)
	{
		for(int columnIndex = 0; columnIndex < Excel_dt.Columns.Count; columnIndex++)
		{
			ws.Cells[rowIndex + 2 , columnIndex + 1] = Excel_dt.Rows[rowIndex].ItemArray[columnIndex].ToString();
		}
	}
	ws.Columns.AutoFit();
}

//파일저장
string strDateTime = strDateTime = DateTime.Now.ToString("yyyyMMddHHmmssFFF");
string strPath = "Files/";
//System.IO.FileInfo fi;
//fi = new System.IO.FileInfo(Server.MapPath("~/Files/")+strDateTime+".xlsx");

excelWorkBook.SaveAs(Server.MapPath("~/"+strPath)+strDateTime+".xlsx",Excel.XlFileFormat.xlOpenXMLWorkbook);
excelWorkBook.Close(true);
ap.Quit();

================================필독 ========================================

- import excel이 필요합니다. office12에 있습니다.

- 데이터 많으면 굉장히 느립니다. 

더 빠른 방법 참고 https://1061025.tistory.com/54