生成Excel透视图、另存为网页

 

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Text;
using DAL;
using System.IO;

using System.Text.RegularExpressions;
using Microsoft.Office.Interop.Excel;

public partial class test_test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // 定義所有 Excel 物件所需的相關變數
        Application eApplication = null;
        Workbook eWorkbook = null;
        Worksheet sheet = null;
        PivotTable pivotTable = null;
        Range pivotData = null;
        Range pivotDestination = null;
        PivotField salesRegion = null;
        PivotField salesAmount = null;
        ChartObjects chartObjects = null;
        ChartObject pivotChart = null;
        SlicerCache salesTypeSlicer = null;
        SlicerCache salesRegionSlicer = null;
        SlicerCache salesPersonSlicer = null;

        // 定義檔案位置等變數
        string workBookName = @"d:\1Excel200Slicers.xlsx";
        string pivotTableName = @"Sales By Type";
        string workSheetName = @"Quarterly Sales";

        try
        {
            // 建立一個 Excel 實體
            eApplication = new Application();

            // 建立工作簿及工作表
            eWorkbook = eApplication.Workbooks.Add(
                XlWBATemplate.xlWBATWorksheet);
            sheet = (Worksheet)(eWorkbook.Worksheets[1]);
            sheet.Name = workSheetName;

            // 把所有數據資料透過 SetRow 方法來新增
            SetRow(sheet, 1,
              "销售区域", "销售代表", "销售类別", "销售金额");
            SetRow(sheet, 2, "东区", "Joe", "批发", "123");
            SetRow(sheet, 3, "东区", "Joe", "零售", "432");
            SetRow(sheet, 4, "东区", "Joe", "政府机关", "111");
            SetRow(sheet, 5, "东区", "Robert", "批发", "564");
            SetRow(sheet, 6, "东区", "Robert", "零售", "234");
            SetRow(sheet, 7, "东区", "Robert", "政府机关", "321");
            SetRow(sheet, 8, "东区", "Michelle", "批发", "940");
            SetRow(sheet, 9, "东区", "Michelle", "零售", "892");
            SetRow(sheet, 10, "东区", "Michelle", "政府机关", "10");
            SetRow(sheet, 11, "西区", "Erich", "批发", "120");
            SetRow(sheet, 12, "西区", "Erich", "零售", "45");
            SetRow(sheet, 13, "西区", "Erich", "政府机关", "410");
            SetRow(sheet, 14, "西区", "Dafna", "批发", "800");
            SetRow(sheet, 15, "西区", "Dafna", "零售", "3409");
            SetRow(sheet, 16, "西区", "Dafna", "政府机关", "123");
            SetRow(sheet, 17, "东区", "Rob", "批发", "777");
            SetRow(sheet, 18, "东区", "Rob", "零售", "450");
            SetRow(sheet, 19, "东区", "Rob", "政府机关", "900");
            sheet.Columns.AutoFit();

            // 選取資料範為以建立 PivotTable
            pivotData = sheet.get_Range("A1", "D19");

            // 選取 PivotTable 位置
            pivotDestination = sheet.get_Range("F2");

            // 新增一個 PivotTable 到工作表中
            sheet.PivotTableWizard(
                XlPivotTableSourceType.xlDatabase,
                pivotData,
                pivotDestination,
                pivotTableName
                );

            // 設定變數以利操作 PivotTable.
            pivotTable =
              (PivotTable)sheet.PivotTables(pivotTableName);
            salesRegion = ((PivotField)pivotTable.PivotFields(3));
            salesAmount = ((PivotField)pivotTable.PivotFields(4));

            // 設定 PivotTable 格式
            pivotTable.TableStyle2 = "PivotStyleLight16";
            pivotTable.InGridDropZones = false;

            // 設定「销售区域」的欄位
            salesRegion.Orientation =
              XlPivotFieldOrientation.xlRowField;

            // 加總「销售金额」 欄位設定
            salesAmount.Orientation =
              XlPivotFieldOrientation.xlDataField;
            salesAmount.Function = XlConsolidationFunction.xlSum;

            // 把 PivotChart 新增到工作表中
            chartObjects = (ChartObjects)sheet.ChartObjects();
            pivotChart = chartObjects.Add(310, 100, 225, 175);

            // 設定 PivotChart 格式
            pivotChart.Chart.ChartWizard(pivotData,
              XlChartType.xlColumnClustered,
              Title: "Sales",
              HasLegend: false,
             CategoryLabels: 3,
             SeriesLabels: 0);

            // 新增「交叉篩選分析器」 至 PivotTable
            salesTypeSlicer =
              eWorkbook.SlicerCaches.Add(pivotTable, "销售类別");
            salesTypeSlicer.Slicers.Add(sheet,
              Top: 10, Left: 540, Width: 100, Height: 100);
            salesRegionSlicer =
             eWorkbook.SlicerCaches.Add(pivotTable, "销售区域");
            salesRegionSlicer.Slicers.Add(sheet,
              Top: 120, Left: 540, Width: 100, Height: 100);
            salesPersonSlicer =
               eWorkbook.SlicerCaches.Add(pivotTable, "销售代表");
            salesPersonSlicer.Slicers.Add(sheet,
             Top: 10, Left: 645, Width: 100, Height: 200);


            eWorkbook.RefreshAll();     //全部更新数据源，这个很有用，可以让透视图表自动更新

            // 把工作表儲存
            sheet.get_Range("A1").Activate();
            saveAs(eWorkbook, workBookName,Type.Missing);

            //eWorkbook.SaveAs(@"d:\etoo.htm", XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            saveAs(eWorkbook, @"d:\etoo.htm", XlFileFormat.xlHtml);

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {

            salesAmount = null;
            salesRegion = null;
            pivotDestination = null;
            pivotData = null;
            pivotChart = null;
            chartObjects = null;
            pivotTable = null;
            salesTypeSlicer = null;
            salesRegionSlicer = null;
            salesPersonSlicer = null;
            sheet = null;

            if (eWorkbook != null)
                eWorkbook = null;

            if (eApplication != null)
            {
                eApplication.Quit();
                eApplication = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

    }

    /// <summary>
    /// Excel另存为文件
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="filePath"></param>
    void saveAs(Workbook workbook, string filePath, object fileFormat)
    {
        if (File.Exists(filePath))
        {
            File.Delete(filePath);
        }
        workbook.SaveAs(filePath, FileFormat: fileFormat, AccessMode: XlSaveAsAccessMode.xlNoChange);
    }


    /// <summary>
    /// 處理欲寫入資料方法
    /// </summary>
    void SetRow(Worksheet sheet, int row, params string[] values)
    {
        for (int x = 0; x < values.Length; x++)
        {
            sheet.Cells[row, x + 1] = values[x];
        }
    }




}