从excel导入数据到数据库中和gridview导出数据到excel中

来源：互联网发布：韩春雨质疑知乎编辑：程序博客网时间：2024/04/29 23:49

页面代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title></title> <mce:style type="text/css"></mce:style><style type="text/css" mce_bogus="1"> .style1 { width: 100%; } </style></head><body> <form id="form1" runat="server"> <table class="style1"> <tr> <td colspan="3" align="center"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" Font-Size="12px" ForeColor="#000066" Height="136px" HorizontalAlign="Left" Width="635px"> <RowStyle ForeColor="#000066" /> <Columns> <asp:BoundField HeaderText="序号" DataField="序号" /> <asp:BoundField HeaderText="学号" DataField="学号" /> <asp:BoundField HeaderText="姓名" DataField="姓名" /> <asp:BoundField HeaderText="获得学分" DataField="获得学分" /> <asp:BoundField HeaderText="编译原理" DataField="编译原理" /> </Columns> <FooterStyle BackColor="White" /> <PagerStyle BackColor="White" ForeColor="#000066" /> <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" /> </asp:GridView> </td> </tr> <tr> <td><asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="从计算机导入EXCEL数据到数据库" /> </td> <td> <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="从数据库中导出数据到GRIDVIEW" /> </td> <td> <asp:Button ID="Button3" runat="server" Text="GRIDVIEW中的数据导出到EXCEL" onclick="Button3_Click" /> </td> </tr> </table> </form></body></html>

后台代码：

public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } public void loaddata(string stylesheet) { string strcon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath ("Book1.xls") + ";Extended Properties=Excel 8.0"; OleDbConnection mycon = new OleDbConnection(strcon); mycon.Open(); DataSet ds = new DataSet(); string str = "select * from ["+stylesheet+"$]"; OleDbDataAdapter da = new OleDbDataAdapter(str,mycon); da.Fill(ds, "[" + stylesheet + "$]"); da.Dispose(); DataTable dt = ds.Tables["["+stylesheet+"$]"]; mycon.Close(); mycon.Dispose(); string sqlcon = @"Data Source=PC-201001310935/SQLEXPRESS;Initial Catalog=测试;Integrated Security=True;Pooling=False"; SqlConnection con = new SqlConnection(sqlcon); con.Open(); for (int j = 0; j <dt.Rows.Count; j++) { string 序号=dt.Rows[j][0].ToString(); string 学号 = dt.Rows[j][1].ToString(); string 姓名 = dt.Rows[j][2].ToString(); string 获得学分 = dt.Rows[j][3].ToString(); string 编译原理 = dt.Rows[j][4].ToString(); string strsql = "insert 成绩表(序号,学号,姓名,获得学分,编译原理)values('"+序号+"','"+学号+"','"+姓名+"','"+获得学分+"','"+编译原理+"')"; SqlCommand sqlconn = new SqlCommand(strsql,con); sqlconn.ExecuteNonQuery(); //if (j == dt.Rows.Count - 1) //{ //} } con.Close(); } protected void Button1_Click(object sender, EventArgs e) { string stylesheet = "Sheet1"; loaddata(stylesheet); } protected void Button2_Click(object sender, EventArgs e) { string strcon = @"Data Source=PC-201001310935/SQLEXPRESS;Initial Catalog=测试;Integrated Security=True;Pooling=False"; string strsql = "select * from 成绩表"; SqlConnection con = new SqlConnection(strcon); SqlDataAdapter da = new SqlDataAdapter(strsql, strcon); DataSet ds = new DataSet(); da.Fill(ds," 成绩表"); con.Open(); GridView1.DataSource = ds; GridView1.DataBind(); con.Close(); } protected void Button3_Click(object sender, EventArgs e) { export("application/ms-excel","编译原理成绩表.xls"); } private void export(string filestye, string filename) { Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF7; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8).ToString()); Response.ContentType = filestye; this.EnableViewState = false; StringWriter st = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(st); GridView1.RenderControl(htw); Response.Write(st.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { }}