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

zhangziyangup

浏览: 1186601 次

最近访客更多访客>>

u012363178

winting

aosp

真新镇的小智

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1434)

社区版块

存档分类

2012-07 ( 149)
2012-06 ( 33)
2012-05 ( 45)
更多存档...

最近想要做一个考试成绩分析模块，对填好的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模板如图：