Click here to Skip to main content
15,030,954 members
Please Sign up or sign in to vote.
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
Updated 20-Jun-17 0:03am
v4

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{ }
   
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
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.
   
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 .

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900