GridView读取EXCEl并保存到数据库中

来源：互联网发布：小米手机3能用4g网络吗编辑：程序博客网时间：2024/05/16 11:40

最近想要做一个考试成绩分析模块，对填好的EXCEL表格进行读取操作，将数据存储到数据库中，并在界面显示读取的EXCEL内容，倒腾半天作出如下效果，不是很好，算是给大家参考吧。有不对的地方还请大家指出，我的邮箱：pplsunny@163.com。。最后将所有源代码贴在文章里。

前台界面显示

如图：

代码;

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="gekexiaofen.aspx.cs" Inherits="STAS.second.gekexiaofen" %><!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></head><body>    <form id="form1" runat="server">     <div >     <center>       <br />        <br />        <table>           <tr>              <td>        请选择要打开的科目小分模板Excel：<br />        <br />                <asp:FileUpload ID="FileUpload1" runat="server" />                    <asp:Label ID="Label1" runat="server"></asp:Label>        <br />        <br />        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="打开" />        </td>        <td >  sddd <asp:Button ID="Button2" runat="server" onclick="BClick" Text="开始统计" />        <br />        <br />          科目：<asp:Label ID="Label2" runat="server"></asp:Label><br />        <br />          考试时间：<asp:Label ID="Label3" runat="server"></asp:Label>        </td>          </tr>        </table>        <br />    <br />        <asp:GridView ID="GridView1" runat="server" Width="80%" EmptyDataText="无数据"             BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px"             CellPadding="3" GridLines="Horizontal">            <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />            <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />            <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />            <AlternatingRowStyle BackColor="#F7F7F7" />        </asp:GridView>            <br />        </center>    </div>    </form></body></html>

后台代码：

using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Data.SqlClient;using System.Diagnostics;using System.IO;using System.Collections.Generic;using System.Data.OleDb;namespace STAS.second{    public partial class gekexiaofen : System.Web.UI.Page    {        protected void Page_Load(object sender, EventArgs e)        {        }        protected void Button1_Click(object sender, EventArgs e)        {            if (FileUpload1.HasFile)//判断fileupload1是否为空            {                Label1.Text = "";                string fullname = FileUpload1.FileName.ToString();                //直接取得文件名                string url = FileUpload1.PostedFile.FileName.ToString();                //取得全部的上传文件路径                string fileName = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);                string typ = FileUpload1.PostedFile.ContentType.ToString();                //获取文件名字 . 后面的字符作为文件类型                string size = FileUpload1.PostedFile.ContentLength.ToString();                //获取文件MIME内容类型                string typ2 = fullname.Substring(fullname.LastIndexOf(".") + 1);                if (typ2 == "xls" || typ2 == "xlsx")                {                    //OpenExcel(url);                    DataSet dataset = CreateDataSource(url);                    GridView1.DataSource = dataset;                    GridView1.DataBind();                                        if (dataset.Tables[0].Rows.Count > 2)                    {                        Label1.Text = "文件打开成功！请见下面↓";                                //将数据存入数据库中对应的表                                string mySql = "";                                //链接SQLserver2005数据库                                   string sqlConn = "server=ljl;database=STAS;user id=sa;pwd=123456";                                SqlConnection con = new SqlConnection(sqlConn);                                if (con.State.ToString() == "Closed")                                {                                    con.Open();                                }                                SqlCommand myCmd = new SqlCommand();                                                       //将数据逐行写入到数据库中                                   for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)                                {                                    for (int j = 0; j <16; j++)                                    {                                        int z = 91007; //表示课程编号                                        int x = 2 * j + 2;//表示分数列号                                        int y = j + 1;//表示题号                                            //数据库中几个个字段，所以插入几列                                           mySql = ("insert into SCscore(scs_stu_id,scs_course_id,scs_tnum,scs_true_score,scs_objselect) values('" + dataset.Tables[0].Rows[i][0].ToString() + "'," + z + "," + y + ",'" + dataset.Tables[0].Rows[i][x + 1].ToString() + "','" + dataset.Tables[0].Rows[i][x].ToString() + "')");                                            myCmd.Connection = con;                                            myCmd.CommandText = mySql;                                            try                                            {                                                myCmd.ExecuteNonQuery();                                            }                                            catch (Exception ex)                                            {                                                Response.Write("将数据插入数据库时出错" + ex.Message);                                            }                                    }                                }                                Response.Write("<SCRIPT>alert('数据已成功导入到数据库！');</SCRIPT>");                                if (con.State.ToString() == "Open")                                {                                    con.Close();                                }                                  }                    else                    {                        Label1.Text = "此文件为空文件！";                    }                }                else                {                    Label1.Text = "只能打开Excel文件！";                }            }            else            {                Label1.Text = "没有选择任何文件！";            }         }        private DataSet CreateDataSource(string path)        {            string strConn;            strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";            OleDbConnection conn = new OleDbConnection(strConn);            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM   [Sheet1$] ", strConn);            DataSet myDataSet = new DataSet();            myCommand.Fill(myDataSet);            return myDataSet;        }        protected void BClick(object sender, EventArgs e)        {         }     }}

EXCEl模板如图：

导入成功后的界面如下：

数据库导入截图如下：