经常需要在开发中使用Excel COM,为简化使用写了这个类,感觉还是不太方便

 1None.gif using  System;
  2 None.gif
  3 None.gif namespace  ExcelHandle
  4 ExpandedBlockStart.gifContractedBlock.gif dot.gif {
  5ExpandedSubBlockStart.gifContractedSubBlock.gif    /**//// <summary>
  6InBlock.gif    /// ExcelHandle 的摘要说明。
  7ExpandedSubBlockEnd.gif    /// </summary>

  8InBlock.gif    public class ExcelHandle
  9ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{
 10InBlock.gif
 11ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 12InBlock.gif        /// Excel
 13ExpandedSubBlockEnd.gif        /// </summary>

 14InBlock.gif        public Excel.Application CurExcel = null;
 15InBlock.gif
 16ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 17InBlock.gif        /// 工作簿
 18ExpandedSubBlockEnd.gif        /// </summary>

 19InBlock.gif        public Excel._Workbook CurBook = null;
 20InBlock.gif
 21ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 22InBlock.gif        /// 工作表
 23ExpandedSubBlockEnd.gif        /// </summary>

 24InBlock.gif        public Excel._Worksheet CurSheet = null;
 25InBlock.gif
 26InBlock.gif        private object mValue = System.Reflection.Missing.Value;
 27InBlock.gif
 28ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 29InBlock.gif        /// 构造函数
 30ExpandedSubBlockEnd.gif        /// </summary>

 31InBlock.gif        public ExcelHandle()
 32ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
 33InBlock.gif            //
 34InBlock.gif            // TODO: 在此处添加构造函数逻辑
 35InBlock.gif            //    
 36InBlock.gif
 37InBlock.gif            this.dtBefore = System.DateTime.Now;
 38InBlock.gif            
 39InBlock.gif            CurExcel = new Excel.Application();
 40InBlock.gif
 41InBlock.gif            this.dtAfter = System.DateTime.Now;
 42InBlock.gif
 43InBlock.gif            this.timestamp = System.DateTime.Now.ToShortDateString().Replace("-"""+ System.DateTime.Now.ToShortTimeString().Replace(":"""+ System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
 44InBlock.gif
 45ExpandedSubBlockEnd.gif        }

 46InBlock.gif
 47ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 48InBlock.gif        /// 构造函数
 49InBlock.gif        /// </summary>
 50ExpandedSubBlockEnd.gif        /// <param name="strFilePath">加载的Excel文件名</param>

 51InBlock.gif        public ExcelHandle(string strFilePath)
 52ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
 53InBlock.gif
 54InBlock.gif            this.dtBefore = System.DateTime.Now;
 55InBlock.gif
 56InBlock.gif            CurExcel = new Excel.Application();
 57InBlock.gif
 58InBlock.gif            this.dtAfter = System.DateTime.Now;
 59InBlock.gif
 60InBlock.gif            CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
 61InBlock.gif
 62InBlock.gif            this.timestamp = System.DateTime.Now.ToShortDateString().Replace("-"""+ System.DateTime.Now.ToShortTimeString().Replace(":"""+ System.DateTime.Now.Second.ToString() + System.DateTime.Now.Millisecond.ToString();
 63InBlock.gif
 64ExpandedSubBlockEnd.gif        }

 65InBlock.gif
 66ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
 67InBlock.gif        /// 释放内存空间
 68ExpandedSubBlockEnd.gif        /// </summary>

 69InBlock.gif        public void Dispose()
 70ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
 71InBlock.gif            try
 72ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
 73InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
 74InBlock.gif                CurSheet = null;
 75InBlock.gif
 76InBlock.gif                CurBook.Close(false, mValue, mValue);
 77InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
 78InBlock.gif                CurBook = null;
 79InBlock.gif
 80InBlock.gif                CurExcel.Quit();
 81InBlock.gif                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
 82InBlock.gif                CurExcel = null;
 83InBlock.gif            
 84InBlock.gif                GC.Collect();
 85InBlock.gif                GC.WaitForPendingFinalizers();
 86InBlock.gif            
 87ExpandedSubBlockEnd.gif            }

 88InBlock.gif            catch(System.Exception ex)
 89ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
 90InBlock.gif                this.MessageWarning("在释放Excel内存空间时发生了一个错误:", ex);
 91ExpandedSubBlockEnd.gif            }

 92InBlock.gif            finally
 93ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
 94InBlock.gif                foreach(System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))                    
 95InBlock.gif                    if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter)
 96InBlock.gif                        pro.Kill();
 97ExpandedSubBlockEnd.gif            }

 98InBlock.gif            System.GC.SuppressFinalize(this);
 99ExpandedSubBlockEnd.gif        }

100InBlock.gif
101InBlock.gif
102InBlock.gif        private string filepath;
103InBlock.gif        private string timestamp;
104InBlock.gif        private System.DateTime dtBefore;
105InBlock.gif        private System.DateTime dtAfter;
106InBlock.gif
107InBlock.gif
108ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
109InBlock.gif        /// Excel文件名
110ExpandedSubBlockEnd.gif        /// </summary>

111InBlock.gif        public string FilePath
112ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
113InBlock.gif            get
114ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
115InBlock.gif                return this.filepath;
116ExpandedSubBlockEnd.gif            }

117InBlock.gif            set
118ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
119InBlock.gif                this.filepath = value;
120ExpandedSubBlockEnd.gif            }

121ExpandedSubBlockEnd.gif        }

122InBlock.gif
123ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
124InBlock.gif        /// 是否打开Excel界面
125ExpandedSubBlockEnd.gif        /// </summary>

126InBlock.gif        public bool Visible
127ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
128InBlock.gif            set
129ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
130InBlock.gif                CurExcel.Visible = value;
131ExpandedSubBlockEnd.gif            }

132ExpandedSubBlockEnd.gif        }

133InBlock.gif
134ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
135InBlock.gif        /// 以时间字符串作为保存文件的名称
136ExpandedSubBlockEnd.gif        /// </summary>

137InBlock.gif        public string TimeStamp
138ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
139InBlock.gif            get
140ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
141InBlock.gif                return this.timestamp;
142ExpandedSubBlockEnd.gif            }

143InBlock.gif            set
144ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
145InBlock.gif                this.timestamp = value;
146ExpandedSubBlockEnd.gif            }

147ExpandedSubBlockEnd.gif        }

148InBlock.gif
149InBlock.gif
150ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
151InBlock.gif        /// 加载Excel文件
152ExpandedSubBlockEnd.gif        /// </summary>

153InBlock.gif        public void Load()
154ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
155InBlock.gif            if (CurBook == null && this.filepath != null)
156InBlock.gif                CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(this.filepath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
157ExpandedSubBlockEnd.gif        }

158InBlock.gif
159ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
160InBlock.gif        /// 加载Excel文件
161InBlock.gif        /// </summary>
162ExpandedSubBlockEnd.gif        /// <param name="strFilePath">Excel文件名</param>

163InBlock.gif        public void Load(string strFilePath)
164ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
165InBlock.gif            if (CurBook == null)
166InBlock.gif                CurBook = (Excel._Workbook)CurExcel.Workbooks.Open(strFilePath, mValue, false, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue, mValue);
167ExpandedSubBlockEnd.gif        }

168InBlock.gif
169ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
170InBlock.gif        /// 新建工作表
171InBlock.gif        /// </summary>
172ExpandedSubBlockEnd.gif        /// <param name="strWorkSheetName">工作表名称</param>

173InBlock.gif        public void NewWorkSheet(string strWorkSheetName)
174ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
175InBlock.gif            CurSheet = (Excel._Worksheet)CurBook.Sheets.Add(CurBook.Sheets[1], mValue, mValue, mValue);
176InBlock.gif            CurSheet.Name = strWorkSheetName;
177ExpandedSubBlockEnd.gif        }

178InBlock.gif
179ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
180InBlock.gif        /// 在指定单元格插入指定的值
181InBlock.gif        /// </summary>
182InBlock.gif        /// <param name="strCell">单元格,如“A4”</param>
183ExpandedSubBlockEnd.gif        /// <param name="objValue">文本、数字等值</param>

184InBlock.gif        public void WriteCell(string strCell, object objValue)
185ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
186InBlock.gif            CurSheet.get_Range(strCell, mValue).Value2 = objValue;
187ExpandedSubBlockEnd.gif        }

188InBlock.gif
189ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
190InBlock.gif        /// 在指定Range中插入指定的值
191InBlock.gif        /// </summary>
192InBlock.gif        /// <param name="strStartCell">Range的开始单元格</param>
193InBlock.gif        /// <param name="strEndCell">Range的结束单元格</param>
194ExpandedSubBlockEnd.gif        /// <param name="objValue">文本、数字等值</param>

195InBlock.gif        public void WriteRange(string strStartCell, string strEndCell, object objValue)
196ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
197InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Value2 = objValue;
198ExpandedSubBlockEnd.gif        }

199InBlock.gif
200InBlock.gif
201ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
202InBlock.gif        /// 合并单元格,并在合并后的单元格中插入指定的值
203InBlock.gif        /// </summary>
204InBlock.gif        /// <param name="strStartCell"></param>
205InBlock.gif        /// <param name="strEndCell"></param>
206ExpandedSubBlockEnd.gif        /// <param name="objValue"></param>

207InBlock.gif        public void WriteAfterMerge(string strStartCell, string strEndCell, object objValue)
208ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
209InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Merge(mValue);
210InBlock.gif            CurSheet.get_Range(strStartCell, mValue).Value2 = objValue;
211ExpandedSubBlockEnd.gif        }

212InBlock.gif
213ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
214InBlock.gif        /// 在连续单元格中插入一个DataTable中的值
215InBlock.gif        /// </summary>
216InBlock.gif        /// <param name="strStartCell">开始的单元格</param>
217ExpandedSubBlockEnd.gif        /// <param name="dtData">存储数据的DataTable</param>

218InBlock.gif        public void WriteTable(string strStartCell, System.Data.DataTable dtData)
219ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
220InBlock.gif            object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count];
221InBlock.gif
222InBlock.gif            for (int i = 0; i < dtData.Rows.Count; i ++)
223InBlock.gif                for (int j = 0; j < dtData.Columns.Count; j ++)
224InBlock.gif                    arrData[i, j] = dtData.Rows[i][j];
225InBlock.gif
226InBlock.gif            CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 1)).Value2 = arrData;
227InBlock.gif
228InBlock.gif            arrData = null;
229ExpandedSubBlockEnd.gif        }

230InBlock.gif
231ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
232InBlock.gif        /// 在连续单元格中插入一个DataTable并作超级链接
233InBlock.gif        /// </summary>
234InBlock.gif        /// <param name="strStartCell">起始单元格标识符</param>
235InBlock.gif        /// <param name="dtData">存储数据的DataTable</param>
236InBlock.gif        /// <param name="strLinkField">链接的地址字段</param>
237ExpandedSubBlockEnd.gif        /// <param name="strTextField">链接的文本字段</param>

238InBlock.gif        public void WriteTableAndLink(string strStartCell, System.Data.DataTable dtData, string strLinkField, string strTextField)
239ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
240InBlock.gif            object[,] arrData = new object[dtData.Rows.Count, dtData.Columns.Count - 1];
241InBlock.gif
242InBlock.gif            for (int i = 0; i < dtData.Rows.Count; i ++)
243ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{
244InBlock.gif                for (int j = 0; j < dtData.Columns.Count; j ++)
245ExpandedSubBlockStart.gifContractedSubBlock.gif                dot.gif{
246InBlock.gif                    if (j > dtData.Columns.IndexOf(strLinkField))
247InBlock.gif                        arrData[i, j - 1= dtData.Rows[i][j];
248InBlock.gif                    else if (j < dtData.Columns.IndexOf(strLinkField))
249InBlock.gif                        arrData[i, j] = dtData.Rows[i][j];
250ExpandedSubBlockEnd.gif                }

251ExpandedSubBlockEnd.gif            }

252InBlock.gif
253InBlock.gif            CurSheet.get_Range(strStartCell, this.GetEndCell(strStartCell, dtData.Rows.Count - 1, dtData.Columns.Count - 2)).Value2 = arrData;
254InBlock.gif
255InBlock.gif            for (int i = 0; i < dtData.Rows.Count; i ++)
256InBlock.gif                this.AddHyperLink(this.NtoL(this.LtoN(this.GetCellLetter(strStartCell)) + dtData.Columns.IndexOf(strTextField)) + System.Convert.ToString(this.GetCellNumber(strStartCell) + i), dtData.Rows[i][strLinkField].ToString() + ".htm""点击查看详细", dtData.Rows[i][strTextField].ToString());
257InBlock.gif
258InBlock.gif            arrData = null;
259ExpandedSubBlockEnd.gif        }

260InBlock.gif
261ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
262InBlock.gif        /// 为单元格设置公式
263InBlock.gif        /// </summary>
264InBlock.gif        /// <param name="strCell">单元格标识符</param>
265ExpandedSubBlockEnd.gif        /// <param name="strFormula">公式</param>

266InBlock.gif        public void SetFormula(string strCell, string strFormula)
267ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
268InBlock.gif            CurSheet.get_Range(strCell, mValue).Formula = strFormula;
269ExpandedSubBlockEnd.gif        }

270InBlock.gif
271ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
272InBlock.gif        /// 设置单元格或连续区域的字体为黑体
273InBlock.gif        /// </summary>
274ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

275InBlock.gif        public void SetBold(string strCell)
276ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
277InBlock.gif            CurSheet.get_Range(strCell, mValue).Font.Bold = true;
278ExpandedSubBlockEnd.gif        }

279InBlock.gif
280ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
281InBlock.gif        /// 设置连续区域的字体为黑体
282InBlock.gif        /// </summary>
283InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
284ExpandedSubBlockEnd.gif        /// <param name="strEndCell">结束单元格标识符</param>

285InBlock.gif        public void SetBold(string strStartCell, string strEndCell)
286ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
287InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Font.Bold = true;
288ExpandedSubBlockEnd.gif        }

289InBlock.gif
290ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
291InBlock.gif        /// 设置单元格或连续区域的字体颜色
292InBlock.gif        /// </summary>
293InBlock.gif        /// <param name="strCell">单元格标识符</param>
294ExpandedSubBlockEnd.gif        /// <param name="clrColor">颜色</param>

295InBlock.gif        public void SetColor(string strCell, System.Drawing.Color clrColor)
296ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
297InBlock.gif            CurSheet.get_Range(strCell, mValue).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
298ExpandedSubBlockEnd.gif        }

299InBlock.gif
300ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
301InBlock.gif        /// 设置连续区域的字体颜色
302InBlock.gif        /// </summary>
303InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
304InBlock.gif        /// <param name="strEndCell">结束单元格标识符</param>
305ExpandedSubBlockEnd.gif        /// <param name="clrColor">颜色</param>

306InBlock.gif        public void SetColor(string strStartCell, string strEndCell, System.Drawing.Color clrColor)
307ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
308InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
309ExpandedSubBlockEnd.gif        }

310InBlock.gif
311ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
312InBlock.gif        /// 设置单元格或连续区域的边框:上下左右都为黑色连续边框
313InBlock.gif        /// </summary>
314ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

315InBlock.gif        public void SetBorderAll(string strCell)
316ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
317InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
318InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
319InBlock.gif
320InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
321InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
322InBlock.gif
323InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
324InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
325InBlock.gif
326InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
327InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
328InBlock.gif
329InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
330InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
331InBlock.gif
332InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
333InBlock.gif            CurSheet.get_Range(strCell, mValue).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
334InBlock.gif
335InBlock.gif
336ExpandedSubBlockEnd.gif        }

337InBlock.gif
338ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
339InBlock.gif        /// 设置连续区域的边框:上下左右都为黑色连续边框
340InBlock.gif        /// </summary>
341InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
342ExpandedSubBlockEnd.gif        /// <param name="strEndCell">结束单元格标识符</param>

343InBlock.gif        public void SetBorderAll(string strStartCell, string strEndCell)
344ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
345InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
346InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
347InBlock.gif
348InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
349InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
350InBlock.gif
351InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
352InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
353InBlock.gif
354InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
355InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
356InBlock.gif
357InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
358InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
359InBlock.gif
360InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
361InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
362ExpandedSubBlockEnd.gif        }

363InBlock.gif
364ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
365InBlock.gif        /// 设置单元格或连续区域水平居左
366InBlock.gif        /// </summary>
367ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

368InBlock.gif        public void SetHAlignLeft(string strCell)
369ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
370InBlock.gif            CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
371ExpandedSubBlockEnd.gif        }

372InBlock.gif
373ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
374InBlock.gif        /// 设置连续区域水平居左
375InBlock.gif        /// </summary>
376InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
377ExpandedSubBlockEnd.gif        /// <param name="strEndCell">结束单元格标识符</param>

378InBlock.gif        public void SetHAlignLeft(string strStartCell, string strEndCell)
379ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
380InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
381ExpandedSubBlockEnd.gif        }

382InBlock.gif
383ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
384InBlock.gif        /// 设置单元格或连续区域水平居左
385InBlock.gif        /// </summary>
386ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

387InBlock.gif        public void SetHAlignCenter(string strCell)
388ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
389InBlock.gif            CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
390ExpandedSubBlockEnd.gif        }

391InBlock.gif
392ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
393InBlock.gif        /// 设置连续区域水平居中
394InBlock.gif        /// </summary>
395InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
396ExpandedSubBlockEnd.gif        /// <param name="strEndCell">结束单元格标识符</param>

397InBlock.gif        public void SetHAlignCenter(string strStartCell, string strEndCell)
398ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
399InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
400ExpandedSubBlockEnd.gif        }

401InBlock.gif
402ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
403InBlock.gif        /// 设置单元格或连续区域水平居右
404InBlock.gif        /// </summary>
405ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

406InBlock.gif        public void SetHAlignRight(string strCell)
407ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
408InBlock.gif            CurSheet.get_Range(strCell, mValue).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
409ExpandedSubBlockEnd.gif        }

410InBlock.gif
411ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
412InBlock.gif        /// 设置连续区域水平居右
413InBlock.gif        /// </summary>
414InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
415ExpandedSubBlockEnd.gif        /// <param name="strEndCell">结束单元格标识符</param>

416InBlock.gif        public void SetHAlignRight(string strStartCell, string strEndCell)
417ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
418InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
419ExpandedSubBlockEnd.gif        }

420InBlock.gif
421ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
422InBlock.gif        /// 设置单元格或连续区域的显示格式
423InBlock.gif        /// </summary>
424InBlock.gif        /// <param name="strCell">单元格标识符</param>
425ExpandedSubBlockEnd.gif        /// <param name="strNF">如"#,##0.00"的显示格式</param>

426InBlock.gif        public void SetNumberFormat(string strCell, string strNF)
427ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
428InBlock.gif            CurSheet.get_Range(strCell, mValue).NumberFormat = strNF;
429ExpandedSubBlockEnd.gif        }

430InBlock.gif
431ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
432InBlock.gif        /// 设置连续区域的显示格式
433InBlock.gif        /// </summary>
434InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
435InBlock.gif        /// <param name="strEndCell">结束单元格标识符</param>
436ExpandedSubBlockEnd.gif        /// <param name="strNF">如"#,##0.00"的显示格式</param>

437InBlock.gif        public void SetNumberFormat(string strStartCell, string strEndCell, string strNF)
438ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
439InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).NumberFormat = strNF;
440ExpandedSubBlockEnd.gif        }

441InBlock.gif
442ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
443InBlock.gif        /// 设置单元格或连续区域的字体大小
444InBlock.gif        /// </summary>
445InBlock.gif        /// <param name="strCell">单元格或连续区域标识符</param>
446ExpandedSubBlockEnd.gif        /// <param name="intFontSize"></param>

447InBlock.gif        public void SetFontSize(string strCell, int intFontSize)
448ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
449InBlock.gif            CurSheet.get_Range(strCell, mValue).Font.Size = intFontSize.ToString();
450ExpandedSubBlockEnd.gif        }

451InBlock.gif
452ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
453InBlock.gif        /// 设置连续区域的字体大小
454InBlock.gif        /// </summary>
455InBlock.gif        /// <param name="strStartCell">开始单元格标识符</param>
456InBlock.gif        /// <param name="strEndCell">结束单元格标识符</param>
457ExpandedSubBlockEnd.gif        /// <param name="intFontSize">字体大小</param>

458InBlock.gif        public void SetFontSize(string strStartCell, string strEndCell, int intFontSize)
459ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
460InBlock.gif            CurSheet.get_Range(strStartCell, strEndCell).Font.Size = intFontSize.ToString();
461ExpandedSubBlockEnd.gif        }

462InBlock.gif
463ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
464InBlock.gif        /// 设置列宽
465InBlock.gif        /// </summary>
466InBlock.gif        /// <param name="strColID">列标识,如A代表第一列</param>
467ExpandedSubBlockEnd.gif        /// <param name="decWidth">宽度</param>

468InBlock.gif        public void SetColumnWidth(string strColID, double dblWidth)
469ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
470ExpandedSubBlockStart.gifContractedSubBlock.gif            ((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[]dot.gif{(strColID + ":" + strColID).ToString()})).ColumnWidth = dblWidth;
471ExpandedSubBlockEnd.gif        }

472InBlock.gif
473ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
474InBlock.gif        /// 为单元格添加超级链接
475InBlock.gif        /// </summary>
476InBlock.gif        /// <param name="strCell">单元格标识符</param>
477InBlock.gif        /// <param name="strAddress">链接地址</param>
478InBlock.gif        /// <param name="strTip">屏幕提示</param>
479ExpandedSubBlockEnd.gif        /// <param name="strText">链接文本</param>

480InBlock.gif        public void AddHyperLink(string strCell, string strAddress, string strTip, string strText)
481ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
482InBlock.gif            CurSheet.Hyperlinks.Add(CurSheet.get_Range(strCell, mValue), strAddress, mValue, strTip, strText);
483ExpandedSubBlockEnd.gif        }

484InBlock.gif
485ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
486InBlock.gif        /// 已知开始的单元格标识,求intR行、intColumn列后的单元格标识
487InBlock.gif        /// </summary>
488InBlock.gif        /// <param name="strStartCell">开始单元格标识</param>
489InBlock.gif        /// <param name="intR">行数</param>
490InBlock.gif        /// <param name="intC">列数</param>
491ExpandedSubBlockEnd.gif        /// <returns>单元格标识符结果</returns>

492InBlock.gif        public string GetEndCell(string strStartCell, int intR, int intC)
493ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
494InBlock.gif
495InBlock.gif            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
496InBlock.gif
497InBlock.gif            return this.NtoL(this.LtoN(regex.Match(strStartCell).Result("${vLetter}")) + intC) + System.Convert.ToString((System.Convert.ToInt32(regex.Match(strStartCell).Result("${vNumber}")) + intR));
498InBlock.gif
499ExpandedSubBlockEnd.gif        }

500InBlock.gif
501ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
502InBlock.gif        /// 获取单元格标识符中的字母
503InBlock.gif        /// </summary>
504InBlock.gif        /// <param name="strCell">单元格标识符</param>
505ExpandedSubBlockEnd.gif        /// <returns>单元格标识符对应的字母</returns>

506InBlock.gif        public string GetCellLetter(string strCell)
507ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
508InBlock.gif            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
509InBlock.gif            return regex.Match(strCell).Result("${vLetter}");
510ExpandedSubBlockEnd.gif        }

511InBlock.gif
512ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
513InBlock.gif        /// 获取单元格标识符中的数字
514InBlock.gif        /// </summary>
515ExpandedSubBlockEnd.gif        /// <param name="strCell">单元格标识符</param>

516InBlock.gif        public int GetCellNumber(string strCell)
517ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
518InBlock.gif            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^(?<vLetter>[A-Z]+)(?<vNumber>\d+)");
519InBlock.gif            return System.Convert.ToInt32(regex.Match(strCell).Result("${vNumber}"));
520ExpandedSubBlockEnd.gif        }

521InBlock.gif
522InBlock.gif
523ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
524InBlock.gif        /// 另存为xls文件
525InBlock.gif        /// </summary>
526ExpandedSubBlockEnd.gif        /// <param name="strFilePath">文件路径</param>

527InBlock.gif        public void Save(string strFilePath)
528ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
529InBlock.gif            CurBook.SaveCopyAs(strFilePath);
530ExpandedSubBlockEnd.gif        }

531InBlock.gif
532ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
533InBlock.gif        /// 另存为html文件
534InBlock.gif        /// </summary>
535ExpandedSubBlockEnd.gif        /// <param name="strFilePath">文件路径</param>

536InBlock.gif        public void SaveHtml(string strFilePath)
537ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
538InBlock.gif            CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
539ExpandedSubBlockEnd.gif        }

540InBlock.gif
541InBlock.gif        public void CreateHtmlFile()
542ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
543InBlock.gif            
544ExpandedSubBlockEnd.gif        }

545InBlock.gif
546InBlock.gif
547InBlock.gif           
548ContractedSubBlock.gifExpandedSubBlockStart.gif        辅助函数#region 辅助函数
549InBlock.gif
550ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
551InBlock.gif        /// 调用MessageBox显示警告信息
552InBlock.gif        /// </summary>
553ExpandedSubBlockEnd.gif        /// <param name="text">警告信息</param>

554InBlock.gif        private void MessageWarning(string text)
555ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
556InBlock.gif            System.Windows.Forms.MessageBox.Show(text, "Excel操作组件", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
557ExpandedSubBlockEnd.gif        }

558InBlock.gif
559ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
560InBlock.gif        /// 调用MessageBox显示警告信息
561InBlock.gif        /// </summary>
562InBlock.gif        /// <param name="text">警告信息</param>
563ExpandedSubBlockEnd.gif        /// <param name="ex">产生警告的异常</param>

564InBlock.gif        private void MessageWarning(string text, System.Exception ex)
565ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
566InBlock.gif            System.Windows.Forms.MessageBox.Show(text + "\n\n错误信息:\n" + ex.Message + "\n堆栈跟踪:" + ex.StackTrace + "\n错误来源:" + ex.Source, "Excel操作组件", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
567ExpandedSubBlockEnd.gif        }

568InBlock.gif
569ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
570InBlock.gif        /// 字母转换为数字,Excel列头,如A-1;AA-27
571InBlock.gif        /// </summary>
572InBlock.gif        /// <param name="strLetter">字母</param>
573ExpandedSubBlockEnd.gif        /// <returns>字母对应的数字</returns>

574InBlock.gif        private int LtoN(string strLetter)
575ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
576InBlock.gif            int intRtn = 0;
577InBlock.gif
578InBlock.gif            string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
579InBlock.gif
580InBlock.gif            if (strLetter.Length == 2)
581InBlock.gif                intRtn += (strLetters.IndexOf(strLetter.Substring(01)) + 1* 26;
582InBlock.gif
583InBlock.gif            intRtn += strLetters.IndexOf(strLetter.Substring(strLetter.Length - 11)) + 1;
584InBlock.gif
585InBlock.gif            return intRtn;
586InBlock.gif
587ExpandedSubBlockEnd.gif        }

588InBlock.gif
589ExpandedSubBlockStart.gifContractedSubBlock.gif        /**//// <summary>
590InBlock.gif        /// 数字转换为字母,Excel列头,如1-A;27-AA
591InBlock.gif        /// </summary>
592InBlock.gif        /// <param name="intNumber">数字</param>
593ExpandedSubBlockEnd.gif        /// <returns>数字对应的字母</returns>

594InBlock.gif        private string NtoL(int intNumber)
595ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{
596InBlock.gif            if (intNumber > 702)
597InBlock.gif                return String.Empty;
598InBlock.gif
599InBlock.gif            if (intNumber == 702)
600InBlock.gif                return "ZZ";
601InBlock.gif
602InBlock.gif            string strRtn = String.Empty;
603InBlock.gif
604InBlock.gif            string strLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
605InBlock.gif
606InBlock.gif            if (intNumber > 26
607InBlock.gif                strRtn = strLetters.Substring(intNumber / 26 - 11);
608InBlock.gif            
609InBlock.gif            strRtn += strLetters.Substring((intNumber % 26- 11);
610InBlock.gif
611InBlock.gif            return strRtn;
612ExpandedSubBlockEnd.gif        }

613InBlock.gif
614ExpandedSubBlockEnd.gif        #endregion
 辅助函数
615InBlock.gif
616InBlock.gif
617InBlock.gif
618ExpandedSubBlockEnd.gif    }

619ExpandedBlockEnd.gif}

转载于:https://www.cnblogs.com/gzsxy8013/archive/2005/11/09/272248.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值