关于excel内数据导入数据库内 excel内的数据,前四行是一些说明性的信息不需要导入第五行 才是标题,第五行一下是内容问题是 我怎么从第五行开始读取数据?求指教 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 是打开access直接导入?还是写程序导入? 写程序 导入到sql server protected void Button1_Click(object sender, EventArgs e) { fun(); } private DataTable subtable(int i, int j, DataTable dt) { int t; DataTable table = new DataTable(); for (t = 0; t < dt.Columns.Count; t++) table.Columns.Add(dt.Columns[t].ColumnName.ToString()); for (t = i; t < j; t++) table.Rows.Add(); for (t = 0; t < dt.Columns.Count; t++) for (int r = i; r < j; r++) table.Rows[r - i][t] = dt.Rows[r][t]; return table; } private void fun() { OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + Server.MapPath("Book1.xls")); OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet3$]",conn); DataTable dt=new DataTable(); da.Fill(dt); for (int i = 0; i < dt.Columns.Count; i++) dt.Columns[i].ColumnName = dt.Rows[3][i].ToString(); dt=subtable(4,dt.Rows.Count,dt); conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=D:\\lx.mdb"); string str = "create table tb("; for (int i = 0; i < dt.Columns.Count; i++) { str = str + dt.Columns[i].ColumnName.ToString() + " "; string p = dt.Columns[i].DataType.ToString(); str = str + p.Substring(7, p.Length - 7)+","; } str = str.Substring(0, str.Length - 1) + ")"; OleDbCommand cmd = new OleDbCommand(str, conn); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); da=new OleDbDataAdapter("select * from tb",conn); string p_columns = "", q_columnstype = ""; for (int i = 0; i < dt.Columns.Count; i++) { p_columns = p_columns + dt.Columns[i].ColumnName.ToString() + (i < dt.Columns.Count - 1 ? "," : ""); q_columnstype = q_columnstype + "@" + dt.Columns[i].ColumnName.ToString() + (i < dt.Columns.Count - 1 ? "," : ""); } str = "insert into tb(" + p_columns + ") values(" + q_columnstype + ")"; cmd = new OleDbCommand(str, conn); for (int i = 0; i < dt.Columns.Count; i++) { cmd.Parameters.Add("@" + dt.Columns[i].ColumnName.ToString(),OleDbType.VarChar, 50, dt.Columns[i].ColumnName.ToString()); } da.InsertCommand = cmd; da.Update(dt); } 那我再问一个问题 怎样遍历excel的所有工作表的名称?楼上楼下的高手 教我 DataSet ds = new DataSet(); OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0; data source=" + Server.MapPath("Book1.xls") + ";extended properties=excel 8.0;"); conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); OleDbDataAdapter sqlada = new OleDbDataAdapter(); foreach (DataRow dr in dt.Rows) { string strSql = "Select * From [" + dr[2].ToString().Trim() + "]"; OleDbCommand objCmd = new OleDbCommand(strSql, conn); sqlada.SelectCommand = objCmd; sqlada.Fill(ds, dr[2].ToString().Trim()); } conn.Close(); 关于输出EXCEL 2007以上版本(xlsx)的代码问题 怎么样在 .aspx里面用到 .aspx.cs里面的变量 去掉IE小窗口地址栏 yahoo邮件服务器发送邮件问题 winform控件的问题 奇怪的问题:为什么没有在dataset里面数据,也有数据显示呢? 关于获得PANEL内控件的个数的奇怪问题 从数据库取出来的值为什么不能显示在以“TextMode="Password"方式的textbox中 求助,同一个域中怎样在两台主机中共巷cookie 日期语法错误,应怎样解决 >< 服务器保存聊天记录的聊天室解决方案 就这么一丝不挂的暴露了
还是写程序导入?
{
fun();
}
private DataTable subtable(int i, int j, DataTable dt)
{
int t;
DataTable table = new DataTable();
for (t = 0; t < dt.Columns.Count; t++)
table.Columns.Add(dt.Columns[t].ColumnName.ToString());
for (t = i; t < j; t++)
table.Rows.Add();
for (t = 0; t < dt.Columns.Count; t++)
for (int r = i; r < j; r++)
table.Rows[r - i][t] = dt.Rows[r][t];
return table;
}
private void fun()
{
OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + Server.MapPath("Book1.xls"));
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet3$]",conn);
DataTable dt=new DataTable();
da.Fill(dt);
for (int i = 0; i < dt.Columns.Count; i++)
dt.Columns[i].ColumnName = dt.Rows[3][i].ToString();
dt=subtable(4,dt.Rows.Count,dt);
conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=D:\\lx.mdb");
string str = "create table tb(";
for (int i = 0; i < dt.Columns.Count; i++)
{
str = str + dt.Columns[i].ColumnName.ToString() + " ";
string p = dt.Columns[i].DataType.ToString();
str = str + p.Substring(7, p.Length - 7)+",";
}
str = str.Substring(0, str.Length - 1) + ")";
OleDbCommand cmd = new OleDbCommand(str, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
da=new OleDbDataAdapter("select * from tb",conn);
string p_columns = "", q_columnstype = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
p_columns = p_columns + dt.Columns[i].ColumnName.ToString() + (i < dt.Columns.Count - 1 ? "," : "");
q_columnstype = q_columnstype + "@" + dt.Columns[i].ColumnName.ToString() + (i < dt.Columns.Count - 1 ? "," : "");
}
str = "insert into tb(" + p_columns + ") values(" + q_columnstype + ")";
cmd = new OleDbCommand(str, conn);
for (int i = 0; i < dt.Columns.Count; i++)
{ cmd.Parameters.Add("@" + dt.Columns[i].ColumnName.ToString(),OleDbType.VarChar, 50, dt.Columns[i].ColumnName.ToString());
}
da.InsertCommand = cmd;
da.Update(dt);
}
楼上楼下的高手 教我
OleDbConnection conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0; data source=" + Server.MapPath("Book1.xls") + ";extended properties=excel 8.0;");
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
OleDbDataAdapter sqlada = new OleDbDataAdapter();
foreach (DataRow dr in dt.Rows)
{
string strSql = "Select * From [" + dr[2].ToString().Trim() + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, conn);
sqlada.SelectCommand = objCmd;
sqlada.Fill(ds, dr[2].ToString().Trim());
}
conn.Close();