Aspose Cells财务报表开发总结

1、最近在开发一个财务的报表,非常复杂,行列各种套公式,开发完总结几个方法。

  1 /// <summary>
  2         /// 设置单元格样式
  3         /// </summary>
  4         /// <param name="ranges"></param>
  5         /// <param name="IsBold">字体是否加粗</param>
  6         /// <param name="color">单元格底色</param>
  7         /// <param name="values">字体大小</param>
  8         public void SetStyle(Cell cell, Style style, bool IsBold, Color color, int FontSize)
  9         {
 10             style.Pattern = BackgroundType.Solid;
 11             style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
 12             style.VerticalAlignment = TextAlignmentType.Center;
 13             style.Font.Name = "宋体";//文字字体
 14             style.Font.Size = FontSize;//文字大小
 15             style.IsLocked = false;//单元格解锁
 16             style.Font.IsBold = IsBold;//粗体
 17             style.IsTextWrapped = true;//单元格内容自动换行
 18             style.ShrinkToFit = true;//自适应
 19             style.Number = 1;
 20             cell.SetStyle(style);//给单元格关联样式
 21         }
 22 
 23 /// <summary>
 24         /// 整行格式设置
 25         /// </summary>
 26         /// <param name="workbook"></param>
 27         /// <param name="sheet"></param>
 28         /// <param name="strValue">“AA:AB”</param>
 29         /// <param name="color"></param>
 30         public void SetRowtStyle(Workbook workbook, Worksheet sheet, string strValue, Color color)
 31         {
 32             string[] str = strValue.Split(':');
 33 
 34             int firstRow = 0;
 35             int firstColumn = 0;
 36             int totalRows = 0;
 37             int totalColumns = 0;
 38 
 39             //列相同多行合并
 40             if (Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase) == Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase))
 41             {
 42                 //"E6:E7"
 43                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
 44                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
 45                 totalRows = Convert.ToInt16(Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase)) - Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) + 1;
 46                 totalColumns = 1;
 47             }
 48             //行相同列合并
 49             else if (Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase) == Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase))
 50             {
 51                 //"E6:K6"
 52                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
 53                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
 54                 totalRows = 1;
 55                 totalColumns = ToIndex(Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase)) - ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase)) + 1;
 56             }
 57             //行列都不相同
 58             else
 59             {
 60                 //"E8:K6"
 61                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
 62                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase)) - 1;
 63                 totalRows = ToIndex(Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase)) - ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
 64                 totalColumns = Convert.ToInt16(Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase)) - Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase));
 65             }
 66 
 67             Style style1 = workbook.Styles[workbook.Styles.Add()];
 68             //单元格背景颜色
 69             style1.ForegroundColor = color;//红色
 70             style1.Number = 1;
 71             style1.Pattern = BackgroundType.Solid;
 72             style1.HorizontalAlignment = TextAlignmentType.Center;//水平居中
 73             style1.IsTextWrapped = true;//单元格内容自动换行
 74                                         //边框样式
 75             style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Dotted; //左边框 
 76             style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Dotted; //右边框  
 77             style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Dotted; //上边框  
 78             style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Dotted; //下边框
 79 
 80             Range range = sheet.Cells.CreateRange(firstRow, firstColumn, totalRows, totalColumns);//第一行第一列单元格
 81             range.ApplyStyle(style1, new StyleFlag() { All = true });
 82         }
 83 
 84 /// <summary>
 85         /// 将excel中字符列转换为数字
 86         /// </summary>
 87         /// <param name="columnName">字母列名称</param>
 88         /// <returns></returns>
 89         public int ToIndex(string columnName)
 90         {
 91             if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) { throw new Exception("invalid parameter"); }
 92 
 93             int index = 0;
 94             char[] chars = columnName.ToUpper().ToCharArray();
 95             for (int i = 0; i < chars.Length; i++)
 96             {
 97                 index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
 98             }
 99             return index - 1;
100         }
101 
102         /// <summary>
103         /// 将数字转换为excel中字母列
104         /// </summary>
105         /// <param name="index">数字</param>
106         /// <returns></returns>
107         public string ToName(int index)
108         {
109             if (index < 0) { throw new Exception("invalid parameter"); }
110 
111             List<string> chars = new List<string>();
112             do
113             {
114                 if (chars.Count > 0) index--;
115                 chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
116                 index = (int)((index - index % 26) / 26);
117             } while (index > 0);
118 
119             return String.Join(string.Empty, chars.ToArray());
120         }
121 
122          /// <summary>
123         /// 单元格合并
124         /// </summary>
125         /// <param name="cells"></param>
126         /// <param name="strValue"></param>
127         public void Merge(Cells cells, string strValue)
128         {
129             ////取出字符串中所有的英文字母   
130             //strSplit1 = Regex.Replace(str20, "[0-9]", "", RegexOptions.IgnoreCase);
131             ////取出字符串中所有的数字   
132             //strSplit2 = Regex.Replace(str20, "[a-z]", "", RegexOptions.IgnoreCase);
133             string[] str = strValue.Split(':');
134 
135             int firstRow = 0;
136             int firstColumn = 0;
137             int totalRows = 0;
138             int totalColumns = 0;
139 
140             //列相同多行合并
141             if (Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase) == Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase))
142             {
143                 //"E6:E7"
144                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
145                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
146                 totalRows = Convert.ToInt16(Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase)) - Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) + 1;
147                 totalColumns = 1;
148             }
149             //行相同列合并
150             else if (Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase) == Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase))
151             {
152                 //"E6:K6"
153                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
154                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
155                 totalRows = 1;
156                 totalColumns = ToIndex(Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase)) - ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase)) + 1;
157             }
158             //行列都不相同
159             else
160             {
161                 //"E8:K6"
162                 firstRow = Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase)) - 1;
163                 firstColumn = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase)) - 1;
164                 totalRows = ToIndex(Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase)) - ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
165                 totalColumns = Convert.ToInt16(Regex.Replace(str[1], "[A-Z]", "", RegexOptions.IgnoreCase)) - Convert.ToInt16(Regex.Replace(str[0], "[A-Z]", "", RegexOptions.IgnoreCase));
166             }
167             cells.Merge(firstRow, firstColumn, totalRows, totalColumns);//合并单元格第6行第4列,合并两行1列
168 
169         }
170 
171         /// <summary>
172         /// 分组
173         /// </summary>
174         /// <param name="cells"></param>
175         /// <param name="strValue"></param>
176         /// <param name="isRowsOrColumns"></param>
177         /// <param name="bl"></param>
178         public void CellsGroup(Cells cells, string strValue, bool isRowsOrColumns, bool bl)
179         {
180             string[] str = strValue.Split(':');
181 
182             int firstIndex = 0;
183             int lastIndex = 0;
184 
185             if (isRowsOrColumns)
186             {
187                 firstIndex = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
188                 lastIndex = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
189                 cells.GroupRows(firstIndex, lastIndex, bl);//第一列到最后一列
190             }
191             else
192             {
193                 firstIndex = ToIndex(Regex.Replace(str[0], "[0-9]", "", RegexOptions.IgnoreCase));
194                 lastIndex = ToIndex(Regex.Replace(str[1], "[0-9]", "", RegexOptions.IgnoreCase)) - 1;
195                 cells.GroupColumns(firstIndex, lastIndex, bl);//第一列到最后一列
196             }
197         }
 1  /// <summary>
 2         /// 下一列
 3         /// </summary>
 4         /// <param name="strLetter"></param>
 5         /// <returns></returns>
 6         public static string NextLetter(string strLetter)
 7         {
 8             if (strLetter.Length == 1)
 9             {
10                 if (strLetter != "Z")
11                 {
12                     strLetter = Letter(Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]));
13                 }
14                 else
15                 {
16                     strLetter = "AA";
17                 }
18             }
19             else
20             {
21                 if (Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) == 90)//char 90 =Z
22                 {
23                     strLetter = Letter(Convert.ToInt16(strLetter.Substring(0, strLetter.Length - 1).ToCharArray()[0])) + "A";
24                 }
25                 else
26                 {
27                     if (Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) == 90)//char 90 =Z
28                     {
29                         strLetter = strLetter.Substring(0, strLetter.Length - 1) + "A";
30                     }
31                     else
32                     {
33                         strLetter = strLetter.Substring(0, strLetter.Length - 1) + Letter(Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]));
34                     }
35                 }
36             }
37             return strLetter;
38         }
39 
40         /// <summary>
41         /// 上一列
42         /// </summary>
43         /// <param name="strLetter"></param>
44         /// <returns></returns>
45         public static string OnLetter(string strLetter)
46         {
47             if (strLetter.Length == 1)
48             {
49                 strLetter = Letter(Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) - 2);
50             }
51             else
52             {
53                 if (Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) == 90)//char 90 =Z
54                 {
55                     strLetter = strLetter.Substring(0, 1) + Letter(Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) - 2);
56                 }
57                 else
58                 {
59                     if (Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) == 90)//char 90 =Z
60                     {
61                         strLetter = strLetter.Substring(0, strLetter.Length - 1) + "A";
62                     }
63                     else
64                     {
65                         if (strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0].ToString() == "A")
66                         {
67                             if (strLetter == "AA")
68                             {
69                                 strLetter = "Z";
70                             }
71                             else
72                             {
73                                 strLetter = Letter(Convert.ToInt16(strLetter.Substring(0, 1).ToCharArray()[0]) - 2) + "Z";
74                             }
75                         }
76                         else
77                         {
78                             strLetter = strLetter.Substring(0, strLetter.Length - 1) + Letter(Convert.ToInt16(strLetter.Substring(strLetter.Length - 1, 1).ToCharArray()[0]) - 2);
79                         }
80                     }
81                 }
82             }
83             return strLetter;
84         }
85 
86         private static string Letter(int iLetter)
87         {
88             iLetter = Convert.ToInt16(iLetter + 1);
89             return Convert.ToChar(iLetter).ToString();
90         }

 

posted @ 2021-02-26 11:17  未风  阅读(147)  评论(0编辑  收藏  举报