C# 엑셀 오토메이션, 엑셀 서식, 테두리, 색상, 셀 넓이 ( interop.excel.dll )

ASP.NET 이나 C#.NET 을 이용해서 그리드뷰나 테이블에 있는 데이터를

엑셀로 다운받아야 하는 경우가 있습니다.

단순하게 데이터만 다운받는게 아니라

그 데이터에 셀 서식까지 적용 시켜서 다운받는 방법이 있다!!!!

아래와 같이 해주면됨. 

참조 추가는 이곳에서
http://support.microsoft.com/kb/302096/ko



Microsofot.Office.Core
Microsofot.Office.Interop.Excel

2개가 들어왔다면 정상 입니다.




=======================================================================================
=======================================================================================




try
{
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(Type.Missing);
    Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
    app.Visible = true;

if (dgv == dbdDgvLst)
{
    //셀 범위, 색상
    //string betweens1 = "A1:E1, G1:K1, G8:K8, G24:K24, A38:E38, G27:K27";
    string betweens1 = "A1:E1, G1:K1, G8:K8, G24:K25, A39:E39, G27:K27";  // 셀경로임.
    ////Microsoft.Office.Interop.Excel.Range range = app.get_Range("a1,b1,c1", Type.Missing); //범위설정
    Microsoft.Office.Interop.Excel.Range range = app.get_Range(betweens1, Type.Missing); //범위설정
    range.NumberFormatLocal = @"@"; //텍스트설정
    Microsoft.Office.Interop.Excel.Style style = workbook.Styles.Add("NewStyle", Type.Missing); //스타일설정
    style.Interior.ColorIndex = 20; //칼라설정
    style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid; //페턴설정
    style.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.XlBackground.xlBackgroundAutomatic;

    //######셀박스#########
    //셀 메뉴, 합산
    //app.get_Range(betweens1).Borders.LineStyle = XlBorderWeight.xlThick;
    //app.get_Range("G27:K27").Borders.LineStyle = BorderStyle.FixedSingle;

    //######셀박스######### //셀 내용 office2010
    ////xlThemeColorAccent1=blue, 2=red, 3=melon, 4=purple, 5=aqua, 6=orange
    ////xlThemeColorDark1=white, xlThemeColorDark2=ivy, 
    ////xlThemeColorHyperlink=dark blue, xlThemeColorFollowedHyperlink=dark purple
    ////xlThemeColorLight1=black, xlThemeColorLight2=dark gray
    //app.get_Range("A2:e37").Borders.ThemeColor = XlThemeColor.xlThemeColorDark2;
    //app.get_Range("g2:k7").Borders.ThemeColor = XlThemeColor.xlThemeColorDark2;
    //app.get_Range("g10:k25").Borders.ThemeColor = XlThemeColor.xlThemeColorDark2;

    //black cell box line
    app.get_Range("A1", "Z1").Borders.LineStyle = BorderStyle.FixedSingle; //line
    //app.get_Range("A2:e38").Borders.LineStyle = BorderStyle.Fixed3D; //sopt line
    //app.get_Range("A2:e38").Borders.LineStyle = XlLineStyle.xlDot; //일반점선

   wSheet.get_Range("A1", "D49").Borders.LineStyle = XlLineStyle.xlContinuous; // 이게 일반 점선

    //app.get_Range("A2:e38").Borders.LineStyle = XlLineStyle.xlSlantDashDot; //굵은점선
    //app.get_Range("A2:e38").Borders.LineStyle = XlLineStyle.xlDash; //굵고긴점선
    //app.get_Range("A2:e38").Borders.LineStyle = XlLineStyle.xlContinuous; //굵은선

    app.get_Range( "A1", "Z1" ).Borders.LineStyle = BorderStyle.Fixed3D;
    app.get_Range( "A1", "Z1" ).Borders.LineStyle = BorderStyle.Fixed3D;

    //셀 열 넓이
    //app.Columns.ColumnWidth = 20; --all width 20 ok
    app.Columns[ "A1", "Z1" ].ColumnWidth = 20;

    //Microsoft.Office.Interop.Excel.Range range2 = app.workbook.get_Range(..,...);
    //Microsoft.Office.Interop.Excel.Border xlBorder = xlRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
    //xlBorder.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
    //xlBorder.ColorIndex = 3; //빨강 자동으로 설정하려면 Excel.XlColorIndex.xlColorIndexAutomatic
    //xlBorder.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;

    //range.Merge(Type.Missing);
    //Selection.NumberFormatLocal = "#,##0_ " '숫자 
    //Selection.NumberFormatLocal = "yyyy""년"" m""월"" d""일"";@" '날짜 
    //excel.Columns("E").ColumnWidth = 12 

    range.Style = style; //적용
}

worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = name;


for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
    worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}

for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
    for (int j = 0; j < dgv.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
    }
}

workbook.SaveAs(dateEnd.Value.ToString("yyyy-MM-dd-") + name + ".xls", Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing
//workbook.SaveAs(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing
, Type.Missing, Type.Missing);

//app.Quit();

//return name + " saved";
}
catch (Exception err)
{
    MessageBox.Show(err.Message);
}

출처 : http://blog.yahoo.com/_TO4QP2357J7II6ZEMTV6BM4ITY/articles/586443