Skip to content

Instantly share code, notes, and snippets.

@tufanbarisyildirim
Created July 4, 2012 19:39
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tufanbarisyildirim/3049194 to your computer and use it in GitHub Desktop.
Save tufanbarisyildirim/3049194 to your computer and use it in GitHub Desktop.
DataTable to Excel
public string DataTableToWorkBook(DataTable dt, string postfix)
{
object oMissing = Missing.Value;
Microsoft.Office.Interop.Excel.Application app;
Microsoft.Office.Interop.Excel.Workbooks wkBks;
Microsoft.Office.Interop.Excel.Workbook wkBk;
Microsoft.Office.Interop.Excel.Sheets wkShts;
Microsoft.Office.Interop.Excel.Worksheet wkSht;
string fileLink;
string filename = Server.MapPath(fileLink = "tmp/" + DateTime.Now.ToFileTime() + "_" + postfix + ".xls");
FileStream fs = new FileStream(filename, FileMode.Create);
fs.Close();
app = new Microsoft.Office.Interop.Excel.Application();
try
{
app.DisplayAlerts = true;
wkBks = app.Workbooks;
wkBk = wkBks.Open(filename, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
wkShts = (Microsoft.Office.Interop.Excel.Sheets)wkBk.Sheets;
wkSht = (Microsoft.Office.Interop.Excel.Worksheet)wkShts.get_Item(1);
#region Captions
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = wkSht.Cells[1, i + 1];
cell.Value = dt.Columns[i].Caption;
}
#endregion
#region Data
for (var r = 0; r < dt.Rows.Count; r++)
for (var c = 0; c < dt.Columns.Count; c++)
{
var cell = wkSht.Cells[r + 2, c + 1];
cell.Value = dt.Rows[r][c];
}
#endregion
app.Visible = true;
wkBk.Close(true, oMissing, oMissing);
Marshal.ReleaseComObject(wkSht);
Marshal.ReleaseComObject(wkShts);
Marshal.ReleaseComObject(wkBk);
Marshal.ReleaseComObject(wkBks);
}
catch { }
finally
{
app.Quit();
Marshal.ReleaseComObject(app);
wkSht = null;
wkBk = null;
app = null;
GC.Collect();
}
return fileLink;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment