그리드뷰 데이타를 엑셀로 내리는 것은 해봤으나 요구 사항이 엑셀 템플릿을 이용하여 내용을 입력 하는 부분이라
이번에 처음으로 Excel API를 이용하여 다운로드를 하였습니다.
string strFileName = @"C:\Templete"; //템플릿 파일 명
ApplicationClass excel = new ApplicationClass();
Workbook wBook;
Worksheet wSheet;
excel.DisplayAlerts = false; // 덮어쓰기메시지 없애기
wBook = excel.Workbooks.Open(strFileName + ".xls"
, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets.get_Item("Sheet명");
시트의 셀에 데이타를 입력 하는 부분
wSheet.Cells[1, 4] = dtCN.Rows[0]["Cell명"].ToString();
Marshal.ReleaseComObject(wSheet.Cells);
디비에서 받은 데이타를 추가 하는 부분
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
excel.Cells[1, colIndex] = dcol.ColumnName;
}
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
excel.Range["A:Z"].NumberFormat = "@";
colIndex = colIndex + 1;
excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
Marshal.ReleaseComObject(excel.Cells)
}
}
wSheet.Columns.AutoFit();
colName = SetColumnStyleName(colIndex);
Range oRang;
//wSheet.get_Range("C3", "Z1").HorizontalAlignment = Constants.xlCenter; //정렬
wSheet.get_Range("C10", ("Z" + rowcount.ToString())).Borders.LineStyle = XlLineStyle.xlContinuous; // 셀 라인 생성
wSheet.get_Range("E"+stratCell+":E" + rowcount.ToString()).Validation.Delete();
wSheet.get_Range("E" + stratCell + ":E" + rowcount.ToString()).Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, control_dropdown_Data("Reliability"), Type.Missing); //엑셀에서 데이타 유효성 검사의 목록을 자동으로 생성 (단 몇자인지 모르지만 생성 제한이 있습니다.)
wSheet.get_Range("F" + stratCell + ":F" + rowcount.ToString()).Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlLineStyleNone;
wSheet.get_Range("F" + stratCell + ":F" + rowcount.ToString()).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlLineStyleNone;
wSheet.get_Range("F" + stratCell + ":F" + rowcount.ToString()).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlLineStyleNone;
wSheet.get_Range("F" + stratCell + ":F" + rowcount.ToString()).Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
wSheet.get_Range("F" + stratCell + ":F" + rowcount.ToString()).Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
oRang = wSheet.get_Range("C" + stratCell + ":Z" + rowcount.ToString());
oRang.EntireColumn.AutoFit();
wSheet.Columns.AutoFit();
String strFileName1 = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "ExcelFile";
strFileName1 = @"C:\test.xls";
wBook.SaveAs(strFileName1, XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
false, false, XlSaveAsAccessMode.xlShared,
XlSaveConflictResolution.xlLocalSessionChanges, false,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);
if (excel != null)
{
excel.DisplayAlerts = false;
wBook.Close();
excel.Quit();
Excel 이 잘 안죽는 경우가 많습니다. 꼭 아래 코드를 써야 합니다.
Marshal.ReleaseComObject(excel);
Marshal.ReleaseComObject(wSheet);
Marshal.ReleaseComObject(wBook);
Marshal.ReleaseComObject(oRang);
}
string filepath = strFileName1;
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + "파일명"+ ".xls");
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.WriteFile(filepath);
HttpContext.Current.Response.End();