C＃将Pivot DataTabel导出到Excel [英] C# Export Pivot DataTabel to Excel

问题描述

我有一个Pivot DataTable，在网络中显示为Pivot：

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

该表格如下所示：

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

但是当尝试将其导出为ex​​cel时，它显示如下：

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

这是我使用的c＃代码：

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)
    {

    }
}

任何人都可以帮助和解释，为什么它出口不好excel？

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

如果有人向我发送修改后的代码，我们将不胜感激

It is appreciated if anyone code send me the modified code

问候，

Nadeem Bader

Nadeem Bader

推荐答案

我认为你仍然需要在导出后在Excel文件上应用数据透视表格式，excel中的表格不是数据透视表，并且存在差异，你的代码只是导入列和行。您导入的网格是什么类型的控件？如果是第三个
派对，你应该参考他们的文档，看它是否支持这样导出到excel作为pivot。如果您没有在Excel中查找数据透视表格式，并且只想删除重复数据，则可以实现小型自定义工作来完成工作。

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/  

这篇关于C＃将Pivot DataTabel导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！