using System; using System.Collections.Generic; using System.Reflection; using System.Web; using Excel = Microsoft.Office.Interop.Excel; /// <summary> ///ExcelClass 的摘要说明 /// </summary> public class ExcelClass {     /// <summary>     /// 构建ExcelClass类     /// </summary>     public ExcelClass()     {         this.m_objExcel = new Excel.Application();     }     /// <summary>     /// 构建ExcelClass类     /// </summary>     /// <param name="objExcel">Excel.Application</param>     public ExcelClass(Excel.Application objExcel)     {         this.m_objExcel = objExcel;     }     /// <summary>     /// 列标号     /// </summary>     private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";     /// <summary>     /// 获取描述区域的字符     /// </summary>     /// <param name="x"></param>     /// <param name="y"></param>     /// <returns></returns>     public string GetAix(int x, int y)     {         char[] AChars = AList.ToCharArray();         if (x >= 26) { return ""; }         string s = "";         s = s + AChars[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)     {         Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);         range.set_Value(miss, text);         if (align.ToUpper() == "CENTER")         {             range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;         }         if (align.ToUpper() == "LEFT")         {             range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;         }         if (align.ToUpper() == "RIGHT")         {             range.HorizontalAlignment = 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)     {         Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);         range.set_Value(miss, text);     }     /// <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, System.Drawing.Color color)     {         this.setValue(x, y, text);         Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);         range.Font.Size = font.Size;         range.Font.Bold = font.Bold;         range.Font.Color = color;         range.Font.Name = font.Name;         range.Font.Italic = font.Italic;         range.Font.Underline = font.Underline;     }     /// <summary>     /// 插入新行     /// </summary>     /// <param name="y">模板行号</param>     public void insertRow(int y)     {         Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));         range.Copy(miss);         range.Insert(Excel.XlDirection.xlDown, miss);         range.get_Range(GetAix(1, y), GetAix(25, y));         range.Select();         sheet.Paste(miss, miss);     }     /// <summary>     /// 把剪切内容粘贴到当前区域     /// </summary>     public void past()     {         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)     {         Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), miss);         ((Excel.Range)range.Cells[x1, y1]).ColumnWidth = Width;     }     public void mergeCell(int x1, int y1, int x2, int y2)     {         Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));         range.Merge(true);     }     public Excel.Range getRange(int x1, int y1, int x2, int y2)     {         Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));         return range;     }     private object miss = Missing.Value; //忽略的参数OLENULL     private Excel.Application m_objExcel;//Excel应用程序实例     private Excel.Workbooks m_objBooks;//工作表集合     private Excel.Workbook m_objBook;//当前操作的工作表     private Excel.Worksheet sheet;//当前操作的表格     public Excel.Worksheet CurrentSheet     {         get         {             return sheet;         }         set         {             this.sheet = value;         }     }     public Excel.Workbooks CurrentWorkBooks     {         get         {             return this.m_objBooks;         }         set         {             this.m_objBooks = value;         }     }     public Excel.Workbook CurrentWorkBook     {         get         {             return this.m_objBook;         }         set         {             this.m_objBook = value;         }     }     /// <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 = (Excel.Workbooks)m_objExcel.Workbooks;         m_objBook = m_objExcel.ActiveWorkbook;         sheet = (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 = (Excel.Workbooks)m_objExcel.Workbooks;         m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));         sheet = (Excel.Worksheet)m_objBook.ActiveSheet;     }     public void SaveAs(string FileName)     {          m_objBook.SaveAs(FileName, miss, miss, miss, miss,          miss, Excel.XlSaveAsAccessMode.xlNoChange,          Excel.XlSaveConflictResolution.xlLocalSessionChanges,          miss, miss, miss, miss);         //m_objBook.Close(false, miss, miss);     }     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);         m_objExcel = null;         m_objBooks = null;         m_objBook = null;         sheet = null;         GC.Collect();     }     public bool KillAllExcelApp()     {         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;     } }    /// <summary>     /// 点击打印按钮事件     /// </summary>     /// <param name="sender"></param>     /// <param name="e"></param>     protected void Sendbu_Click(object sender, EventArgs e)     {         try         {                      //查找部门分类用户             DataTable Duser = EduOA.DBUtility.DbHelperSQL.Query("select count(*) as count,d.Id as DId FROM OA_User u,OA_Department d where u.DepartmentID=d.Id  group by d.Id").Tables[0];             ExcelClass Ec = new ExcelClass();//创建Excel操作类对象             int Ycount = 1;             Ec.CreateExceFile();//创建Excel文件             Ec.setValue(Ycount, 1, "CENTER", "组织部门");             Ec.setValue(Ycount, 2, "CENTER", "姓名");             Ec.setValue(Ycount, 3, "CENTER", "性别");             Ec.setValue(Ycount, 4, "CENTER", "职位");             Ec.setValue(Ycount, 5, "CENTER", "移动电话");             Ec.setValue(Ycount, 6, "CENTER", "电话");             Ec.setValue(Ycount, 7, "CENTER", "电子邮箱");             Ec.setBorder(1, 1, 1, 1, 50);             Ec.setBorder(1, 2, 2, 2, 20);             Ec.setBorder(1, 5, 5, 5, 20);             Ec.setBorder(1, 6, 6, 6, 20);             Ec.setBorder(1, 7, 7, 7, 20);             for (int i = 0; i < Duser.Rows.Count; i++)             {                 Ycount += 1;                 Ec.setValue(Ycount, 1, "CENTER", Common.DeleteHtml(Getdept(Duser.Rows[i]["count"], Duser.Rows[i]["DId"])));                 DataTable dtuser = GetData(Duser.Rows[i]["DId"]);                 for (int k = 0; k < dtuser.Rows.Count; k++)                 {                     Ec.setValue(Ycount, 2, "CENTER", dtuser.Rows[k]["TrueName"].ToString());                     Ec.setValue(Ycount, 3, "CENTER", dtuser.Rows[k]["sex"].ToString());                     Ec.setValue(Ycount, 4, "CENTER", dtuser.Rows[k]["PositionId"].ToString());                     Ec.setValue(Ycount, 5, "CENTER", dtuser.Rows[k]["Telephone"].ToString());                     Ec.setValue(Ycount, 6, "CENTER", dtuser.Rows[k]["Mobile"].ToString());                     Ec.setValue(Ycount, 7, "CENTER", dtuser.Rows[k]["Email"].ToString());                     Ycount += 1;                 }             }             string path = Server.MapPath("Contactfiles\\");             Ec.SaveAs(path+"通讯录.xlsx");             //*******释放Excel资源***********             Ec.ReleaseExcel();             Response.Redirect("Contactfiles/通讯录.xlsx");                    }         catch (Exception ex)         {             PageError("导出出错！"+ex.ToString(),"");         }     }