.net 操作Excel的类

 using System;using System.Collections.Generic;using System.Text;using System.Reflection;using System.Runtime.InteropServices;using System.Drawing;using Microsoft.Office.Interop.Excel;using System.IO;using System.Web;/* desc:处理ms excel的类(注：执行效率有待改进) zhangl author:BOSSMA 博友 url:http://blog.bossma.cn/archives/2009/02/257 */public class MSExcel{ #region field private object miss = Missing.Value; //忽略的参数OLENULL private Microsoft.Office.Interop.Excel.Application m_objExcel;//Excel应用程序实例 private Microsoft.Office.Interop.Excel.Workbooks m_objBooks;//工作表集合 private Microsoft.Office.Interop.Excel.Workbook m_objBook;//当前操作的工作表 private Microsoft.Office.Interop.Excel.Worksheet sheet;//当前操作的表格 #endregion #region property public Microsoft.Office.Interop.Excel.Worksheet CurrentSheet { get { return sheet; } set { this.sheet = value; } } public Microsoft.Office.Interop.Excel.Workbooks CurrentWorkBooks { get { return this.m_objBooks; } set { this.m_objBooks = value; } } public Microsoft.Office.Interop.Excel.Workbook CurrentWorkBook { get { return this.m_objBook; } set { this.m_objBook = value; } } #endregion #region construct /// <summary> /// 构建ExcelClass类 /// </summary> public MSExcel() { //别忘了需要添加Excel Library的引用 this.m_objExcel = new Microsoft.Office.Interop.Excel.Application(); } /// <summary> /// 构建ExcelClass类 /// </summary> /// <param name="objExcel">Excel.Application</param> public MSExcel(Microsoft.Office.Interop.Excel.Application objExcel) { this.m_objExcel = objExcel; } #endregion /// <summary> /// 列标号，Excel最大列数是256 /// </summary> private string[] ALists = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ", "FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK", "FL", "FM", "FN", "FO", "FP", "FQ", "FR", "FS", "FT", "FU", "FV", "FW", "FX", "FY", "FZ", "GA", "GB", "GC", "GD", "GE", "GF", "GG", "GH", "GI", "GJ", "GK", "GL", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", "GU", "GV", "GW", "GX", "GY", "GZ", "HA", "HB", "HC", "HD", "HE", "HF", "HG", "HH", "HI", "HJ", "HK", "HL", "HM", "HN", "HO", "HP", "HQ", "HR", "HS", "HT", "HU", "HV", "HW", "HX", "HY", "HZ", "IA", "IB", "IC", "ID", "IE", "IF", "IG", "IH", "II", "IJ", "IK", "IL", "IM", "IN", "IO", "IP", "IQ", "IR", "IS", "IT", "IU", "IV" }; /// <summary> /// 获取描述区域的字符 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <returns></returns> public string GetAix(int x, int y) { if (x > 256) { return ""; } string s = ""; s = s + ALists[x - 1].ToString(); s = s + y.ToString(); return s; } /// <summary> /// 给单元格赋值1 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="align">对齐（CENTER、LEFT、RIGHT）</param> /// <param name="text">值</param> public void setValue(int y, int x, string align, string text) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); if (align.ToUpper() == "CENTER") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } if (align.ToUpper() == "LEFT") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } if (align.ToUpper() == "RIGHT") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } } /// <summary> /// 给单元格赋值2 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="text">值</param> public void setValue(int y, int x, string text) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); range.EntireColumn.AutoFit(); //设置单元格自适应宽度 //设置单元格边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); } /// <summary> /// 给单元格赋值3 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="text">值</param> /// <param name="font">字符格式</param> /// <param name="color">颜色</param> public void setValue(int y, int x, string text, System.Drawing.Font font, int color) { this.setValue(x, y, text); Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.Font.Size = font.Size; range.Font.Bold = font.Bold; //这里是int型的颜色 range.Font.Color = ColorTranslator.ToOle(ColorTranslator.FromWin32(color)); range.Font.Name = font.Name; range.Font.Italic = font.Italic; range.Font.Underline = font.Underline; } /// <summary> /// 给单元格赋值3 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="text">值</param> /// <param name="font">字符格式</param> /// <param name="color">颜色</param> public void setValue(int y, int x, string text, System.Drawing.Font font, int color, string align) { this.setValue(x, y, text); Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.Font.Size = font.Size; range.Font.Bold = font.Bold; //这里是int型的颜色 range.Font.Color = ColorTranslator.ToOle(ColorTranslator.FromWin32(color)); range.Font.Name = font.Name; range.Font.Italic = font.Italic; range.Font.Underline = font.Underline; if (align.ToUpper() == "CENTER") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } if (align.ToUpper() == "LEFT") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } if (align.ToUpper() == "RIGHT") { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } } /// <summary> /// 插入新行 /// </summary> /// <param name="y">模板行号</param> public void insertRow(int y) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(255, y)); range.Copy(miss); range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss); range.get_Range(GetAix(1, y), GetAix(255, y)); range.Select(); sheet.Paste(miss, miss); } /// <summary> /// 把剪切内容粘贴到当前区域 /// </summary> public void paste() { string s = "a,b,c,d,e,f,g"; sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s); } /// <summary> /// 设置边框 /// </summary> /// <param name="x1"></param> /// <param name="y1"></param> /// <param name="x2"></param> /// <param name="y2"></param> /// <param name="Width"></param> public void setBorder(int x1, int y1, int x2, int y2, int Width) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Borders.Weight = Width; } public void mergeCell(int x1, int y1, int x2, int y2) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Merge(true); } public Microsoft.Office.Interop.Excel.Range getRange(int x1, int y1, int x2, int y2) { Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); return range; } /// <summary> /// 打开Excel文件 /// </summary> /// <param name="filename">路径</param> public void OpenExcelFile(string filename) { UserControl(false); m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBook = m_objExcel.ActiveWorkbook; sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet; } public void UserControl(bool usercontrol) { if (m_objExcel == null) { return; } m_objExcel.UserControl = usercontrol; m_objExcel.DisplayAlerts = usercontrol; m_objExcel.Visible = usercontrol; } public void CreateExceFile() { UserControl(false); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss)); sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet; } public void SaveAs(string FileName) { string path = System.IO.Path.GetTempPath() + FileName; //如果存在先删除 if (File.Exists(path)) File.Delete(path); //重新生成 m_objBook.SaveAs(path, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); //m_objBook.Close(false, miss, miss); //释放所有资源 zhangl ReleaseExcel(); //zhangl增加,以"流"的方式输出 if (File.Exists(path)) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "gb2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); //Response.AddHeader("Content-Length", new FileInfo(filename).Length); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.WriteFile(path); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.Close(); } } public void ReleaseExcel() { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet); sheet = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(0); } #region KillAllExcel public bool KillAllExcel() { try { if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag. { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); //释放COM组件，其实就是将其引用计数减1 //System.Diagnostics.Process theProc; foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { //先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了， //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它:p if (theProc.CloseMainWindow() == false) { theProc.Kill(); } } m_objExcel = null; return true; } } catch { return false; } return true; } #endregion #region Kill Special Excel Process [DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); //推荐这个方法，找了很久，不容易啊 public void KillSpecialExcel() { try { if (m_objExcel != null) { int lpdwProcessId; GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } } catch (Exception ex) { Console.WriteLine("Delete Excel Process Error:" + ex.Message); } } #endregion}//end class/****导出Excel测试例子 zhangl*****/class test{ static void Main() { MSExcel mexc = new MSExcel(); mexc.CreateExceFile(); mexc.insertRow(1);//注意从1开始的 int col = (256 * 256 * 255) + (256 * 192) + 192;//颜色 mexc.setValue(1, 1, "张三", new System.Drawing.Font("Arial", 18), col, "center");//设置单元格内容和样式 mexc.mergeCell(1, 1, 4, 1);//合并单元格 for (int i = 2; i <= 101; i++) { mexc.insertRow(i);//插入行 for (int j = 1; j <= 4; j++)//注意从1开始的 { mexc.setValue(i, j, i + "--" + j);//设置单元格内容 } } mexc.SaveAs("demo.xls");//保存文件的文件名 mexc.KillSpecialExcel();//关闭创建的Excel进程 }}