locked
export my repater with perfect alignment same as output RRS feed

Answers

  • User283571144 posted

    Hi Gopi.MCA,

    Any one here to help me how to set border line in excel export..

    As far as I know, we could not export style code directly to excel file.

    We have to use 3rd library. I suggest use NPOI library to render style to excel by using below codes.

     style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;// we could also set the BorderStyle to BorderStyle.Medium

    You could install it from Nuget.

    https://www.nuget.org/packages/NPOI.Excel/

    More detail, you could refer to code below:

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="repeater.aspx.cs" Inherits="WebApplication.views.repeater" %>
     
    <!DOCTYPE html>
     
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" />
    </head>
    <body>
        <form id="form1" runat="server">
            <p>
                <asp:Button ID="btnExport" runat="server" Text="Export via NPOI" OnClick="btnExport_Click" />
            </p>
            <div>
                <asp:Repeater ID="rptData" runat="server">
                    <HeaderTemplate>
                        <table class="table table-bordered bg-secondary">
                            <thead>
                                <tr>
                                    <th class="text-center">BatchNo</th>
                                    <th class="text-center">Total Qty</th>
                                    <th colspan="2" class="text-center">Dep1</th>
                                    <th colspan="2" class="text-center">Dep2</th>
                                    <th colspan="2" class="text-center">Total For Dep1 & Dep2</th>
                                </tr>
                                <tr>
                                    <th class="text-center"></th>
                                    <th class="text-center"></th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Total Qty</th>
                                    <th class="text-center">Total Value</th>
                                </tr>
                            </thead>
                            <tbody>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td class="text-center"><%#Eval("BatchNo") %></td>
                            <td class="text-center"><%#Eval("TotalQty") %></td>
                            <td class="text-center"><%#Eval("Dep1Qty") %></td>
                            <td class="text-center"><%#Eval("Dep1Value") %></td>
                            <td class="text-center"><%#Eval("Dep2Qty") %></td>
                            <td class="text-center"><%#Eval("Dep2Value") %></td>
                            <td class="text-center"><%#Eval("DepTotalQty") %></td>
                            <td class="text-center"><%#Eval("DepTotalValue") %></td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                        </tr>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center">Total</td>
                            <td class="text-center"><asp:Literal ID="TotalQTY" runat="server"></asp:Literal></td>
                            <td class="text-center"><asp:Literal ID="TotalValue" runat="server"></asp:Literal></td>
                        </tr>
                        </tbody>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
            </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using System;
    using System.Data;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
     
    namespace WebApplication.views
    {
        public partial class repeater : System.Web.UI.Page
        {
            private static DataTable source = new DataTable();
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    source.Columns.Add(new DataColumn("BatchNo"));
                    source.Columns.Add(new DataColumn("TotalQty"));
                    source.Columns.Add(new DataColumn("Dep1Qty"));
                    source.Columns.Add(new DataColumn("Dep1Value"));
                    source.Columns.Add(new DataColumn("Dep2Qty"));
                    source.Columns.Add(new DataColumn("Dep2Value"));
                    source.Columns.Add(new DataColumn("DepTotalQty"));
                    source.Columns.Add(new DataColumn("DepTotalValue"));
     
                    DataRow row1 = source.NewRow();
                    row1["BatchNo"] = "B1";
                    row1["TotalQty"] = "100";
                    row1["Dep1Qty"] = "50";
                    row1["Dep1Value"] = "1000";
                    row1["Dep2Qty"] = "60";
                    row1["Dep2Value"] = "2000";
                    row1["DepTotalQty"] = "110";
                    row1["DepTotalValue"] = "3000";
                    source.Rows.Add(row1);
     
                    DataRow row2 = source.NewRow();
                    row2["BatchNo"] = "B1";
                    row2["TotalQty"] = "150";
                    row2["Dep1Qty"] = "10";
                    row2["Dep1Value"] = "4000";
                    row2["Dep2Qty"] = "20";
                    row2["Dep2Value"] = "5000";
                    row2["DepTotalQty"] = "30";
                    row2["DepTotalValue"] = "9000";
                    source.Rows.Add(row2);
     
                    DataRow row3 = source.NewRow();
                    row3["BatchNo"] = "B3";
                    row3["TotalQty"] = "200";
                    row3["Dep1Qty"] = "0";
                    row3["Dep1Value"] = "0";
                    row3["Dep2Qty"] = "60";
                    row3["Dep2Value"] = "10000";
                    row3["DepTotalQty"] = "60";
                    row3["DepTotalValue"] = "10000";
                    source.Rows.Add(row3);
                }
     
                this.rptData.DataSource = source;
                this.rptData.DataBind();
     
                //get the footer of the reperter
                Control footerControl = this.rptData.Controls[1 + source.Rows.Count];
     
                // set the totalqty and total value
                Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                literal.Text = "200";
                literal = footerControl.FindControl("TotalValue") as Literal;
                literal.Text = "22000";
            }
     
            protected void btnExport_Click(object sender, EventArgs e)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
     
                try
                {
                    NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
     
                    ICellStyle style = book.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
     
     
                    NPOI.SS.UserModel.IRow header1 = sheet.CreateRow(0);
                    header1.HeightInPoints = 20;
     
                    ICell cellBatchNo = header1.CreateCell(0);
                    cellBatchNo.CellStyle = style;
                    cellBatchNo.SetCellValue("BatchNo");
     
                    ICell cellTotalQty = header1.CreateCell(1);
                    cellTotalQty.CellStyle = style;
                   cellTotalQty.SetCellValue("Total Qty");
     
                    ICell cellDep1 = header1.CreateCell(2);
                    cellDep1.CellStyle = style;
                    cellDep1.SetCellValue("Dep1");
     
                    ICell cellDep2 = header1.CreateCell(4);
                    cellDep2.CellStyle = style;
                    cellDep2.SetCellValue("Dep2");
     
                    ICell cellTotalForDep1Dep2 = header1.CreateCell(6);
                    cellTotalForDep1Dep2.CellStyle = style;
                    cellTotalForDep1Dep2.SetCellValue("Total For Dep1 & Dep2");
     
                    ICell cellEmpty3 = header1.CreateCell(7);
                    cellEmpty3.CellStyle = style;
                    cellEmpty3.SetCellValue("");
     
                    CellRangeAddress cellRangeAddressDep1 = new CellRangeAddress(0, 0, 2, 3);
                    sheet.AddMergedRegion(cellRangeAddressDep1);
     
                    CellRangeAddress cellRangeAddressDep2 = new CellRangeAddress(0, 0, 4, 5);
                    sheet.AddMergedRegion(cellRangeAddressDep2);
     
                    CellRangeAddress cellRangeAddressTotalForDep1Dep2 = new CellRangeAddress(0, 0, 6, 7);
                    sheet.AddMergedRegion(cellRangeAddressTotalForDep1Dep2);
     
                    NPOI.SS.UserModel.IRow header2 = sheet.CreateRow(1);
                    header2.HeightInPoints = 20;
     
                    ICell cellEmpty1 = header2.CreateCell(0);
                    cellEmpty1.CellStyle = style;
                    cellEmpty1.SetCellValue("");
     
                    ICell cellEmpty2 = header2.CreateCell(1);
                    cellEmpty2.CellStyle = style;
                    cellEmpty2.SetCellValue("");
     
                    ICell cellQty1 = header2.CreateCell(2);
                    cellQty1.CellStyle = style;
                    cellQty1.SetCellValue("Qty");
     
                    ICell cellValue1 = header2.CreateCell(3);
                    cellValue1.CellStyle = style;
                    cellValue1.SetCellValue("Value");
     
                    ICell cellQty2 = header2.CreateCell(4);
                    cellQty2.CellStyle = style;
                    cellQty2.SetCellValue("Qty");
     
                    ICell cellValue2 = header2.CreateCell(5);
                    cellValue2.CellStyle = style;
                    cellValue2.SetCellValue("Value");
     
                    ICell cellTotalQty2 = header2.CreateCell(6);
                    cellTotalQty2.CellStyle = style;
                    cellTotalQty2.SetCellValue("Total Qty");
     
                    ICell cellTotalValue2 = header2.CreateCell(7);
                    cellTotalValue2.CellStyle = style;
                    cellTotalValue2.SetCellValue("Total Value");
     
                    int rowIndex = 2;
                    for (int i = 0; i < source.Rows.Count; i++)
                    {
                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
                        row.HeightInPoints = 20;
     
                        for (int x = 0; x < source.Columns.Count; x++)
                        {
                            ICell cell = row.CreateCell(x);
                            cell.CellStyle = style;
                            cell.SetCellValue(source.Rows[i][x].ToString());
                        }
     
                        rowIndex++;
                    }
     
                    using (MemoryStream ms = new MemoryStream())
                    {
                        book.Write(ms);
                        Response.AddHeader("Content-Disposition", "attachment; filename=RepeaterExport.xls");
                        Response.BinaryWrite(ms.ToArray());
                        Response.End();
                    }
                }
                catch
                {
     
                }
                finally
                {
                    book = null;
                }
     
            }
        }
    }
    

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 30, 2018 9:10 AM

All replies

  • Wednesday, August 15, 2018 11:28 AM
  • User283571144 posted

    Hi Gopi.MCA,

    According to your description, I suggest you could use the HtmlTextWriter to export the table to excel.

    Notice: This way doesn't support export the CSS style to the excel.

    It just suggort default table property like border.

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReapterExportExcel.aspx.cs" Inherits="AspNetNormalIssue.Webform.ReapterExportExcel" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
                    <p>
                <asp:Button ID="btnExport" runat="server" Text="Export" OnClick="btnExport_Click" />
            </p>
    
            <div>
                           <asp:Repeater ID="rptData" runat="server">
                    <HeaderTemplate>
                        <table   border="1">
                            <thead>
                                <tr>
                                    <th class="text-center">BatchNo</th>
                                    <th class="text-center">Total Qty</th>
                                    <th colspan="2" class="text-center">Dep1</th>
                                    <th colspan="2" class="text-center">Dep2</th>
                                    <th colspan="2" class="text-center">Total For Dep1 & Dep2</th>
                                </tr>
                                <tr>
                                    <th class="text-center"></th>
                                    <th class="text-center"></th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Total Qty</th>
                                    <th class="text-center">Total Value</th>
                                </tr>
                            </thead>
                            <tbody>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td class="text-center"><%#Eval("BatchNo") %></td>
                            <td class="text-center"><%#Eval("TotalQty") %></td>
                            <td class="text-center"><%#Eval("Dep1Qty") %></td>
                            <td class="text-center"><%#Eval("Dep1Value") %></td>
                            <td class="text-center"><%#Eval("Dep2Qty") %></td>
                            <td class="text-center"><%#Eval("Dep2Value") %></td>
                            <td class="text-center"><%#Eval("DepTotalQty") %></td>
                            <td class="text-center"><%#Eval("DepTotalValue") %></td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                        </tr>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center">Total</td>
                            <td class="text-center"><asp:Literal ID="TotalQTY" runat="server"></asp:Literal></td>
                            <td class="text-center"><asp:Literal ID="TotalValue" runat="server"></asp:Literal></td>
                        </tr>
                        </tbody>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
    
            </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace AspNetNormalIssue.Webform
    {
        public partial class ReapterExportExcel : System.Web.UI.Page
        {
            private  static DataTable source = new DataTable();
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    source.Columns.Add(new DataColumn("BatchNo"));
                    source.Columns.Add(new DataColumn("TotalQty"));
                    source.Columns.Add(new DataColumn("Dep1Qty"));
                    source.Columns.Add(new DataColumn("Dep1Value"));
                    source.Columns.Add(new DataColumn("Dep2Qty"));
                    source.Columns.Add(new DataColumn("Dep2Value"));
                    source.Columns.Add(new DataColumn("DepTotalQty"));
                    source.Columns.Add(new DataColumn("DepTotalValue"));
    
                    DataRow row1 = source.NewRow();
                    row1["BatchNo"] = "B1";
                    row1["TotalQty"] = "100";
                    row1["Dep1Qty"] = "50";
                    row1["Dep1Value"] = "1000";
                    row1["Dep2Qty"] = "60";
                    row1["Dep2Value"] = "2000";
                    row1["DepTotalQty"] = "110";
                    row1["DepTotalValue"] = "3000";
                    source.Rows.Add(row1);
    
                    DataRow row2 = source.NewRow();
                    row2["BatchNo"] = "B1";
                    row2["TotalQty"] = "150";
                    row2["Dep1Qty"] = "10";
                    row2["Dep1Value"] = "4000";
                    row2["Dep2Qty"] = "20";
                    row2["Dep2Value"] = "5000";
                    row2["DepTotalQty"] = "30";
                    row2["DepTotalValue"] = "9000";
                    source.Rows.Add(row2);
    
                    DataRow row3 = source.NewRow();
                    row3["BatchNo"] = "B3";
                    row3["TotalQty"] = "200";
                    row3["Dep1Qty"] = "0";
                    row3["Dep1Value"] = "0";
                    row3["Dep2Qty"] = "60";
                    row3["Dep2Value"] = "10000";
                    row3["DepTotalQty"] = "60";
                    row3["DepTotalValue"] = "10000";
                    source.Rows.Add(row3);
                }
    
                this.rptData.DataSource = source;
                this.rptData.DataBind();
    
                //get the footer of the reperter
                Control footerControl = this.rptData.Controls[1 + source.Rows.Count];
    
                // set the totalqty and total value
                Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                literal.Text = "200";
                literal = footerControl.FindControl("TotalValue") as Literal;
                literal.Text = "22000";
    
            }
    
    
            protected void btnExport_Click(object sender, EventArgs e)
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=RepeaterExport.xls");
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.Charset = "UTF-8";
                Response.ContentType = "application/vnd.ms-excel";
                this.rptData.Page.EnableViewState = false;
                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);
                this.rptData.RenderControl(hw);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
    
        }
    }

    Result:

    Best Regards,

    Brando

    Thursday, August 16, 2018 8:00 AM
  • User-807418713 posted

    Hello

    Thanks for your code

    In excel its not showing border line 1px 

    it just showing plain 

    Friday, August 17, 2018 6:20 PM
  • User-1171043462 posted

    100% exact output you won't get.

    Friday, August 17, 2018 8:40 PM
  • User-807418713 posted

    Hello

    I mean to say how to add this code

     <style type="text/css">
        table {
        border-collapse: collapse;
    }
        
        table, th, tr, td {
        border: 1px solid black;
    }
    </style>

    so that on export excel look perfect

    Saturday, August 18, 2018 5:04 AM
  • User-807418713 posted

    Hello

    Any one here to help me how to set border line in excel export..

    Thanking You

    Thursday, August 30, 2018 5:34 AM
  • User283571144 posted

    Hi Gopi.MCA,

    Any one here to help me how to set border line in excel export..

    As far as I know, we could not export style code directly to excel file.

    We have to use 3rd library. I suggest use NPOI library to render style to excel by using below codes.

     style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;// we could also set the BorderStyle to BorderStyle.Medium

    You could install it from Nuget.

    https://www.nuget.org/packages/NPOI.Excel/

    More detail, you could refer to code below:

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="repeater.aspx.cs" Inherits="WebApplication.views.repeater" %>
     
    <!DOCTYPE html>
     
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" />
    </head>
    <body>
        <form id="form1" runat="server">
            <p>
                <asp:Button ID="btnExport" runat="server" Text="Export via NPOI" OnClick="btnExport_Click" />
            </p>
            <div>
                <asp:Repeater ID="rptData" runat="server">
                    <HeaderTemplate>
                        <table class="table table-bordered bg-secondary">
                            <thead>
                                <tr>
                                    <th class="text-center">BatchNo</th>
                                    <th class="text-center">Total Qty</th>
                                    <th colspan="2" class="text-center">Dep1</th>
                                    <th colspan="2" class="text-center">Dep2</th>
                                    <th colspan="2" class="text-center">Total For Dep1 & Dep2</th>
                                </tr>
                                <tr>
                                    <th class="text-center"></th>
                                    <th class="text-center"></th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Qty</th>
                                    <th class="text-center">Value</th>
                                    <th class="text-center">Total Qty</th>
                                    <th class="text-center">Total Value</th>
                                </tr>
                            </thead>
                            <tbody>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td class="text-center"><%#Eval("BatchNo") %></td>
                            <td class="text-center"><%#Eval("TotalQty") %></td>
                            <td class="text-center"><%#Eval("Dep1Qty") %></td>
                            <td class="text-center"><%#Eval("Dep1Value") %></td>
                            <td class="text-center"><%#Eval("Dep2Qty") %></td>
                            <td class="text-center"><%#Eval("Dep2Value") %></td>
                            <td class="text-center"><%#Eval("DepTotalQty") %></td>
                            <td class="text-center"><%#Eval("DepTotalValue") %></td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                        </tr>
                        <tr>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center"></td>
                            <td class="text-center">Total</td>
                            <td class="text-center"><asp:Literal ID="TotalQTY" runat="server"></asp:Literal></td>
                            <td class="text-center"><asp:Literal ID="TotalValue" runat="server"></asp:Literal></td>
                        </tr>
                        </tbody>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
            </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using System;
    using System.Data;
    using System.IO;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
     
    namespace WebApplication.views
    {
        public partial class repeater : System.Web.UI.Page
        {
            private static DataTable source = new DataTable();
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    source.Columns.Add(new DataColumn("BatchNo"));
                    source.Columns.Add(new DataColumn("TotalQty"));
                    source.Columns.Add(new DataColumn("Dep1Qty"));
                    source.Columns.Add(new DataColumn("Dep1Value"));
                    source.Columns.Add(new DataColumn("Dep2Qty"));
                    source.Columns.Add(new DataColumn("Dep2Value"));
                    source.Columns.Add(new DataColumn("DepTotalQty"));
                    source.Columns.Add(new DataColumn("DepTotalValue"));
     
                    DataRow row1 = source.NewRow();
                    row1["BatchNo"] = "B1";
                    row1["TotalQty"] = "100";
                    row1["Dep1Qty"] = "50";
                    row1["Dep1Value"] = "1000";
                    row1["Dep2Qty"] = "60";
                    row1["Dep2Value"] = "2000";
                    row1["DepTotalQty"] = "110";
                    row1["DepTotalValue"] = "3000";
                    source.Rows.Add(row1);
     
                    DataRow row2 = source.NewRow();
                    row2["BatchNo"] = "B1";
                    row2["TotalQty"] = "150";
                    row2["Dep1Qty"] = "10";
                    row2["Dep1Value"] = "4000";
                    row2["Dep2Qty"] = "20";
                    row2["Dep2Value"] = "5000";
                    row2["DepTotalQty"] = "30";
                    row2["DepTotalValue"] = "9000";
                    source.Rows.Add(row2);
     
                    DataRow row3 = source.NewRow();
                    row3["BatchNo"] = "B3";
                    row3["TotalQty"] = "200";
                    row3["Dep1Qty"] = "0";
                    row3["Dep1Value"] = "0";
                    row3["Dep2Qty"] = "60";
                    row3["Dep2Value"] = "10000";
                    row3["DepTotalQty"] = "60";
                    row3["DepTotalValue"] = "10000";
                    source.Rows.Add(row3);
                }
     
                this.rptData.DataSource = source;
                this.rptData.DataBind();
     
                //get the footer of the reperter
                Control footerControl = this.rptData.Controls[1 + source.Rows.Count];
     
                // set the totalqty and total value
                Literal literal = footerControl.FindControl("TotalQTY") as Literal;
                literal.Text = "200";
                literal = footerControl.FindControl("TotalValue") as Literal;
                literal.Text = "22000";
            }
     
            protected void btnExport_Click(object sender, EventArgs e)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
     
                try
                {
                    NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
     
                    ICellStyle style = book.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
     
     
                    NPOI.SS.UserModel.IRow header1 = sheet.CreateRow(0);
                    header1.HeightInPoints = 20;
     
                    ICell cellBatchNo = header1.CreateCell(0);
                    cellBatchNo.CellStyle = style;
                    cellBatchNo.SetCellValue("BatchNo");
     
                    ICell cellTotalQty = header1.CreateCell(1);
                    cellTotalQty.CellStyle = style;
                   cellTotalQty.SetCellValue("Total Qty");
     
                    ICell cellDep1 = header1.CreateCell(2);
                    cellDep1.CellStyle = style;
                    cellDep1.SetCellValue("Dep1");
     
                    ICell cellDep2 = header1.CreateCell(4);
                    cellDep2.CellStyle = style;
                    cellDep2.SetCellValue("Dep2");
     
                    ICell cellTotalForDep1Dep2 = header1.CreateCell(6);
                    cellTotalForDep1Dep2.CellStyle = style;
                    cellTotalForDep1Dep2.SetCellValue("Total For Dep1 & Dep2");
     
                    ICell cellEmpty3 = header1.CreateCell(7);
                    cellEmpty3.CellStyle = style;
                    cellEmpty3.SetCellValue("");
     
                    CellRangeAddress cellRangeAddressDep1 = new CellRangeAddress(0, 0, 2, 3);
                    sheet.AddMergedRegion(cellRangeAddressDep1);
     
                    CellRangeAddress cellRangeAddressDep2 = new CellRangeAddress(0, 0, 4, 5);
                    sheet.AddMergedRegion(cellRangeAddressDep2);
     
                    CellRangeAddress cellRangeAddressTotalForDep1Dep2 = new CellRangeAddress(0, 0, 6, 7);
                    sheet.AddMergedRegion(cellRangeAddressTotalForDep1Dep2);
     
                    NPOI.SS.UserModel.IRow header2 = sheet.CreateRow(1);
                    header2.HeightInPoints = 20;
     
                    ICell cellEmpty1 = header2.CreateCell(0);
                    cellEmpty1.CellStyle = style;
                    cellEmpty1.SetCellValue("");
     
                    ICell cellEmpty2 = header2.CreateCell(1);
                    cellEmpty2.CellStyle = style;
                    cellEmpty2.SetCellValue("");
     
                    ICell cellQty1 = header2.CreateCell(2);
                    cellQty1.CellStyle = style;
                    cellQty1.SetCellValue("Qty");
     
                    ICell cellValue1 = header2.CreateCell(3);
                    cellValue1.CellStyle = style;
                    cellValue1.SetCellValue("Value");
     
                    ICell cellQty2 = header2.CreateCell(4);
                    cellQty2.CellStyle = style;
                    cellQty2.SetCellValue("Qty");
     
                    ICell cellValue2 = header2.CreateCell(5);
                    cellValue2.CellStyle = style;
                    cellValue2.SetCellValue("Value");
     
                    ICell cellTotalQty2 = header2.CreateCell(6);
                    cellTotalQty2.CellStyle = style;
                    cellTotalQty2.SetCellValue("Total Qty");
     
                    ICell cellTotalValue2 = header2.CreateCell(7);
                    cellTotalValue2.CellStyle = style;
                    cellTotalValue2.SetCellValue("Total Value");
     
                    int rowIndex = 2;
                    for (int i = 0; i < source.Rows.Count; i++)
                    {
                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex);
                        row.HeightInPoints = 20;
     
                        for (int x = 0; x < source.Columns.Count; x++)
                        {
                            ICell cell = row.CreateCell(x);
                            cell.CellStyle = style;
                            cell.SetCellValue(source.Rows[i][x].ToString());
                        }
     
                        rowIndex++;
                    }
     
                    using (MemoryStream ms = new MemoryStream())
                    {
                        book.Write(ms);
                        Response.AddHeader("Content-Disposition", "attachment; filename=RepeaterExport.xls");
                        Response.BinaryWrite(ms.ToArray());
                        Response.End();
                    }
                }
                catch
                {
     
                }
                finally
                {
                    book = null;
                }
     
            }
        }
    }
    

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 30, 2018 9:10 AM