Question

vc如何读excel表格中信息存入sql数据库

Answer 1

之前做过一次，在网上找到的人家的源码，自己加强了一下健壮性，你试试看能不能用。

using System;
using System.Configuration;
using System.Data;
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.Data.OleDb;
using System.Data.SqlClient;

public partial class ImportSCI : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnUpload_Click(object sender, EventArgs e)
{
//确保已经选择了待导入的文件，首先上传，然后在服务器端完成导入
if (this.fuOpen.PostedFile.FileName != "")
{
//确保文件是excel格式
//Response.Write(this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.')));
if (this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.') + 1) == "xls")
{
Random rd = new Random(1);
string filename = DateTime.Now.Date.ToString("yyyymmdd") + DateTime.Now.ToLongTimeString().Replace(":", "") + rd.Next(9999).ToString() + ".xls";
try
{
this.fuOpen.PostedFile.SaveAs(@Server.MapPath("fileupload/") + filename);
}
catch (HttpException he)
{
Response.Write("文件上传不成功，请检查文件是否过大，是否有写权限！");
return;
}
#region --------读取文件内容到服务器内存----------
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("fileupload") + "/" + filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
//要保证字段名和excel表中的字段名相同
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close();
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
#endregion

#region --------插入到数据库中---------
string conn1 = "Server=(local);database=QK;Uid=sa1;Pwd=1234";
SqlConnection thisconnection1 = new SqlConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;

for (int i = 0; i < count; i++)
{
string ID,TITLE, ISSN, ei, DL, XL;
ID = ds.Tables["[Sheet1$]"].Rows[i]["ID"].ToString();
TITLE = ds.Tables["[Sheet1$]"].Rows[i]["TITLE"].ToString();
ISSN = ds.Tables["[Sheet1$]"].Rows[i]["ISSN"].ToString();
ei = ds.Tables["[Sheet1$]"].Rows[i]["e"].ToString();
DL = ds.Tables["[Sheet1$]"].Rows[i]["DL"].ToString();
//XL = ds.Tables["[Sheet1$]"].Rows[i]["XL"].ToString();

//string excelsql = "insert into SCI(ID, TITLE, ISSN, e, DL, XL) values ('" + ID + "','" + TITLE + "','" + ISSN + "','" + ei + "','"+DL+"','"+XL+"') ";
string excelsql = "insert into SCI(ID, TITLE, ISSN, e, DL) values ('" + ID + "','" + TITLE + "','" + ISSN + "','" + ei + "','" + DL + "'"+") ";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
try
{
mycommand1.ExecuteNonQuery();
}
catch (SqlException ode)
{
//Response.Write("<b>导入过程出现异常，请检查是否需要重新导入！</b>");
Response.Write(ode.Message.ToString());
return;
}
}
Response.Write("更新成功");
thisconnection1.Close();
#endregion
}
else
{
Response.Write("导入文件的格式不正确！");
}

}
else
{
Response.Write("您还没有选择要导入的文件！");
}

}
}