string strPath=MapPath("ImportData");
string FileName=strPath+"\\"+"PIWS.xls";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties=Excel 8.0;";
string sql = "Select '' as ID,Item,'' as SO,Date,Line,Qty,SO from [PIWS$]";
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( connectionString );
System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter( sql, connection );
System.Data.DataSet dataSet = new DataSet();
connection.Open();
dataAdapter.Fill( dataSet, "PIWS" );
connection.Close();
string FileName=strPath+"\\"+"PIWS.xls";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties=Excel 8.0;";
string sql = "Select '' as ID,Item,'' as SO,Date,Line,Qty,SO from [PIWS$]";
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection( connectionString );
System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter( sql, connection );
System.Data.DataSet dataSet = new DataSet();
connection.Open();
dataAdapter.Fill( dataSet, "PIWS" );
connection.Close();
在数据库开发/sql server讨论区搜索一下,很多这方面讨论
未处理的“System.ArgumentException”类型的异常出现在 system.data.dll 中其他信息:对象不是 ADODB.Recordset 或 ADODB.Record。
==================================================================================
代码如下:
private void button1_Click(object sender, System.EventArgs e)
{
string cnstr=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\1.xsl;Extended Properties=Excel 8.0";
OleDbConnection cn=new OleDbConnection(cnstr);
OleDbDataAdapter da=new OleDbDataAdapter("SELECT * FROM [dd$]", cn);
try
{
DataTable dt=new DataTable();
da.Fill(dt,"dd");
string s=dt.Rows[0].RowState.ToString();
textBox1.Text=s;
}
finally
{
cn.Close();
}
}
app = new Excel.Application();Excel.Workbook oBook;
Workbooks oBooks = app.Workbooks;
object missing = Missing.Value;// 打开一个Excel文件
oBook = oBooks.Open(filePath, missing, missing, missing, missing,
missing, missing, missing, missing,
missing, missing, missing, missing);Worksheet oSheet = (Worksheet)oBook.Worksheets.get_Item("Stat");
System.Data.DataTable table = new System.Data.DataTable();// 添加table中的列
foreach(string strHeader in arrColumnName)
{
string strColumn = "";
switch(strHeader)
{
case "药品编码":
strColumn = "MediID";
break;
case "药品名称":
strColumn = "MediName"; // MediName暂时没用
break;
case "公司名称":
strColumn = "ComName";
break;
case "价格":
strColumn = "Price";
break;
case "日期":
strColumn = "DealDate";
break;
case "数量":
strColumn = "Amount";
break;
case "金额":
strColumn = "Total";
break;
case "数据类型":
strColumn = "DealType";
break;
}
table.Columns.Add(strColumn);
}int columnCount = arrColumnName.GetLength(0);
Range rangeCell = null;// 取数据从第二行开始,忽略第一行表头
for(int i = 2; ; i ++)
{
rangeCell = (Range)oSheet.Cells[i,1];
if(rangeCell.Text.ToString() == "")
{
break;
} System.Data.DataRow row = table.NewRow();
for(int j = 1; j <= columnCount ; j++)
{
rangeCell = (Range)oSheet.Cells[i,j];
row[j-1] = rangeCell.Value;
}
table.Rows.Add(row);
}ds.Tables.Add(table);
app.Quit();
app = null;
OdbcConnection cn=new OdbcConnection(cnstr);
OdbcDataAdapter da=new OdbcDataAdapter("select * from [sheet1$]",cn);