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

ASP.NET中 Execl导出的六种方法实例

程序员文章站 2024-02-26 14:35:46
复制代码 代码如下:        ///   &n...

复制代码 代码如下:

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="page"></param>
        /// <param name="dt"></param>
        //方法一:
        public void importexcel(page page, datatable dt)
        {
            try
            {

                string filename = guid.newguid().tostring() + ".xls";
                string webfilepath = page.server.mappath("/" + filename);
                createexcelfile(webfilepath, dt);
                using (filestream fs = new filestream(webfilepath, filemode.openorcreate))
                {
                    //让用户输入下载的本地地址
                    page.response.clear();
                    page.response.buffer = true;
                    page.response.charset = "gb2312";

                    //page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");
                    page.response.appendheader("content-disposition", "attachment;filename=" + filename);
                    page.response.contentencoding = system.text.encoding.getencoding("gb2312");
                    page.response.contenttype = "application/ms-excel";

                    // 读取excel数据到内存
                    byte[] buffer = new byte[fs.length - 1];
                    fs.read(buffer, 0, (int)fs.length - 1);

                    // 写到aspx页面
                    page.response.binarywrite(buffer);
                    page.response.flush();
                    //this.applicationinstance.completerequest(); //停止页的执行

 
                    fs.close();
                    fs.dispose();

                    //删除临时文件
                    file.delete(webfilepath);
                }

            }
            catch (exception ex)
            {
                throw ex;
            }
        }


     方法二:
复制代码 代码如下:

        public void importexcel(page page, dataset ds)
        {

            try

            {

                string filename = guid.newguid().tostring() + ".xls";

                string webfilepath = page.server.mappath("/" + filename);

                createexcelfile(webfilepath, ds);

                using (filestream fs = new filestream(webfilepath, filemode.openorcreate))

                {

                    //让用户输入下载的本地地址

                    page.response.clear();

                    page.response.buffer = true;

                    page.response.charset = "gb2312";

 

                    //page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");

                    page.response.appendheader("content-disposition", "attachment;filename=" + filename);

                    page.response.contentencoding = system.text.encoding.getencoding("gb2312");

                    page.response.contenttype = "application/ms-excel";

 

                    // 读取excel数据到内存

                    byte[] buffer = new byte[fs.length - 1];

                    fs.read(buffer, 0, (int)fs.length - 1);

 

                    // 写到aspx页面

                    page.response.binarywrite(buffer);

                    page.response.flush();

                    //this.applicationinstance.completerequest(); //停止页的执行

 

 

                    fs.close();

                    fs.dispose();

 

                    //删除临时文件

                    file.delete(webfilepath);

                }

 

            }

            catch (exception ex)

            {

                throw ex;

            }

        }

        方法三:
复制代码 代码如下:
     
        public void importexcel(page page, datatable dt1, datatable dt2, string conditions)

        {

            try

            {

 

                string filename = guid.newguid().tostring() + ".xls";

                string webfilepath = page.server.mappath("/" + filename);

                createexcelfile(webfilepath, dt1, dt2, conditions);

                using (filestream fs = new filestream(webfilepath, filemode.openorcreate))

                {

                    //让用户输入下载的本地地址

                    page.response.clear();

                    page.response.buffer = true;

                    page.response.charset = "gb2312";

 

                    //page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");

                    page.response.appendheader("content-disposition", "attachment;filename=" + filename);

                    page.response.contentencoding = system.text.encoding.getencoding("gb2312");

                    page.response.contenttype = "application/ms-excel";

 

                    // 读取excel数据到内存

                    byte[] buffer = new byte[fs.length - 1];

                    fs.read(buffer, 0, (int)fs.length - 1);

 

                    // 写到aspx页面

                    page.response.binarywrite(buffer);

                    page.response.flush();

                    //this.applicationinstance.completerequest(); //停止页的执行

 

 

                    fs.close();

                    fs.dispose();

 

                    //删除临时文件

                    file.delete(webfilepath);

                }

 

            }

            catch (exception ex)

            {

                throw ex;

            }

        }

        方法四:
复制代码 代码如下:

        private void createexcelfile(string filepath, datatable dt)

        {

            if (file.exists(filepath))

            {

                file.delete(filepath);

            }

            oledbconnection oledbconn = new oledbconnection();

            oledbcommand oledbcmd = new oledbcommand();

 

            try

            {

                string ssql = "";

                oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";

                oledbconn.open();

                oledbcmd.commandtype = commandtype.text;

                oledbcmd.connection = oledbconn;

                //写列名

                ssql = "create table sheet1(";

                for (int i = 0; i < dt.columns.count; i++)

                {

                    if (i < dt.columns.count - 1)

                    {

                        if (dt.columns[i].datatype.name == "string")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] text,";

                        }

                        else if (dt.columns[i].datatype.name == "datetime")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] datetime,";

                        }

                        else

                        {

                            ssql += "[" + dt.columns[i].columnname + "] decimal,";

                        }

                    }

                    else

                    {

                        if (dt.columns[i].datatype.name == "string")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] text)";

                        }

                        else if (dt.columns[i].datatype.name == "datetime")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] datetime)";

                        }

                        else

                        {

                            ssql += "[" + dt.columns[i].columnname + "] decimal)";

                        }

                    }

                }

                oledbcmd.commandtext = ssql;

                oledbcmd.executenonquery();

 

                for (int j = 0; j < dt.rows.count; j++)

                {

                    ssql = "insert into sheet1 values(";

                    for (int i = 0; i < dt.columns.count; i++)

                    {

                        if (i < dt.columns.count - 1)

                        {

                            if (dbnull.value.equals(dt.rows[j][i]))

                            {

                                ssql += "null,";

                            }

                            else

                            {

                                if (dt.columns[i].datatype.name == "decimal")

                                {

                                    ssql += dt.rows[j][i].tostring() + ",";

                                }

                                else

                                {

                                    ssql += "'" + dt.rows[j][i].tostring() + "',";

                                }

                            }

                        }

                        else

                            if (dbnull.value.equals(dt.rows[j][i]))

                            {

                                ssql += "null)";

                            }

                            else

                            {

                                if (dt.columns[i].datatype.name == "decimal")

                                {

                                    ssql += dt.rows[j][i].tostring() + ")";

                                }

                                else

                                {

                                    ssql += "'" + dt.rows[j][i].tostring() + "')";

                                }

                            }

                    }

                    oledbcmd.commandtext = ssql;

                    oledbcmd.executenonquery();

                }

            }

            catch (system.exception ex)

            {

                throw ex;

            }

            finally

            {

                //断开连接

                oledbcmd.dispose();

                oledbconn.close();

                oledbconn.dispose();

            }

        }

        方法五:
复制代码 代码如下:
      
        private void createexcelfile(string filepath, dataset ds)

        {

            if (file.exists(filepath))

            {

                file.delete(filepath);

            }

            oledbconnection oledbconn = new oledbconnection();

            oledbcommand oledbcmd = new oledbcommand();

 

            try

            {

                string ssql = "";

                oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";

                oledbconn.open();

                oledbcmd.commandtype = commandtype.text;

                oledbcmd.connection = oledbconn;

                //写列名

                for(int k=0;k<ds.tables.count;k++)

                {

                    datatable dt = ds.tables[k];

                    ssql = "create table sheet" + (k + 1).tostring() + "(";

                    for (int i = 0; i < dt.columns.count; i++)

                    {

                        if (i < dt.columns.count - 1)

                        {

                            if (dt.columns[i].datatype.name == "string" || dt.columns[i].datatype.name=="guid")

                            {

                                ssql += "["+dt.columns[i].columnname + "] text,";

                            }

                            else if (dt.columns[i].datatype.name == "datetime")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] datetime,";

                            }

                            else

                            {

                                ssql += "[" + dt.columns[i].columnname + "] decimal,";

                            }

                        }

                        else

                        {

                            if (dt.columns[i].datatype.name == "string")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] text)";

                            }

                            else if (dt.columns[i].datatype.name == "datetime")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] datetime)";

                            }

                            else

                            {

                                ssql += "[" + dt.columns[i].columnname + "] decimal)";

                            }

                        }

                    }

                    oledbcmd.commandtext = ssql;

                    oledbcmd.executenonquery(); for (int j = 0; j < dt.rows.count; j++)

                    {

                        ssql = "insert into sheet" + (k + 1).tostring() + " values(";

                        for (int i = 0; i < dt.columns.count; i++)

                        {

                            if (i < dt.columns.count - 1)

                            {

                                if (dbnull.value.equals(dt.rows[j][i]))

                                {

                                    ssql += "null,";

                                }

                                else

                                {

                                    if (dt.columns[i].datatype.name == "decimal")

                                    {

                                        ssql += dt.rows[j][i].tostring() + ",";

                                    }

                                    else

                                    {

                                        ssql += "'" + dt.rows[j][i].tostring().replace("'", "''") + "',";

                                    }

                                }

                            }

                            else

                                if (dbnull.value.equals(dt.rows[j][i]))

                                {

                                    ssql += "null)";

                                }

                                else

                                {

                                    if (dt.columns[i].datatype.name == "decimal")

                                    {

                                        ssql += dt.rows[j][i].tostring() + ")";

                                    }

                                    else

                                    {

                                        ssql += "'" + dt.rows[j][i].tostring().replace("'","''") + "')";

                                    }

                                }

                        }

                        oledbcmd.commandtext = ssql;

                        oledbcmd.executenonquery();

                    }

                }

            }

            catch (system.exception ex)

            {

                throw ex;

            }

            finally

            {

                //断开连接

                oledbcmd.dispose();

                oledbconn.close();

                oledbconn.dispose();

            }

        }

        方法六:
复制代码 代码如下:
      
        private void createexcelfile(string filepath, datatable dt1,datatable dt2,string conditions)

        {

            if (file.exists(filepath))

            {

                file.delete(filepath);

            }

            oledbconnection oledbconn = new oledbconnection();

            oledbcommand oledbcmd = new oledbcommand();

            try

            {

                string ssql = "";

                oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";

                oledbconn.open();

                oledbcmd.commandtype = commandtype.text;

                oledbcmd.connection = oledbconn;

                //写列名

                ssql = "create table sheet1(";

                datatable dt = dt1.copy();

                dt.columns.remove("mguid");

                for (int i = 0; i < dt.columns.count; i++)

                {

                    if (i < dt.columns.count - 1)

                    {

                        if (dt.columns[i].datatype.name == "string")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] text,";

                        }

                        else if (dt.columns[i].datatype.name == "datetime")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] datetime,";

                        }

                        else

                        {

                            ssql += "[" + dt.columns[i].columnname + "] decimal,";

                        }

                    }

                    else

                    {

                        if (dt.columns[i].datatype.name == "string")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] text)";

                        }

                        else if (dt.columns[i].datatype.name == "datetime")

                        {

                            ssql += "[" + dt.columns[i].columnname + "] datetime)";

                        }

                        else

                        {

                            ssql += "[" + dt.columns[i].columnname + "] decimal)";

                        }

                    }

                }

                oledbcmd.commandtext = ssql;

                oledbcmd.executenonquery();

                dataview dv = new dataview();

                dv.table = dt;

                dataview dv1 = new dataview();

                dv1.table = dt1;

                if (conditions != "")

                {

                    dv.rowfilter = conditions;

                    dv1.rowfilter = conditions;

                }

                dt = dv.totable();

                dt1 = dv1.totable();

                string mguids = "";

                for (int j = 0; j < dt.rows.count; j++)

                {

                    mguids += ",'" + dt1.rows[j]["mguid"].tostring() + "'";

                    ssql = "insert into sheet1 values(";

                    for (int i = 0; i < dt.columns.count; i++)

                    {

                        if (i < dt.columns.count - 1)

                        {

                            if (dbnull.value.equals(dt.rows[j][i]))

                            {

                                ssql += "null,";

                            }

                            else

                            {

                                if (dt.columns[i].datatype.name == "decimal")

                                {

                                    ssql += dt.rows[j][i].tostring() + ",";

                                }

                                else

                                {

                                    ssql += "'" + dt.rows[j][i].tostring() + "',";

                                }

                            }

                        }

                        else

                            if (dbnull.value.equals(dt.rows[j][i]))

                            {

                                ssql += "null)";

                            }

                            else

                            {

                                if (dt.columns[i].datatype.name == "decimal")

                                {

                                    ssql += dt.rows[j][i].tostring() + ")";

                                }

                                else

                                {

                                    ssql += "'" + dt.rows[j][i].tostring() + "')";

                                }

                            }

                    }

                    oledbcmd.commandtext = ssql;

                    oledbcmd.executenonquery();

                }

                if (dt2 != null)

                {

                    ssql = "create table sheet21(";

                    dt = dt2.copy();

                    dt.columns.remove("mguid");

                    dt.columns.remove("dguid");

                    for (int i = 0; i < dt.columns.count; i++)

                    {

                        if (i < dt.columns.count - 1)

                        {

                            if (dt.columns[i].datatype.name == "string")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] text,";

                            }

                            else if (dt.columns[i].datatype.name == "datetime")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] datetime,";

                            }

                            else

                            {

                                ssql += "[" + dt.columns[i].columnname + "] decimal,";

                            }

                        }

                        else

                        {

                            if (dt.columns[i].datatype.name == "string")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] text)";

                            }

                            else if (dt.columns[i].datatype.name == "datetime")

                            {

                                ssql += "[" + dt.columns[i].columnname + "] datetime)";

                            }

                            else

                            {

                                ssql += "[" + dt.columns[i].columnname + "] decimal)";

                            }

                        }

                    }

                    oledbcmd.commandtext = ssql;

                    oledbcmd.executenonquery();

                    dv = new dataview();

                    dv.table = dt2;                   

                    if (mguids != "")

                    {

                        dv.rowfilter = "mguid in(" + mguids.substring(1) + ")";

                    }

                    dt = dv.totable();

                    for (int j = 0; j < dt.rows.count; j++)

                    {

                        ssql = "insert into sheet1 values(";

                        for (int i = 0; i < dt.columns.count; i++)

                        {

                            if (i < dt.columns.count - 1)

                            {

                                if (dbnull.value.equals(dt.rows[j][i]))

                                {

                                    ssql += "null,";

                                }

                                else

                                {

                                    if (dt.columns[i].datatype.name == "decimal")

                                    {

                                        ssql += dt.rows[j][i].tostring() + ",";

                                    }

                                    else

                                    {

                                        ssql += "'" + dt.rows[j][i].tostring() + "',";

                                    }

                                }

                            }

                            else

                                if (dbnull.value.equals(dt.rows[j][i]))

                                {

                                    ssql += "null)";

                                }

                                else

                                {

                                    if (dt.columns[i].datatype.name == "decimal")

                                    {

                                        ssql += dt.rows[j][i].tostring() + ")";

                                    }

                                    else

                                    {

                                        ssql += "'" + dt.rows[j][i].tostring() + "')";

                                    }

                                }

                        }

                        oledbcmd.commandtext = ssql;

                        oledbcmd.executenonquery();

                    }

 

                }

            }

            catch (system.exception ex)

            {

                throw ex;

            }

            finally

            {

                //断开连接

                oledbcmd.dispose();

                oledbconn.close();

                oledbconn.dispose();

            }

        }