How to write to excel file using Datarow in C# Wpf

Question

I have a Wpf application in which I want to insert data row to excel file. At the time when datarow is added to the datatable, I want that datarow to get saved in Excel file.

Datarow should be saved in excel file before this C# statement:

table.Rows.Add(datarow);

This process will be repeated each time when Datarow is added to datatable.It should not replace the previous data present in the Excel file, rather it should append datarow to excel file each time.

Answer 1

try the below code and make sure add Microsoft.Office.Interop.Excel dll reference from references.

If you want you can optimize the code further but the below one works for your basic understanding

using System;
using System.Data;
using System.Windows;
using Excel = Microsoft.Office.Interop.Excel;

namespace DatagridDemo
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            //Define Datatble
            DataTable dt = new DataTable();

            //Create Excel Application Instance
            Excel.Application ExcelApp = new Excel.Application();

            //Create workbook Instance and open the workbook from the below location
            Excel.Workbook ExcelWorkBook = ExcelApp.Workbooks.Open(@"E:\test.xlsx");

            dt.Columns.Add("EmpNo", typeof(int));
            dt.Columns.Add("EmpName", typeof(string));


            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                ExcelApp.Cells[1, i] = dt.Columns[i - 1].ColumnName;
            }

            //Create DataRow
            DataRow dr = dt.NewRow();

            dr[0] = 1;
            dr[1] = "ABC";

            ExcelApp.Cells[2, 1] = dr[0].ToString();
            ExcelApp.Cells[2, 2] = dr[1].ToString();

            dt.Rows.Add(dr);

            dr = dt.NewRow();

            dr[0] = 2;
            dr[1] = "DEF";

            ExcelApp.Cells[3, 1] = dr[0].ToString();
            ExcelApp.Cells[3, 2] = dr[1].ToString();

            dt.Rows.Add(dr);

            dr = dt.NewRow();

            dr[0] = 3;
            dr[1] = "XYZ";

            ExcelApp.Cells[4, 1] = dr[0].ToString();
            ExcelApp.Cells[4, 2] = dr[1].ToString();

            dt.Rows.Add(dr);

            //Save the workbook
            ExcelWorkBook.Save();

            //Close the workbook
            ExcelWorkBook.Close();

            //Quit the excel process
            ExcelApp.Quit();
        }
    }
}