Export DataSet to multiple Excel Sheets (Worksheets) in ASP.Net using C# .Net

using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Data.SqlClient;
using System.Configuration;



protected void ExportExcel_Click(object sender, EventArgs e)
    {
        using (XLWorkbook wb = new XLWorkbook())
        {
            //Loop through the GridView pages.
            for (int i = 0; i < GridView1.PageCount; i++)
            {
                //Set the Current Page.
                GridView1.PageIndex = i;
                this.BindGrid();

                //Create a DataTable with schema same as GridView columns.
                DataTable dt = new DataTable("Page_" + (i + 1));
                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    dt.Columns.Add(cell.Text);
                }

                //Loop and add rows from GridView to DataTable
                foreach (GridViewRow row in GridView1.Rows)
                {
                    dt.Rows.Add();
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                    }
                }

                //Add DataTable as Worksheet.
                wb.Worksheets.Add(dt);


                //2nd Sheet
                GridView1.PageIndex = i;
                this.BindGrid();

                //Create a DataTable with schema same as GridView columns.
                dt = new DataTable("Page_" + (i + 2));
                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    dt.Columns.Add(cell.Text);
                }

                //Loop and add rows from GridView to DataTable
                foreach (GridViewRow row in GridView1.Rows)
                {
                    dt.Rows.Add();
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                    }
                }

                //Add DataTable as Worksheet.
                wb.Worksheets.Add(dt);
            }

            //Export the Excel file.
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }

When json get using viewbag string come " then remove " from my Json in javascript?

Presumably you have it in a variable and are using JSON.parse(data);. In which case, use:

JSON.parse(data.replace(/"/g,'"'));

You might want to fix your JSON-writing script though, because " is not valid in a JSON object.

shareimprove this answer

answered Feb 11 '12 at 23:07 Niet the Dark Absol 200k32224350

add a comment

Read Excel File using mvc C# and Instaall Excel reader package

Are you using traditional way to read .xls and .xlsx files in ASP.NET/C#? If yes, you might be using following providers:
Microsoft.Jet.OLEDB.4.0 provider for Excel 97-2003 format (.xls)
Microsoft.ACE.OLEDB.12.0 provider for Excel 2007+ format (.xlsx)

and you may get the following common errors:

The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

and probably, you would go for following approach to fix them

1. Installation of Office 2007 Data Connectivity Components or Office 2010 Database Engine on the Server.
2. The application pool that the site is running set “Enable 32-bit applications” to “True”
3. In project, Change Platform target from: Any CPU to x86 and Rebuild it.

What would happen if you have shared hosting or Azure Website hosting and You are not allowed to install ACE Database engine on the server?

In this post, we will see how to upload and read both .xls and .xlsx excel files without any server dependencies in ASP.NET MVC. We will use ExcelDataReader, an Open Source project to read excel files.

Steps:

1. Create ASP.NET MVC 5 Empty Project

2. To install ExcelDataReader, run the following command in the Package Manager Console:

Install-Package ExcelDataReader

3. Add New Controller say HomeController and add following action:

?

public ActionResult Upload()        {            return View();        }

4. Add View of Upload action and use following code:

?

@model System.Data.DataTable @using System.Data;   <h2>Upload File</h2>   @using (Html.BeginForm("Upload", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" })) {     @Html.AntiForgeryToken()        @Html.ValidationSummary()           <div class="form-group">         <input type="file" id="dataFile" name="upload" />     </div>           <div class="form-group">         <input type="submit" value="Upload" class="btn btn-default" />     </div>           if (Model != null)     {         <table>             <thead>                 <tr>                     @foreach (DataColumn col in Model.Columns)                     {                                 <th>@col.ColumnName</th>                     }                 </tr>             </thead>             <tbody>                 @foreach (DataRow row in Model.Rows)                 {                            <tr>                         @foreach (DataColumn col in Model.Columns)                         {                                         <td>@row[col.ColumnName]</td>                         }                     </tr>                 }             </tbody>         </table>     } }

We will read excel, get data in DataTable and show DataTable in View. 5. To read the submitted file: ? [HttpPost]        [ValidateAntiForgeryToken]        public ActionResult Upload(HttpPostedFileBase upload)        {            if (ModelState.IsValid)            {                  if (upload != null && upload.ContentLength > 0)                {                    // ExcelDataReader works with the binary Excel file, so it needs a FileStream                    // to get started. This is how we avoid dependencies on ACE or Interop:                    Stream stream = upload.InputStream;                      // We return the interface, so that                    IExcelDataReader reader = null;                        if (upload.FileName.EndsWith(".xls"))                    {                        reader = ExcelReaderFactory.CreateBinaryReader(stream);                    }                    else if (upload.FileName.EndsWith(".xlsx"))                    {                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);                    }                    else                    {                        ModelState.AddModelError("File", "This file format is not supported");                        return View();                    }                      reader.IsFirstRowAsColumnNames = true;                                         DataSet result = reader.AsDataSet();                    reader.Close();                      return View(result.Tables[0]);                }                else                {                    ModelState.AddModelError("File", "Please Upload Your file");                }            }            return View();        }