怎么实现Excel表格导入导出功能

[Gracewen17]

请问：我如果在asp.net页面中用GridView显示数据，并实现Excel表格导入导出功能怎么写？
谢谢各位!

[whb147]

baidu搜索一下，很多的

[liujun198773]

private void DaoRu()   
        {    
            OpenFileDialog myDiag = new OpenFileDialog();   
            myDiag.Filter = "Excel文件(*.xls)|*.xls";   
            string myFile = myDiag.FileName;   
            if (myDiag.ShowDialog() == DialogResult.OK)   
            {   
                OleDbConnection con = new OleDbConnection("server=.;uid=sa;pwd=sa;database=MyOffice;max pool size=30");   
                con.Open();   
                string sql = "insert   into   Execl(id,name)  select  *   from  "+   
                    "OpenDataSource( 'Microsoft.Jet.OLEDB.4.0 ', 'Data   Source= " + myDiag.FileName + ";Extended   properties=Excel   8.0 ')...[Book1$]";   
                OleDbCommand cmd = new OleDbCommand(sql, con);   
                cmd.ExecuteNonQuery();   
                con.Close();   
            }   
        } 

[Gracewen17]

谢谢大家！能导出但是没有数据，只显示标题，是什么原因？

[hanqing_liu]

......

[无涯乌鸦五雅]

用 asp:Panel 这个试试，我之前用的都是这个


/// <summary>
/// GridView 导出数据 excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void lbtnexcel_Click(object sender, EventArgs e)
{
gridwiew.DataSource = DataBase.LoadProductByIsShangxiajia("上架", out strError); //DataBase.LoadAllProducts(out strError);
gridwiew.DataBind();
// gridwiew.BottomPagerRow.Visible = false;//导出到Excel表后，隐藏分页部分
// gridwiew.Columns[12].Visible = false;//隐藏“编辑”列
// gridwiew.Columns[13].Visible = false;//隐藏“删除”列
DateTime dt = DateTime.Now;//给导出后的Excel表命名，结合表的用途以及系统时间来命名
string filename = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString();
/*如导出的表中有某些列为编号、身份证号之类的纯数字字符串，如不进行处理，则导出的数据会默认为数字，例如原字符串"0010"则会变为数字10，字符串"1245787888"则会变为科学计数法1.236+E9，这样便达不到我们想要的结果，所以需要在导出前对相应列添加格式化的数据类型，以下为格式化为字符串型*/
foreach (GridViewRow dg in this.gridwiew.Rows)
{
dg.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[5].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[6].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
dg.Cells[8].Attributes.Add("style", "vnd.ms-excel.numberformat: @;");
}
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("名称" + filename, System.Text.Encoding.UTF8) + ".xls");//导出文件命名
Response.ContentEncoding = System.Text.Encoding.UTF7;//如果设置为"GB2312"则中文字符可能会乱码
Response.ContentType = "applicationshlnd.xls";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
Panel1.RenderControl(oHtmlTextWriter);//Add the Panel into the output Stream.
Response.Write(oStringWriter.ToString());//Output the stream.
Response.Flush();
Response.End();
}
//重载VerifyRenderingInServerForm方法，否则运行的时候会出现如下错误提示：“类型“GridView”的控件“gridwiew”必须放在具有 runat=server 的窗体标记内”
public override void VerifyRenderingInServerForm(Control control)
{
//override VerifyRenderingInServerForm.
}

[Gracewen17]

谢谢了！

[zthsn]

www.baidu.com
N多

[打一壶酱油]

到处数据时 我看还是 用 dataset 导出吧，直接 gridview 导出效果会让人崩溃的

[酷儿]

呵呵 你导出再入的时候会出问题的 如果出了不是标准的excel的话再来找我吧 呵呵 就是导出的excel再导入

[SamuDra]

每天回帖即可获得10分可用分！

[冬冬瓜]

Response.Charset = "gb2312";
       Response.ContentEncoding = System.Text.Encoding.UTF7;//设置http输出流的格式,必须有，否则导出乱码
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("filename.xls").ToString());
       //Response.ContentType = "Application/ms-excel";
       //  this.EnableViewState = false;
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        string n = ConfigurationManager.AppSettings["con"].ToString() + System.Web.HttpContext.Current.Server.MapPath("") + ConfigurationManager.AppSettings["dbPath"] + ";";
        OleDbConnection connect = new OleDbConnection(n);
        connect.Open();
        //string que = "select * from renkou order by id desc";
        OleDbDataAdapter da = new OleDbDataAdapter(que, connect);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
       // GridView1.AllowPaging = false;
       // GridView1.DataKeyNames = new string[] { "id" };
        GridView1.DataBind();
        int num = GridView1.Rows.Count;//获得行数
        for (int i = 0; i < num; i++)
        {
            CheckBox ch = (CheckBox)GridView1.Rows[i].FindControl("CheckBox1");
            ch.Visible = false;
        }
        GridView1.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();

gridview控件中如果包含其他服务器控件，为报错，

[冬冬瓜]

补充，导出的时候必须重写VerifyRenderingInServerForm方法：
public override void VerifyRenderingInServerForm(Control control)//必须要有
{
//不需添加任何内容
}

[Gracewen17]

13,14楼说得那样。
可能是这些问题吧。
如果控件在ajax控件内也不可以吧
谢谢！

[happy664618843]

http://blog.csdn.net/happy664618843/archive/2010/06/23/5689478.aspx参考

[GlyphVectory]

http://download.csdn.net/source/2647486
简单明了。

[GlyphVectory]

啊。。难道非的把代码贴在这里吗。。%>_<%

[Hertz_liu]

用第三方的，Syncfusion.XlsIO.Base.dll，个人觉得这个比较好用

//导入到Excel
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
            IWorksheet sheet1 = workbook.Worksheets[0];
            sheet1.ImportDataTable(datasource, isfieldnameshown, firstrow, firstcolumn);
            sheet1.SaveAs("test.xls", ExcelSaveType.SaveAsXLS, HttpContext.Current.Response, ExcelDownloadType.PromptDialog);

//导出Excel
ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fuExcel.PostedFile.FileName);
            IWorksheet sheet1 = workbook.Worksheets[1];

            //取出Sheet中的数据放入DataTable
            DataTable dt1 = sheet1.ExportDataTable(sheet1.Range, ExcelExportDataTableOptions.None);

[Gracewen17]

谢谢各位了！

[zrrsj]

lz,我以前也做过这个，你百度下，很多代码，都有用的。

[Gracewen17]

好的，谢谢你了！