Comparing 2 cells from same column in specific column in excel exported sheet using WPF

Question

0.00/5 (No votes)

See more:

I need to export MySQL dataTable using wpf to an Excel sheet, which is done .

I want to compare cell 1 "A1" with cell 2 "A2" in the specific column A and delete the second if it has the same Value Content as the first one .

My problem is that the code compare every column cells A,B,C,D while I want to compare only in 1 specific column A !

Thank you for your help .

What I have tried:

this is only the exporting code :

private void export_Click(object sender, RoutedEventArgs e)
        {
            using (var conx = new MySqlConnection(constring))
            {
                conn.Open();
 
            //// extraction excel 
 
            string Mysql = null;
            string data = null;
            int i = 0;
            int j = 0;
 
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
 
            object misValue = System.Reflection.Missing.Value;
 
            xlApp = new Excel.Application();
            xlApp.Visible = true;
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 

            Mysql = "SELECT * from database.Table";
 
            MySqlDataAdapter cmd = new MySqlDataAdapter(Mysql,conn);
            DataSet ds = new DataSet();
            cmd.Fill(ds);
 
            for (i = 0; i <= ds.Tables[0].Rows.Count-1 ; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                 
                      /// excel sheet start from A5
                    xlWorkSheet.Cells[ i+5, j+1] = data;
 

                }
 
            }
           }
 
           }

and I added this comparing code in my exporting loop :

for (i = 0; i <= ds.Tables[0].Rows.Count-1 ; i++)
        {
            for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
            {
                data = ds.Tables[0].Rows[i].ItemArray[j].ToString();

                xlWorkSheet.Cells[ i+1, j+1] = data;

           //// My added comparing code
            try
                {

           if (xlWorkSheetCarteProd.Cells[i, j].Value ==

              xlWorkSheetCarteProd.Cells[i + 1, j].Value)
                    {
                        xlWorkSheetCarteProd.Cells[i +1, j].Value = "";

                    }
                }
                catch{ }
             ////////////////
            }

        }

Posted 18-Jun-17 23:57pm

EM_Y

Updated 20-Jun-17 0:03am

v4

Add a Solution

2 solutions

Solution 1

Your compare code is inside the "per column" loop so is being run on every column. If you're only interested in the first column then move it out of that loop

for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
    data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                    
    xlWorkSheet.Cells[ i+1, j+1] = data;
}
 
// using a hard-coded value of 0 for the first column
try   
{
    if (xlWorkSheetCarteProd.Cells[i, 0].Value == 
         xlWorkSheetCarteProd.Cells[i + 1, 0].Value)
    {
        xlWorkSheetCarteProd.Cells[i +1, 0].Value = "";
     }
}
catch{ }

Posted 19-Jun-17 0:05am

F-ES Sitecore

Comments

EM_Y 19-Jun-17 7:41am

@F-ES
Thank you ,but It doesn't work for me I tried to play code refer to your suggestion but It doesn't work either ,
and error shows : Exception from HRESULT: 0x800A03EC
It means that excel didn't find any result !

EM_Y 20-Jun-17 4:39am

It works when and inside loop j=1 ,
it works but it compare only the 2 first rows .
My objective is to delete the repeated Texts cell in column A and let the first copy ,While my excel data sheet start from A5 .
I wish your help ! Thank you

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)

Patrice T · Accepted Answer · 2017-06-19T08:35:00

Solution 2

I think the problem is that Excel cells rows and columns are starting at 1, not zero.
Add 1 to every row and column in your comparison and it should do the trick.

Posted 19-Jun-17 8:35am

Patrice T

Comments

EM_Y 20-Jun-17 4:25am

You are right about j=1 ,
it works but it compare only the 2 first rows .
My objective is to delete the repeated Texts cell in column A and let the first copy ,While my excel data sheet start from A5 .
I wish your help ! Thank you

Patrice T 20-Jun-17 4:38am

You should add an example input and result you want

EM_Y 20-Jun-17 8:02am

@ppolymorphe

I exported MySQL table to excel using wpf ,
my excel sheet start from cell position A5,
Rows of first column can had same value .
I want to let the first value and delete the rest cell content that has the same value .

the code that I've tried ,compare row1col1 and row2Col1 and if it had the same value row2col2 will be deleted , and pass to row3col1 and compare it with row4 and etc...
Hide Copy Code

try { if (xlWorkSheetCarteProd.Cells[i,1].Value==xlWorkSheetCarteProd.Cells[i + 1, 1].Value) {         xlWorkSheetCarteProd.Cells[i +1, 1].Value = "";        }} catch{ }

I wish I explained it well ?

Thank you for your attention .