大家好:请问各位如何实现在一个网页中通过一个浏览控件上传本地Excel表格,来实现直接将表格的内容导入到数据库中?
说明:1)数据库中的表已经存在
2)数据库中表的字段Excel表格中可能只是部分的,没有全部字段。
3)语言C#的,数据库sql2005,开发环境VS2005.
谢谢各位,在线等!
说明:1)数据库中的表已经存在
2)数据库中表的字段Excel表格中可能只是部分的,没有全部字段。
3)语言C#的,数据库sql2005,开发环境VS2005.
谢谢各位,在线等!
调试欢乐多
嘿嘿,不过还没有完成.
暂时给你一个普通的类
#region 导入IsslueLog
/// <summary>
/// 导入IsslueLog
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="isCreate">导入方式 (覆盖/添加)</param>
/// <returns></returns>
public string IssueLogImport(string filePath, bool isCreate)
{
string message = "";
//打开excel文件
OleDbConnection Excelconn =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=NO;IMEX=1'" + ";");
System.Data.OleDb.OleDbCommand cmd = null;
System.Data.OleDb.OleDbDataReader rdr = null; Dictionary<String, String> dict = new Dictionary<String, String>();
#region 声明变量
string IssueNo;
string IssueStatus = "";
string Originator = "";
string OpenDate = "";
string Phase = "";
string ModuleName="";
string FunctionName = ""; #endregion
try
{
//打开excel
Excelconn.Open();
cmd = Excelconn.CreateCommand(); DataTable dt = Excelconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = "[" + dt.Rows[0]["TABLE_NAME"] + "]";
cmd.CommandText = "SELECT * FROM " + sheetName + "";
rdr = cmd.ExecuteReader();
rdr.Read();
ProjectCode = rdr.GetValue(2).ToString(); //项目代码
rdr.GetValue(6).ToString(); //项目名称
//ProjectCode验证
if(ProjectCode.Trim().Equals(string.Empty) || !ProjectExist(ProjectCode))
{
message = " ProjectCode不存在";
return message;
}
//---------------------------------------------------------------
if (!isCreate)
{
DeleteIssue(ProjectCode);
UpdateSEQ("Project",ProjectCode);
}
//---------------------------------------------------------------
rdr.Read();
while (rdr.Read())
{
#region 赋值
//IssueNo=CommonLib.CommonFun.sp_GetSequence("Project", ProjectCode).ToString();
//IssueNo = rdr.GetValue(0).ToString();
IssueStatus = rdr.GetValue(1).ToString();
Originator = rdr.GetValue(2).ToString();
OpenDate = rdr.GetValue(3).ToString();
Phase = rdr.GetValue(4).ToString();
ModuleName = rdr.GetValue(5).ToString();
FunctionName = rdr.GetValue(6).ToString();
Description = rdr.GetValue(7).ToString();
Priority = rdr.GetValue(8).ToString();
IsInternal =changeBit(rdr.GetValue(9).ToString()); if (!rdr.GetValue(10).ToString().Trim().Equals(string.Empty))
{
Deadline = rdr.GetValue(10).ToString();
}
PIC = rdr.GetValue(11).ToString();
AnswerDescription = rdr.GetValue(12).ToString();
ResolvedBy = rdr.GetValue(13).ToString();
if (!rdr.GetValue(14).ToString().Trim().Equals(string.Empty))
{
ResolvedDate = rdr.GetValue(14).ToString();
}
Comments = rdr.GetValue(15).ToString();
CCR = changeBit(rdr.GetValue(16).ToString());
if (!rdr.GetValue(17).ToString().Trim().Equals(string.Empty))
{
Cost = rdr.GetValue(17).ToString();
}
#endregion
if (rdr.GetValue(0).ToString().Trim().Equals(string.Empty) && IssueStatus.Trim().Equals(string.Empty) && Originator.Trim().Equals(string.Empty))
{
break;
}
#region 添加参数
IssueNo = CommonLib.CommonFun.sp_GetSequence("Project", ProjectCode).ToString();
dict.Clear();
dict.Add("@IssueNo", IssueNo);
dict.Add("@IssueStatus", IssueStatus);
dict.Add("@Originator", Originator);
dict.Add("@OpenDate", OpenDate);
dict.Add("@Phase", Phase);
dict.Add("@ModuleName", ModuleName);
dict.Add("@FunctionName", FunctionName);
dict.Add("@Description", Description);
dict.Add("@Priority", Priority);
dict.Add("@Deadline", Deadline);
dict.Add("@PIC", PIC);
dict.Add("@AnswerDescription", AnswerDescription);
dict.Add("@ResolvedBy", ResolvedBy);
dict.Add("@ResolvedDate", ResolvedDate);
dict.Add("@Comments", Comments);
dict.Add("@CCR", CCR);
dict.Add("@Cost", Cost);
dict.Add("@ProjectCode", ProjectCode);
dict.Add("@IsInternal", IsInternal);
#endregion
//打开事务
SqlTransaction trans = SqlHelperExtension.OpenTransaction();
//导入一条记录
dal.InseertIssueLog(dict);
//提交事务
SqlHelperExtension.CommitTransaction(trans); }
}
catch (Exception etc)
{
//回滚事务
//SqlHelperExtension.RollbackTransaction(trans);
throw etc;
}
finally
{
rdr.Close();
Excelconn.Close();
//conn.Close();
}
if (message=="")
{
message = "导入成功!";
}
return message;
}
#endregion
参考
参考
2.应用如下函数将电子文件里面的数据读取到datatable里面
/// <summary>
/// 返回打开的电子表格中的某个表并形成datatable
/// </summary>
/// <param name="path">文件绝对路径</param>
/// <param name="target">要打开的表名</param>
/// <returns>返回DATATABLE数据库集</returns>
private DataTable ReadExcel(string path,string target)
{
DataSet myDataset = new DataSet();
string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionstring);
OleDbCommand myOleDbCommand;
myOleDbCommand = new OleDbCommand("SELECT * FROM [" + target + "]",myOleDbConnection);
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
try
{
myData.Fill(myDataset);
}
catch(Exception ex)
{
throw ex;
}
return myDataset.Tables[0];
}
3.逐一读取上述DATATABLE,并至数据库中判断,有存在则不写入,不存在则写入
string myValue = "";
for(int i=0;i<myTab.Rows.Count;i++)
{
myValue = myTab.Rows[i][0].ToString();
if(bolBeing(myValue) == false)
{
//写插入SQL语句,将此数据插入至数据库中
}
else
{
//将已经存在的数据输出页面进行查看.
}
}
//检测传递的值是否存在数据库中,是则返回true,不存在则返回false
private bool bolBeing(string myValue)
{
string sql = "select count(*) from 表名 where 字段 = '" + myValue + "' ";
//求取返回值
if(返回值 == "0")
{
return(false);
}
else
{
return(true);
}
}
有很多办法呀,这是导入到sql的,你改一下就可以了。
C# code public DataSet ExcelToDS(string Path)
{
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
object missing = Missing.Value;
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = false;
try
{
excelApp.Workbooks.Open(path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Excel.Worksheet ws = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[1];
string sheetName = ws.Name; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null; strExcel = "select * from [" + sheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds);
conn.Close();
excelApp.Workbooks.Close();
excelApp.Quit();
excelApp = null;
return ds;
}
catch
{
MessageBox.Show("没有关闭的EXCEL进程,请关闭后重新导入文件", "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
KillProcess("EXCEL");
return null;
}
}