How to change Excel direction to RTL when exporting using OpenXML in ASP.Net

Last Reply on Jun 09, 2016 07:51 AM By Shashikant

Posted on Jun 08, 2016 07:57 AM

Hi
I export data to excel sheet from asp.net application using c# , Open XML
i want to export data in arabic and i want to convert sheet direction from LTR to be RTL.
Please give me a help , i am using open xml to export to excel.

Thanks

 

You are viewing reply posted by: Shashikant on Jun 09, 2016 07:51 AM.
Posted on Jun 09, 2016 07:51 AM

Hi,

Please refer below code

HTML

<div>
    <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
        runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
            <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
            <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
</div>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
        dt.Rows.Add(1, "انگریزی نہیں", "ے ایڈیٹر");
        dt.Rows.Add(2, "یہ انگریزی", " کو استعمال");
        dt.Rows.Add(3, "نہیں", "تمام لکھائی");
        dt.Rows.Add(4, "یہ", "ے ایڈیٹر");
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach (TableCell cell in GridView1.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }
    foreach (GridViewRow row in GridView1.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
        }
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt);
        wb.Worksheets.FirstOrDefault().RightToLeft = true;
        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();
        }
    }
}

Screenshot

1)

2)