Click here to Skip to main content
15,905,427 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 can be done.
My exported data will then be pasted beginning in cell "A1" of the Excel sheet.

Is there a possibility to put it into a specific cell? I need to export the data frame to a range beginning at cell A5. Is this possible?

What I have tried:

here is my exporting code it works perfectly :

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();
                    
                    xlWorkSheet.Cells[ i+1, j+1] = data;


                }

            }
           }

           }
Posted
Updated 16-Jun-17 3:38am
v2

1 solution

Usually, you don't use this code,
C#
xlWorkSheet.Cells[ i+1, j+1] = data;

and it is replaced by something like
C#
xlWorkSheet.Cells[ i+1, j+1].Value = data;

or
C#
xlWorkSheet.Cells[ i+1, j+1].Formula = data;

depending in what is the data.
Quote:
Is there a possibility to put it into a specific cell? I need to export the data frame to a range beginning at cell A5. Is this possible?

Your code begin to paste in A1, just add 4 and you are done.
C#
xlWorkSheet.Cells[ i+5, j+1] = data;
 
Share this answer
 
Comments
EM_Y 19-Jun-17 3:29am    
It works ,Thank you for your help !
EM_Y 19-Jun-17 5:10am    
I want to compare 2 cells from the same column when exporting data to excel
I added this code in my loop but it doesn't work for me :
Thank you for you help :)

if (xlWorkSheetCarteProd.Cells[i + 5, j].Value  ==  xlWorkSheetCarteProd.Cells[i + 6, j].Value)                    {          xlWorkSheetCarteProd.Cells[i + 6, j].Value = "";    }


@ppolymorphe
Patrice T 19-Jun-17 5:24am    
You can open a new question for this.
"I added this code in my loop but it doesn't work for me"
You forgot to tell what you want to do with this code.
Define "doesn't work for me"
EM_Y 19-Jun-17 5:30am    
I'll open a new question .

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