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 }
