How to validate the excel sheet using ASP.NET C#?

Question

1.00/5 (2 votes)

See more:

i upload one excel document with more than one sheet and stored in dataset as data table.now i want to validate the dataset.
if 'item no' is mandatory.the item number must be mandatory all sheets in excel sheet.
if item number in 7 th row in all sheets but column value is different. how to validate,
*All mandatory fields rows are same in all sheets but column is difference how to validate it.

What I have tried:

C#

public Boolean validate(DataSet ds)
    {
        int mn = 0;
        int count = 0;
        DataTable dt = new DataTable();
        dt.Columns.Add("Error Log");
        string msg = "Please Check Uploaded Excel File";

        int tagcountvalue = 0;
        int Columncountvalue = 0;
 
for (int s = 0; s < ds.Tables.Count; s++)
        {

            for (int z = 0; z < ds.Tables[s].Columns.Count; z++)
            {
                if (ds.Tables[s].Rows[7][z].ToString().ToUpper().Trim() == "item no")//Item number based count taken
                {
                    tagcountvalue = z - 1;
                    Columncountvalue = z;
                    z = ds.Tables[0].Columns.Count;
                }
            }
for (int z = 0; z < ds.Tables[s].Columns.Count; z++)
            {
                if (ds.Tables[s].Rows[10][z].ToString().ToUpper().Trim() == "ITEM NUMBER" || count != 0)//Item number based count taken
                {
                    count = count + 1;
                }
            }
 for (int i = 2; i < tagcountvalue; i++)
            {
                if (ds.Tables[0].Rows[2][i].ToString() != "" && ds.Tables[0].Rows[10][i].ToString() == "")
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Tag Number Should be mandatory";
                    mn = mn + 1;
                }
                if (ds.Tables[0].Rows[1][i].ToString() == "" && ds.Tables[0].Rows[7][i].ToString() != "")
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Tag Description Should be mandatory against Tag Number";
                    mn = mn + 1;
                }
                int tagpart = 0;
                for (int j = 21; j < ds.Tables[0].Rows.Count; j++)
                {
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[7][i].ToString() != "")
                    {
                        tagpart = tagpart + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() == "" && ds.Tables[0].Rows[7][i].ToString() == "")
                    {
                        tagpart = tagpart + 1;
                    }
                }
                if (tagpart == 0)
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Parts per unit Should be mandatory against Tag Number";
                    mn = mn + 1;
                }
 for (int j = 12; j < ds.Tables[0].Rows.Count; j++)
                {

                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 2].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Installed Qty Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && (ds.Tables[0].Rows[j][Columncountvalue + 9].ToString() == "" && ds.Tables[0].Rows[j][Columncountvalue + 10].ToString() == ""))
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Supplier/Manufacturer PartNumber Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 23].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Lead time Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 22].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Unit Price Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 20].ToString() != "")
                    {
                        string units = ds.Tables[0].Rows[j][Columncountvalue + 20].ToString();
                        string[] arrayString = units.Split(' ');
                        int counts = arrayString.Count();
                        if (counts == 1)
                        {
                            dt.Rows.Add();
                            dt.Rows[mn][0] = "Currency Should be provided with Unit Price";
                            mn = mn + 1;
                        }
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && (ds.Tables[0].Rows[j][Columncountvalue + 13].ToString() == "" && ds.Tables[0].Rows[j][Columncountvalue + 14].ToString() == "" && ds.Tables[0].Rows[j][Columncountvalue + 16].ToString() == "" && ds.Tables[0].Rows[j][Columncountvalue + 17].ToString() == ""))
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Supplier/Manufacturer Recommended Qty Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Item Number Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 4].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Item Description Should be mandatory";
                        mn = mn + 1;
                    }
                    if (ds.Tables[0].Rows[j][i].ToString() != "" && ds.Tables[0].Rows[j][Columncountvalue + 12].ToString() == "")
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Manufacturer Name Should be mandatory";
                        mn = mn + 1;
                    }
                    int tagpart1 = 0;
                    for (int k = 21; k < ds.Tables[0].Rows.Count; k++)
                    {
                        if ((ds.Tables[0].Rows[j][Columncountvalue].ToString() == ds.Tables[0].Rows[k][Columncountvalue].ToString()) && ds.Tables[0].Rows[k][Columncountvalue].ToString() != "" && ds.Tables[0].Rows[k][Columncountvalue].ToString() != "0")
                        {
                            tagpart1 = tagpart1 + 1;
                        }
                    }
                    if (tagpart1 > 1)
                    {
                        dt.Rows.Add();
                        dt.Rows[mn][0] = "Item Number Should be Unique";
                        mn = mn + 1;
                    }
                }
                if (ds.Tables[0].Rows[7][Columncountvalue + 12].ToString() == "")
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Equipment Description Should be mandatory";
                    mn = mn + 1;
                }
                if (ds.Tables[0].Rows[1][Columncountvalue + 14].ToString() == "")
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Revision No Should be mandatory";
                    mn = mn + 1;
                }
                if (ds.Tables[0].Rows[10][Columncountvalue + 16].ToString() == "")
                {
                    dt.Rows.Add();
                    dt.Rows[mn][0] = "Supplier Name Should be mandatory";
                    mn = mn + 1;
                }
            }

        }
        if (dt.Rows.Count > 0)
        {
            dt = dt.DefaultView.ToTable(true);//Table value unique purpose provided
            grview.Visible = true;
            grerror.DataSource = dt;
            grerror.DataBind();
            lnkDownload.Visible = true;
            ExportDataTabletoFile(dt, " ", true);
            return false;
        }
        else
        {
            grview.Visible = false;
            lnkDownload.Visible = false;
            return true;
        }


    }

I have used this code but not working properly.

Posted 25-Aug-16 1:42am

Raja Ganapathy

Updated 25-Aug-16 3:56am

Patrice T

v3

Add a Solution

Comments

Patrice T 25-Aug-16 9:55am

Define "not working properly"

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)

The Praveen Singh · Answer 1 · 2016-08-25T02:42:00

Solution 1

Go through following link:-
c# - Importing an Excel Sheet and Validate the Imported Data with Loosely Coupled - Stack Overflow[^]

Posted 25-Aug-16 2:42am

The Praveen Singh

Patrice T · Answer 2 · 2016-08-25T03:56:00

Use the debugger to see what your code is doing and what it is "not working properly".

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
When the code don't do what is expected, you are close to a bug.