asp.net导出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(),"");
        }
    }