请问如何把gridview的数据导出到excal谢谢

[redfalson]

请问如何把gridview的数据导出到excal谢谢

[threenewbee]

GridView导出为Excel对于很多人来说都是比较常用的方法，而且网络上也有一些比较多的方法，但是这些方法多数都是需要在服务器端安装excel或相关的.dll。可是我们最近做的程序，服务器上居然不让安装，那就有点郁闷了，最后花了点时间终于找到了可用的方法，现在和大家分享一下。

实现的基本思想是通过ODBC中的Microsofe Excel Driver先将GridView中的数据读取出来生成一个.xls的临时文件在服务器端，然后再将该文件的内容读取到换从中，接着用Response的方法提示用户下载到本机的指定位置上。

以下是实现的具体代码，供参看（测试通过的代码）：

view plaincopy to clipboardprint?
/// <summary>
/// 在磁盘上创建一个临时的excel文件
/// </summary>
private void CreateExcelFile(string filePath)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}

OleDbConnection oleDbConn = new OleDbConnection();
OleDbCommand oleDbCmd = new OleDbCommand();
string sSql = "";

try
{
//设置导出所有数据
gvShowInfo.AllowPaging = false; //清除分页
gvShowInfo.AllowSorting = false; //清除排序
this.gvShowInfo.DataBind();

//打开Microsoft.Jet.OleDb.4.0连接
oleDbConn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.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 < this.gvShowInfo.Columns.Count; i++)
{
if (i < this.gvShowInfo.Columns.Count - 1)
{
sSql += this.gvShowInfo.Columns[i].HeaderText + " Text(100),";
}
else
{
sSql += this.gvShowInfo.Columns[i].HeaderText + " Text(200))";
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();

//写行数据
for (int j = 0; j < this.gvShowInfo.Rows.Count; j++)
{
sSql = "INSERT INTO sheet1 VALUES('";
for (int i = 0; i < gvShowInfo.Columns.Count; i++)
{
if (i < gvShowInfo.Columns.Count - 1)
sSql += gvShowInfo.Rows[j].Cells[i].Text + " ','";
else
try
{
sSql += ((System.Web.UI.WebControls.Label)gvShowInfo.Rows[j].FindControl("lblType")).Text + " ')";
}
catch { sSql += "" + " ')"; }
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}

//恢复到原来的页面显示方式
gvShowInfo.AllowSorting = true; //恢复分页
gvShowInfo.AllowPaging = true; //恢复排序
this.gvShowInfo.DataBind(); //再次绑定


}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}

}

/// <summary>
/// 数据导出
/// </summary>
private void ImportExcel()
{
try
{
//随机文件名
string FileName = "temp\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(9999) + ".xls";
//临时文件路径
string filePath = this.Request.PhysicalApplicationPath + FileName;

//创建临时文件
CreateExcelFile(filePath);

using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
{
//让用户输入下载的本地地址
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";

Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";

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

// 写到aspx页面
Response.BinaryWrite(buffer);
Response.Flush();
this.ApplicationInstance.CompleteRequest(); //停止页的执行


fs.Close();
fs.Dispose();

//删除临时文件
File.Delete(filePath);

MsgLabel1.Alert = "导出EXCE成功！";
}

}
catch (Exception ex)
{
MsgLabel1.Alert = "导出EXCEL失败:" + ex.Message;

}
}


http://blog.csdn.net/houlinghouling/archive/2009/01/23/3851128.aspx

[YnSky]

this.GridView1.AllowPaging = false;
// Bindcontrols.BindGridView(GridView1,sql);//重新绑定GridView
Response.ClearContent();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("名字", System.Text.Encoding.UTF8) + ".xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
//去掉多余列
GridView1.Columns[GridView1.Columns.Count - 1].Visible = false;
//第一行颜色
//this.GridView1.Rows[0].BackColor= System.Drawing.Color.FromName("#aab8d5");
//表头颜色
this.GridView1.HeaderRow.BackColor = System.Drawing.Color.FromName("#ffffff");
this.GridView1.FooterRow.BackColor = System.Drawing.Color.FromName("#ffffff");
this.GridView1.FooterRow.BorderColor = System.Drawing.Color.FromName("#ffffff");
this.GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
//开启分页功能
this.GridView1.AllowPaging = true;

[zhangguofang1129]

学习了

[kaka_answer]

学习了

[bulenghai]

/// <summary>
/// DataGrid导出到Excel
/// </summary>
/// <param name="ctl"></param>
public void DGToExcel(System.Web.UI.Control ctl)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType = "application/ms-excel";
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}

[孤芳-自赏]

一个封装好的导入导出EXCEL类，可以直接调用

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;

namespace ImportExportToExcel
{
    public class ImportExportToExcel
    {
        private string strConn ;
        
        private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
        private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
        
        public ImportExportToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this.openFileDlg.DefaultExt = "xls";
            this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

            this.saveFileDlg.DefaultExt="xls";
            this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";

        }

        
        #region 从Excel文件导入到DataSet
        //        /// <summary>
        //        /// 从Excel导入文件
        //        /// </summary>
        //        /// <param name="strExcelFileName">Excel文件名</param>
        //        /// <returns>返回DataSet</returns>
        //        public DataSet ImportFromExcel(string strExcelFileName)
        //        {
        //            return doImport(strExcelFileName);
        //        }
        /**//// <summary>
        /// 从选择的Excel文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromExcel()
        {
            DataSet ds=new DataSet();
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                ds=doImport(openFileDlg.FileName);
            return ds;
        }
        /**//// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public DataSet ImportFromExcel(string strFileName)
        {
            DataSet ds=new DataSet();
            ds=doImport(strFileName);
            return ds;
        }
        /**//// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private DataSet doImport(string strFileName)
        {
            if (strFileName=="") return null;
              
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strFileName + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

            DataSet ExcelDs = new DataSet();
            try
            {
                ExcelDA.Fill(ExcelDs, "ExcelInfo");
                
            }
            catch(Exception err)
            {
                System.Console.WriteLine( err.ToString() );
            }
            return ExcelDs;
            
            
        
        }
        #endregion

        #region 从DataSet到出到Excel
        /**//// <summary>
        /// 导出指定的Excel文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的Excel文件名</param>
        public void ExportToExcel(DataSet ds,string strExcelFileName)
        {
            if (ds.Tables.Count==0 || strExcelFileName=="") return;
            doExport(ds,strExcelFileName);
   

        }
        /**//// <summary>
        /// 导出用户选择的Excel文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToExcel(DataSet ds)
        {
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExport(ds,saveFileDlg.FileName);
            
        }
        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的文件名</param>
        private void doExport(DataSet ds,string strExcelFileName)
        {
            
            Excel.Application excel= new Excel.Application();
            
            //            Excel.Workbook obj=new Excel.WorkbookClass();
            //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

            int rowIndex=1;
            int colIndex=0;

            excel.Application.Workbooks.Add(true);
            
    
            System.Data.DataTable table=ds.Tables[0] ;
            foreach(DataColumn col in table.Columns)
            {
                colIndex++;    
                excel.Cells[1,colIndex]=col.ColumnName;                
            }

            foreach(DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
                }
            }
            excel.Visible=false;
            
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
                Excel.XlFileFormat.xlExcel9795, null, null, false,
                false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);
            
            
            //wkbNew.SaveAs strBookName


            //excel.Save(strExcelFileName);
            excel.Quit();
            excel=null;
            
            GC.Collect();//垃圾回收
        }
        #endregion

        #region 从XML导入到Dataset

        /**//// <summary>
        /// 从选择的XML文件导入
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ImportFromXML()
        {
            DataSet ds=new DataSet();
            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
            openFileDlg.DefaultExt="xml";
            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
                catch{}
            return ds;
        }
        /**//// <summary>
        /// 从指定的XML文件导入
        /// </summary>
        /// <param name="strFileName">XML文件名</param>
        /// <returns></returns>
        public DataSet ImportFromXML(string strFileName)
        {
            if (strFileName=="")
                return null;
            DataSet ds=new DataSet();
            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
            catch{}
            return ds;
        }
        
        #endregion

        #region 从DataSet导出到XML
        /**//// <summary>
        /// 导出指定的XML文件
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strXMLFileName">要导出的XML文件名</param>
        public void ExportToXML(DataSet ds,string strXMLFileName)
        {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /**//// <summary>
        /// 导出用户选择的XML文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        public void ExportToXML(DataSet ds)
        {
            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
            saveFileDlg.DefaultExt="xml";
            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExportXML(ds,saveFileDlg.FileName);
        }

        /**//// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="ds">要导出的DataSet</param>
        /// <param name="strExcelFileName">要导出的XML文件名</param>
        private void doExportXML(DataSet ds,string strXMLFileName)
        {
            try
            {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
            catch(Exception ex)
            {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
        }

        #endregion 
    }

}

[redfalson]

调用时如何调用的呢，封装好的程序
我使用
http://blog.csdn.net/zhoufoxcn/archive/2008/05/16/2450642.aspx的方法结果导出的是这个网页

[阿彪兄]

上面说得很清楚了，要么以流的方式，要么把Excel当作一个数据库，将Excel中不同的sheet当作是不同的表进行操作就行了。

[baoxuetianxia]

http://www.cnblogs.com/huangjianhuakarl/archive/2009/02/03/1381710.html
看看

[redfalson]

我还是找其他的文件弄去了