Last post Nov 12, 2011 04:15 PM by Cathy Mi - MSFT
Oct 20, 2011 09:30 AM|robinspaul|LINK
I am inserting values into excel document using OLEDB in my website written in ASP.NET(C#).When I insert numbers, they are inserted as strings instead of numbers.
How do I convert string to numbers programmically and insert them into excel as number NOT strings?My code:-======== string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MYExcelFile + ";Extended Properties=Excel 8.0"; OleDbConnection MyConnection; OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(); string sql = null; MyConnection = new OleDbConnection(connString); MyConnection.Open(); myCommand.Connection = MyConnection; string tempTitle1 = "Title1"; string tempTitleField1 = "[" + tempTitle1 + "]"; string tempTitle2 = "Title2"; string tempTitleField2 = "[" + tempTitle2 + "]"; string tempTitle3 = "Title3"; string tempTitleField3 = "[" + tempTitle3 + "]"; sql = "Insert into " + MyRangeName + " (" + tempTitleField1 + "," + tempTitleField2 + "," + tempTitleField3 + ") values('" + Value1 + "','" + Value2 + "','" + Value3 + "')"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); MyConnection.Close(); Thanks so much,
Oct 20, 2011 08:10 PM|nicksoft|LINK
Did you try converting the String to Int through C# statements:-
numVal = Convert.ToInt32(input);
if that causes to change the entire number , i guess next thing you weants to try to change the Excel Cell format to Number and then export the data.
try :- cells["A:A"].NumberFormat
which most of the time works..........
Oct 21, 2011 11:41 AM|robinspaul|LINK
I tried it. But it is not working.
Oct 21, 2011 11:49 AM|mkonanki|LINK
check below links
Oct 21, 2011 12:44 PM|nicksoft|LINK
what version of Excel you have in this project?
some times you need to make sure how the environment set up in the web server...because most of the tme webserver doesn't carry Excel installed on that machine because of bing technical reasons.
if you are doing this temporary work and for your development box then i guess we need to work on that.
Oct 24, 2011 03:05 PM|robinspaul|LINK
Hi,I am using Office 2010.
I have set the data type of the excel cells as 'Number'.
It will work fine when I enter numbers.
But, there are some fields to which I may not have number available, so in those fields,
I need to enter 'No Value is avalible'.
This shows an error message - "Type mismatching".
Oct 26, 2011 10:23 AM|robinspaul|LINK
I inserted -99 in all the columns of the first row of the template to which I am going to inset numbers.
Now when I insert the numbers using my program, those numbers are saved as numbers instead of text.
But, I can't delete the first row using my program.
Is there any way I can delete first row?
Oct 26, 2011 12:22 PM|nicksoft|LINK
whithin your code, you must be able to skip any number of rows and column depending upon your need.
you just need to skip that insert statement when you loop through specific loop.
Also sometime if you wants to enter the text out of excel, Format using # option works.
Can you provide some code how you inserting the data from excel??
Oct 26, 2011 12:29 PM|robinspaul|LINK
I manually inserted -99 in the first row, so that when the program does the insertion, it will follow the format of the previous row.
Now after the insertion is completed, I want to delete the first row.
Oct 28, 2011 08:01 PM|Cathy Mi - MSFT|LINK
You can insert and update rows in Excel from ADO.NET but not delete. You could do an Update for the first intended Insert, using a Where clause in the Update statement. For instance, if the first row you wanted to insert was 99, 99, 99:
"UPDATE MyRange SET Title1 = 99, Title2 = 99, Title3 = 99 WHERE Title1 = -99"
That would modify the -99 row to the data you intended for the first row. Then, you could perform Insert statements for the remaining rows.
That is, of course, assuming that first, manually entered row is the only row that contains -99 when the UPDATE is executed.
Microsoft Online Community Support
Nov 01, 2011 11:55 AM|robinspaul|LINK
But, the problem is I don't know what will be the first row.
Sometimes all the columns will not have values; in that case some fields will have -99 unchanged.
Nov 12, 2011 04:15 PM|Cathy Mi - MSFT|LINK
Were you ever able to come up with a solution to meet all your needs? As I stated before, you will not be able to delete rows in Excel using ADO.NET.
If you still need assistance, please visit the below link to see the various paid support options that are available to better meet your needs.