// Writes an Excel file from the datatable (using late binding) public static void WriteXls([NotNull] this DataTable dataTbl, string filename, [CanBeNull] string sheetName, bool visible) { var mis = Type.Missing; var xlApp = LateBinding.GetOrCreateInstance("Excel.Application"); xlApp.Set("DisplayAlerts", false); var workbooks = xlApp.Get("Workbooks"); object worksheet; var workbook = File.Exists(filename) ? workbooks.Invoke("Open", filename) : workbooks.Invoke("Add", mis); if (string.IsNullOrEmpty(sheetName)) { worksheet = workbook.Get("Activesheet"); } else { var worksheets = workbook.Get("Worksheets"); try { worksheet = worksheets.Get("Item", sheetName); worksheet.Get("Cells").Invoke("Clear"); } catch { worksheet = worksheets.Invoke("Add", mis); worksheet.Set("Name", sheetName); } } var range = worksheet.Get("Cells"); dataTbl.GetColumnNames() .Iterate((name, i) => range.Get("Item", 1, i + 1).Set("Value2", name)); dataTbl.Rows .Cast <DataRow>() .Iterate((row, i) => row.ItemArray .Iterate((item, j) => range.Get("Item", i + 2, j + 1).Set("Value2", item))); xlApp.Set("DisplayAlerts", true); if (visible) { xlApp.Set("Visible", true); } else { if (File.Exists(filename)) { workbook.Invoke("Save"); } else { var fileFormat = string.CompareOrdinal("11.0", (string)xlApp.Get("Version")) < 0 && filename.EndsWith(".xlsx", StringComparison.CurrentCultureIgnoreCase) ? 51 : -4143; workbook.Invoke("Saveas", filename, fileFormat, string.Empty, string.Empty, false, false, 1, 1); } workbook.Invoke("Close"); xlApp.ReleaseInstance(); } }