• Twitter
  • Facebook
  • Google+
  • Instagram
  • Youtube

Thursday, June 12, 2008

Create and Export-Import Excel file in Asp.Net

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
}

}

0 comments:

Contact

Get in touch with me


Adress/Street

12 Street West Victoria 1234 Australia

Phone number

+(12) 3456 789

Website

www.johnsmith.com