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

  1 using System.Collections.Generic;
  2 using System.Linq;
  3 using System.Text.RegularExpressions;
  4 using Microsoft.Office.Interop.Owc11;
  5
  6 private List<Range> selectedRangeList = new List<Range>();//选中的Range集合
  7
  8 //ssExcel为Excel控件
  9 //鼠标操作事件
 10 private void ssExcel_MouseUpEvent(object sender, AxMicrosoft.Office.Interop.Owc11.ISpreadsheetEventSink_MouseUpEvent e)
 11 {
 12     if (e.shift == 2)
 13     {
 14         selectedRangeList.Add(ssExcel.Selection);
 15     }
 16     else
 17     {
 18         selectedRangeList.Clear();
 19         selectedRangeList.Add(ssExcel.Selection);
 20     }
 21     AutoTotal();
 22 }
 23
 24 //键盘操作事件
 25 private void ssExcel_KeyUpEvent(object sender, AxMicrosoft.Office.Interop.Owc11.ISpreadsheetEventSink_KeyUpEvent e)
 26 {
 27     if (e.shift == 0 && e.keyCode > 36 && e.keyCode < 41)
 28     {
 29         AutoTotal();
 30     }
 31     /*e.shift=1/shift
 32        e.shift=2/ctrl
 33        e.shift=3/ctrl+shift*/
 34     if (e.shift > 0 && e.keyCode > 36 && e.keyCode < 41)
 35     {
 36         selectedRangeList.Clear();
 37         selectedRangeList.Add(ssExcel.Selection);
 38         AutoTotal();
 39     }
 40 }
 41
 42 //labelTotal为显示求和/计数的label
 43 public void AutoTotal()
 44 {
 45     /*求和*/
 46     var cellList = new List<object>();
 47     var rangeList = GetValidRangeList(selectedRangeList);
 48     foreach (var range in rangeList)
 49     {
 50         var datas = GetRangeValue(range);
 51         cellList.AddRange(datas.Cast<object>().Where(x => !string.IsNullOrWhiteSpace(Convert.ToString(x))));
 52     }
 53     if (cellList.Count > 0)
 54     {
 55         decimal totalNum = 0M;
 56         int totalCount = 0;
 57         bool notnum = false;
 58         try
 59         {
 60             var isallEmpty = cellList.All(x => string.IsNullOrWhiteSpace(Convert.ToString(x)));
 61             if (isallEmpty)
 62             {
 63                 labelTotal.Visible = false;
 64                 return;
 65             }
 66             foreach (var item in cellList)
 67             {
 68                 if (item != null && Regex.IsMatch(Convert.ToString(item), @"^-?\d+\.?\d*%$"))
 69                 {
 70                     totalNum += Convert.ToDecimal(Convert.ToString(item).Replace("%", "")) / 100;
 71                     totalCount += 1;
 72                 }
 73                 else if (item != null && Regex.IsMatch(Convert.ToString(item), @"^-?\d+\.?\d*$"))
 74                 {
 75                     totalNum += Convert.ToDecimal(Convert.ToString(item));
 76                     totalCount += 1;
 77                 }
 78                 else
 79                 {
 80                     if (item != null && !string.IsNullOrWhiteSpace(Convert.ToString(item)))
 81                     {
 82                         notnum = true;
 83                         totalCount += 1;
 84                     }
 85                 }
 86             }
 87             if (notnum && totalCount != 0)
 88             {
 89                 labelTotal.Text = @"计数：" + totalCount;
 90                 labelTotal.Visible = true;
 91             }
 92             else
 93             {
 94                 labelTotal.Text = @"求和：" + totalNum;
 95                 labelTotal.Visible = true;
 96             }
 97         }
 98         catch (Exception ex)
 99         {
100             labelTotal.Visible = false;
101         }
102     }
103     else
104     {
105         labelTotal.Visible = false;
106     }
107 }
108
109 private List<Range> GetValidRangeList(List<Range> selectedRangeList)
110 {
111     List<Range> resultList = new List<Range>();
112     Range uRange = ssExcel.ActiveSheet.UsedRange;
113     Range userRange = GetRange(0, 0, uRange.Column + uRange.Columns.Count - 1, uRange.Row + uRange.Rows.Count - 1);
114
115     foreach (Range range in selectedRangeList)
116     {
117         resultList.Add(LimiteRange(range, userRange));
118     }
119     return resultList;
120 }
121
122 public Range GetRange(int row, int col, int width, int height)
123 {
124     return ssExcel.get_Range(ssExcel.Cells[row + 1, col + 1], ssExcel.Cells[row + height, col + width]);
125 }
126
127 private Range LimiteRange(Range range1, Range range2)
128 {
129     int width = range1.Columns.Count;
130     int height = range1.Rows.Count;
131     if (range1.Columns.Count >= range1.EntireRow.Columns.Count)
132     {
133         width = range2.Columns.Count - range1.Column + 1;
134     }
135     if (range1.Rows.Count >= range1.EntireColumn.Rows.Count)
136     {
137         height = range2.Rows.Count - range1.Row + 1;
138     }
139     if (width != range1.Columns.Count || height != range1.Rows.Count)
140     {
141         return GetRange(range1.Row - 1, range1.Column - 1, width, height);
142     }
143     else
144     {
145         return range1;
146     }
147 }
148
149 private object[,] GetRangeValue(Range range)
150 {
151     if (range.Rows.Count == 1 && range.Columns.Count == 1)
152     {
153         object[,] datas = new object[2, 2];
154         datas[1, 1] = range.get_Value(Type.Missing);
155         return datas;
156     }
157     else
158     {
159         return (object[,])range.get_Value(Type.Missing);
160     }
161 }