Create and Export-Import Excel file in Asp.Net
=======================================
// This method create an Excel file and export it for download
private void CreateExcelFileandDownload()
{
try
{
// Create a new Excel file.
string[] connectStrings = new string[] {
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\TEMP\\TestExcel2003Output.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;\";",
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"C:\\TEMP\\TestExcel2007Output.xlsx\";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\";"
};
string dropTableStmt = "DROP TABLE [test]";
string createTableStmt = "CREATE TABLE [test] ( [Integer] int, [String] varchar(40), [Double] float, [Date] datetime, [Boolean] bit )";
string insertStmt = "INSERT INTO [test] ([Integer], [String], [Double], [Date], [Boolean]) VALUES ({0}, '{1}', {2}, '{3}', {4})";
object[] data = new object[] {
new object[] { 2628013, "Anderson", 0.617715356, new DateTime( 2008, 5, 5 ), true },
new object[] { 2628015, "Rainaud", 0.64933168, new DateTime( 2007, 4, 10 ), false },
new object[] { 2628017, "Dennis", 0.62140731, new DateTime( 2006, 3, 15 ), true },
new object[] { 2628019, "Schoenster", 0.599058708, new DateTime( 2005, 2, 20 ), false },
new object[] { 2628041, "Ganun", 0.593402527, new DateTime( 2004, 1, 25 ), true }
};
foreach (string connect in connectStrings)
{
OleDbConnection con = new OleDbConnection(connect);
con.Open();
if (con.State == ConnectionState.Open)
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandTimeout = 0;
try
{
// Only need this on runs subsequent to first time
cmd.CommandText = dropTableStmt;
cmd.ExecuteNonQuery();
}
catch
{
// First run will cause exception because table (worksheet) doesn't exist
}
cmd.CommandText = createTableStmt;
cmd.ExecuteNonQuery();
foreach (object[] row in data)
{
cmd.CommandText = String.Format(insertStmt, row[0], row[1], row[2], row[3], row[4]);
cmd.ExecuteNonQuery();
}
cmd.Dispose();
if (con.State == ConnectionState.Open)
con.Close();
con.Dispose();
}
}
// Download Created File
// For Office 2007 format
string FileName = @"C:\TEMP\TestExcel2007Output.xlsx";
// For Office 97 - 2003 format
string FileName2 = @"C:\TEMP\TestExcel2003Output.xls";
Response.Clear();
Response.ClearContent();
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Disposition", "attachment; filename=Name.xlsx;");
byte[] buffer = System.IO.File.ReadAllBytes(FileName);
System.IO.MemoryStream mem = new System.IO.MemoryStream();
mem.Write(buffer, 0, buffer.Length);
mem.WriteTo(Response.OutputStream);
Response.End();
}
catch (Exception ex)
{
// throw an exception
}
}
