【转】关于C#使用Excel的数据透视表的例子

收到消息，下星期又有导出 Excel 报表的代码要写。
心想，不就是 OleDb 先 CREATE 表， 然后 INSERT 么？
都是体力活啊......

结果拿到纸张的报表，我就悲剧了。
报表的结构，像下面这种结构，行/列都不确定的

 

 

 

 

因为
行是日期，外部用户指定
列是物品，可能有，可能没有，取决于外部用户的日期范围。

如果在C#里面，先把所有出现过的物品，作为列名
然后再自己分别组合计算，最后再去 OleDb 那里去 CREATE + INSERT 嘛
这个报表又有个那么大的标题。

 

假如显示的报表，是一个 Sheet ，报表的数据在另外一个 Sheet 里面。
我C#导出 Excel 的时候，把数据写到一个 [数据Sheet] 里面。
显示的 [报表Sheet] 里面，设置好引用那个 [数据Sheet] 的数据。
想一下，应该是可行的。

既然应该是可行的，那么我C#里面，干脆连计算也别计算了。
因为这样的效果，在 Excel 里面，使用数据透视表，点个三五下，就结束的工作。
我还跑C#里面计算来计算去的，还容易出错。

 

首先嘛，先去创建一个模版 Excel, 2个 Sheet
1个[数据Sheet]，1个[报表Sheet]
然后[数据Sheet]里面，造点测试数据，
[报表Sheet] 里面，折腾好格式 与 数据透视表

这个模版就暂时保存为 Template.xls

然后这个文件，加入 C# 的项目，设置为 “如果较新则复制”

C# 项目，是一个 WinForm 的项目，就2按钮
1个是把数据 通过 OleDB 写进 Excel
1个是更新 Excel 中的 数据透视表信息。

按钮1的代码

        /// <summary>
        /// 使用模版文件创建 Excel 文件.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnUseTemplate_Click(object sender, EventArgs e)
        {
            // 如果文件存在，先删除.
            if (File.Exists(EXCEL_FILE_NAME))
            {
                File.Delete(EXCEL_FILE_NAME);
            }
            // 模版复制为目标文件.
            string filePath = HttpContext.Current.Request.MapPath("temp/Template.xls");
            File.Copy(filePath, EXCEL_FILE_NAME);
            String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + EXCEL_FILE_NAME + ";Extended Properties=Excel 8.0;";
            //excel2007的ConnectionString，其中Microsoft.Jet.OLEDB.12.0必须安装AccessDatabaseEngine.exe
            //String sConnectionString = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + EXCEL_FILE_NAME + ";Extended Properties=Excel 12.0;";
            OleDbConnection cn = new OleDbConnection(sConnectionString);
            // 打开连接.
            cn.Open();
            string sqlCreate =
             @"CREATE TABLE [销售数据] ([日期] Date, [商品] VarChar, [数量] Int)";
            OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
            // 创建 Sheet
            cmd.ExecuteNonQuery();
            OleDbCommand icmd = new OleDbCommand();
            icmd.Connection = cn;
            icmd.CommandText = "INSERT INTO [销售数据] ([日期], [商品], [数量]) VALUES (@SaleData, @SaleGoods, @SaleMoney)";
            for (int i = 0; i < 10; i++)
            {
                for (int j = 0; j < 10; j++)
                {
                    OleDbParameter[] paraArray = new OleDbParameter[3];
                    paraArray[0] = new OleDbParameter("@SaleData", OleDbType.Date);
                    paraArray[1] = new OleDbParameter("@SaleGoods", OleDbType.VarChar);
                    paraArray[2] = new OleDbParameter("@SaleMoney", OleDbType.Integer);
                    paraArray[0].Value = DateTime.Today.AddDays(-i);
                    paraArray[1].Value = "商品" + j;
                    paraArray[2].Value = i + j;
                    icmd.Parameters.Clear();
                    icmd.Parameters.AddRange(paraArray);
                    icmd.ExecuteNonQuery();
                }
            }
            cn.Close();
            MessageBox.Show("处理完毕！！！");
        }

 

按钮2的代码

        /// <summary>
        /// 更新数据透视表的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnBuildData_Click(object sender, EventArgs e)
        {
            ExcelService service = new ExcelService();
            // 打开 Excel.
            service.OpenExcel();
            // 打开 Excel 文件.
            //service.OpenExcelFile(AppDomain.CurrentDomain.BaseDirectory + EXCEL_FILE_NAME);
            service.OpenExcelFile(EXCEL_FILE_NAME);
            // 设置 数据透视表数据.
            service.AddPivotTable("销售数据", "统计报表", "数据透视表1");
            // 保存 Excel 文件.
            service.SaveExcelFile();
            // 关闭 Excel.
            service.CloseExcel();
            MessageBox.Show("处理完毕！！！");
        }

 

其中 ExcelService  的代码如下 ：

using System;
using System.Data;
using System.Drawing;
using Excel = Microsoft.Office.Interop.Excel;
namespace ZXCCLib.Web.UILoader.Test
{
    /// <summary>
    /// Excel 服务.
    /// </summary>
    public class ExcelService
    {
        #region OPEN WORKBOOK VARIABLES
        private static object vk_missing = System.Reflection.Missing.Value;
        private static object vk_visible = true;
        private static object vk_false = false;
        private static object vk_true = true;
        private object vk_update_links = 0;
        private object vk_read_only = vk_true;
        private object vk_format = 1;
        private object vk_password = vk_missing;
        private object vk_write_res_password = vk_missing;
        private object vk_ignore_read_only_recommend = vk_true;
        private object vk_origin = vk_missing;
        private object vk_delimiter = vk_missing;
        private object vk_editable = vk_false;
        private object vk_notify = vk_false;
        private object vk_converter = vk_missing;
        private object vk_add_to_mru = vk_false;
        private object vk_local = vk_false;
        private object vk_corrupt_load = vk_false;
        #endregion
        /// <summary>
        /// Excel App.
        /// </summary>
        private Excel.Application xlApp;
        /// <summary>
        /// Excel 工作簿.
        /// </summary>
        private Excel.Workbook xlBook = null;
        /// <summary>
        /// Excel 工作表.
        /// </summary>
        private Excel.Worksheet xlSheet = null;
        /// <summary>
        /// 打开 Excel.
        /// </summary>
        public void OpenExcel()
        {
            // 启动 Excel.
            xlApp = new Excel.ApplicationClass();
            // 可见/不可见.
            xlApp.Visible = true;
            xlApp.UserControl = true;
            xlApp.DisplayAlerts = true;
        }

        /// <summary>
        /// 退出 Excel.
        /// </summary>
        public void CloseExcel()
        {
            xlApp.DisplayAlerts = false;
            xlApp.Workbooks.Close();
            xlApp.Quit();
        }
        /// <summary>
        /// 打开 Excel 文件.
        /// </summary>
        /// <param name="excelFile"></param>
        public void OpenExcelFile(String excelFile)
        {
            // 打开文件.
            xlBook = xlApp.Workbooks.Open(excelFile,
               vk_update_links, vk_missing, vk_format, vk_password,
               vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
               vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
               vk_local, vk_corrupt_load);
            // 取得第一个 Sheet.
            foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
            {
                xlSheet = displayWorksheet;
                break;
            }
        }
        /// <summary>
        /// 保存 Excel 文件.
        /// </summary>
        public void SaveExcelFile()
        {
            xlBook.Save();
        }
        /// <summary>
        /// 另存为 Excel 文件.
        /// </summary>
        public void SaveAsExcelFile(String asFileName)
        {
            xlBook.SaveCopyAs(asFileName);
        }

        /// <summary>
        /// 关闭 Excel 文件.
        /// </summary>
        public void CloseExcelFile()
        {
            xlBook.Close(false, false, false);
        }
        /// <summary>
        /// 选择工作表.
        /// </summary>
        /// <param name="sheetName"></param>
        public void SelectSheet(String sheetName)
        {
            // 选择工作表.
            xlSheet = (Excel.Worksheet)xlBook.Sheets.get_Item(sheetName);
        }
        /// <summary>
        /// 取得单元格文本.
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public String GetStringValue(int row, int col)
        {
            return xlSheet.get_Range(xlSheet.Cells[row, col], xlSheet.Cells[row, col]).Text.ToString().Trim();
        }
        /// <summary>
        /// 设置单元格文本./// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="value"></param>
        public void SetValue(int row, int col, String value)
        {
            // 取得范围.
            Excel.Range range = xlSheet.get_Range(xlSheet.Cells[row, col], xlSheet.Cells[row, col]);
            // 原有的数值.
            String oldValue = range.Text.ToString().Trim();
            // 设置数值.
            xlSheet.Cells[row, col] = value;
            // 修改背景色
            range.Interior.ColorIndex = 19;
            // 添加备注.
            range.AddComment("修改前数值：" + oldValue);
        }
        /// <summary>
        /// 设置指定单元格为 字符格式.
        /// </summary>
        /// <param name="row1"></param>
        /// <param name="col1"></param>
        /// <param name="row2"></param>
        /// <param name="col2"></param>
        public void SetTextFormat(int row1, int col1, int row2, int col2)
        {
            // 选择区域.
            Excel.Range myrange = xlSheet.get_Range(xlSheet.Cells[row1, col1], xlSheet.Cells[row2, col2]);
            // 文本格式
            myrange.NumberFormatLocal = "@";
        }

        /// <summary>
        /// 更新数据透视表数据.
        /// </summary>
        /// <param name="dataSheerName">数据Sheet名</param>
        /// <param name="pivotSheetName">报表Sheet名</param>
        /// <param name="pivottableName">数据视图表名</param>
        public void AddPivotTable(string dataSheerName, string pivotSheetName, string pivottableName)
        {
            // 首先定位到 数据的 Sheet. 设定 数据透视表的 的数据源.
            xlSheet = null;
            foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
            {
                if (dataSheerName == displayWorksheet.Name)
                {
                    xlSheet = displayWorksheet;
                    break;
                }
            }
            if (xlSheet != null)
            {
                // 取得数据的Sheet的行数与列数
                int rowCount = xlSheet.UsedRange.Rows.Count;
                int colCount = xlSheet.UsedRange.Columns.Count;
                // 拼写好 数据源的名字，准备后面用于更新 数据透视表的数据源.
                string sourceData = dataSheerName + "!R1C1:R" + rowCount + "C" + colCount;
                // 然后定位到 数据透视表的 Sheet. 刷新数据.
                xlSheet = null;
                foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
                {
                    if (pivotSheetName == displayWorksheet.Name)
                    {
                        xlSheet = displayWorksheet;
                        break;
                    }
                }
                if (xlSheet != null)
                {
                    // 修改 Excel 文件中 数据透视表的 数据源
                    ((Excel.PivotTable)xlSheet.PivotTables(pivottableName)).SourceData = sourceData;
                    // 刷新数据 : 重新计算 数据透视表数据
                    ((Excel.PivotTable)xlSheet.PivotTables(pivottableName)).Update();
                }
            }
        }
    }
}

 

测试运行一下，按第一个按钮，先产生一个 [销售数据] 的Sheet.

按第2个按钮，
打开 Excel
设置 [统计报表]那个Sheet中，数据透视表的数据源为 [销售数据] 中的数据。
然后重新计算
最后保存

最后运行的结果如下：

 

 

---完---