|
Hi there, Great Job !!!the code works perfect !!!
I have one question, how can i update an excell file that is shared among other users ???
The code you wrote DOES update the file when it's in shared mode, BUT I have to close the file and open it again to see the changes. For some reason it won't refresh automatically. Can you help me with this ??
Thanks in advance.
Facu20yo.
modified on Sunday, April 19, 2009 10:04 AM
|
|
|
|
|
i trying to read and write data from/to excel file row by row.how i can identified row and column.your code given only to read the entire data.please help me...
|
|
|
|
|
Hi
Actually I am not very clear with your requirement. I have two points coming in my mind. Please confrim are you looking the solution corresponding to that points or any other requirement you have.
1. Are you want to constantly conecting to Excel sheet and read the row one by one.
2. Or you want to read the complete data from Excel at one shot and use the data one by one with the help of Binding Manager. For e.g. in you application you want to display the data in some form and move between different records by pressing the Next, Previous button on the form.
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
actually i'm trying to read data from cell and use it for calculation in my program.i have a lot of data in excel file.
i want to read data from row 1 and multiple column.for example : i read data from row 1 and column A to P.i need to read the A1 and do the calculation.read data B1 and do the calculation and so on...
i think i should put some looping.but i don't know how??please help me...
|
|
|
|
|
by the way bro, im Paul Jacquez, 2nd year student SSCT Surigao City, Philippines. i want studying vb.net bro because i want a programmer someday bro, plz help me bro. i want codes edit,delete and search with use of excel as database bro. plz help me bro.?
Thank you bro. God Bless U.
|
|
|
|
|
Hi Paul
Please check the code given below which helps you to edit and update the record in Excel. Rest of the code same as I was posted on website.
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Update [Sheet1$] set " & _
"FirstName=@FName, LastName=@LName, Age=@Age, Phone=@Phone where " & _
"FirstName=@Name"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
pram = olecomm1.Parameters.Add("@Name", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram.SourceVersion = DataRowVersion.Original
If IsNothing(ds) = False Then
dr = ds.Tables(0).Rows(0)
dr.BeginEdit()
dr("FirstName") = "Bunty"
dr("LastName") = "Aggarwal"
dr("Age") = 26
dr("Phone") = 98989898
dr.EndEdit()
oleadpt.UpdateCommand = olecomm1
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
The above code shows that how you can update the first row from dataset, you can customize your code to delete the specific row from the dataset.
As far as delete is concerned, it is currently not allowed in Excel using code but you can achive the same by delete the old excel file and writing the new one.
In case of any query please feel free to contact me.
Thanks
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
base in your program about "TestExcel2" bro, i need your help bro on how to edit the existing record bro, using vb.net bro. plz help me bro?
|
|
|
|
|
Hi bro,i have a question how to delete data in excel 2007 using vb.net?..pls reply with this codes thank you....and god bless...
|
|
|
|
|
Hi bro, wow very nice bro like it. Thank you so much bro, i really appreciate your code project "Reading and writing excel file using VB.NET."
|
|
|
|
|
Please remove yourself from the computer programming field, jesus.
|
|
|
|
|
Thanks for the suggention. I will definatly think about this, but could you please let me know why you want me to quite from my job. If you are having some issue with the code then let me know. I will try my best to reslove your issue.
Regards
Raman Tayal
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Because the code you posted doesn't not run "out of the box". What's the point of posting code that doesn't work? When I say doesn't work, I should be able to download a code example and it should run without an issue.
|
|
|
|
|
Hi
I really appologies for the inconvenience caused. I forget to include the sample excel file in the source code. This code is running perfectly and thousands for users are using this code. As of now to run this code please create an excel file on c drive name as test.xls. Add the four column in the excel as shown in the image given in the article.
e.g.: -
1. FirstName
2. LastName
3. Age
4. Phone.
Now try to run the code, this should work as expected. If you are still facing any issue please feel free to contact me.
Thanks
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks bro, don't listen to lazy people that instead of critizicing should thank you for your time uploading this solution.
Regards,
|
|
|
|
|
Thanks for the appriciation
Raman Tayal
Microsoft Certified Technology Specialist in BizTalk Server 2006
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Thanks alot, I love it when people post code that simple doesn't run out of the box. Please, remove yourself from the field.
|
|
|
|
|
Please reply me ASAP. Thanks
Hi, I used your code for select, insert data in excel.
I implemented it and runs successfully.
If number of rows in dataset is less, i.e. aroud 100 then it works fast. But I have more than 2000 rows in my dataset and the mathod oleDataAdepter.InsertCommand = cmdInsertCommand is taking too much time to write thode data in excel. it takes around 10 minutes.
whereas Time taken when I manually loop throw my dataset and wrte data using wrksheet.Range("A" & rowNum).Value = dsPlan.Tables(0).Rows(i)("mphId") is arond 3-4 minutes.
I thought your code will work fast as it does not involves looping. and use Inser, Update methods of oleDBComman. But I dont know why it takes much time.
Here is my method which accepts dataset as input parameter which contains data selected from database tables of SQL Server. I want to write those data in excel sheet.
Private Sub WriteVegUnitData(ByVal ds As DataSet)
'Parameter dataset "ds" which contains data selected from SQL Server
Dim olecon As OleDbConnection
Dim oleSelectCmd As OleDbCommand
Dim oleInsertCmd As OleDbCommand
Dim oleadpt As OleDbDataAdapter
Dim dsExcel As DataSet
Dim pram As OleDbParameter
Dim drExcel As DataRow
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
oleSelectCmd = New OleDbCommand
oleSelectCmd.CommandText = "Select ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL from [VEGUNIT$]"
oleSelectCmd.Connection = olecon
oleInsertCmd = New OleDbCommand
oleInsertCmd.CommandText = "Insert into [VEGUNIT$] " & _
"(ID, VEGWORKPLANID, ACTION, VEGSPECIESNAME, VEGSIZENAME, ACTIONDETAILNAME, ACTIONDETAILDISTANCE, VEGSPECIESGROWTHTYPEID, MAPPING_ID, NEW_REC_ID, DEL) values (@ID, @VEGWORKPLANID, @ACTION, @VEGSPECIESNAME, @VEGSIZENAME, @ACTIONDETAILNAME, @ACTIONDETAILDISTANCE, @VEGSPECIESGROWTHTYPEID, @MAPPING_ID, @NEW_REC_ID, @DEL)"
oleInsertCmd.Connection = olecon
pram = oleInsertCmd.Parameters.Add("@ID", OleDbType.VarChar)
pram.SourceColumn = "ID"
pram = oleInsertCmd.Parameters.Add("@VEGWORKPLANID", OleDbType.VarChar)
pram.SourceColumn = "VEGWORKPLANID"
pram = oleInsertCmd.Parameters.Add("@ACTION", OleDbType.VarChar)
pram.SourceColumn = "ACTION"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESNAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESNAME"
pram = oleInsertCmd.Parameters.Add("@VEGSIZENAME", OleDbType.VarChar)
pram.SourceColumn = "VEGSIZENAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILNAME", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILNAME"
pram = oleInsertCmd.Parameters.Add("@ACTIONDETAILDISTANCE", OleDbType.VarChar)
pram.SourceColumn = "ACTIONDETAILDISTANCE"
pram = oleInsertCmd.Parameters.Add("@VEGSPECIESGROWTHTYPEID", OleDbType.VarChar)
pram.SourceColumn = "VEGSPECIESGROWTHTYPEID"
pram = oleInsertCmd.Parameters.Add("@MAPPING_ID", OleDbType.VarChar)
pram.SourceColumn = "MAPPING_ID"
pram = oleInsertCmd.Parameters.Add("@NEW_REC_ID", OleDbType.VarChar)
pram.SourceColumn = "NEW_REC_ID"
pram = oleInsertCmd.Parameters.Add("@DEL", OleDbType.VarChar)
pram.SourceColumn = "DEL"
oleadpt = New OleDbDataAdapter(oleSelectCmd)
dsExcel = New DataSet
olecon.Open()
oleadpt.Fill(dsExcel)
If IsNothing(dsExcel) = False Then
For Each dr As DataRow In ds.Tables(0).Rows
drExcel = dsExcel.Tables(0).NewRow
drExcel("ID") = dr("strID")
drExcel("VEGWORKPLANID") = dr("wpdWphID")
drExcel("ACTION") = dr("wpdPruneRemove")
drExcel("VEGSPECIESNAME") = dr("vspSpeciesDesc")
drExcel("VEGSIZENAME") = dr("vszSizeDesc")
drExcel("ACTIONDETAILNAME") = dr("vacActionDesc")
drExcel("ACTIONDETAILDISTANCE") = dr("vdsDistanceDesc")
drExcel("VEGSPECIESGROWTHTYPEID") = dr("wpdGrowthTypeId")
drExcel("MAPPING_ID") = dr("wpdID")
drExcel("NEW_REC_ID") = "A"
drExcel("DEL") = dr("dlt")
dsExcel.Tables(0).Rows.Add(drExcel)
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = oleInsertCmd
Dim i As Integer = oleadpt.Update(dsExcel)
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
oleSelectCmd = Nothing
oleInsertCmd = Nothing
oleadpt = Nothing
dsExcel = Nothing
drExcel = Nothing
pram = Nothing
End Try
End Sub
|
|
|
|
|
Hi
Sorry for my delayed response to your query. I have gone through your code and my understanding is you are trying to fetch the data from one excel or any database and put the same into some other excel file or any database. I have found some performance issues in your code. I have try to simulate the same case at my end and which is working perfectly and take approximate some couple of seconds to do all the processing.
Following are the details for the same
I have taken 2 buttons on my windows application one for fetching the data and one for inserting the data. In this code I am reading more than 2000 records from Test.xls file and inserting the same in Test1.xls file. One thing I am assuming here is xls file are already present on your system.
Dim olecon As OleDbConnection
Dim oleadpt As OleDbDataAdapter
Dim ds As DataSet
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Const connstring1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Private Sub btnFetch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim olecomm As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
olecomm = New OleDbCommand
olecomm.CommandText = "Select FirstName, LastName, Age, Phone from [Sheet1$]"
olecomm.Connection = olecon
oleadpt = New OleDbDataAdapter(olecomm)
ds = New DataSet
olecon.Open()
oleadpt.Fill(ds, "Sheet1")
If (IsNothing(ds) = False) Then
DataGridView1.DataSource = ds.Tables(0)
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
End Try
End Sub
Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim pram As OleDbParameter
Dim dr As DataRow
Dim olecomm1 As OleDbCommand
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring1
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Insert into [Sheet1$] " & _
"(FirstName, LastName, Age, Phone) values (@FName, @LName, @Age, @Phone)"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
If (IsNothing(ds) = False) Then
For Each dr In ds.Tables(0).Rows
dr.SetAdded()
Next
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = olecomm1
olecon.Open()
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If (IsNothing(olecon) = False) Then
olecon.Close()
End If
olecon = Nothing
olecomm1 = Nothing
dr = Nothing
pram = Nothing
End Try
End Sub
I hope this will answer your query.
Please feel free to write me in case of any query
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|
Dataset ds contains data from SQL Server.
I have more than 32 colums in dataset which I need to transform to excel sheet.
Earlier I was assigning values to each cell by looping through dataset as below.
dim i as integer
dim rowNum as integer
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A" & rowNum).Value = ds.Tables(0).Rows(i)("wphId")
wrksheet.Range("B" & rowNum).Value = ds.Tables(0).Rows(i)("wphMpdId")
wrksheet.Range("C" & rowNum).Value = ds.Tables(0).Rows(i)("wphCustId")
wrksheet.Range("D" & rowNum).Value = ds.Tables(0).Rows(i)("wphComnts")
'
'
'
'
so on up to total 32 colums
'
'
i += 1
rowNum += 1Loop
Loop
But I found that, instead of going trough all cells one by one we can directly assign values to all cells by using following code
which gives me result within couple if seconds
compare to 32 lines above now i have to write onlyn one line.
Do While i < ds.Tables(0).Rows.Count
wrksheet.Range("A1:AF1").Value = ds.Tables(0).Rows(i).ItemArray
i += 1
Loop
of course your code will give even faster result than above one, but as I was in hurry and need to complete task within my time limit, I found above one as a faster alternative.
today I tried your code, you used something like, "dr.SetAdded()", I am using VB.NET2003 and this gives me error like datarow have no method named SetAdded()
Any way, thanks a lot for answering my question. It was my first question in this website and had good experience.
Thanks
|
|
|
|
|
Dear,,
after I read the excel file and converted it into a dataset
how can I insert these dataset into my dataase(SQLserver database)?
Thanks in advance
gho;.lkijh
|
|
|
|
|
|
|
|
Hello all,
Please help me.
I am reading the excel data using ADO.NET. however when the user selects the excel file from the openfile dialog box it throws the message
"object reference not set to an instance" on the target machine.
however same executable does work in the development enviornment. Development enviornment has Office 2003 and target machine has 2000. I have tried running the applicatino on a computer which has Office 2000. It works there as well but not on the target machine. The target machine is a CITRIX SERVER. However an old exe of the same application works fine on the target machine.
Please help me urgently.
Thanks in advance
|
|
|
|
|
Please send me the piece of code. Also mentioned the location where you get error and at what condition
Raman Tayal
Microsoft Certified Application Developer .NET
My Blog: - http://ramantayal.blogspot.com/
|
|
|
|
|