还是EXCEL导入SQL的问题,有点头疼!!
基本实现把excel导入到sql中,但别人要求我动态导入，比如在EXCEL的某张表中,如果表里含有标题的话，导入SQL后SQL表中的列名就会出现问题.在不对EXCEL表做修改的情况下，如何选择性的导入SQL?(EXCEL遇到标题或者空行就不读)?请说详细!不甚感激!

[解决办法]
建议LZ使用oledb进行操作,这样可以摆脱对office的依赖,就算客户机没有安装office也可以进行导出!
例子:
使用oledbconnection打开连接,连接语句 "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';Data Source= "+路径;
选择语句为"select * from ["+工作表名称+"$"+开始位置+":"+结算位置+"]"
例如:
select * from ["+工资+"$A:W]":选择从A列到W列(行同样);
select * from ["+工资+"$A3:W4]":选择从A列第三行到W列第四行;
选择好后从内存的DataTable导入到数据库
[解决办法]
你所谓的动态导入，也就是说有可能每次导入的excel标题有可能不同？
你可以使用配置文件或者是界面输入方式，让操作者手动输入excel标题就可以了。
DialogResult X = MessageBox.Show("你确定要导入:"+comboBox1.Text.Trim()+"应收金额 的数据吗吗?", "导入确认", MessageBoxButtons.YesNo);
if (X == DialogResult.Yes)
{
OleDbConnection oled = new OleDbConnection("provider=microsoft.jet.OLEDB.4.0;Data source=d:\\mysqlserver\\dgmk.mdb");
string ConnectionString = "[sheet1$]";
OleDbConnection conn = new OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+label5.Text.Trim()+";Extended Properties='Excel 8.0;IMEX=1'";
OleDbConnection olconn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [sheet1$]", olconn);
DataSet ds = new DataSet();
oled.Open();
try
{
oada.Fill(ds, "[sheet1$]");
OleDbCommand comm = new OleDbCommand("select * from 网吧名称", conn);
comm.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[0]["应结"].ToString() != " ")
{
string o = comboBox1.Text.Trim() + "应收金额";
OleDbCommand oleddata = new OleDbCommand("Update cattle Set " + o + "='" + ds.Tables[0].Rows[i]["应结"].ToString() + "' where 网吧名称='" + ds.Tables[0].Rows[i]["网吧名称"].ToString() + "'", oled);
oleddata.ExecuteNonQuery();
}
}
MessageBox.Show("导入完成");
}
catch (Exception ex)
{
MessageBox.Show("excel表格格式不对，或者数据连接失败，检查后重试"+ex.ToString(), "导入失败");
}
}

上面的代码是将excel数据导入access，其中应结是excel标题，你所说的情况，只需要装excel标题动态就可以了。
[解决办法]
protected void Button1_Click(object sender, System.EventArgs e)
{
if (FileUpload1.HasFile)
{
name = FileUpload1.PostedFile.FileName;
//取得文件名(抱括路径)里最后一个"."的索引
int i = name.LastIndexOf(".");
//取得文件扩展名
string newext = name.Substring(i);
if (newext == ".xls"||newext ==".XLS")
{
FileInfo file = new FileInfo(name);

fileName = file.Name;
webFilePath = Server.MapPath("~/Files/"+fileName);
FileUpload1.SaveAs(webFilePath);
}
else
{
Response.Write("<script>alert('上文件必Excel文!')</script>");
}

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + webFilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
string query = "select * from [Sheet1$]";
OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(strConn));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
//将Excel的[Sheet1]表内容填充到DataSe对象
try
{
oleAdapter.Fill(myDataSet, "[Sheet1$]");
this.GridView1.DataSource = myDataSet;
this.GridView1.DataMember = "[Sheet1$]";
this.GridView1.DataBind();
this.GridView1.Visible = true;
this.Button2.Visible = true;
}
catch (Exception exx)
{
Response.Write(exx.Message);
}
finally
{
if (File.Exists(webFilePath))
{
File.Delete(webFilePath);
}
}
}
else
{
Response.Write("<script>alert('正的路!')</script>");
}
}

protected void Button2_Click(object sender, System.EventArgs e)
{
string conn = ConfigurationManager.AppSettings["ConnectionString"];
SqlConnection sqlConn = new SqlConnection(conn);
{
try
{
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
string myyear = GridView1.Rows[i].Cells[0].Text.Trim().Replace(" ", " ");
string mymonth = GridView1.Rows[i].Cells[1].Text.Trim().Replace(" ", " ");
string workid = GridView1.Rows[i].Cells[2].Text.Trim().Replace(" ", " ");
string name = GridView1.Rows[i].Cells[3].Text.Trim().Replace(" ", " ");
string workday = GridView1.Rows[i].Cells[4].Text.Trim().Replace(" ", " ");
string offday = GridView1.Rows[i].Cells[5].Text.Trim().Replace(" ", " ");
string off1 = GridView1.Rows[i].Cells[6].Text.Trim().Replace(" ", " ");
string off2 = GridView1.Rows[i].Cells[7].Text.Trim().Replace(" ", " ");
string others = GridView1.Rows[i].Cells[8].Text.Trim().Replace(" ", " ");
string overwork = GridView1.Rows[i].Cells[9].Text.Trim().Replace(" ", " ");
string weekend = GridView1.Rows[i].Cells[10].Text.Trim().Replace(" ", " ");
string basic = GridView1.Rows[i].Cells[11].Text.Trim().Replace(" ", " ");

string life = GridView1.Rows[i].Cells[12].Text.Trim().Replace(" ", " ");
string overfee = GridView1.Rows[i].Cells[13].Text.Trim().Replace(" ", " ");
string weekendfee = GridView1.Rows[i].Cells[14].Text.Trim().Replace(" ", " ");
string bonus = GridView1.Rows[i].Cells[15].Text.Trim().Replace(" ", " ");
string allowance = GridView1.Rows[i].Cells[16].Text.Trim().Replace(" ", " ");
string sumsalary = GridView1.Rows[i].Cells[17].Text.Trim().Replace(" ", " ");
string eatfee = GridView1.Rows[i].Cells[18].Text.Trim().Replace(" ", " ");
string tax = GridView1.Rows[i].Cells[19].Text.Trim().Replace(" ", " ");
string insurance = GridView1.Rows[i].Cells[20].Text.Trim().Replace(" ", " ");
string offsalary = GridView1.Rows[i].Cells[21].Text.Trim().Replace(" ", " ");
string salary = GridView1.Rows[i].Cells[22].Text.Trim().Replace(" ", " ");

if (myyear.Trim() != "" && mymonth.Trim() != "" && workid.Trim() != "" && name.Trim() != "")
{
StrSql = "insert into salary(myyear,mymonth,WorkId,name,WorkDay,OffDay,Off1,Off2,Others,OverWork,Weekend,Basic,Life,OverFee,WeekendFee,bonus,allowance,SumSalary,EatFee,Tax,Insurance,OffSalary,Salary)values('" + myyear + "','" + mymonth + "','" + workid + "','" + name + "','" + workday + "','" + offday + "','" + off1 + "','" + off2 + "','" + others + "','" + overwork + "','" + weekend + "','" + basic + "','" + life + "','" + overfee + "','" + weekendfee + "','" + bonus + "','" + allowance + "','" + sumsalary + "','" + eatfee + "','" + tax + "','" + insurance + "','" + offsalary + "','" + salary + "')";
cmd = new SqlCommand(StrSql, sqlConn);
try
{
cmd.ExecuteNonQuery();
}
catch
{
Response.Write("<script>window.alert('入失!')</script>");
}
}
}
Response.Write("<script>window.alert('入成功!')</script>");
}
catch (Exception exx)
{
Response.Write(exx.Message);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
this.GridView1.Visible = false;
this.Button2.Visible = false;
}
}