Visual studio excel pivottable not showing data

Question

0.00/5 (No votes)

See more:

Hello, I tried to make an excel PivotTable with C# windowsForm.
I need to get data from SqlServer but nothing is written on my Excel file.

What I have tried:

private void button2_Click(object sender, EventArgs e)
       {
           string fileTest = @"C\Test\Pivot.xlsx";
           if (File.Exists(fileTest))
           {
               File.Delete(fileTest);
           }

           Excel.Application oApp;
           Excel.Worksheet oSheet;
           Excel.Workbook oBook;

           oApp = new Excel.Application();
           oBook = oApp.Workbooks.Add();
           oSheet = (Excel.Worksheet)oBook.Worksheets.get_Item(1);

           string constr = @"Data Source=XXX;Initial Catalog=XXX;User ID=XXXX;Password=XXXXX";
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlDataAdapter sda = new SqlDataAdapter("SELECT [C9],[OID],[USERMODIF] FROM [AffecAnalytique].[dbo].[C9_V] where[OID] != '" + textBox1.Text + "' order by [DATEMODIF] DESC", con))
               {
                   //Fill the DataTable with records from Table.
                   DataTable dt = new DataTable();
                   sda.Fill(dt);

                   //Insert the Default Item to DataTable.
                   DataRow row = dt.NewRow();
                   dt.Rows.InsertAt(row, 0);
                   row[0] = "C9";
                   row[1] = "OID";
                   row[2] = "USERMODIF";
                   //string x = row[0].ToString();
                   oSheet.Cells[1, 1] = row[0].ToString();
                   oSheet.Cells[1, 2] = row[1].ToString();
                   oSheet.Cells[1, 3] = row[2].ToString();
                   //MessageBox.Show(x);
               }
           }
           // now capture range of the first sheet = I will need this to create pivot table
           Excel.Range oRange = oSheet.Range["A1", "C1000"];

           // create second sheet
           if (oApp.Application.Sheets.Count < 2)
           {
               oSheet = (Excel.Worksheet)oBook.Worksheets.Add();
           }
           else
           {
               oSheet = oApp.Worksheets[2];
           }
           oSheet.Name = "Pivot Table";

           // specify first cell for pivot table
           Excel.Range oRange2 = oSheet.Cells[1, 1];

           // create Pivot Cache and Pivot Table

           Excel.PivotCache oPivotCache = oBook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange, Excel.XlPivotTableVersionList.xlPivotTableVersion14);
           Excel.PivotTable oPivotTable = oPivotCache.CreatePivotTable(TableDestination: oRange2, TableName: "Summary");

           // create Pivot Field, note that name will be the same as column name on sheet one
           Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("C9");
           oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
           oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
           oPivotField.Name = " C9";
           // by default name will be something like sumOfSalary, to change it, assign new name to it
           // name cannot be the same as Pivot Fields, therefore I added empty space in front of it.
           // name cannot be empty either

           // save
           oBook.SaveAs(fileTest);
           oBook.Close();
           oApp.Quit();

       }

Posted 9-Oct-20 4:00am

houssem eddine ayari

Updated 12-Oct-20 23:16pm

Add a Solution

Comments

[no name] 9-Oct-20 10:28am

Write (test) first, then pivot. At this point, you don't know what works and what doesn't.

1 solution

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)

houssem eddine ayari · Accepted Answer · 2020-10-12T23:16:00

C#

<pre>private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            for (int x = 1; x < dataGridView3.Columns.Count + 1; x++)
            {
                xlWorkSheet.Cells[1, x] = dataGridView3.Columns[x - 1].HeaderText;
            }
            int i = 0;
            int j = 0;

            for (i = 0; i <= dataGridView3.RowCount - 1; i++)
            {
                for (j = 0; j <= dataGridView3.ColumnCount - 1; j++)
                {
                    try
                    {
                        DataGridViewCell cell = dataGridView3[j, i];
                        xlWorkSheet.Cells[i + 2, j + 1] = dataGridView3[j, i].Value;
                    }

                    catch (Exception ex)
                    {

                        MessageBox.Show("Exception Occured while exporting data " + ex.ToString());
                    }
                }
            }

            // now capture range of the first sheet = I will need this to create pivot table
            Excel.Range oRange = xlWorkSheet.Range["A1", "F3000"];

            // create second sheet
            if (xlApp.Application.Sheets.Count < 2)
            {
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add();
            }
            else
            {
                xlWorkSheet = xlApp.Worksheets[2];
            }
            xlWorkSheet.Name = "Pivot Table";

            // specify first cell for pivot table
            Excel.Range oRange2 = xlWorkSheet.Cells[1, 1];

            // create Pivot Cache and Pivot Table
            Excel.PivotCache oPivotCache = (Excel.PivotCache)xlWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
            Excel.PivotTable oPivotTable = (Excel.PivotTable)xlWorkSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Summary");

            // create Pivot Field, note that name will be the same as column name on sheet one
            Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("V");
            oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
            oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
            oPivotField.Name = "Somme V";
            // by default name will be something like sumOfSalary, to change it, assign new name to it
            // name cannot be the same as Pivot Fields, therefore I added empty space in front of it.
            // name cannot be empty either


            xlWorkBook.SaveAs("‪C9.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file C9.xls");
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }