string fileExcel, filePath, fileName, strLine, sql; FileStream objFileStream; StreamWriter objStreamWriter; Random nRandom = new Random(DateTime.Now.Millisecond); SqlConnection cnn = new SqlConnection("data source=(local);initial catalog=chinapackage;user id=sa;password=;"); //Create a random file name. fileExcel = "t" + nRandom.Next().ToString() + ".xls"; //Set a virtual folder to save the file. //Make sure to change the application name to match your folder. filePath = Server.MapPath("\\StartExcel"); fileName = filePath + "\\" + fileExcel; //Use FileSystemObject to create the .xls file. objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write); objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode); //Use a DataReader object to connect to the Pubs database. cnn.Open(); //sql = "select au_id,au_lName,au_fname,phone,address,city,state,zip,contract from authors"; sql = "select * from tbdownload"; SqlCommand cmd = new SqlCommand(sql, cnn); SqlDataReader dr; dr = cmd.ExecuteReader(); //Initialize the string that is used to build the file. strLine = ""; //Enumerate the field names and the records that are used to build //the file. for (int i = 0; i <= dr.FieldCount-1; i++) { strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9); } //Write the field name information to the file. objStreamWriter.WriteLine(strLine); //Reinitialize the string for data. strLine = ""; //Enumerate the database that is used to populate the file. while (dr.Read()) { for (int i = 0; i <= dr.FieldCount-1; i++) { strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine=""; } //Clean up. dr.Close(); cnn.Close(); objStreamWriter.Close(); objFileStream.Close(); //Include a link to the Excel file. HyperLink1.Text="Open Excel"; HyperLink1.NavigateUrl=fileExcel;
一个通过oledb数据engine访问excle
一个办法通过调用office的com对象来实现,本人推荐第二种办法。
OleDbConnection odc=new OleDbConnection(connstr);
string selectstr="select * from TestData";
OleDbDataAdapter oda=new OleDbDataAdapter();
odc.Open();
oda.SelectCommand=new OleDbCommand(selectstr);
oda.SelectCommand.Connection=odc;
oda.SelectCommand.ExecuteNonQuery();
DataSet ds=new DataSet();
oda.Fill(ds);
odc.Close();
this.dataGrid1.DataSource=ds;
this.dataGrid1.SetDataBinding(ds,"");Test.xls中有一个sheet,名为TestData,我想用上面的程序将数据读到DataSet中,然后绑定在DataGrid上,但执行到oda.SelectCommand=new OleDbCommand(selectstr);时系统报错,我想问链接一个excel文件的方法是这样么?是不是我的连接字符串中的Provider不应该写Microsoft.Jet.OLEDB.4.0?按照我的方法能作出来么?谢谢!
string selectstr="select * from `" &文件路径& "`.`TestData` `TestData`";
Dim myOleDbConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _dbFile & _
";Extended Properties=Excel 8.0;")
Dim myOleDbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM [联系人$]", myOleDbConnection)
Dim myData As OledbDataAdapter = New OledbDataAdapter(myOleDbCommand)
myData.Fill(_address) myOleDbCommand.CommandText = "SELECT * FROM [客户$]"
myData.Fill(_customer) myOleDbCommand.CommandText = "SELECT * FROM [合作单位$]"
myData.Fill(_collaborator) dgMyData.DataSource = _address
dgMyData.Update() myOleDbCommand.Dispose()
myOleDbConnection.Dispose() End Sub Private Sub bnSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnSelectFile.Click
If dlgOpenExcel.ShowDialog() = DialogResult.OK Then
BindData()
Button1.Visible = True
Button2.Visible = True
Button3.Visible = True
End If
End Sub这个是通过oledb来操作的代码
通过com来操作的到msdn上查一下,我现在没有