How to read columns one by one in .xlsx file through C#

Question

0.00/5 (No votes)

See more:

I have two columns in .xlsx file having different set of IP's, i want to ping those ip with c# with all the IP's available in column A one by one and then repeat the same with column B.

I am done with ping code, but stuck with reading values from .xslx file with C#

Below is the reference code i got on net.

Please suggest how can i read column values one by one and execute my ping task!!!

What I have tried:

Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Test.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    string data = xlRange.Cells[i, j].Value2.ToString();
                    MessageBox.Show(data);
                }
            }

Posted 16-Feb-17 0:39am

iHimanshugehlot

Updated 16-Feb-17 0:46am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-02-16T00:46:00

Solution 1

Quote:
Below is the reference code i got on net.

And what does it do?
Simple: it reads each row, and inside each row, it reads each column.
So all you have to do is remove the inner loop and replace it with a line of code that accesses the one column you are interested in, convert it appropriately to match your ping code, and pass it to your existing ping code for processing.

To be honest, if you can't work that out for yourself with a simple piece of code like you show, then you need to go right back to first principles in your C# course and learn them properly. The idea of "development" is as the word suggests: "The systematic use of scientific and technical knowledge to meet specific objectives or requirements." BusinessDictionary.com[^]
That's not the same thing as "have a quick google and give up if I can't find exactly the right code".
You will never find exactly the right code to fit your needs - you are expected to be able to either write it yourself, or understand it well enough to modify existing code so it fits your needs.

Posted 16-Feb-17 0:46am

OriginalGriff

Comments

iHimanshugehlot 16-Feb-17 6:52am

Sir, i executed above code but it's returning one value from column A then one value from column B.

As suggested i looped same column but it's giving exception.

I am stuck with this line :

string data = xlRange.Cells[i, j].Value2.ToString();

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException was unhandled
HResult=-2146233088
Message=Cannot perform runtime binding on a null reference
Source=Anonymously Hosted DynamicMethods Assembly
StackTrace:
at CallSite.Target(Closure , CallSite , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
at CallSite.Target(Closure , CallSite , Object )
at ConceptCode.frmReadXlms.button1_Click(Object sender, EventArgs e) in e:\1_Working\0\1_All\ConceptCode\ConceptCode\frmReadXlms.cs:line 38
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ConceptCode.Program.Main() in e:\1_Working\0\1_All\ConceptCode\ConceptCode\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

OriginalGriff 16-Feb-17 7:17am

What part of that line do you not understand?
Explain to me what it is doing, and you may see where you are going wrong!

How to read columns one by one in .xlsx file through C#

1 solution

Solution 1

Add your solution here

Preview 0

How to read columns one by one in .xlsx file through C#

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us