欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

asp.net导出Excel类库代码分享

程序员文章站 2024-02-28 21:35:58
复制代码 代码如下:using system;using system.collections.generic;using system.reflection;using...

复制代码 代码如下:

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(),"");
        }
    }