.net数据导入导出Excel

Question

.net中怎么实现把数据从数据库中导出到Excel中，和把数据从Excel导入到数据库(sqlserver)中，谁能介绍种比较好的方法，实现数据快速的导入导出。

Answer 1

string style = @"<style> .text { mso-number-format:\@; } </script> ";
response.ClearContent();
response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
response.ContentType = "application/ms-excel";
response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);response.Write(style);
response.Write(sw.ToString());
response.End();还要加上这个方法
public override void VerifyRenderingInServerForm(Control control)
{}
这是导出

Answer 2

Excel数据导入到GridView中     //读取Excel数据的代码：这个很简单的
     private DataSet CreateDataSource()
     {
         string strCon;
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";
        OleDbConnection olecon = new OleDbConnection(strCon);
        OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
         DataSet myds = new DataSet();
         myda.Fill(myds);
         return myds;
     }
     protected void Button1_Click(object sender, EventArgs e)
     {
         GridView1.DataSource = CreateDataSource();
         GridView1.DataBind();
     } 

Answer 3

导出到Excel
public class GridViewToExcel
    {
        HttpResponse Response = null;
        public GridViewToExcel(HttpResponse response)
        {
            Response = response;
        }        /// <summary>
        /// 将GridView里的数据导入到Excel文件中，并另存文档
        /// </summary>
        /// <param name="FileName">文件名称</param>
        /// <param name="GridViewTeacher">要导出数据的GridView</param>
        public void ExportToExcel(string FileName, GridView gridview)
        {
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF7;
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            Response.ContentType = "application/ms-excel";//导出excel文件
            //this.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            gridview.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }
    }

Answer 4

private void btnOk_Click(object sender, EventArgs e)
        {
            if (this.txtFilePath.Text.Trim() == "")
            {
                MessageBox.Show("请选择要导入的EXCEL文件！", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                try
                {
                    string fileName = this.txtFilePath.Text;
                    Excel.Application excel = new Excel.Application();
                    object oMissing = System.Reflection.Missing.Value;
                    excel.Application.Workbooks.Open(fileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                    Excel.Workbook book = excel.Workbooks[1];
                    Excel.Worksheet sheet = (Excel.Worksheet)excel.Worksheets[1];                    string strSheetName = sheet.Name;
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";                    string strExcel = "select * from  [" + strSheetName + "$]";
                    DataSet ds = new DataSet();
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
                    adapter.Fill(ds);
                    DataRow[] dr = ds.Tables[0].Select();
                    int ss = ds.Tables[0].Rows.Count;
                    int rowsnum = ds.Tables[0].Rows.Count;
                    if (rowsnum == 0)
                    {
                        MessageBox.Show("Excel表为空表,无数据！", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }                    for (int i = 1; i < rowsnum; i++)
                    {
                        string name = dr[i][0].ToString();
                        string sex = dr[i][1].ToString();
                        string birthday = dr[i][2].ToString();
                        string userType = dr[i][3].ToString();
                        string miniTel = dr[i][4].ToString();
                        string mobilePhone = dr[i][5].ToString();
                        string homeTel = dr[i][6].ToString();
                        string jobTel = dr[i][7].ToString();
                        string fax = dr[i][8].ToString();
                        string qqNo = dr[i][9].ToString();
                        string email = dr[i][10].ToString();
                        string address = dr[i][11].ToString();
                        string personPage = dr[i][12].ToString();
                        string note = dr[i][13].ToString();                        string sql = "insert into userinfo values('" + name + "','" + sex + "','" + birthday + "','" + userType + "','" + miniTel + "','" + mobilePhone + "','" + homeTel + "','" + jobTel + "','" + fax + "','" + qqNo + "','" + email + "','" + address + "','" + personPage + "','" + note + "')";                        string findUser = "select 姓名 from userinfo where 姓名='" + name + "'";
                        DBCon oleCon = new DBCon();
                        object obj = oleCon.Scale(findUser);
                        ds = oleCon.Query(findUser);
                        if (obj != null)
                        {
                            MessageBox.Show("用户名'" + name + "'已经存在，请修改后再导入！", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }                        oleCon.Updata(sql);
                        frm.LoadData();                        //System.Diagnostics.Process[] ps = System.Diagnostics.Process.GetProcesses();
                        //foreach (System.Diagnostics.Process p in ps)
                        //{
                        //    if (p.ProcessName.ToUpper().Equals("EXCEL"))
                        //    {
                        //        p.Kill();
                        //    }
                        //}
                        excel.Quit();                    }
                    int num=rowsnum-1;
                    MessageBox.Show("成功导入" + num + "条用户信息！", "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "友情提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

Answer 5

这是导入将Excel文件导入数据库

Answer 6

//导出到Excel
    protected void BtnExcel_Click(object sender, EventArgs e)
    {
        //判断当前是否有数据可导出
        if (reppriv.Items.Count == 0)
        {
            Response.Write("<script>alert('没有可导出的数据');</script>");
            return;
        }
        //输出保存Excel
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        this.reppriv.RenderControl(hw);        Response.Clear();
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        reppriv.Page.EnableViewState = true;
        Response.AppendHeader("Content-Disposition", "attachment;filename=Dept.xls");
        Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=UTF8\"><title> Excel</title></head><body><table><tr><td>信息1</td><td> 信息2</td><td>信息3</td><td>信息4</td></tr>");
        Response.Write(sw.ToString().Replace("隐藏列的名字", string.Empty)); 
        Response.Write("</table></body></html>");
        Response.End();
    }//Excel数据导入SQL
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GOinsert into InitializeStock 
select * FROM OpenDataSource('microsoft.jet.oledb.4.0',
'Data Source="D:\Excel.xls ";User ID=admin;Password=;Extended properties=Excel 5.0') 
...[Sheet1$]  where 列名 is NOT nullEXEC sp_configure 'Ad Hoc Distributed Queries', 0
GO

Answer 7

如果你对导出Excel格式没要求，就按上面的来，如果有要求就按我的博客写的
http://www.cnblogs.com/wuxing

Answer 8

导入的时候连接excel数据库"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";导出的时候我使用owc11，但是在单元格自动换行处卡住了，谁能解决问题我出300分。前提是基于owc11导出excel的。有兴趣的联系我QQ170366419

Answer 9

这种东西没什么技术含量，只要把代码Copy一下，下次用的时候，改改就可以了！

Answer 10

这个数据导入导出跟Excel版本有没有关系。