C#实现Excel动态生成PivotTable

其中 TileJs是一个开源的构建类似win8 Metro风格的javascript库。

编写后台脚本：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.Style;
using OfficeOpenXml.Utils;
using OfficeOpenXml.Table.PivotTable;
using System.IO;
using System.Data.SqlClient;
using System.Data;
namespace ExcelPivot.Web
{
  public partial class index : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private DataTable getDataSource()
    {
      //createDataTable();
      //return ProductInfo;

      SqlConnection conn = new SqlConnection();
      conn.ConnectionString = "Data Source=.;Initial Catalog=olap;Persist Security Info=True;User ID=sa;Password=sa";
      conn.Open();

      SqlDataAdapter ada = new SqlDataAdapter("select * from v_pm_olap_test", conn);
      DataSet ds = new DataSet();
      ada.Fill(ds);

      return ds.Tables[0];



    }
   
    protected void btn1_ServerClick(object sender, EventArgs e)
    {
      try
      {
        DataTable table = getDataSource();
        string path = "_demo_" + System.Guid.NewGuid().ToString().Replace("-", "_") + ".xls";
        //string path = "_demo.xls";
        FileInfo fileInfo = new FileInfo(path);
        var excel = new ExcelPackage(fileInfo);

        var wsPivot = excel.Workbook.Worksheets.Add("Pivot");
        var wsData = excel.Workbook.Worksheets.Add("Data");
        wsData.Cells["A1"].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium6);
        if (table.Rows.Count != 0)
        {
          foreach (DataColumn col in table.Columns)
          {
           
            if (col.DataType == typeof(System.DateTime))
            {
              var colNumber = col.Ordinal + 1;
              var range = wsData.Cells[2, colNumber, table.Rows.Count + 1, colNumber];
              range.Style.Numberformat.Format = "yyyy-MM-dd";
            }
            else
            {

            }
          }
        }

        var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
        dataRange.AutoFitColumns();
        var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "Pivot");
        pivotTable.MultipleFieldFilters = true;
        pivotTable.RowGrandTotals = true;
        pivotTable.ColumGrandTotals = true;
        pivotTable.Compact = true;
        pivotTable.CompactData = true;
        pivotTable.GridDropZones = false;
        pivotTable.Outline = false;
        pivotTable.OutlineData = false;
        pivotTable.ShowError = true;
        pivotTable.ErrorCaption = "[error]";
        pivotTable.ShowHeaders = true;
        pivotTable.UseAutoFormatting = true;
        pivotTable.ApplyWidthHeightFormats = true;
        pivotTable.ShowDrill = true;
        pivotTable.FirstDataCol = 3;
        //pivotTable.RowHeaderCaption = "行";

        //row field
        var field004 = pivotTable.Fields["销售客户经理"];
        pivotTable.RowFields.Add(field004);

        var field001 = pivotTable.Fields["项目简称"];
        pivotTable.RowFields.Add(field001);
        //field001.ShowAll = false;

        //column field
        var field002 = pivotTable.Fields["年"];
        pivotTable.ColumnFields.Add(field002);
        field002.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
        var field005 = pivotTable.Fields["月"];
        pivotTable.ColumnFields.Add(field005);
        field005.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;

        //data field
        var field003 = pivotTable.Fields["回款金额"];
        field003.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Descending;
        pivotTable.DataFields.Add(field003);

        pivotTable.RowGrandTotals = false;
        pivotTable.ColumGrandTotals = false;
       
        //save file
        excel.Save();
        //open excel file
        string file = @"C:Windowsexplorer.exe";
        System.Diagnostics.Process.Start(file, path);

      }
      catch (Exception ex)
      {
       Response.Write(ex.Message);
      }
    }
  }
}
								 
 2/3   首页 上一页 1 2 3 下一页 尾页