附上一段原创常用代码 计算列标题字符串
Function CalcColumn(ByVal c As Integer) As String Dim temp As String Dim x As Integer If c > 0 And c <= 256 Then x = (c - 1) \ 26 If x > 0 Then temp = CStr(Chr(Asc("A") + x - 1)) End If x = c - (c \ 26) * 26 x = IIf(x = 0, 26, x) temp = temp + CStr(Chr(Asc("A") + x Mod 27 - 1)) End If CalcColumn = temp End Function
1 public static int ToIndex(string columnName) 2 { 3 if (!Regex.IsMatch(columnName.ToUpper(), @"[A-Z]+")) { throw new Exception("invalid parameter"); } 4 int index = 0; 5 char[] chars = columnName.ToUpper().ToCharArray(); 6 for (int i = 0; i < chars.Length; i++) 7 { 8 index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1); 9 } 10 return index - 1; 11 } 12 13 public static string ToName(int index) 14 { 15 if (index < 0) { throw new Exception("invalid parameter"); } 16 List<string> chars = new List<string>(); 17 do 18 { 19 if (chars.Count > 0) index--; 20 chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString()); 21 index = (int)((index - index % 26) / 26); 22 } while (index > 0); 23 return String.Join(string.Empty, chars.ToArray()); 24 } 25 26 private void button1_Click(object sender, EventArgs e) 27 { 28 // 数字转字母列 29 //this.textBox2.Text = ToName(Convert.ToInt16(this.textBox1.Text.ToString().Trim())); 30 this.textBox2.Text = ToName(Convert.ToInt16(this.textBox1.Text.ToString().Trim()) - 1); 31 } 32 33 private void button2_Click(object sender, EventArgs e) 34 { 35 // 字母转数字列 36 //this.textBox2.Text = (ToIndex(this.textBox1.Text.Trim())).ToString(); 37 this.textBox2.Text = (ToIndex(this.textBox1.Text.Trim()) + 1).ToString(); 38 }
根据列数计算列标题字母
1 using System; 2 using System.IO; 3 //using Microsoft.Office.Core; 4 using Excel = Microsoft.Office.Interop.Excel; 5 using System.Reflection; 6 7 namespace DoVBAMacro 8 { 9 /// 10 /// 执行Excel VBA宏帮助类 11 /// 12 public class ExcelMacroHelper 13 { 14 /// 15 /// 执行Excel中的宏 16 /// 17 /// Excel文件路径 18 /// 宏名称 19 /// 宏参数组 20 /// 宏返回值 21 /// 执行时是否显示 Excel 22 public void RunExcelMacro( 23 string excelFilePath, 24 string macroName, 25 object[] parameters, 26 out object rtnValue, 27 bool isShowExcel 28 ) 29 { 30 try 31 { 32 #region 检查入参 33 34 // 检查文件是否存在 35 if (!File.Exists(excelFilePath)) 36 { 37 throw new System.Exception(excelFilePath + " 文件不存在 "); 38 } 39 40 // 检查是否输 入宏名称 41 if (string.IsNullOrEmpty(macroName)) 42 { 43 throw new System.Exception("请输 入宏的名称"); 44 } 45 46 #endregion 47 48 #region 调用宏处理 49 50 // 准备打开Excel文件时的缺省参数对象 51 object oMissing = Missing.Value; 52 53 // 根 据参数组是否为空，准备参数组对象 54 object[] paraObjects; 55 56 if (parameters == null) 57 { 58 paraObjects = new object[] { macroName }; 59 } 60 else 61 { 62 // 宏参数组长度 63 int paraLength = parameters.Length; 64 65 paraObjects = new object[paraLength + 1]; 66 67 paraObjects[0] = macroName; 68 for (int i = 0; i < paraLength; i++) 69 { 70 paraObjects[i + 1] = parameters[i]; 71 } 72 } 73 74 // 创建Excel对象示例 75 Excel.ApplicationClass oExcel = new Excel.ApplicationClass(); 76 77 // 判断是否要求执行时Excel可见 78 if (isShowExcel) 79 { 80 // 使创建的对 象可见 81 oExcel.Visible = true; 82 } 83 84 // 创建Workbooks对象 85 Excel.Workbooks oBooks = oExcel.Workbooks; 86 87 // 创 建Workbook对象 88 Excel._Workbook oBook = null; 89 90 // 打开指定的Excel文件 91 oBook = oBooks.Open( 92 excelFilePath, 93 oMissing, 94 oMissing, 95 oMissing, 96 oMissing, 97 oMissing, 98 oMissing, 99 oMissing, 100 oMissing, 101 oMissing, 102 oMissing, 103 oMissing, 104 oMissing, 105 oMissing, 106 oMissing 107 ); 108 109 // 执行Excel中的宏 110 rtnValue = this.RunMacro(oExcel, paraObjects); 111 112 // 保存更改 113 oBook.Save(); 114 115 // 退出Workbook 116 oBook.Close(false, oMissing, oMissing); 117 118 #endregion 119 120 #region 释放对象 121 122 // 释放Workbook对象 123 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); 124 oBook = null; 125 126 // 释放Workbooks对象 127 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); 128 oBooks = null; 129 130 // 关闭Excel 131 oExcel.Quit(); 132 133 // 释放Excel对象 134 System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); 135 oExcel = null; 136 137 // 调用垃圾回收 138 GC.Collect(); 139 140 #endregion 141 } 142 catch (Exception ex) 143 { 144 throw ex; 145 } 146 } 147 148 /// 149 /// 执行宏 150 /// 151 152 /// Excel对象 153 /// 参数（第一个参数为指定宏名称，后面为指定宏的参数值） 154 /// 宏返回值 155 private object RunMacro(object oApp, object[] oRunArgs) 156 { 157 try 158 { 159 // 声明一个返回对象 160 object objRtn; 161 162 // 反射方式执行宏 163 objRtn = oApp.GetType().InvokeMember( 164 "Run", 165 BindingFlags.Default | BindingFlags.InvokeMethod, 166 null, 167 oApp, 168 oRunArgs 169 ); 170 171 // 返回值 172 return objRtn; 173 } 174 catch (Exception ex) 175 { 176 // 如果有底层异常，抛出底层异 常 177 if (ex.InnerException.Message.ToString().Length > 0) 178 { 179 throw ex.InnerException; 180 } 181 else 182 { 183 throw ex; 184 } 185 } 186 } 187 } 188 } 189 190 ////示例三个VBA宏方法： 191 //Sub getTime() 192 193 //Sheet1.Cells(1, 1) = Now 194 195 //End Sub 196 197 198 //Sub getTime2(title As String) 199 200 //Sheet1.Cells(2, 1) = title & " : " & Now 201 202 //End Sub 203 204 //Function getTime3(title As String) As String 205 206 //getTime3 = title & " : " & Now 207 208 //End Function 209 210 ////对应 的三个使用方法 211 //不带参数的宏调用（兼演示执行过程显示Excel文件） 212 //带 参数的宏调用（兼演示执行过程不显示Excel文件） 213 //有返回值的宏调用 214 //private void btnExe_Click(object sender, EventArgs e) 215 //{ 216 //try 217 //{ 218 //// 返回对象 219 //object objRtn = new object(); 220 221 //// 获得一个ExcelMacroHelper对象 222 //ExcelMacroHelper excelMacroHelper = new ExcelMacroHelper(); 223 224 //// 执行指定Excel中的宏，执行时显示Excel 225 //excelMacroHelper.RunExcelMacro( 226 //@"E:\csharp_study\DoVBAMacro\test.xls", 227 //"getTime2", 228 //new Object[] { "现在时刻" }, 229 //out objRtn, 230 //true 231 //); 232 233 //// 执行指定Excel中的宏，执行时不显示Excel 234 //excelMacroHelper.RunExcelMacro( 235 //@"E:\csharp_study\DoVBAMacro\test.xls", 236 //"getTime2", 237 //new Object[] { "现在时刻" }, 238 //out objRtn, 239 //false 240 //); 241 242 //// 执行指定Excel中的宏，执行时显示Excel，有返回值 243 //excelMacroHelper.RunExcelMacro( 244 //@"E:\csharp_study\DoVBAMacro\test.xls", 245 //"getTime3", 246 //new Object[] { "现在时刻" }, 247 //out objRtn, 248 //true 249 //); 250 251 //MessageBox.Show((string)objRtn); 252 253 //} 254 //catch(System.Exception ex) 255 //{ 256 //MessageBox.Show(ex.Message); 257 //} 258 //}