您现在的位置是：网站首页> 编程资料编程资料

ASP.NET中 Execl导出的六种方法实例_实用技巧_

2023-05-24 349人已围观

简介 ASP.NET中 Execl导出的六种方法实例_实用技巧_

复制代码 代码如下:

        ///
        /// 导出Excel
        ///
        ///
        ///
        //方法一：
        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() + ",";

                                }

                       

上一篇：ASP.NET对HTML页面元素进行权限控制(一)_实用技巧_

下一篇：注册或者点击按钮时,怎么防止用户重复提交数据(实例讲解)_实用技巧_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网