.NET的ExcelOperate

Posted on 2016-07-16 21:37 坐峰怀雪灬阅读(506) 评论(0) 编辑收藏举报
  1 using System;
  2 using System.Web;
  3 using Excel = Microsoft.Office.Interop.Excel;
  4 
  5 namespace Comm
  6 {
  7     /// <summary>
  8     /// ExcelOperate 的摘要说明。Excel操作函数
  9     /// </summary>
 10     public class ExcelOperate
 11     {
 12         private object mValue = System.Reflection.Missing.Value;
 13 
 14         public ExcelOperate()
 15         {
 16             //
 17             // TODO: 在此处添加构造函数逻辑
 18             //
 19         }
 20 
 21         /// <summary>
 22         /// 合并单元格
 23         /// </summary>
 24         /// <param name="CurSheet">Worksheet</param>
 25         /// <param name="objStartCell">开始单元格</param>
 26         /// <param name="objEndCell">结束单元格</param>
 27         public void Merge(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
 28         {
 29             CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
 30         }
 31         /// <summary>
 32         /// 设置连续区域的字体大小
 33         /// </summary>
 34         /// <param name="CurSheet">Worksheet</param>
 35         /// <param name="strStartCell">开始单元格</param>
 36         /// <param name="strEndCell">结束单元格</param>
 37         /// <param name="intFontSize">字体大小</param>
 38         public void SetFontSize(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize)
 39         {
 40             CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
 41         }
 42 
 43         /// <summary>
 44         /// 横向打印
 45         /// </summary>
 46         /// <param name="CurSheet"></param>
 47         public void xlLandscape(Excel._Worksheet CurSheet)
 48         {
 49             CurSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
 50 
 51         }
 52         /// <summary>
 53         /// 纵向打印
 54         /// </summary>
 55         /// <param name="CurSheet"></param>
 56         public void xlPortrait(Excel._Worksheet CurSheet)
 57         {
 58             CurSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
 59         }
 60 
 61 
 62         /// <summary>
 63         /// 在指定单元格插入指定的值
 64         /// </summary>
 65         /// <param name="CurSheet">Worksheet</param>
 66         /// <param name="Cell">单元格 如Cells[1,1]</param>
 67         /// <param name="objValue">文本、数字等值</param>
 68         public void WriteCell(Excel._Worksheet CurSheet, object objCell, object objValue)
 69         {
 70             CurSheet.get_Range(objCell, mValue).Value2 = objValue;
 71 
 72         }
 73 
 74         /// <summary>
 75         /// 在指定Range中插入指定的值
 76         /// </summary>
 77         /// <param name="CurSheet">Worksheet</param>
 78         /// <param name="StartCell">开始单元格</param>
 79         /// <param name="EndCell">结束单元格</param>
 80         /// <param name="objValue">文本、数字等值</param>
 81         public void WriteRange(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
 82         {
 83             CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
 84         }
 85 
 86         /// <summary>
 87         /// 合并单元格，并在合并后的单元格中插入指定的值
 88         /// </summary>
 89         /// <param name="CurSheet">Worksheet</param>
 90         /// <param name="objStartCell">开始单元格</param>
 91         /// <param name="objEndCell">结束单元格</param>
 92         /// <param name="objValue">文本、数字等值</param>
 93         public void WriteAfterMerge(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
 94         {
 95             CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
 96             CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;
 97 
 98         }
 99 
100         /// <summary>
101         /// 为单元格设置公式
102         /// </summary>
103         /// <param name="CurSheet">Worksheet</param>
104         /// <param name="objCell">单元格</param>
105         /// <param name="strFormula">公式</param>
106         public void SetFormula(Excel._Worksheet CurSheet, object objCell, string strFormula)
107         {
108             CurSheet.get_Range(objCell, mValue).Formula = strFormula;
109         }
110 
111 
112         /// <summary>
113         /// 单元格自动换行
114         /// </summary>
115         /// <param name="CurSheet">Worksheet</param>
116         /// <param name="objStartCell">开始单元格</param>
117         /// <param name="objEndCell">结束单元格</param>
118         public void AutoWrapText(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
119         {
120             CurSheet.get_Range(objStartCell, objEndCell).WrapText = true;
121         }
122 
123         /// <summary>
124         /// 设置整个连续区域的字体颜色
125         /// </summary>
126         /// <param name="CurSheet">Worksheet</param>
127         /// <param name="objStartCell">开始单元格</param>
128         /// <param name="objEndCell">结束单元格</param>
129         /// <param name="clrColor">颜色</param>
130         public void SetColor(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
131         {
132             CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
133         }
134 
135         /// <summary>
136         /// 设置整个连续区域的单元格背景色
137         /// </summary>
138         /// <param name="CurSheet"></param>
139         /// <param name="objStartCell"></param>
140         /// <param name="objEndCell"></param>
141         /// <param name="clrColor"></param>
142         public void SetBgColor(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
143         {
144             CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
145         }
146 
147         /// <summary>
148         /// 设置连续区域的字体名称
149         /// </summary>
150         /// <param name="CurSheet">Worksheet</param>
151         /// <param name="objStartCell">开始单元格</param>
152         /// <param name="objEndCell">结束单元格</param>
153         /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
154         public void SetFontName(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
155         {
156             CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
157         }
158 
159         /// <summary>
160         /// 设置连续区域的字体为黑体
161         /// </summary>
162         /// <param name="CurSheet">Worksheet</param>
163         /// <param name="objStartCell">开始单元格</param>
164         /// <param name="objEndCell">结束单元格</param>
165         public void SetBold(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
166         {
167             CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
168         }
169 
170 
171         /// <summary>
172         /// 设置连续区域的边框：上下左右都为黑色连续边框
173         /// </summary>
174         /// <param name="CurSheet">Worksheet</param>
175         /// <param name="objStartCell">开始单元格</param>
176         /// <param name="objEndCell">结束单元格</param>
177         public void SetBorderAll(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
178         {
179             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
180             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
181 
182             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
183             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
184 
185             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
186             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
187 
188             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
189             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
190 
191             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
192             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
193 
194             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
195             CurSheet.get_Range(objStartCell, objEndCell).Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
196 
197         }
198 
199         /// <summary>
200         /// 设置连续区域水平居中
201         /// </summary>
202         /// <param name="CurSheet">Worksheet</param>
203         /// <param name="objStartCell">开始单元格</param>
204         /// <param name="objEndCell">结束单元格</param>
205         public void SetHAlignCenter(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
206         {
207             CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
208         }
209 
210         /// <summary>
211         /// 设置连续区域水平居左
212         /// </summary>
213         /// <param name="CurSheet">Worksheet</param>
214         /// <param name="objStartCell">开始单元格</param>
215         /// <param name="objEndCell">结束单元格</param>
216         public void SetHAlignLeft(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
217         {
218             CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
219         }
220 
221         /// <summary>
222         /// 设置连续区域水平居右
223         /// </summary>
224         /// <param name="CurSheet">Worksheet</param>
225         /// <param name="objStartCell">开始单元格</param>
226         /// <param name="objEndCell">结束单元格</param>
227         public void SetHAlignRight(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
228         {
229             CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
230         }
231 
232 
233         /// <summary>
234         /// 设置连续区域的显示格式
235         /// </summary>
236         /// <param name="CurSheet">Worksheet</param>
237         /// <param name="objStartCell">开始单元格</param>
238         /// <param name="objEndCell">结束单元格</param>
239         /// <param name="strNF">如"#,##0.00"的显示格式</param>
240         public void SetNumberFormat(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF)
241         {
242             CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
243         }
244 
245         /// <summary>
246         /// 设置列宽
247         /// </summary>
248         /// <param name="CurSheet">Worksheet</param>
249         /// <param name="strColID">列标识，如A代表第一列</param>
250         /// <param name="dblWidth">宽度</param>
251         public void SetColumnWidth(Excel._Worksheet CurSheet, string strColID, double dblWidth)
252         {
253             ((Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth;
254         }
255 
256         /// <summary>
257         /// 设置列宽
258         /// </summary>
259         /// <param name="CurSheet">Worksheet</param>
260         /// <param name="objStartCell">开始单元格</param>
261         /// <param name="objEndCell">结束单元格</param>
262         /// <param name="dblWidth">宽度</param>
263         public void SetColumnWidth(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth)
264         {
265             CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth;
266         }
267 
268 
269         /// <summary>
270         /// 设置行高
271         /// </summary>
272         /// <param name="CurSheet">Worksheet</param>
273         /// <param name="objStartCell">开始单元格</param>
274         /// <param name="objEndCell">结束单元格</param>
275         /// <param name="dblHeight">行高</param>
276         public void SetRowHeight(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight)
277         {
278             CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight;
279         }
280 
281 
282         /// <summary>
283         /// 为单元格添加超级链接
284         /// </summary>
285         /// <param name="CurSheet">Worksheet</param>
286         /// <param name="objCell">单元格</param>
287         /// <param name="strAddress">链接地址</param>
288         /// <param name="strTip">屏幕提示</param>
289         /// <param name="strText">链接文本</param>
290         public void AddHyperLink(Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText)
291         {
292             CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText);
293         }
294 
295         /// <summary>
296         /// 另存为xls文件
297         /// </summary>
298         /// <param name="CurBook">Workbook</param>
299         /// <param name="strFilePath">文件路径</param>
300         public void Save(Excel._Workbook CurBook, string strFilePath)
301         {
302             CurBook.SaveCopyAs(strFilePath);
303         }
304 
305         /// <summary>
306         /// 保存文件
307         /// </summary>
308         /// <param name="CurBook">Workbook</param>
309         /// <param name="strFilePath">文件路径</param>
310         public void SaveAs(Excel._Workbook CurBook, string strFilePath)
311         {
312             CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
313         }
314 
315         /// <summary>
316         /// 另存为html文件
317         /// </summary>
318         /// <param name="CurBook">Workbook</param>
319         /// <param name="strFilePath">文件路径</param>
320         public void SaveHtml(Excel._Workbook CurBook, string strFilePath)
321         {
322             CurBook.SaveAs(strFilePath, Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
323         }
324 
325 
326         /// <summary>
327         /// 释放内存
328         /// </summary>
329         public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
330         {
331             try
332             {
333                 System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
334                 CurSheet = null;
335                 CurBook.Close(false, mValue, mValue);
336                 System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
337                 CurBook = null;
338 
339                 CurExcel.Quit();
340                 System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
341                 CurExcel = null;
342 
343                 GC.Collect();
344                 GC.WaitForPendingFinalizers();
345             }
346             catch (System.Exception ex)
347             {
348                 HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
349             }
350             finally
351             {
352                 foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
353                     //if (pro.StartTime < DateTime.Now)
354                     pro.Kill();
355             }
356             System.GC.SuppressFinalize(this);
357 
358         }
359 
360 
361     }
362 }
刷新页面返回顶部
坐峰怀雪灬

公告

.NET的ExcelOperate
