Blazor Export Excel based on template excel file C#

 Target:

i have mudtable contains data from model object.

i need when user press on export excel button to load object data for mudtable to ready excel file template and write data for this template then download it for end user on the browser.

Used Nuget: NPOI version 2.6.0

Create custom component for export excel ExportExcel.razor

@inherits BaseComponentCustom

<a href="" @onclick="Export" class="btn btn-white btn-sm  shadow-sm fw-bold" data-bs-toggle="modal" data-bs-target="#kt_share">

    @trans["ExportData"]

    <!--begin:: Icon-->

    <img src="assets/media/svg/icon/share-qoute.svg" class="h-25px">

    <!--end:: Icon-->

</a>

*-------------------------------------------------------------------------------

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

namespace YourNameSpace;

public partial class ExportExcel

{

    private FileStream _file;

    [Parameter] public ExcelDataObject? ExcelDataObject{ get; set; }

    private async void Export()

    {

        if (ExcelDataObject!= null)

        {

            string rootpath = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot/assets/Templates/");

            string name = "TemplateFile.xlsx";

            string FileName = Path.Combine(rootpath, name);

            IWorkbook _workbook;

            var memoryStream = new MemoryStream();

            ISheet excelSheet;

            _file?.Close();

            var bytes = new byte[0];

            _file = new FileStream(FileName, FileMode.Open, FileAccess.Read);

            {

                    _workbook = new XSSFWorkbook(_file);

                    _workbook.MissingCellPolicy = MissingCellPolicy.RETURN_NULL_AND_BLANK;

                _file.Close();

            }

            excelSheet = _workbook.GetSheetAt(0);

            IWorkbook workbook = excelSheet.Workbook;

            int i = 1;

            foreach (var member in ExcelDataObject.ListToGetColumnsData)

            {

                IRow row = excelSheet.CreateRow(i);

                row.CreateCell(0).SetCellValue(member.cell0Value);

                row.CreateCell(1).SetCellValue(member.cell2Value);

                row.CreateCell(2).SetCellValue(member.cell3Value);

               );

                i++;

            }

            using (var ms = new MemoryStream())

            {

                workbook.Write(ms, false);

                bytes = ms.ToArray();

            }

            await SaveAs(JS, FileName + ".xlsx", bytes);

        }

        async Task SaveAs(IJSRuntime js, string fileName, byte[] data)

        {

            await js.InvokeAsync<object>(

                "BlazorDownloadFile",

                fileName,

                Convert.ToBase64String(data)

            );

        }

    }

}

---------------------------------------------------------------------------------------------

Javascript Function

function BlazorDownloadFile(filename, bytesBase64) {

    var link = document.createElement('a');

    link.download = filename;

    link.href = "data:application/octet-stream;base64," + bytesBase64;

    document.body.appendChild(link); // Needed for Firefox

    link.click();

    document.body.removeChild(link);

}

Gridview format date string using string.format

example

    <asp:TemplateField HeaderText="من تاريخ">
                                                                    <ItemTemplate>
                                                                        <asp:Label runat="server" ID="LBLhIKELTEXT" Text='<%# String.Format("{0:yyyy/MM/dd}", Eval("FROM_DATE")) %>'></asp:Label>
                                                                    </ItemTemplate>
                                                                </asp:TemplateField>

export windows form datagridview to excel file using c#

1- add reference to the library
Microsoft.Office.Interop.Excel
2- copy past the following funciton to export excel sheet (it saved in temp directory for current user )

System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();


            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);


            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            // see the excel sheet behind the program
            app.Visible = true;

            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            worksheet =(Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets["Sheet1"];
            worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;

            // changing the name of active sheet
            worksheet.Name = "Exported from gridview";


            // storing header part in Excel
            for (int i = 1; i < grdCity.Columns.Count + 1; i++)
            {
                worksheet.Cells[1, i] = grdCity.Columns[i - 1].HeaderText;
            }



            // storing Each row and column value to excel sheet
            for (int i = 0; i < grdCity.Rows.Count - 1; i++)
            {
                for (int j = 0; j < grdCity.Columns.Count-1; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = grdCity.Rows[i].Cells[j].Value.ToString();
                }
            }

            string myTempFile = Path.Combine(Path.GetTempPath(), "ratings.xlsx");
            // save the application
            workbook.SaveCopyAs(myTempFile);// (myTempFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            worksheet = null;
            workbook = null;
           // File.Open(myTempFile, FileMode.Open);
            // Exit from the application
            app.Quit();

----------------------------------------------------------------------------------
Export DataTable To Excel

 public static  void ExporttoExcel(DataTable table)
     {
         var sb = new StringBuilder();
         HttpContext.Current.Response.Clear();
         HttpContext.Current.Response.ClearContent();
         HttpContext.Current.Response.ClearHeaders();
         HttpContext.Current.Response.Buffer = true;
         HttpContext.Current.Response.ContentType = "application/ms-excel;charset=windows-1256";
         sb.Append(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
         HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
         HttpContext.Current.Response.Charset = "UTF-8";
         HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
         sb.Append("<font style='font-size:10.0pt; font-family:Calibri;'>");
         sb.Append("<BR><BR><BR>");
         //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
         sb.Append("<Table border='1' bgColor='#ffffff' " +
           "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
           "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
         //am getting my grid's column headers
         int columnscount = table.Columns.Count;

         for (int j = 0; j < columnscount; j++)
         {      //write in new column
             sb.Append("<Td>");
             //Get column headers  and make it as bold in excel columns
             sb.Append("<B>");
             sb.Append(table.Columns[j].ColumnName.ToString());
             sb.Append("</B>");
             sb.Append("</Td>");
         }
         sb.Append("</TR>");
         foreach (DataRow row in table.Rows)
         {//write in new row
             sb.Append("<TR>");
             for (int i = 0; i < table.Columns.Count; i++)
             {
                 sb.Append("<Td>");
                 sb.Append(row[i].ToString());
                 sb.Append("</Td>");
             }

             sb.Append("</TR>");
         }
         sb.Append("</Table>");
         sb.Append("</font>");
         HttpContext.Current.Response.Write(sb);
         HttpContext.Current.Response.Flush();
         HttpContext.Current.Response.End();
     }