how to chek all data of excel sheet using c#

Question

1.12/5 (3 votes)

See more:

Hi friends,
I want check all data present in excel sheet ,if any of cell contain any data (except 0 amount) I want display message "this cell contain except 0 amount please verify" like that please help me on that

Posted 9-Mar-15 21:53pm

Member 10562086

Add a Solution

Comments

Richard MacCutchan 10-Mar-15 4:32am

Then you need to read the worksheet and iterate through the cells to check them. Where are you stuck?

Member 10562086 10-Mar-15 4:47am

I am going to write code but I don't know how to wright code for this condition ..which logic and how to implement ??

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Mar-15 5:57am

If you get the cells, then get the value and compare that with 0. That is the logic.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Subramanyam Shankar · Accepted Answer · 2015-03-09T23:03:00

I have written a sample example. Hope this helps. In this example it is checking for data only in column B. You can do the same for whatever columns you require.

C#

void CheckExcel() 
        {

            string filePath = @"F:\Book2.xlsx";

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

            try
            {
                xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 0);

                Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets["Sheet1"];

                int sheet1LastRowCount = worksheet1.UsedRange.Rows.Count;

                for (int i = 2; i <=sheet1LastRowCount; i++)
                {
                        if (Convert.ToInt32(worksheet1.Range["B" + i, "B" + i].Value2) == 0)
                        {
                            Console.WriteLine("Value is equal to 0 in row "+i);
                        }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                xlApp.Quit();
            }


        }

Subramanyam Shankar · Accepted Answer · 2015-03-09T23:37:00

Solution 2

For all columns and all rows. Please accept the answer if it's correct

C#

try
           {
               xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 0);

               Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets["Sheet1"];

               int sheet1LastRowCount = worksheet1.UsedRange.Rows.Count;
               int _val=0;

               for (int i = 2; i <=sheet1LastRowCount; i++)
               {
                   for(int j=1;j<=worksheet1.UsedRange.Columns.Count;j++)
                   {
                       if( Int32.TryParse(Convert.ToString(worksheet1.Cells[i,j].Value2),out _val))
                       {
                           if (Convert.ToInt32(_val) == 0)
                           {
                               Console.WriteLine("Value is equal to 0 in row " + i + " and column " + j);
                           }
                       }
                   }
               }
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }
           finally
           {
               xlApp.Quit();
           }

Posted 9-Mar-15 23:37pm

Subramanyam Shankar

Updated 9-Mar-15 23:38pm

v2

Comments

Member 10562086 10-Mar-15 9:46am

can I use same code for Coded UI?? Is it work? I have to implement by using coded ui test case .

Subramanyam Shankar 10-Mar-15 11:09am

The logic will work the same. it will work for UI also. If you face some issues,We are here to help you.

Member 10562086 11-Mar-15 6:09am

thank you Shankar.i have one question ,why you initialize i=2 and j=1 ,which is row and which is column .suppose I want check data from particular row range(10th row to 25 th row )and particular column range (column "E" to "G" column )on this condition how to check

Subramanyam Shankar 11-Mar-15 7:05am

i represents row and j represents the columns in your excel.i=1 because your row will start from 2 as the first row will have header information. j=1 because you need to start with first column "A". if you want to check data from 10th to 25th row then you need to replace the condition with for(i=10;i<=25;i++) and for(j=5;j<=8;j++).

Member 10562086 12-Mar-15 10:30am

hello sir I need one help. I want display meaasge inside message box ,message is like "this sheet have non zero amount in the pariculor(row no) and and pariculor ( column no) please check it"
I am comparing pariculor cell value with "0" by the method Assert.AreEqual("0", values); and once it catch it will go to catch and display message like what I mention in above and my test case should be fail .
but what ever I declare row and column variable ,how to use inside the catch and how to display that row and column in the meaasge box .bellow code is there

try
{
int row;
int column;
for (Row = 22; Row <=23; Row++)
{
for (Colm = 5; Colm <= 18; Colm++)
{
excelProperties.excelCell.Set_CellIndices(Row, Colm);
string values = excelProperties.excelCell.Get_CellValue();

Assert.AreEqual("0", values);
Playback.Wait(500);
}

}

MessageBox.Show("There is no zero data in revenue input sheet");

}
catch (Exception ex)
{

MessageBox.Show("This sheet contains non zero amount, please check it in row " + Row + " and column " + Colm);
Assert.IsNull(ex, ex.Message);
}
}

Subramanyam Shankar 16-Mar-15 8:39am

int row;
int column;

try
{

for (Row = 22; Row <=23; Row++)
{
for (Colm = 5; Colm <= 18; Colm++)
{
excelProperties.excelCell.Set_CellIndices(Row, Colm);
string values = excelProperties.excelCell.Get_CellValue();

Assert.AreEqual("0", values);
Playback.Wait(500);
}

}

MessageBox.Show("There is no zero data in revenue input sheet");

}
catch (Exception ex)
{

MessageBox.Show("This sheet contains non zero amount, please check it in row " + Row + " and column " + Colm);
Assert.IsNull(ex, ex.Message);
}
}