ASP.NET
JavaScriptASP.NETPHP编程正则表达式AJAX相关ASP编程JSP编程编程10000问CSS/HTMLFlex脚本加解密web2.0XML/RSS网页编辑器相关技巧安全相关网页播放器DartVue.js

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

发布时间：2022-04-16 发布网站：脚本宝典
脚本宝典收集整理的这篇文章主要介绍了ASP.NET中 Execl导出的六种方法实例，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

复制代码 代码如下:

&nbsp;       /// <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();

            }

        }

您可能感兴趣的文章:

脚本宝典总结

以上是脚本宝典为你收集整理的ASP.NET中 Execl导出的六种方法实例全部内容，希望文章能够帮你解决ASP.NET中 Execl导出的六种方法实例所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：ASP.NET
猜你在找的ASP.NET相关文章
其他相关热搜词更多
phpjavapython程序员loadpost-format-gallerydivString参数开发ListcapMapClass工具安装SQL数据库资源this
全站导航更多
html5HTML/XhtmlCSSXML/XSLTDreamweaver教程Frontpage教程心得技巧JavaScriptASP.NETPHP编程正则表达式AJAX相关ASP编程JSP编程编程10000问CSS/HTMLFlexvbsDOS/BAThtahtcpythonperl游戏相关VBA远程脚本ColdFusionMsSqlMysqlmariadboracleDB2mssql2008mssql2005SQLitePostgreSQLMongoDB星外虚拟主机华众虚拟主机Linuxwin服务器FTP服务器DNS服务器Tomcatnginxzabbix云和虚拟化bios系统安装系统系统进程Windows系列LINUXRedHat/CentosUbuntu/DebianFedoraSolaris红旗Linux建站经验微信营销网站优化网站策划网络赚钱网络创业站长故事alexa域名photoshop教程摄影教程Fireworks教程CorelDraw教程Illustrator教程Painter教程Freehand教程IndesignSketch笔记本主板内存CPU存储显卡显示器光存储鼠标键盘平板电脑安全教程杀毒防毒安全设置病毒查杀脚本攻防入侵防御工具使用业界动态Exploit漏洞分析
最新ASP.NET教程
热门ASP.NET教程