How to achieve Import/Export on button click from grid view to excel sheet using asp.net mvc

I have a gridview with data. I need to put one button to Export/import that开发者_StackOverflow社区 gridview data to excel sheet using asp.net mvc.

Can anybody help me out how to do this.

Thanks.

You can bypass excel automation and use OleDbConnection like this

connectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;'";
...

connectionString = string.Format(connectionString, fileName);
OleDbConnection conn = new OleDbConnection(connectionString)
.............
// You can create table or insert value using SQL statement 
// Create your OleDbCommand and execute it. 
............
conn.Close();

If the data is to be submitted by the client then POST it to the server. Once it is on the server, create a StringBuilder and turn the data into a large string, using commas for column separators and newlines after each row. Then return a new FileResult:

In your controller, after having created the StringBuilder sb:

MemoryStream ms = new MemoryStream();
StreamWriter writer = new StreamWriter(ms, Encoding.UTF8);
writer.Write(sb.ToString());
writer.Flush();
ms.Position = 0;

return File(ms, "application/vnd.ms-excel", "myfile.csv");

Importing Excel data is another story. I haven't done much of that, so hopefully someone else will be able to provide some insights on that.

To import from excel, upload the file as usual. Then with a reference to Microsoft.Office.Interop.Excel.dll (from office 11) you can use the excel api to iterate through the sheets and extract the data.

My recommendation is to use LocalReport to render the results. You only have to create a rdlc file (add a new report in the project) with the layout you want. Then you have to pass the the data you wish to bind and call the Render method (you can return excel as well as pdf). Honestly I don't have any sample code in hand, but if you google around a little bit you will surely find out how to do it. Good luck.