日期:2014-05-18  浏览次数:20765 次

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&qu