c#把excel导入到SQL2005数据库

爱静相随 2010-04-10 04:41:17
c#把excel导入到SQL2005数据库?各位帮帮忙
...全文
329 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
justiceyves 2010-08-26
  • 打赏
  • 举报
回复
我觉得这还是很简单的 lz应该还是初学者吧 没事搞搞AE开发 对程序理解很好的!
Rain_Franklin 2010-08-26
  • 打赏
  • 举报
回复
学习....mark..
今天正好用上了..哈哈
梦想橡皮擦 2010-08-26
  • 打赏
  • 举报
回复
很好 学习了
捷哥1999 2010-08-26
  • 打赏
  • 举报
回复
1、首先读取出Excel数据
2、将Excle数据写入SqlServer数据库。
爱静相随 2010-08-26
  • 打赏
  • 举报
回复
学习了,学学各位!
  • 打赏
  • 举报
回复
上代码:
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;

namespace _8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
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];
}
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;
OleDbConnection conn = new OleDbConnection(constr);
bool success = true;
dt = GetExcelTable(sss);
success = input(dt, conn, success);

}
}

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

conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;




for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[0].ToString() != "")
{
string s = "insert into [Order] 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] + ")";
// OleDbCommand cmd = new OleDbCommand( s,conn);
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
}

}
catch (Exception eer)
{
MessageBox.Show(eer.ToString());
success = false;
conn.Close();
}
if (success)
label1.Text = "导入成功";
else
label1.Text = "导入失败";
}
return success;
}
元元0123 2010-08-24
  • 打赏
  • 举报
回复
很好,谢谢
hubyl 2010-05-20
  • 打赏
  • 举报
回复
学习了。谢谢
booxood 2010-04-11
  • 打赏
  • 举报
回复
mark 下....
爱静相随 2010-04-11
  • 打赏
  • 举报
回复
谢谢各位!
wuyq11 2010-04-10
  • 打赏
  • 举报
回复
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\a.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
上传excel文档到服务器,再打开服务器上excel导入数据到数据库。
遍历excel,实现数据集更新,插入数据到数据库
using(OleDbConnection myConn = new OleDbConnection(""))
{
myConn.Open();
string str = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(str, myConn);
DataSet ds= new DataSet();
myCommand.Fill(ds);
myConn.Close();
}
liujiabjaccp 2010-04-10
  • 打赏
  • 举报
回复
日期 编号 姓名 底薪 考核 奖励 津贴 加班 值班 绩效 合计
200905 10001 张三 1100 -80 40 150 40 800 2050
200905 10002 李四 1100 50 150 120 900 2320
200905 10003 王五 1200 -50 100 150 780 2180
200905 10004 马林 1005 60 150 90 1000 2305
200905 10005 韩非 1000 -10 80 150 160 900 2280
200905 10006 翰林 1200 -80 120 150 140 950 2480
这样的excel表
liujiabjaccp 2010-04-10
  • 打赏
  • 举报
回复
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
//51a/spx
public partial class _Default : System.Web.UI.Page
{
string strConn = "Data Source=.;DataBase=hhaspx;Uid=sa;Pwd=sa"; //链接SQL数据库
/// <summary>
/// 初始化先查询 数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
SqlDataAdapter sda = new SqlDataAdapter("select * from hhaspx_gz", cn);
DataSet ds = new DataSet();
sda.Fill(ds, "hhaspx_gz");
this.GridView1.DataSource = ds.Tables["hhaspx_gz"];
this.GridView1.DataKeyNames = new string[] { "hhaspx_id" };
this.GridView1.DataBind();

}
/// <summary>
/// 查询EXCEL电子表格添加到DATASET
/// </summary>
/// <param name="filenameurl">服务器路径</param>
/// <param name="table">表名</param>
///


//保存
public DataSet ExecleDs(string filenameurl, string table)
{
//连接串
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
//打开
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
//并返回Dateset
odda.Fill(ds, table);
conn.Close();
return ds;


}
/// <summary>
/// 导入按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
///如果没文件
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
//如果有文件
/*
重要的地方了

*/
//System.IO.Path.GetExtension获得文件的扩展名
//强大!!!
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
//判断文件类型
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
//获取Execle文件名 DateTime日期函数 以保存在文件夹下
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;
//保存文件路径
string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
//保存文件
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
//调用方法
DataSet ds = ExecleDs(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 hhaspx_rq = dr[i]["日期"].ToString();//日期 excel列名【名称不能变,否则就会出错】
string hhaspx_bh = dr[i]["编号"].ToString();//编号 列名 以下类似
string hhaspx_xm = dr[i]["姓名"].ToString();
string hhaspx_dx = dr[i]["底薪"].ToString();
string hhaspx_kh = dr[i]["考核"].ToString();
string hhaspx_jl = dr[i]["奖励"].ToString();
string hhaspx_jt = dr[i]["津贴"].ToString();
string hhaspx_jb = dr[i]["加班"].ToString();
string hhaspx_zb = dr[i]["值班"].ToString();
string hhaspx_jx = dr[i]["绩效"].ToString();
string hhaspx_hj = dr[i]["合计"].ToString();
//判断是否存在相同数据 以日期和人名为准
string sqlcheck = "select count(*) from hhaspx_gz where hhaspx_rq='" + hhaspx_rq + "'And hhaspx_xm='" + hhaspx_xm + "'"; //检查用户是否存在
SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
//如果小于1 可以存
int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
if (count < 1)
{
//插入语句
string insertstr = "insert into hhaspx_gz (hhaspx_rq,hhaspx_bh,hhaspx_xm,hhaspx_dx,hhaspx_kh,hhaspx_jl,hhaspx_jt,hhaspx_jb,hhaspx_zb,hhaspx_jx,hhaspx_hj) values('" + hhaspx_rq + "','" + hhaspx_bh + "','" + hhaspx_xm + "','" + hhaspx_dx + "','" + hhaspx_kh + "','" + hhaspx_jl + "','" + hhaspx_jt + "','" + hhaspx_jb + "','" + hhaspx_zb + "','" + hhaspx_jx + "','" + hhaspx_hj + "')";

SqlCommand cmd = new SqlCommand(insertstr, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
else
{
Response.Write("<script>alert('内容重复!禁止导入');location='default.aspx'</script></script> ");
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
}

cn.Close();

}
}

110,499

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

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