Not able to print data table value in particular excel column using C#.NET

See more:

C#

MS-Excel

I am struggling last few days to print data table value in particular excel column.

I have the master table with data.

When i print master table data to excel, there is a condition to swap the column based on the reference table data
and print.

step1
-----
Read the master table. screen shot below

Master Table


step2
-----
While printing the data into excel, first refer the column as mentioned below and swap the column and then print

Reference Column


step3
-----
Final Output in the excel after swaping the columns.

Note : pls. note down the master table and output in excel, there will be swaping the columns.
since the column has been refrenced.

Output Template

My Source Code :

pls. have a look inside the code, and let me know where we have to change the code to print appropriate columns in excel...

Waiting for favourable reply.

What I have tried:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office;

namespace WinForm
{
    public partial class Form1 : Form
    {
        public string country = string.Empty;
        public Excel.Application xlApp = null;
        public Excel.Workbook XLWB = null;
        public Excel.Worksheet wsht = null;
        int IdxCurr = 0;
        int IdxPrev = 0;
        string colLineNoToPrint;

        public Form1()
        {
            InitializeComponent();
          
        }

        private void button1_Click(object sender, EventArgs e)
        {
            country = comboBox1.SelectedItem.ToString();
            DataTable boundTable = new DataTable();
            DataTable dtRefColumn = RefColumn();
            if (!string.IsNullOrEmpty(country))
            {

                IEnumerable<DataRow> query = from customer in dtRefColumn.AsEnumerable()
                                where customer.Field<string>("Country_Name") == country
                                select customer;
             

                boundTable = query.CopyToDataTable<DataRow>();
            }
            else
            {

            }

            DataTable dtMaster = dtMasterData();

            //print data to output template according to reference column
            SaveToExcel(dtMaster, dtRefColumn, country);

         
        }

        public void SaveToExcel(DataTable dtMaster, DataTable dtReftable, string country)
        {
            xlApp = new Excel.Application();
            xlApp.Workbooks.Add(true);
            wsht = xlApp.Worksheets.Add();
            wsht.Name = "S2";

            if (dtMaster.Rows.Count > 0)
            {
                for (int i = 0; i < dtMaster.Rows.Count; i++)
                {

                         //************* Struggling to print rows from here ****************
                         DataView dv = new DataView(dtMaster);

                         DataTable dt = dv.ToTable(true, dtMaster.Columns[i].ToString());

                        //Get Column Name
                        string colname = dtMaster.Columns[i].ToString();
                        //Get Reference column 
                        string RefCol = (dtReftable.AsEnumerable().Where(p => p.Field<string>("Country_Name") == country && p.Field<string>("Column_Name") == colname).Select(p => p.Field<string>("Excel_column"))).FirstOrDefault();
                        int TotRows = dtMaster.Rows.Count;
                        TotRows = 12 + TotRows;

                       
                       //To Print : Excel rows Should start to print from Rows 10
                        wsht.Cells[i][10] = dt.Rows[i].ToString();
                       //************* Struggling to print rows from here ****************
                  
                }
            }
            xlApp.Visible = true;
            xlApp.ActiveWorkbook.SaveAs(@"C:\Users\CompUMZ1A\Desktop\test.xlsx");
            xlApp.Quit();
        }

        public static DataTable dtMasterData()
        {

                DataTable tblData = new DataTable();
                tblData.Columns.Add("Prod_ID", typeof(string));
                tblData.Columns.Add("Prod_Name", typeof(string));
                tblData.Columns.Add("Prod_Quantity", typeof(string));
                tblData.Columns.Add("Prod_Expiry", typeof(string));
                tblData.Columns.Add("Prod_Manf_Date", typeof(string));
                tblData.Columns.Add("Prod_Region", typeof(string));
                tblData.Columns.Add("Prod_Head", typeof(string));
                tblData.Rows.Add("2020-1A", "Horlicks", "5000", "10-Jun-20", "05-Jan-20", "NewYork", "Sharuk");
                tblData.Rows.Add("2020-1B", "VIVA", "2000", "10-Jun-20", "05-Jan-20", "California", "Amit");
                tblData.Rows.Add("2020-1C", "Complan", "30000", "10-Jun-20", "05-Jan-20", "Mexico", "John");
                tblData.Rows.Add("2020-1D", "Bournvita", "10000", "10-Jun-20", "05-Jan-20", "NewJersy", "Rauf");

                return tblData;
          
        }


        public static DataTable RefColumn()
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Column_ID", typeof(string));
            dataTable.Columns.Add("Column_Name", typeof(string));
            dataTable.Columns.Add("Excel_column", typeof(string));
            dataTable.Columns.Add("Country_Name", typeof(string));
            dataTable.Columns.Add("Status", typeof(string));
            dataTable.Rows.Add("Prod_ID", "Prod.No", "H", "USA", "Active");
            dataTable.Rows.Add("Prod_Name", "Prod.Name", "A", "USA", "Active");
            dataTable.Rows.Add("Prod_Quantity", "Quantity", "C", "USA", "Active");
            dataTable.Rows.Add("Prod_Expiry", "Expiry Date", "D", "USA", "Active");
            dataTable.Rows.Add("Prod_Manf_Date", "Manufacturing Date", "E", "USA", "Active");
            dataTable.Rows.Add("Prod_Region", "Region", "B", "USA", "Active");
            dataTable.Rows.Add("Prod_Head", "Head of Region", "G", "USA", "Active");
            dataTable.Rows.Add("Prod_ID", "Prod. No", "B", "UK", "Active");
            dataTable.Rows.Add("Prod_Name", "Prod. Name", "A", "UK", "Active");
            dataTable.Rows.Add("Prod_Quantity", "Quantity", "X", "UK", "Active");
            dataTable.Rows.Add("Prod_Expiry", "Expiry Date", "M", "UK", "Active");
            dataTable.Rows.Add("Prod_Manf_Date", "Manufacturing Date", "N", "UK", "Active");
            dataTable.Rows.Add("Prod_Region", "Region", "O", "UK", "Active");
            dataTable.Rows.Add("Prod_Head", "Head of Region", "K", "UK", "Active");
            dataTable.Rows.Add("Prod_ID", "Prod. No", "A", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Name", "Prod. Name", "B", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Quantity", "Quantity", "C", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Expiry", "Expiry Date", "D", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Manf_Date", "Manufacturing Date", "E", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Region", "Region", "F", "INDIA", "Active");
            dataTable.Rows.Add("Prod_Head", "Head of Region", "F", "INDIA", "Active");

            return dataTable;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            comboBox1.Items.Add("USA");
            comboBox1.Items.Add("UK");
            comboBox1.Items.Add("INDIA");
        }
    }
}