none
C# Export Pivot DataTabel to Excel RRS feed

  • Question

  • Hi,

    I have a Pivot DataTable which is displayed as Pivot in web:

    The table is displayed simply as below:

    grdBothPivot.DataSource = pvt.PivotData("CTC", AggregateFunction.Max, new string[] { "Designation", "Year" }, new string[] { "Company", "Department" });
    grdBothPivot.DataBind();


    But when trying to export it to excel, it displayed as below:

    Here is the c# code which I use:

    public void ExportToExcel(System.Data.DataTable Tbl, string ExcelFilePath = null)
    {
        try
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");
    
            // load excel, and create a new workbook
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Workbooks.Add();
    
            // single worksheet
            Microsoft.Office.Interop.Excel._Worksheet workSheet = (Microsoft.Office.Interop.Excel._Worksheet)excelApp.ActiveSheet;
    
            // column headings
            for (int i = 0; i < Tbl.Columns.Count; i++)
            {
                workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
            }
    
            // rows
            for (int i = 0; i < Tbl.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < Tbl.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                }
            }
    
            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    workSheet.SaveAs(ExcelFilePath);
                    excelApp.Quit();
                    //  MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                excelApp.Visible = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
    
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            System.Data.DataTable dt = SqlLayer.GetDataTable("GetEmployee");
            Pivot pvt = new Pivot(dt);
            System.Data.DataTable dwwt = pvt.PivotData("CTC", AggregateFunction.Max, new string[] { "Designation", "Year" }, new string[] { "Company", "Department" });
    
            ExportToExcel(dwwt, @"C:\Users\myma\Documents\dd.xlsx");
        }
        catch (Exception ex)
        {
    
        }
    }

    Any one can help and explain, why it is exported bad to excel?

    It is appreciated if anyone code send me the modified code

    Regards,

    Nadeem Bader


    • Edited by Nadim2522 Monday, March 21, 2016 1:40 PM
    • Moved by CoolDadTx Monday, March 21, 2016 2:58 PM Office related
    Monday, March 21, 2016 1:38 PM

All replies

  • I think you still need to apply Pivot format on the Excel file after export, the table in excel is not a Pivot one and there is difference, your code is just importing column and rows. What type of control is that grid that you importing? If its a third party one you should refer their documentation to see if it supports such export to excel as pivot. If you not looking for a Pivot format in Excel and you just want to remove duplicate data, you can implement small custom work to do the job.

    Check this article on how to create a Pivot table programmatically:

    https://blogs.msdn.microsoft.com/andreww/2008/07/25/creating-a-pivottable-programmatically/ 


    Fouad Roumieh


    Thursday, March 24, 2016 7:10 AM
  • Hi Nadim2522,

    As the reply from Fouad, the table in excel is not Pivot, you just write columns and rows into excel. You could try suggestion from Fouad to create a new Pivot table with the data.

    In other way, I suggest you format the tables to achieve your requirement. For details code about formatting table, I suggest you record the steps you format the table, and write C# code according your recorded code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 25, 2016 3:09 AM
  • Hi,

    Thank you for reply.

    The PivotData function is a function that I created (Not third Party function).
    It just create a new Pivot DataTable (Lets call it PivotDataTable) from exist DataTable. In other words, it do all required functionalities and create a Pivot Table as DataTable object.

    Based on the link that you posted before, is that possible to make PivotCache reads the data from the PivotDataTable and display it in Excel file? If so, how? hope you send me code which implemnts this idea.

    Regards,

    Nadeem

    Friday, April 1, 2016 11:34 PM
  • Hi Nadeem,

    Do you have any issue for your original issue about table formatting? If not, I suggest you mark the helpful reply as answer to closet this thread.

    >> is that possible to make PivotCache reads the data from the PivotDataTable and display it in Excel file?
    For this new issue about get data from PivotDataTable, I suggest you refer the link below, if you still have any issue about this, I suggest you post a new thread for this, and then more community members would help you.

    # PivotTable.SourceData Property (Excel)

    https://msdn.microsoft.com/EN-US/library/office/ff193521.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 5, 2016 1:15 AM