我是新手，往数据库导入excel固定表格，显示导入成功后，数据库里没有反应，好像只上传到服务器了，怎么弄到数据库中
if (FileUpload1.HasFile==false)
{
Response.Write("<script>alert('请选择文件！')</script>");
return;
}
string isXSL = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower(); //获取文件的扩展名
if (isXSL!=".xls")
{
Response.Write("<script>alert('请选择Excel文件！')</script>");
return;
}
string filename = FileUpload1.FileName;
string savePath = Server.MapPath("upfiles\") + filename;
FileUpload1.SaveAs(savePath);
DataSet ds = ExcelSqlConnection(savePath,filename);
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else{
for (int i = 0; i < dr.Length;i++ )
{
string studentID = dr[i]["学号"].ToString();
string name = dr[i]["姓名"].ToString();
string courseName = dr[i]["课程名称"].ToString();
string credit = dr[i]["学分"].ToString();
string psScore = dr[i]["平时成绩"].ToString();
string qzScore = dr[i]["期中成绩"].ToString();
string qmScore = dr[i]["期末成绩"].ToString();
string syScore = dr[i]["实验成绩"].ToString();
string endScore = dr[i]["总评成绩"].ToString();
string zsScore = dr[i]["折算成绩"].ToString();
string bkScore = dr[i]["补考成绩"].ToString();
string cxScore = dr[i]["重修成绩"].ToString();
string Gradepoint = dr[i]["绩点"].ToString();
string Coursexz = dr[i]["课程性质"].ToString();
string cxSign = dr[i]["重修标记"].ToString();
string remark = dr[i]["备注"].ToString();
string year = dr[i]["学年"].ToString();
string term = dr[i]["学期"].ToString();
string academy = dr[i]["学院"].ToString();
string classa = dr[i]["班级"].ToString();
}
Response.Write("<script>alert('Excle表导入成功!');</script>");
}
}
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
[解决办法]
可以一条条插入,也可以通过sql命令一次导入
[解决办法]
你只保存在服务器上没有用啊，得循环insert到数据库才行
------解决方案--------------------
string sql="Insert INTO tb1(字段1,字段2...) SELECT 字段1,字段2... FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=2;DATABASE="+ savePath +"',[Sheet1$])";
