locked
How to Read a Specific Column of Excel and Update Related Values RRS feed

  • Question

  • Can you please let me know how I can use C# to read  a specific column values base on the column header?

    For example, let say I have a column  with header "pAge" how I can loop into the column and update values for the specific rows related to the garbed values. What I want to do is looping in  "pAge" column and find all ages over 50 then change the value of those rows in other column "Eligible" to "yes"

    The reason that i would like to use the column header instead of column range is because I am getting some data from our clients which are not necessarily in the same order but they have always same name.

    Thanks for your time

    • Moved by Alexander Sun Thursday, May 31, 2012 10:02 AM Move to more appropriate forum (From:Visual C# Language)
    Wednesday, May 30, 2012 6:01 PM

Answers

  • Hi Behseini,

    Welcome to the MSDN forum!

    Please refer to the following code:

                           
    // using Excel = Microsoft.Office.Interop.Excel;

    Excel._Application oApp = new Excel.Application(); oApp.Visible = true; Excel.Workbook oWorkbook = oApp.Workbooks.Open("E:\\Test\\Test.xlsx"); Excel.Worksheet oWorksheet = oWorkbook.Worksheets["Sheet2"]; ChangeValue(oWorksheet); oWorkbook.Save(); oWorkbook.Close(); oApp.Quit(); oWorksheet = null; oWorkbook = null; oApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); private static void ChangeValue(Excel.Worksheet oWorksheet) { int colNo = oWorksheet.UsedRange.Columns.Count; int rowNo = oWorksheet.UsedRange.Rows.Count; // read the value into an array. object[,] array = oWorksheet.UsedRange.Value; for (int j = 1; j <= colNo; j++) { for (int i = 1; i <= rowNo; i++) { if (array[i, j] != null) if (array[i, j].ToString() == "pAge") { for (int m = i+1; m < rowNo; m++) { if (Convert.ToInt32(array[m, j].ToString()) > 50) { array[m, j+1] = "Yes"; } } // set the value back into the range. oWorksheet.UsedRange.Value = array; return; } } } }

    Please add this Excel object library first: "Microsoft Excel 14.0 Object Libray".

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Proposed as answer by pradeep1210 Saturday, June 2, 2012 8:40 PM
    • Marked as answer by Dummy yoyo Tuesday, June 5, 2012 3:34 AM
    Friday, June 1, 2012 5:49 AM
  • + 1 YoYo :) Nice use of Arrays.

    @Behseini: Here is another way which doesn't use array but directly inserts the formula in Col E

    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;
     
    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                Microsoft.Office.Interop.Excel.Range xlRange;
    
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Excel.Application();
    
                xlexcel.Visible = true;
    
                //~~> Open a File
                xlWorkBook = xlexcel.Workbooks.Open("C:\\Users\\Siddharth Rout\\Desktop\\Book1.xlsx", 0, false, 5, "", "", true,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
                //~~> Set Sheet 1 as the sheet you want to work with
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                //~~> Get the last Row of Col D
                long lRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 4].End(Excel.XlDirection.xlUp).Row;
    
                //~~> Set your relevant range in E
                xlRange = xlWorkSheet.get_Range("E5:E" + lRow, misValue);
    
                //~~> Insert the Excel Formula
                xlRange.Formula = "=IF(D5>50,\"Yes\",\"No\")";
    
                //~~> Once done close, Save and quit Excel
                xlWorkBook.Close(true, misValue, misValue);
                xlexcel.Quit();
    
                //~~> Clean UP
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlexcel);
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            } 
        }
    }

    SNAPSHOT


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    • Proposed as answer by pradeep1210 Saturday, June 2, 2012 8:40 PM
    • Marked as answer by Dummy yoyo Tuesday, June 5, 2012 3:34 AM
    Saturday, June 2, 2012 4:07 AM

All replies

  • Hi Behseini,

    Welcome to the MSDN forum.

    According to your description, your problem is related to office development, so I will move your thread to corresponding forum for better support.

    Sorry for any inconvenience.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 31, 2012 10:01 AM
  • Hi, I will share two links down, you can have a try.

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.columnname.aspx

    The next is a similar discussion quite related.

    http://stackoverflow.com/questions/7164508/how-to-read-data-column-headers-and-data-of-each-cell-in-excel-using-c-sharp.

    That what I refered to when I meet the similar question. 

    Best Regards

    Friday, June 1, 2012 2:24 AM
  • Hi Behseini,

    Welcome to the MSDN forum!

    Please refer to the following code:

                           
    // using Excel = Microsoft.Office.Interop.Excel;

    Excel._Application oApp = new Excel.Application(); oApp.Visible = true; Excel.Workbook oWorkbook = oApp.Workbooks.Open("E:\\Test\\Test.xlsx"); Excel.Worksheet oWorksheet = oWorkbook.Worksheets["Sheet2"]; ChangeValue(oWorksheet); oWorkbook.Save(); oWorkbook.Close(); oApp.Quit(); oWorksheet = null; oWorkbook = null; oApp = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); private static void ChangeValue(Excel.Worksheet oWorksheet) { int colNo = oWorksheet.UsedRange.Columns.Count; int rowNo = oWorksheet.UsedRange.Rows.Count; // read the value into an array. object[,] array = oWorksheet.UsedRange.Value; for (int j = 1; j <= colNo; j++) { for (int i = 1; i <= rowNo; i++) { if (array[i, j] != null) if (array[i, j].ToString() == "pAge") { for (int m = i+1; m < rowNo; m++) { if (Convert.ToInt32(array[m, j].ToString()) > 50) { array[m, j+1] = "Yes"; } } // set the value back into the range. oWorksheet.UsedRange.Value = array; return; } } } }

    Please add this Excel object library first: "Microsoft Excel 14.0 Object Libray".

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    • Proposed as answer by pradeep1210 Saturday, June 2, 2012 8:40 PM
    • Marked as answer by Dummy yoyo Tuesday, June 5, 2012 3:34 AM
    Friday, June 1, 2012 5:49 AM
  • + 1 YoYo :) Nice use of Arrays.

    @Behseini: Here is another way which doesn't use array but directly inserts the formula in Col E

    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;
     
    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                Microsoft.Office.Interop.Excel.Range xlRange;
    
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Excel.Application();
    
                xlexcel.Visible = true;
    
                //~~> Open a File
                xlWorkBook = xlexcel.Workbooks.Open("C:\\Users\\Siddharth Rout\\Desktop\\Book1.xlsx", 0, false, 5, "", "", true,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
                //~~> Set Sheet 1 as the sheet you want to work with
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                //~~> Get the last Row of Col D
                long lRow = xlWorkSheet.Cells[xlWorkSheet.Rows.Count, 4].End(Excel.XlDirection.xlUp).Row;
    
                //~~> Set your relevant range in E
                xlRange = xlWorkSheet.get_Range("E5:E" + lRow, misValue);
    
                //~~> Insert the Excel Formula
                xlRange.Formula = "=IF(D5>50,\"Yes\",\"No\")";
    
                //~~> Once done close, Save and quit Excel
                xlWorkBook.Close(true, misValue, misValue);
                xlexcel.Quit();
    
                //~~> Clean UP
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlexcel);
            }
    
            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            } 
        }
    }

    SNAPSHOT


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    • Proposed as answer by pradeep1210 Saturday, June 2, 2012 8:40 PM
    • Marked as answer by Dummy yoyo Tuesday, June 5, 2012 3:34 AM
    Saturday, June 2, 2012 4:07 AM