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