Retain formulas and format while exporting to Excel with ClosedXML using C# and VB.Net in ASP.Net

Hi mahesh213,

Refer below code.

C#

protected void ExportToExcel(object sender, EventArgs e)
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("~/Customers.xml"));
    using (XLWorkbook wb = new XLWorkbook())
    {
        //Create a DataTable with schema same as DataSet Table columns.
        DataTable dt = new DataTable("Customers");
        foreach (DataColumn column in ds.Tables[0].Columns)
        {
            dt.Columns.Add(column.ColumnName);
        }

        DataRow dr = dt.NewRow();
        foreach (DataColumn column in ds.Tables[0].Columns)
        {
            dr[column.ColumnName] = column.ColumnName;
        }
        //Add Header rows from DataSet Table to DataTable.
        dt.Rows.Add(dr);
        //Loop and add rows from DataSet Table to DataTable.
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            dt.ImportRow(row);
        }

        var ws = wb.Worksheets.Add(dt.TableName);
        ws.Cell(1, 1).InsertData(dt.Rows);
        ws.Columns().AdjustToContents();

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ws.Cell((i + 2), (j + 1)).Value = ws.Cell((i + 2), (j + 1)).Value;
            }
            // Apply Formala to column 1.
            ws.Cell((i + 2), 1).FormulaA1 = ws.Cell((i + 2), 1).Value.ToString();
        }

        //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=Customers.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim ds As DataSet = New DataSet()
    'Create a DataTable with schema same as DataSet Table columns.
    ds.ReadXml(Server.MapPath("~/Customers.xml"))
    Using wb As XLWorkbook = New XLWorkbook()
        Dim dt As DataTable = New DataTable("Customers")
        For Each column As DataColumn In ds.Tables(0).Columns
            dt.Columns.Add(column.ColumnName)
        Next
        Dim dr As DataRow = dt.NewRow()
        For Each column As DataColumn In ds.Tables(0).Columns
            dr(column.ColumnName) = column.ColumnName
        Next
        'Add Header rows from DataSet Table to DataTable.
        dt.Rows.Add(dr)
        'Loop and add rows from DataSet Table to DataTable.
        For Each row As DataRow In ds.Tables(0).Rows
            dt.ImportRow(row)
        Next
        Dim ws = wb.Worksheets.Add(dt.TableName)
        ws.Cell(1, 1).InsertData(dt.Rows)
        ws.Columns().AdjustToContents()

        For i As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
                ws.Cell((i + 2), (j + 1)).Value = ws.Cell((i + 2), (j + 1)).Value
            Next
            ' Apply Formala to column 1.
            ws.Cell((i + 2), 1).FormulaA1 = ws.Cell((i + 2), 1).Value.ToString()
        Next

        '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=Customers.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Screenshot