c# 客户端 上传excel数据到sql，弄了一晚都弄不好,该如何解决

c# 客户端 上传excel数据到sql，弄了一晚都弄不好
 
  现在是 需要把一些xls文件上传到数据库并新建新表，表的列名结构不一样。该怎么做？我在网上搜了一晚，看到opendatasourse的方法挺好用的，可是网上都是直接在sqlserver操作的。
下面这个代码能实现吗？

C# code

BaseClass.SqlClass sqlclass = new HRP.BaseClass.SqlClass();                         SqlConnection con = sqlclass.SqlConBind();                         con.Open();                         SqlCommand scd = new SqlCommand("SELECT   * INTO NEWTABLE OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=c:\a.xls',sheet1$)", con);                         scd.CommandTimeout = 20;                         scd.ExecuteNonQuery();                         con.Close();

报错信息：未处理的“System.Data.SqlClient.SqlException”类型的异常出现在 System.Data.dll 中。其他信息: 关键字 'OPENROWSET' 附近有语法错误。
  刚开始摸索编程觉得它很难哦，遇到问题的时候很多时候找不到方向。有些气馁。熬夜好伤哦，为什么男孩子那么喜欢熬夜？不是很熟悉csdn的操作，如果哪里不对，请帮我指出。

[解决办法]
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using ExcelApplication=Microsoft.Office.Interop.Excel.Application; 

private void button2_Click(object sender, System.EventArgs e)
{
string message="确认导入?";
string caption="导入对话框";
int wxc=1;
int row=2;
string wFinterid;
string combo="";
bool result=false;
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult rResult;
rResult=MessageBox.Show(this,message,caption,buttons);

if(rResult==DialogResult.Yes)
{
if ((comboBox1.Text!="")&&(comboBox2.Text!="") &&(textBox1.Text!="") )
combo=comboBox1.Text.ToString();
else
{
MessageBox.Show("仓库和仓位和导入文件路径选择不能为空");
return;
}

this.Cursor=Cursors.WaitCursor;
ExcelApplication excel=new ExcelApplication();
excel.Visible=true;
object missing=System.Reflection.Missing.Value;
try
{

excel.Workbooks.Open(textBox1.Text,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
Worksheet worksheet=(Worksheet)excel.ActiveSheet;
Range rELNO=worksheet.get_Range("A"+row,Missing.Value);
Object oELNO=rELNO.Select();
string wELNO=excel.ActiveCell.Text.ToString();


string eSql="insert into icstockbillentry(fbrno,finterid,fentryid,fitemid,fqtymust,fqty,fprice,"+
"famount,funitid,fauxprice,fauxqty,fauxqtymust,fqtyactual,fauxqtyactual,"+
"fdcspid,fdcstockid,fentryselfc0230)" +
"values('0',"+wFinterid+","+wxc+","+eFitemid+","+wauxqtymust+","+wauxqtymust+","+welnoprice+","+tmount+","+wfunitid+","+welnoprice+","+wauxqtymust+","+wauxqtymust+",0,0,"+cwfspid+","+cwfitemid+","+wreason.ToString()+")";
//用于访问数据库
conndatabase conne=new conndatabase(cserver,cdatabase,cuser,cpwd);
conne.ExecuteSql(eSql); 
wxc++;
wRow++;
rELNO=worksheet.get_Range("A"+wRow,Missing.Value);
oELNO=rELNO.Select();
wELNO=excel.ActiveCell.Text.ToString();

}

}
else

{
MessageBox.Show("请重新检查导入文件内容");
return;
}

}

catch(Exception ex)
{
MessageBox.Show(ex.Message) ;
this.Cursor=Cursors.Default; 

}
}


else
{
System.Windows.Forms.Application.Exit();
}
this.Cursor=Cursors.Default; 
}

加上用于访问数据库的类
using System;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;


namespace klose
{
/// <summary>
/// conndatabase 的摘要说明。
/// </summary>
public class conndatabase
{
private SqlConnection Connstr;
private SqlDataAdapter ConnAdapter;
private SqlCommand Strcommand;
string mConnString;
public conndatabase(string cserver,string cdatabase,string cuid,string cpwd)
{
//
// TODO: 在此处添加构造函数逻辑
//
Connstr=new SqlConnection(); 
mConnString="server="+cserver+";database="+cdatabase+";uid="+cuid+";pwd="+cpwd+"";

Connstr.ConnectionString=mConnString;
ConnAdapter=new SqlDataAdapter();
Strcommand=new SqlCommand(); 

 
}
public void Open()
{
try
{
if(Connstr.State != ConnectionState.Open)
Connstr.Open();
}
catch(System.Exception e)
{
throw new System.Exception("不能打开" + e.Message);
}
}

public bool ExecuteSql(string SQL)
{
try
{
if(Connstr.State!=ConnectionState.Open)
{
Connstr.Open();
}
Strcommand = Connstr.CreateCommand();
Strcommand.CommandText =SQL;
int i=Strcommand.ExecuteNonQuery();
Connstr.Close();
return true;
}
catch(System.Exception)
{
if(Connstr.State!=ConnectionState.Closed)
Connstr.Close();
return false;
}
}

public SqlDataReader ExecuteReaderSqls(string SQL)
{
try
{

if (Connstr.State !=ConnectionState.Open)
Connstr.Open();
SqlCommand sqlExectueCommand = new SqlCommand();
sqlExectueCommand.CommandText =SQL;
sqlExectueCommand.Connection = Connstr;
SqlDataReader myDataReader = sqlExectueCommand.ExecuteReader();
return myDataReader;
}
catch(System.Exception e)
{
if(Connstr.State!=ConnectionState.Closed)
Connstr.Close();
throw new SystemException("不能打开"+ e.Message);
}
}
public void close()
{
if (Connstr.State!=ConnectionState.Closed)
{
Connstr.Close(); 
}

}
}
}

你需要增加openfiledialog1,要分三部分，一部分是要打开EXCEL读取数据，还有一个就是访问数据库的类，再就是插入数据。

[解决办法]
如果数据库是SQL2005,SQL语句这样写

SQL code

SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS ID,    *,    GETDATE() AS ACTDTTM INTO newtableFROM OPENDATASOURCE( 'MICROSOFT.JET.OLEDB.4.0','DATA SOURCE="C:\a.xls";USER ID=;PASSWORD=;EXTENDED PROPERTIES=EXCEL 8.0')...[SHEET1$] 

 

查看更多 下一篇