VSTOS – Excel File Control Exsample

public void SaveAsExcel(System.Data.DataTable dtData, string filePath)

{

Excel.ApplicationClass app = null;

Excel.Workbooks workbooks = null;

Excel.Workbook workbook = null;

Excel.Sheets sheets = null;

Excel.Worksheet sheet = null;

Excel.Range cells = null;

Excel.Range range = null;

string saveAsFile = string.Empty;

try

{

app = new Excel.ApplicationClass();

workbooks = app.Workbooks;

workbook = workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing,

missing, missing, missing, missing, missing, missing, missing);

sheets = workbook.Worksheets;

sheet = (Excel.Worksheet)sheets[1];

sheet.Unprotect(missing);

cells = (Excel.Range)sheet.Cells;

if (dtData.Rows.Count <= 0)

{

MessageBox.Show("데이타 존재하지 않습니다.", "데이타 없음", MessageBoxButtons.OK, MessageBoxIcon.Information);

return;

}

((Excel.Range)cells.get_Item(7, "B")).Value2 = dtData.Rows[0]["custNm"];

((Excel.Range)cells.get_Item(9, "J")).Value2 = dtData.Rows[0]["InvoiceNo"];

((Excel.Range)cells.get_Item(10, "J")).Value2 = dtData.Rows[0]["InvDate"];

((Excel.Range)cells.get_Item(17, "C")).Value2 = dtData.Rows[0]["LPort"];

((Excel.Range)cells.get_Item(18, "C")).Value2 = dtData.Rows[0]["DPort"];

// Row Insert

range = (Excel.Range)cells.get_Item("13", "Z");

range.Rows.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);

app.DisplayAlerts = false; // 저장할 것인가 확인 하도록 설정

saveAsFile = OpenSaveFileDialog("다른 이름으로 저장", "Microsoft Excel Files|*.xls", "xls");

if (saveAsFile == string.Empty) return;

//workbook.SaveCopyAs(saveAsFile);

workbook.SaveAs(saveAsFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);

//app.Save(fileName);

app.Quit();

System.Windows.Forms.MessageBox.Show("저장 되었습니다.", "엑셀 저장", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);

}

catch (Exception ex)

{

System.Windows.Forms.MessageBox.Show(ex.Message);

}

finally

{

if(range != null) Marshal.ReleaseComObject(range);

if (cells != null) Marshal.ReleaseComObject(cells);

if (sheet != null) Marshal.ReleaseComObject(sheet);

if (sheets != null) Marshal.ReleaseComObject(sheets);

if (workbook != null) Marshal.ReleaseComObject(workbook);

if (workbooks != null) Marshal.ReleaseComObject(workbooks);

if (app != null)

{

app.Quit();

Marshal.ReleaseComObject(app);

}

GC.Collect();

GC.WaitForPendingFinalizers();

}

}