Download excel with multiple sheets using StringBuilder in ASP.Net MVC

nabilabolo
 
on Nov 15, 2021 01:14 AM
385 Views

Hi, i want to download excel file with multiple sheets in it. 

I'm using stringBuilder to design the Excel file. As i google many suggest use closedXML but based on my requirement i think string builder is much simplier to design in excel.

So any idea if i'm using stringbuilder to download excel with multiple excel sheets in mvc?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 15, 2021 01:20 AM
nabilabolo says:
I'm using stringBuilder to design the Excel file

 Share the code.

nabilabolo
 
on Nov 15, 2021 01:51 AM
on Nov 15, 2021 01:55 AM

 

 public ActionResult MonthlyNGReport()
 {
     var sbHtml = new StringBuilder();
     sbHtml.Append("<table><tr><td colspan='7' style='text-align: center; font-size:26px'><b>MONTHLY REPORT</b></td></tr></table><br/>");
     sbHtml.Append("<table><tr><td colspan='7'><b>OVERALL SUMMARY</b></td></tr></table><br/><br/>");

     //-------------------------------------- first table ---------------------------------------------------//
     sbHtml.Append("<table border='1'");
     sbHtml.Append("<tr>");
     sbHtml.Append("<td></td>");
     sbHtml.Append("<td colspan='2' style='text-align:center'>Inpsected Lots</td>");
     sbHtml.Append("<td colspan='2' style='text-align:center'> NG Lots </td>");
     sbHtml.Append("</tr>");
     sbHtml.Append("<tr>");
     sbHtml.Append("<td>Process</td>");
     sbHtml.Append("<td>Lot</td>");
     sbHtml.Append("<td>Qty (mt)</td>");
     sbHtml.Append("<td>Lot</td>");
     sbHtml.Append("<td>Qty (mt)</td>");
     sbHtml.Append("</tr>");
     sbHtml.Append("</table>");

     byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString());

     return File(fileContents, "application/ms-excel", "MonthlyNGReport" + DateTime.Now.ToString("ddMMyyyy hh_mm_s_tt") + ".xls");
 }

for now, i just display the header only.

dharmendr
 
on Nov 15, 2021 02:53 AM

Hi nabilabolo,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        NorthwindEntities entities = new NorthwindEntities();
        return View(from customer in entities.Customers.Take(10)
                    select customer);
    }

    [HttpPost]
    public FileResult Export()
    {
        NorthwindEntities entities = new NorthwindEntities();
        var sbHtml = new System.Text.StringBuilder();
        sbHtml.Append("<table><tr><td colspan='7' style='text-align: center; font-size:26px'><b>MONTHLY REPORT</b></td></tr></table><br/>");
        sbHtml.Append("<table><tr><td colspan='7'><b>OVERALL SUMMARY</b></td></tr></table><br/><br/>");
        sbHtml.Append("<table border='1'");
        sbHtml.Append("<tr>");
        sbHtml.Append("<td>CustomerId</td>");
        sbHtml.Append("<td style='text-align:center'>Name</td>");
        sbHtml.Append("<td style='text-align:center'>City</td>");
        sbHtml.Append("<td style='text-align:center'>Country</td>");
        sbHtml.Append("</tr>");

        var customers = from customer in entities.Customers.Take(10)
                        select customer;

        foreach (var customer in customers)
        {
            sbHtml.Append("<tr>");
            sbHtml.Append("<td>" + customer.CustomerID + "</td>");
            sbHtml.Append("<td>" + customer.ContactName + "</td>");
            sbHtml.Append("<td>" + customer.City + "</td>");
            sbHtml.Append("<td>" + customer.Country + "</td>");
            sbHtml.Append("</tr>");
        }
        sbHtml.Append("</table>");
        byte[] fileContents = System.Text.Encoding.Default.GetBytes(sbHtml.ToString());
        return File(fileContents, "application/vnd.ms-excel", "Grid.xls");
    }
}

View

@model IEnumerable<Export_Excel_MVC.Customer>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <h4>Customers</h4>
    <hr />
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>CustomerID</th>
            <th>ContactName</th>
            <th>City</th>
            <th>Country</th>
        </tr>
        @foreach (Customer customer in Model)
        {
            <tr>
                <td>@customer.CustomerID</td>
                <td>@customer.ContactName</td>
                <td>@customer.City</td>
                <td>@customer.Country</td>
            </tr>
        }
    </table>
    <br />
    <br />
    @using (Html.BeginForm("Export", "Home", FormMethod.Post))
    {
        <input type="submit" value="Export" />
    }
</body>
</html>

Screenshot

nabilabolo
 
on Nov 15, 2021 07:01 PM

but this one is for only one sheet right? How about download on multiple sheet

https://pasteboard.co/Rr14uBfyPzv4.png

 

 

dharmendr
 
on Nov 16, 2021 03:54 AM

You are not creating an Excel (or Excel WorkSheets), instead you are trying to open HTML content in Excel format.

So Use Microsoft's OpenXml SDK, through which you can create any number of Sheets and inject data into sheets.

You can try the code from below link.

https://www.codeproject.com/Articles/18189/Exporting-Data-into-Excel-in-Multiple-Worksheets