当前位置:Gxlcms > asp.net > ASP.NET中 Execl导出的六种方法实例

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

时间:2021-07-01 10:21:17 帮助过:8人阅读

代码如下:
        /// <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;
            }
        }


     方法二:
代码如下:

  1. <br>        public void ImportExcel(Page page, DataSet ds)<br>        {<br><br>            try<br><br>            {<br><br>                string filename = Guid.NewGuid().ToString() + ".xls";<br><br>                string webFilePath = page.Server.MapPath("/" + filename);<br><br>                CreateExcelFile(webFilePath, ds);<br><br>                using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))<br><br>                {<br><br>                    //让用户输入下载的本地地址<br><br>                    page.Response.Clear();<br><br>                    page.Response.Buffer = true;<br><br>                    page.Response.Charset = "GB2312";<br><br> <br><br>                    //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");<br><br>                    page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);<br><br>                    page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");<br><br>                    page.Response.ContentType = "application/ms-excel";<br><br> <br><br>                    // 读取excel数据到内存<br><br>                    byte[] buffer = new byte[fs.Length - 1];<br><br>                    fs.Read(buffer, 0, (int)fs.Length - 1);<br><br> <br><br>                    // 写到aspx页面<br><br>                    page.Response.BinaryWrite(buffer);<br><br>                    page.Response.Flush();<br><br>                    //this.ApplicationInstance.CompleteRequest(); //停止页的执行<br><br> <br><br> <br><br>                    fs.Close();<br><br>                    fs.Dispose();<br><br> <br><br>                    //删除临时文件<br><br>                    File.Delete(webFilePath);<br><br>                }<br><br> <br><br>            }<br><br>            catch (Exception ex)<br><br>            {<br><br>                throw ex;<br><br>            }<br><br>        }<br><br>        方法三:<br><span><u></u></span> 代码如下:      <br>        public void ImportExcel(Page page, DataTable dt1, DataTable dt2, string conditions)<br><br>        {<br><br>            try<br><br>            {<br><br> <br><br>                string filename = Guid.NewGuid().ToString() + ".xls";<br><br>                string webFilePath = page.Server.MapPath("/" + filename);<br><br>                CreateExcelFile(webFilePath, dt1, dt2, conditions);<br><br>                using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))<br><br>                {<br><br>                    //让用户输入下载的本地地址<br><br>                    page.Response.Clear();<br><br>                    page.Response.Buffer = true;<br><br>                    page.Response.Charset = "GB2312";<br><br> <br><br>                    //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");<br><br>                    page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);<br><br>                    page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");<br><br>                    page.Response.ContentType = "application/ms-excel";<br><br> <br><br>                    // 读取excel数据到内存<br><br>                    byte[] buffer = new byte[fs.Length - 1];<br><br>                    fs.Read(buffer, 0, (int)fs.Length - 1);<br><br> <br><br>                    // 写到aspx页面<br><br>                    page.Response.BinaryWrite(buffer);<br><br>                    page.Response.Flush();<br><br>                    //this.ApplicationInstance.CompleteRequest(); //停止页的执行<br><br> <br><br> <br><br>                    fs.Close();<br><br>                    fs.Dispose();<br><br> <br><br>                    //删除临时文件<br><br>                    File.Delete(webFilePath);<br><br>                }<br><br> <br><br>            }<br><br>            catch (Exception ex)<br><br>            {<br><br>                throw ex;<br><br>            }<br><br>        }<br><br>        方法四:<br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>        private void CreateExcelFile(string filePath, DataTable dt)<br><br>        {<br><br>            if (File.Exists(filePath))<br><br>            {<br><br>                File.Delete(filePath);<br><br>            }<br><br>            OleDbConnection oleDbConn = new OleDbConnection();<br><br>            OleDbCommand oleDbCmd = new OleDbCommand();<br><br> <br><br>            try<br><br>            {<br><br>                string sSql = "";<br><br>                oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br>                oleDbConn.Open();<br><br>                oleDbCmd.CommandType = CommandType.Text;<br><br>                oleDbCmd.Connection = oleDbConn;<br><br>                //写列名<br><br>                sSql = "CREATE TABLE sheet1(";<br><br>                for (int i = 0; i < dt.Columns.Count; i++)<br><br>                {<br><br>                    if (i < dt.Columns.Count - 1)<br><br>                    {<br><br>                        if (dt.Columns[i].DataType.Name == "String")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Text,";<br><br>                        }<br><br>                        else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br>                        }<br><br>                        else<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br>                        }<br><br>                    }<br><br>                    else<br><br>                    {<br><br>                        if (dt.Columns[i].DataType.Name == "String")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br>                        }<br><br>                        else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br>                        }<br><br>                        else<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br>                        }<br><br>                    }<br><br>                }<br><br>                oleDbCmd.CommandText = sSql;<br><br>                oleDbCmd.ExecuteNonQuery();<br><br> <br><br>                for (int j = 0; j < dt.Rows.Count; j++)<br><br>                {<br><br>                    sSql = "INSERT INTO sheet1 VALUES(";<br><br>                    for (int i = 0; i < dt.Columns.Count; i++)<br><br>                    {<br><br>                        if (i < dt.Columns.Count - 1)<br><br>                        {<br><br>                            if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                            {<br><br>                                sSql += "NULL,";<br><br>                            }<br><br>                            else<br><br>                            {<br><br>                                if (dt.Columns[i].DataType.Name == "Decimal")<br><br>                                {<br><br>                                    sSql += dt.Rows[j][i].ToString() + ",";<br><br>                                }<br><br>                                else<br><br>                                {<br><br>                                    sSql += "'" + dt.Rows[j][i].ToString() + "',";<br><br>                                }<br><br>                            }<br><br>                        }<br><br>                        else<br><br>                            if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                            {<br><br>                                sSql += "NULL)";<br><br>                            }<br><br>                            else<br><br>                            {<br><br>                                if (dt.Columns[i].DataType.Name == "Decimal")<br><br>                                {<br><br>                                    sSql += dt.Rows[j][i].ToString() + ")";<br><br>                                }<br><br>                                else<br><br>                                {<br><br>                                    sSql += "'" + dt.Rows[j][i].ToString() + "')";<br><br>                                }<br><br>                            }<br><br>                    }<br><br>                    oleDbCmd.CommandText = sSql;<br><br>                    oleDbCmd.ExecuteNonQuery();<br><br>                }<br><br>            }<br><br>            catch (System.Exception ex)<br><br>            {<br><br>                throw ex;<br><br>            }<br><br>            finally<br><br>            {<br><br>                //断开连接<br><br>                oleDbCmd.Dispose();<br><br>                oleDbConn.Close();<br><br>                oleDbConn.Dispose();<br><br>            }<br><br>        }<br><br>        方法五:<br><span><u></u></span> 代码如下:       <br>        private void CreateExcelFile(string filePath, DataSet ds)<br><br>        {<br><br>            if (File.Exists(filePath))<br><br>            {<br><br>                File.Delete(filePath);<br><br>            }<br><br>            OleDbConnection oleDbConn = new OleDbConnection();<br><br>            OleDbCommand oleDbCmd = new OleDbCommand();<br><br> <br><br>            try<br><br>            {<br><br>                string sSql = "";<br><br>                oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br>                oleDbConn.Open();<br><br>                oleDbCmd.CommandType = CommandType.Text;<br><br>                oleDbCmd.Connection = oleDbConn;<br><br>                //写列名<br><br>                for(int k=0;k<ds.Tables.Count;k++)<br><br>                {<br><br>                    DataTable dt = ds.Tables[k];<br><br>                    sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "(";<br><br>                    for (int i = 0; i < dt.Columns.Count; i++)<br><br>                    {<br><br>                        if (i < dt.Columns.Count - 1)<br><br>                        {<br><br>                            if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid")<br><br>                            {<br><br>                                sSql += "["+dt.Columns[i].ColumnName + "] Text,";<br><br>                            }<br><br>                            else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                            {<br><br>                                sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br>                            }<br><br>                            else<br><br>                            {<br><br>                                sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br>                            }<br><br>                        }<br><br>                        else<br><br>                        {<br><br>                            if (dt.Columns[i].DataType.Name == "String")<br><br>                            {<br><br>                                sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br>                            }<br><br>                            else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                            {<br><br>                                sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br>                            }<br><br>                            else<br><br>                            {<br><br>                                sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br>                            }<br><br>                        }<br><br>                    }<br><br>                    oleDbCmd.CommandText = sSql;<br><br>                    oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++)<br><br>                    {<br><br>                        sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES(";<br><br>                        for (int i = 0; i < dt.Columns.Count; i++)<br><br>                        {<br><br>                            if (i < dt.Columns.Count - 1)<br><br>                            {<br><br>                                if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                                {<br><br>                                    sSql += "NULL,";<br><br>                                }<br><br>                                else<br><br>                                {<br><br>                                    if (dt.Columns[i].DataType.Name == "Decimal")<br><br>                                    {<br><br>                                        sSql += dt.Rows[j][i].ToString() + ",";<br><br>                                    }<br><br>                                    else<br><br>                                    {<br><br>                                        sSql += "'" + dt.Rows[j][i].ToString().Replace("'", "''") + "',";<br><br>                                    }<br><br>                                }<br><br>                            }<br><br>                            else<br><br>                                if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                                {<br><br>                                    sSql += "NULL)";<br><br>                                }<br><br>                                else<br><br>                                {<br><br>                                    if (dt.Columns[i].DataType.Name == "Decimal")<br><br>                                    {<br><br>                                        sSql += dt.Rows[j][i].ToString() + ")";<br><br>                                    }<br><br>                                    else<br><br>                                    {<br><br>                                        sSql += "'" + dt.Rows[j][i].ToString().Replace("'","''") + "')";<br><br>                                    }<br><br>                                }<br><br>                        }<br><br>                        oleDbCmd.CommandText = sSql;<br><br>                        oleDbCmd.ExecuteNonQuery();<br><br>                    }<br><br>                }<br><br>            }<br><br>            catch (System.Exception ex)<br><br>            {<br><br>                throw ex;<br><br>            }<br><br>            finally<br><br>            {<br><br>                //断开连接<br><br>                oleDbCmd.Dispose();<br><br>                oleDbConn.Close();<br><br>                oleDbConn.Dispose();<br><br>            }<br><br>        }<br><br>        方法六:<br><span><u></u></span> 代码如下:       <br>        private void CreateExcelFile(string filePath, DataTable dt1,DataTable dt2,string conditions)<br><br>        {<br><br>            if (File.Exists(filePath))<br><br>            {<br><br>                File.Delete(filePath);<br><br>            }<br><br>            OleDbConnection oleDbConn = new OleDbConnection();<br><br>            OleDbCommand oleDbCmd = new OleDbCommand();<p></p></li><li><p>            try<br><br>            {<br><br>                string sSql = "";<br><br>                oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";<br><br>                oleDbConn.Open();<br><br>                oleDbCmd.CommandType = CommandType.Text;<br><br>                oleDbCmd.Connection = oleDbConn;<br><br>                //写列名<br><br>                sSql = "CREATE TABLE sheet1(";<br><br>                DataTable dt = dt1.Copy();<br><br>                dt.Columns.Remove("MGUID");<br><br>                for (int i = 0; i < dt.Columns.Count; i++)<br><br>                {<br><br>                    if (i < dt.Columns.Count - 1)<br><br>                    {<br><br>                        if (dt.Columns[i].DataType.Name == "String")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Text,";<br><br>                        }<br><br>                        else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,";<br><br>                        }<br><br>                        else<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,";<br><br>                        }<br><br>                    }<br><br>                    else<br><br>                    {<br><br>                        if (dt.Columns[i].DataType.Name == "String")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Text)";<br><br>                        }<br><br>                        else if (dt.Columns[i].DataType.Name == "DateTime")<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)";<br><br>                        }<br><br>                        else<br><br>                        {<br><br>                            sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)";<br><br>                        }<br><br>                    }<br><br>                }<br><br>                oleDbCmd.CommandText = sSql;<br><br>                oleDbCmd.ExecuteNonQuery();<br><br>                DataView dv = new DataView();<br><br>                dv.Table = dt;<br><br>                DataView dv1 = new DataView();<br><br>                dv1.Table = dt1;<br><br>                if (conditions != "")<br><br>                {<br><br>                    dv.RowFilter = conditions;<br><br>                    dv1.RowFilter = conditions;<br><br>                }<br><br>                dt = dv.ToTable();<br><br>                dt1 = dv1.ToTable();<br><br>                string MGUIDs = "";<br><br>                for (int j = 0; j < dt.Rows.Count; j++)<br><br>                {<br><br>                    MGUIDs += ",'" + dt1.Rows[j]["MGUID"].ToString() + "'";<br><br>                    sSql = "INSERT INTO sheet1 VALUES(";<br><br>                    for (int i = 0; i < dt.Columns.Count; i++)<br><br>                    {<br><br>                        if (i < dt.Columns.Count - 1)<br><br>                        {<br><br>                            if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                            {<br><br>                                sSql += "NULL,";<br><br>                            }<br><br>                            else<br><br>                            {<br><br>                                if (dt.Columns[i].DataType.Name == "Decimal")<br><br>                                {<br><br>                                    sSql += dt.Rows[j][i].ToString() + ",";<br><br>                                }<br><br>                                else<br><br>                                {<br><br>                                    sSql += "'" + dt.Rows[j][i].ToString() + "',";<br><br>                                }<br><br>                            }<br><br>                        }<br><br>                        else<br><br>                            if (DBNull.Value.Equals(dt.Rows[j][i]))<br><br>                            {<br><br>                                sSql += "NULL)";<br><br>                            }<br><br>              & </p></li></ol></pre>

人气教程排行