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

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

+ 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.