求一个Excel导入数据库的代码,网上找的都不给力啊~~~

b0172716 2011-09-27 09:04:13
如题,要详细的,包括命名空间,前后台代码,最好有注释,呵呵,省的再百度了,网上找了好多,都有这样那样的问题,都不详细,大家谁有做过这个的,发下吧~~数据库为SQL2005
...全文
170 19 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
少多慢快 2011-09-28
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 net5354 的回复:]

]1.上传EXCEL文件到服务器 2.连接EXCEL 3.逐条插入SQL
if (FileUpload1.HasFile)//上传控件FileUpload1 {
string filenameid = System.DateTime.Now.ToString("yyyyMMddhhmmssff") + ".xls";//定义文件名为年:2008.xsl的格式
……
[/Quote]

可以用
PitTroll 2011-09-28
  • 打赏
  • 举报
回复
excel导入的话,是不是还要对excel中的内容做处理、做验证、做提示。如果直接导入那很简单的。但是如果要做特殊处理,那就要自己写业务逻辑判处理了,例如:有的不能为空的列是否输入了空值,有些需要验证的列是否格式不对,数据行是否出现重复,数据行是否与现有数据库中的内容出现重复等等。这些都是需要考虑的。金蝶的一些产品这些方面做的很不友好。
  • 打赏
  • 举报
回复

private void button3_Click(object sender, EventArgs e)
{
cleartb();

}

private void cleartb()
{
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
bool w = true;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "truncate table [Budget] truncate table [Budget_Detail]";
try
{
cmd.ExecuteNonQuery();
}
catch
{
w = false;
}
if (w)

{
label1.Text = "表已清空";
MessageBox.Show("表已清空","信息提示");
}

else
{ label1.Text = "清空失败"; MessageBox.Show("表清空失败", "信息提示"); }
}
private void cleartbing()
{
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
bool w = true;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "truncate table [Order]";
try
{
cmd.ExecuteNonQuery();
}
catch
{
w = false;
}
if (!w)
label1.Text = "处理数据失败";
}

private void button2_Click(object sender, EventArgs e)
{
this.openFileDialog2.InitialDirectory = "C:\\";
this.openFileDialog2.Filter = "excel文件(*.xls)|*.xls";
this.openFileDialog2.FilterIndex = 2;
this.openFileDialog2.RestoreDirectory = true;
this.openFileDialog2.Title = "打开excel文件";
if (openFileDialog2.ShowDialog() == DialogResult.OK)
{
string sss = openFileDialog2.FileName;
textBox2.Text = sss;
System.Data.DataTable dt;
//OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
//string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
// string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
bool success = true;
dt = GetExcelTable(sss);
//success = input(dt, conn, success, "Budget");//总额
success = input(dt, conn, success, "Budget_Detail");//明细
}
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void textBox1_TextChanged(object sender, EventArgs e)
{

}
}
}

哈哈
  • 打赏
  • 举报
回复

protected void AddExcel(DataSet ds, ref bool s)
{
try
{
System.Data.DataTable dt = ds.Tables[0];

// string fileName ="C:\\数据处理文件夹\\"+ Guid.NewGuid() + ".xls";
//string str = DateTime.Now.ToShortDateString();
// string[] strar=str.Split(' ');
// string str1 =strar[0]+strar[1];
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = "C:\\";
sf.Filter = "excel文件(*.xls)|*.xls";

if (sf.ShowDialog()==DialogResult.OK)
{
string fileName = sf.FileName; //"C:\\数据处理文件夹\\" +str+ ".xls";
Excel.Application excel = new Excel.ApplicationClass();

int rowIndex = 1;
int colIndex = 0;

excel.Application.Workbooks.Add(true);

foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}

excel.Visible = false;
// excel.ActiveWorkbook.s
excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excel.Save(fileName);

excel.Quit();
excel = null;

GC.Collect();//垃圾回收
}
}
catch
{
s = false;
}
// }

}


private void button1_Click(object sender, EventArgs e)
{
this.openFileDialog1.InitialDirectory = "C:\\";
this.openFileDialog1.Filter = "excel文件(*.xls)|*.xls";
this.openFileDialog1.FilterIndex = 2;
this.openFileDialog1.RestoreDirectory = true;
this.openFileDialog1.Title = "打开excel文件";
if (openFileDialog1.ShowDialog()==DialogResult.OK)

{
string sss = openFileDialog1.FileName;
textBox1.Text = sss;
System.Data.DataTable dt;
//OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
//string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
// string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
bool success = true;
dt = GetExcelTable(sss);
success = input(dt, conn, success,"Budget");//总额
// success = input(dt, conn, success, "Budget_Detail");//明细

}
}

private bool input(System.Data.DataTable dt, SqlConnection conn, bool success,string flag)
{
if (dt != null)
{
try
{

conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
List<string> L = new List<string>();


if (flag == "Budget")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[0].ToString() != "")
{
string s = "insert into [Budget] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "')";
// OleDbCommand cmd = new OleDbCommand( s,conn);
L.Add(s);
}
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[0].ToString() != "")
{
string s = "insert into [Budget_Detail] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "','" + dt.Rows[i].ItemArray[4] + "','" + dt.Rows[i].ItemArray[5] + "','" + dt.Rows[i].ItemArray[6] + "','" + dt.Rows[i].ItemArray[9] + "')";
// OleDbCommand cmd = new OleDbCommand( s,conn);
L.Add(s);
}
}
}

if (!OperateDatasWithTransaction(L))
{
success = false;
}

}
catch (Exception eer)
{
MessageBox.Show(eer.ToString());
success = false;
conn.Close();
}
if (success)
{
label1.Text = "导入成功";
if (flag == "Budget")
MessageBox.Show("预算资金总账导入成功!", "信息提示");
else
MessageBox.Show("预算资金明细导入成功!", "信息提示");

}
else
{
label1.Text = "导入失败";
if (flag == "Budget")
MessageBox.Show("预算资金总账导入失败!", "信息提示");
else
MessageBox.Show("预算资金明细导入失败!", "信息提示");
}
}
return success;
}
  • 打赏
  • 举报
回复

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace _8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 带事务的批量操作数据。
/// </summary>
/// <param name="sqlInsert">SQL</param>
/// <returns>操作是否成功</returns>
public static bool OperateDatasWithTransaction(List<string> sqlList)
{
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string item in sqlList)
{
myCommand.CommandText = item;
myCommand.ExecuteNonQuery();
}
myTrans.Commit();
return true;
}
catch (Exception)
{
myTrans.Rollback();
}
finally
{
myConnection.Close();
}
return false;
}
private System.Data.DataTable GetExcelTable(string uploadPath)
{
DataSet ds = new DataSet();
string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1 F2 F3
OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
try
{
Conn.Open();
string sql_str = "select * from [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
da.Fill(ds, "excel_data");
Conn.Close();
}
catch
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
return null;
}
finally
{
Conn.Dispose();
}

if (ds == null)
{
return null;
}

if (ds.Tables.Count < 1)
{
return null;
}

return ds.Tables[0];
}
永动bug制造机 2011-09-28
  • 打赏
  • 举报
回复
关注中。。。
风2013 2011-09-28
  • 打赏
  • 举报
回复
var str1 = string.Format("Select * from [{0}]", tableName+"A:E");
这一句 你自己看着办改 A:E 只读取五列 如果不需要限制 +"A:E" 就去掉这一段
风2013 2011-09-28
  • 打赏
  • 举报
回复
这是我目前用的方法 你不妨试一试
风2013 2011-09-28
  • 打赏
  • 举报
回复

public void DataSet()
{
string type2 = FileUpload1.FileName;
var type3 = type2.Substring(type2.LastIndexOf('.'));
if(type3.Equals(".xls") || type3.Equals(".xlsx"))
{
string newName = Server.MapPath("..//App_Data//Uploads//") + DateTime.Now.ToString("hhmmss") + ".xls";

FileUpload1.SaveAs(newName);
string connStr = string.Empty;

if (type3.Equals(".xlsx"))
{
connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + newName +
";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
}

else if (type3.Equals(".xls"))
{
connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + newName +
";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
OleDbConnection conn = new OleDbConnection(connStr);
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var tableName = dt.Rows[0][2].ToString().Trim();
var str1 = string.Format("Select * from [{0}]", tableName+"A:E");
OleDbDataAdapter oda = new OleDbDataAdapter(str1, conn);
DataSet ds = new DataSet();
oda.Fill(ds);
conn.Close();
File.Delete( newName );
return ds
}
CalvinR 2011-09-28
  • 打赏
  • 举报
回复
6楼的代码就挺好的
楼主参考一下吧
弦弦 2011-09-28
  • 打赏
  • 举报
回复

请安装office驱动。
public static DataSet ExcelToDS(string Pathm, string TableName, string type)
{
DataSet ds = new DataSet();
string strConn = "";
if (type == "excel2003")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Pathm + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
}
else if (type == "excel2007")
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + Pathm + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}
try
{
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", TableName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, TableName);
}
catch (Exception)
{
return ds;
}
return ds;
}
魂千 2011-09-28
  • 打赏
  • 举报
回复
想问问LZ怎么才算是给力?
蓬莱仙客 2011-09-28
  • 打赏
  • 举报
回复
楼主 度娘上面 可以教会你很多 如果你自己不用心去思考 那以后你永远不懂得如果去把别人的代码转换成你自己需要的 那么以后你有问题不可能每次都有人给你贴代码. 自己好好想想吧 思想才是最重要的 代码只是实现的一种方式而已
酷儿 2011-09-28
  • 打赏
  • 举报
回复
楼主 度娘上面 可以教会你很多 如果你自己不用心去思考 那以后你永远不懂得如果去把别人的代码转换成你自己需要的 那么以后你有问题不可能每次都有人给你贴代码. 自己好好想想吧 思想才是最重要的 代码只是实现的一种方式而已
net5354 2011-09-28
  • 打赏
  • 举报
回复
]1.上传EXCEL文件到服务器 2.连接EXCEL 3.逐条插入SQL
if (FileUpload1.HasFile)//上传控件FileUpload1 {
string filenameid = System.DateTime.Now.ToString("yyyyMMddhhmmssff") + ".xls";//定义文件名为年:2008.xsl的格式
string serverpath = Server.MapPath("~/xls/") + filenameid;//保存路径
FileUpload1.PostedFile.SaveAs(serverpath);//保存上传的Excel文件

//连接Excel文件,读入myDstaSet
String strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", serverpath);//excel2003的
OleDbConnection Excel_conn = new OleDbConnection(strConnectionString);
Excel_conn.Open();
DataTable dtExcelSchema = Excel_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//建立连接Excel的数据表
string SheetName = "";
SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();//取出第一个工作表我名称
Excel_conn.Close();
//Label1.Text = SheetName;

string query = "SELECT * FROM " + "[" + SheetName + "]";//查询Excel字符串
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, Excel_conn);
DataSet myDataSet = new DataSet();
Excel_conn.Open();
oleAdapter.Fill(myDataSet, "Excel_Sheet1");
Excel_conn.Close();

GridView1.DataSource = myDataSet.Tables["Excel_Sheet1"];
GridView1.DataBind();

sqlcon = new SqlConnection(strCon);//srtCom是连接数据库字符串,自己写
for (int i = 0; i <= myDataSet.Tables["Excel_Sheet1"].Rows.Count - 1; i++)
{
string intostr = "insert into chengji" +//....插入语句自己写

sqlcon.Open();
sqlcom = new SqlCommand(intostr, sqlcon);
sqlcom.ExecuteNonQuery();//添加插入
sqlcon.Close();

//....
}

}
GHOSTSEA 2011-09-28
  • 打赏
  • 举报
回复
学习中········
szjarvis 2011-09-28
  • 打赏
  • 举报
回复
正在用的,参考一下:
protected void btnImport_Click(object sender, EventArgs e)
{
OleDbConnection xlsConn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + Server.MapPath("xx.xls") + "; Extended Properties = 'Excel 8.0; HDR=YES; IMEX=1'");
xlsConn.Open();

string xlsSQL = "select * from [sheet1$]";
OleDbCommand xlsCmd = new OleDbCommand(xlsSQL, xlsConn);
OleDbDataReader dr = xlsCmd.ExecuteReader();

string strSQL = "", strErr = "";
ArrayList arySQL = new ArrayList();
while (dr.Read())
{
#region 初始化数据
string v1 = (dr[1].ToString().Trim() == "") ? "" : dr[1].ToString();
string v2 = (dr[2].ToString().Trim() == "") ? "" : dr[2].ToString();
string v3 = (dr[3].ToString().Trim() == "") ? "" : dr[3].ToString();
string v4 = (dr[4].ToString().Trim() == "") ? "" : dr[4].ToString();
string v5 = (dr[5].ToString().Trim() == "") ? "" : dr[5].ToString();
string v6 = (dr[6].ToString().Trim() == "") ? "" : dr[6].ToString();
string v7 = (dr[7].ToString().Trim() == "") ? "" : dr[7].ToString();
string v8 = (dr[8].ToString().Trim() == "") ? "" : dr[8].ToString();
string v9 = (dr[9].ToString().Trim() == "") ? "" : dr[9].ToString();
string v10 = (dr[10].ToString().Trim() == "") ? "" : dr[10].ToString();
string v11 = (dr[11].ToString().Trim() == "") ? "" : dr[11].ToString();
string v12 = (dr[12].ToString().Trim() == "") ? "" : dr[12].ToString();
float v13 = (dr[13].ToString().Trim() == "") ? 0 : Math.Abs(Convert.ToSingle(dr[13].ToString()));
string v14 = (dr[14].ToString().Trim() == "") ? "" : dr[14].ToString();
string v15 = (dr[15].ToString().Trim() == "") ? "" : dr[15].ToString();
string v16 = (dr[16].ToString().Trim() == "") ? "" : dr[16].ToString();
string v17 = (dr[17].ToString().Trim() == "") ? "" : dr[17].ToString();
string v18 = (dr[18].ToString().Trim() == "") ? "" : dr[18].ToString();
#endregion

strSQL = "insert into asset_asset(asset_typeid,asset_sclassid,asset_no,asset_name,asset_model,asset_sn,asset_collocate,asset_pinpai,asset_providerid,asset_buyer,asset_buydate,asset_buyprice,asset_memo,asset_keeper,asset_keepdate,asset_statusid,asset_empno,asset_adddate)";
strSQL += " values('" + v1 + "','" + v2 + "','" + v3 + "','" + v4 + "','" + v5 + "','" + v6 + "','" + v7 + "','" + v8 + "','" + v9 + "','" + v10 + "','" + v11 + "','" + v12 + "'," + v13 + ",'" + v14 + "','" + v15 + "','" + v16 + "','" + v17 + "')";
arySQL.Add(strSQL);
}

dr.Close();
dr.Dispose();
xlsConn.Close();
xlsConn.Dispose();
}

DbConnSql.ExcuSS(arySQL); // 此处的DbConnSql是一个类,你可以自己写一个执行sql的代码。
dalmeeme 2011-09-27
  • 打赏
  • 举报
回复

62,244

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧