Microsoft Office 2003 Web Components 自动计数/自动求和

1.Microsoft Office 2003 Web Components 简介：

Microsoft Office Web Components are a collection of Component Object Model (COM) controls for publishing spreadsheets, charts, and databases to the Web. They are also used to view these items when published and to view data access pages.If you have Microsoft Office FrontPage 2003, Microsoft Office Access 2003, and Microsoft Office Excel 2003 installed, Office Web Components allow you to publish interactive data as part of a Web page. Used with Microsoft Internet Explorer version 5.01 or later, Office Web Components allow you to view a published control (spreadsheet, chart, or database) on a Web page and to view data access pages. 

2.AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet 也可以集成到WinForm程序上(添加Microsoft.Office.Interop.Owc11.dll引用)，

但集成的时候是没有右下角的求和/计数显示的，而且Microsoft Office 2003 Web Components之后也没有更新的版本。

3.我们可以在Excel控件下面在添加一个Label控件(labelTotal)，用来显示求和/计数；

  具体代码如下：

  (1)：Excel控件的事件

　　ssExcel：AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet 控件

using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.Office.Interop.Owc11;
//https://www.cnblogs.com/yellow3gold/
private List<Range> selectedRangeList = new List<Range>();//在Excel上选中的Range集合

//ssExcel为Excel控件
//鼠标操作事件
private void ssExcel_MouseUpEvent(object sender, AxMicrosoft.Office.Interop.Owc11.ISpreadsheetEventSink_MouseUpEvent e)
{
    if (e.shift == 2)
        selectedRangeList.Add(ssExcel.Selection);
    else
    {
        selectedRangeList.Clear();
        selectedRangeList.Add(ssExcel.Selection);
    }
    AutoTotal();
}

//键盘操作事件
private void ssExcel_KeyUpEvent(object sender, AxMicrosoft.Office.Interop.Owc11.ISpreadsheetEventSink_KeyUpEvent e)
{
    /*e.shift=1/Shift      代表按住了Shift键
      e.shift=2/Ctrl       代表按住了Ctrl键
      e.shift=3/Ctrl+Shift 代表同时按住了Ctrl+Shift键*/
    if (e.shift == 0 && e.keyCode > 36 && e.keyCode < 41)
        AutoTotal();    
    if (e.shift > 0 && e.keyCode > 36 && e.keyCode < 41)
    {
        selectedRangeList.Clear();
        selectedRangeList.Add(ssExcel.Selection);
        AutoTotal();
    }
}

  (2)：求和的方法

//labelTotal为显示求和/计数的label
public void AutoTotal()
{
    //https://www.cnblogs.com/yellow3gold/
    var cellList = new List<object>();
    var rangeList = GetValidRangeList(selectedRangeList);
    foreach (var range in rangeList)
    {
        var datas = GetRangeValue(range);
        cellList.AddRange(datas.Cast<object>().Where(x => !string.IsNullOrWhiteSpace(Convert.ToString(x))));
    }
    if (cellList.Count > 0)
    {
        decimal totalNum = 0M;
        int totalCount = 0;
        bool notnum = false;
        try
        {
            var isallEmpty = cellList.All(x => string.IsNullOrWhiteSpace(Convert.ToString(x)));
            if (isallEmpty)
            {
                labelTotal.Text = string.Empty;
                return;
            }
            foreach (var item in cellList)
            {
                if (item != null && Regex.IsMatch(Convert.ToString(item), @"^-?\d+\.?\d*%$"))
                {
                    totalNum += Convert.ToDecimal(Convert.ToString(item).Replace("%", "")) / 100;
                    totalCount += 1;
                }
                else if (item != null && Regex.IsMatch(Convert.ToString(item), @"^-?\d+\.?\d*$"))
                {
                    totalNum += Convert.ToDecimal(Convert.ToString(item));
                    totalCount += 1;
                }
                else
                {
                    if (item != null && !string.IsNullOrWhiteSpace(Convert.ToString(item)))
                    {
                        notnum = true;
                        totalCount += 1;
                    }
                }
            }
            if (notnum && totalCount != 0)
                labelTotal.Text = @"计数：" + totalCount;
            else
                labelTotal.Text = @"求和：" + totalNum;
        }
        catch (Exception ex)
        {
            labelTotal.Text = string.Empty;
        }
    }
    else
        labelTotal.Text = string.Empty;
}

  (3)：一些的基础方法

//获取有效的选中区域
private List<Range> GetValidRangeList(List<Range> selectedRangeList)
{
    List<Range> resultList = new List<Range>();
    Range uRange = ssExcel.ActiveSheet.UsedRange;
    Range userRange = GetRange(0, 0, uRange.Column + uRange.Columns.Count - 1, uRange.Row + uRange.Rows.Count - 1);
    foreach (Range range in selectedRangeList)
        resultList.Add(LimiteRange(range, userRange));
    return resultList;
}
//https://www.cnblogs.com/yellow3gold/
public Range GetRange(int row, int col, int width, int height)
{
    return ssExcel.get_Range(ssExcel.Cells[row + 1, col + 1], ssExcel.Cells[row + height, col + width]);
}
//https://www.cnblogs.com/yellow3gold/
//获取交集
private Range LimiteRange(Range range1, Range range2)
{
    int width = range1.Columns.Count;
    int height = range1.Rows.Count;
    if (range1.Columns.Count >= range1.EntireRow.Columns.Count)
        width = range2.Columns.Count - range1.Column + 1;
    if (range1.Rows.Count >= range1.EntireColumn.Rows.Count)
        height = range2.Rows.Count - range1.Row + 1;
    if (width != range1.Columns.Count || height != range1.Rows.Count)
        return GetRange(range1.Row - 1, range1.Column - 1, width, height);
    else
        return range1;
}
//https://www.cnblogs.com/yellow3gold/
//获取选中区域的数据
private object[,] GetRangeValue(Range range)
{
    if (range.Rows.Count == 1 && range.Columns.Count == 1)
    {
        object[,] datas = new object[2, 2];
        datas[1, 1] = range.get_Value(Type.Missing);
        return datas;
    }
    else
        return (object[,])range.get_Value(Type.Missing);
}