locked
Not able to print data table value in particular column of MS-Excel using C#.NET RRS feed

  • Question

  • User-1024101449 posted

    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 :

    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");
            }
        }
    }

    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.

    Thursday, January 23, 2020 7:49 AM

All replies

  • User-1024101449 posted

    Any update pls.

    This is urgent and i want to implement like this concept in many places...

    Friday, January 24, 2020 4:31 AM