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

  • Question

  • 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:48 AM

Answers

  • Hi Gani tpt,

    I change the 'SaveToExcel' method based on your description.

    you can refer to the following code:

            public void SaveToExcel(DataTable dtMaster, DataTable boundTable, int columnNumber)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Microsoft.Office.Interop.Excel._Workbook XLWB;
                Microsoft.Office.Interop.Excel._Worksheet wsht;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = true;
    
                XLWB = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
                wsht = (Microsoft.Office.Interop.Excel._Worksheet)XLWB.ActiveSheet;
                wsht.Name = "S2";
                int rowcount = dtMaster.Rows.Count;
                
                if (dtMaster.Rows.Count > 0)
                {
                    for (int i = 0; i < dtMaster.Columns.Count; i++)
                    {
                        var collection = boundTable.AsEnumerable().Where(p => p.Field<string>("Column_ID") == dtMaster.Columns[i].ColumnName);
                        string Name = collection.Select(p => p.Field<string>("Column_Name")).FirstOrDefault();
                        string column = collection.Select(p => p.Field<string>("Excel_column")).FirstOrDefault();
                        string[] arr1 = dtMaster.AsEnumerable().Select(r => r.Field<string>(dtMaster.Columns[i].ColumnName)).ToArray();
                        
                        string[,] arr2 = new string[arr1.Length, 1];
                        for (int j=0;j<arr1.Length;j++)
                        {
                            arr2[j, 0] = arr1[j];
                        }
                        int number = ExcelColumnNameToNumber(column);
                        wsht.Cells[columnNumber, number].Value = Name;
                        string rangeTop = column + (columnNumber + 1);
                      string rangeBottom = column + (columnNumber + arr1.Length);
                      wsht.get_Range(rangeTop, rangeBottom).Value2 = arr2;    
                      wsht.get_Range(rangeTop, rangeBottom).EntireColumn.AutoFit();               
                    }
                }
                xlApp.Visible = false;
                xlApp.UserControl = false;
                XLWB.SaveAs("your file path");
                XLWB.Close();
                xlApp.Quit();
            }

    Result of my test.

    SaveToExcel(dtMaster, boundTable,10);

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.





    Wednesday, January 29, 2020 1:48 AM
    Moderator

All replies

  • Any update pls.

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

    Friday, January 24, 2020 4:31 AM
  • Hi Gani tpt,

    Thank you for posting here.

    Check the folowing reference about writing some data to excel file.

    How to write some data to excel file(.xlsx)

    Then you can refer to the following code before writing data to excel:

                    for (int i = 0; i < dtMaster.Columns.Count; i++)
                    {
                        var collection = boundTable.AsEnumerable().Where(p => p.Field<string>("Column_ID") == dtMaster.Columns[i].ColumnName);
                        string Name = collection.Select(p => p.Field<string>("Column_Name")).FirstOrDefault();
                        string column = collection.Select(p => p.Field<string>("Excel_column")).FirstOrDefault();
                        string[] arr = dtMaster.AsEnumerable().Select(r => r.Field<string>(dtMaster.Columns[i].ColumnName)).ToArray();
                        //...                  
                    }

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 24, 2020 6:54 AM
    Moderator
  • Thanks for your reply.

    I am struggling to print in the appropriate column in the excel excel

    I am little bit confused to print the value to appropriate column of excel.

    Can you pls. give us some sample code to print in the appropriate column of excel...?

    Waiting....


    • Edited by Gani tpt Friday, January 24, 2020 11:43 AM
    Friday, January 24, 2020 7:42 AM
  • Hi Xingyu Zhao,

    Can you pls. update...?

    Friday, January 24, 2020 1:03 PM
  • Hi Gani tpt,

    Thanks for your feedback.

    I make a test on my side and hope the following code could be helpful.

            public void SaveToExcel(DataTable dtMaster, DataTable boundTable)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Microsoft.Office.Interop.Excel._Workbook XLWB;
                Microsoft.Office.Interop.Excel._Worksheet wsht;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = true;
    
                XLWB = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
                wsht = (Microsoft.Office.Interop.Excel._Worksheet)XLWB.ActiveSheet;
                wsht.Name = "S2";
                int rowcount = dtMaster.Rows.Count;
                
                if (dtMaster.Rows.Count > 0)
                {
                    for (int i = 0; i < dtMaster.Columns.Count; i++)
                    {
                        var collection = boundTable.AsEnumerable().Where(p => p.Field<string>("Column_ID") == dtMaster.Columns[i].ColumnName);
                        string Name = collection.Select(p => p.Field<string>("Column_Name")).FirstOrDefault();
                        string column = collection.Select(p => p.Field<string>("Excel_column")).FirstOrDefault();
                        string[] arr1 = dtMaster.AsEnumerable().Select(r => r.Field<string>(dtMaster.Columns[i].ColumnName)).ToArray();
                        
                        string[,] arr2 = new string[arr1.Length, 1];
                        for (int j=0;j<arr1.Length;j++)
                        {
                            arr2[j, 0] = arr1[j];
                        }
                        int number = JudgeNumber(column);
                        wsht.Cells[1, number] = Name;
                        wsht.get_Range(column + "2", column + arr1.Length + 1).Value2 = arr2;
                        wsht.get_Range(column + "2", column + arr1.Length + 1).EntireColumn.AutoFit();               
                    }
                }
                xlApp.Visible = false;
                xlApp.UserControl = false;
                XLWB.SaveAs("your file path");
                XLWB.Close();
                xlApp.Quit();
            }
            static int JudgeNumber(string str)
            {
                switch (str)
                {
                    case "A":
                        return 1;
                    case "B":
                        return 2;
                    case "C":
                        return 3;
                    case "D":
                        return 4;
                    case "E":
                        return 5;
                    case "F":
                        return 6;
                    case "G":
                        return 7;
                    case "H":
                        return 8;
                    case "I":
                        return 9;
                    case "J":
                        return 10;
                    //...
                    default:
                        MessageBox.Show("Default case");
                        return 0;
                }
            }

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 28, 2020 2:24 AM
    Moderator
  • Yes. you really rocking...Thanks a lot.

    I have two doubts here.

    1. I have more than 100 columns to use. for that, shall i use switch case like A=1,B=2,C=3,...AA=27,AB=28,AC=29,etc..

      Is there anyother shortcut method to use instead above

    2. After printing the values, some of the rows are filled as "#N/A". How it will come..This should not come...?

    below is the reference from the output..

    Not Required to print #NA

    Tuesday, January 28, 2020 7:50 AM
  • Hi Gani tpt,

    >>I have more than 100 columns to use. for that, shall i use switch case like A=1,B=2,C=3,...AA=27,AB=28,AC=29,etc..

    You need to use following method other than 'JudgeNumber' method:

            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }

    >>After printing the values, some of the rows are filled as "#N/A"

    Apologize for my careless, when the range is bigger than the array, the #N/A error will show up.

    You need to change the code in 'wsht.get_Range(column + "2", column + arr1.Length + 1).Value2 = arr2'.

                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).Value2 = arr2;    
                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).EntireColumn.AutoFit(); 

    Here's the correct code:

            public void SaveToExcel(DataTable dtMaster, DataTable boundTable)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Microsoft.Office.Interop.Excel._Workbook XLWB;
                Microsoft.Office.Interop.Excel._Worksheet wsht;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = true;
    
                XLWB = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
                wsht = (Microsoft.Office.Interop.Excel._Worksheet)XLWB.ActiveSheet;
                wsht.Name = "S2";
                int rowcount = dtMaster.Rows.Count;
                
                if (dtMaster.Rows.Count > 0)
                {
                    for (int i = 0; i < dtMaster.Columns.Count; i++)
                    {
                        var collection = boundTable.AsEnumerable().Where(p => p.Field<string>("Column_ID") == dtMaster.Columns[i].ColumnName);
                        string Name = collection.Select(p => p.Field<string>("Column_Name")).FirstOrDefault();
                        string column = collection.Select(p => p.Field<string>("Excel_column")).FirstOrDefault();
                        string[] arr1 = dtMaster.AsEnumerable().Select(r => r.Field<string>(dtMaster.Columns[i].ColumnName)).ToArray();
                        
                        string[,] arr2 = new string[arr1.Length, 1];
                        for (int j=0;j<arr1.Length;j++)
                        {
                            arr2[j, 0] = arr1[j];
                        }
                        int number = ExcelColumnNameToNumber(column);
                        wsht.Cells[1, number].Value = Name;
                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).Value2 = arr2;   
                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).EntireColumn.AutoFit();               
                    }
                }
                xlApp.Visible = false;
                xlApp.UserControl = false;
                XLWB.SaveAs("your path");
                XLWB.Close();
                xlApp.Quit();
            }
            public static int ExcelColumnNameToNumber(string columnName)
            {
                if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
    
                columnName = columnName.ToUpperInvariant();
    
                int sum = 0;
    
                for (int i = 0; i < columnName.Length; i++)
                {
                    sum *= 26;
                    sum += (columnName[i] - 'A' + 1);
                }
    
                return sum;
            }

    Result of my test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, January 28, 2020 10:11 AM
    Moderator
  • Really you done a very good job.

     wsht.Cells[1, number].Value = Name;
                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).Value2 = arr2;   
                        wsht.get_Range(column + "2", column + (arr1.Length + 1)).EntireColumn.AutoFit();         

    i am trying to change the above code as mentioned below.

    i'm trying to print Column name in the row of 10

    and data values to print in the row of 11.

    wsht.Cells[10, number].Value = Name;
                        wsht.get_Range(column + "11, column + (arr1.Length + 1)).Value2 = arr2;   
                        wsht.get_Range(column + "11", column + (arr1.Length + 1)).EntireColumn.AutoFit();         

    i am getting final output below.

    when i change the print row number, column will not be displaying and #N/A also displaying after data values..


    • Edited by Gani tpt Tuesday, January 28, 2020 12:01 PM
    Tuesday, January 28, 2020 11:59 AM
  • Hi Gani tpt,

    I change the 'SaveToExcel' method based on your description.

    you can refer to the following code:

            public void SaveToExcel(DataTable dtMaster, DataTable boundTable, int columnNumber)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Microsoft.Office.Interop.Excel._Workbook XLWB;
                Microsoft.Office.Interop.Excel._Worksheet wsht;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = true;
    
                XLWB = (Microsoft.Office.Interop.Excel._Workbook)(xlApp.Workbooks.Add(""));
                wsht = (Microsoft.Office.Interop.Excel._Worksheet)XLWB.ActiveSheet;
                wsht.Name = "S2";
                int rowcount = dtMaster.Rows.Count;
                
                if (dtMaster.Rows.Count > 0)
                {
                    for (int i = 0; i < dtMaster.Columns.Count; i++)
                    {
                        var collection = boundTable.AsEnumerable().Where(p => p.Field<string>("Column_ID") == dtMaster.Columns[i].ColumnName);
                        string Name = collection.Select(p => p.Field<string>("Column_Name")).FirstOrDefault();
                        string column = collection.Select(p => p.Field<string>("Excel_column")).FirstOrDefault();
                        string[] arr1 = dtMaster.AsEnumerable().Select(r => r.Field<string>(dtMaster.Columns[i].ColumnName)).ToArray();
                        
                        string[,] arr2 = new string[arr1.Length, 1];
                        for (int j=0;j<arr1.Length;j++)
                        {
                            arr2[j, 0] = arr1[j];
                        }
                        int number = ExcelColumnNameToNumber(column);
                        wsht.Cells[columnNumber, number].Value = Name;
                        string rangeTop = column + (columnNumber + 1);
                      string rangeBottom = column + (columnNumber + arr1.Length);
                      wsht.get_Range(rangeTop, rangeBottom).Value2 = arr2;    
                      wsht.get_Range(rangeTop, rangeBottom).EntireColumn.AutoFit();               
                    }
                }
                xlApp.Visible = false;
                xlApp.UserControl = false;
                XLWB.SaveAs("your file path");
                XLWB.Close();
                xlApp.Quit();
            }

    Result of my test.

    SaveToExcel(dtMaster, boundTable,10);

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.





    Wednesday, January 29, 2020 1:48 AM
    Moderator
  • Yes. It's excellent and awesome. I checked and it's perfect.

    can you help to suggest below post if you have no problem.

    PS : How to convert Excel to data table in simple way

    Link : https://social.msdn.microsoft.com/Forums/en-US/0b05aa8a-5816-4e5f-814a-72205faa63f7/how-to-convert-excel-to-data-table-in-simple-way?forum=csharpgeneral


    • Edited by Gani tpt Wednesday, January 29, 2020 4:56 AM another suggestion
    Wednesday, January 29, 2020 4:41 AM