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,
r pPosted Oct 26, 2011, 6:23 AM
Hi,
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?
Thanks
r pPosted Oct 21, 2011, 7:39 AM
The columns in the Excel Template are set to Number.
' MyRangeName' is the name of the range in the Excel document to which I insert the values.
But, no luck, as soon as I insert using OLEDB, the data comes with an apostrophe and there is an error message saying that number is stored as text.
Sam HobbsPosted Oct 20, 2011, 2:48 PM
I think that there are at least a couple of things that you have not shown; more specifically I don't see what any of the following are:
- what data type is the OLEDB field
- what type that you set the Excel column to
- what is MyRangeName
It has been years since I worked with Excel for putting data into a spreadsheet but I know it is possible to set the format of columns and that is highly relevant to what you are doing. When you use the default, Excel guesses and of course it does not always guess correctly. If the data you are giving Excel is defined to be a string then of course it is likely to misunderstand.r pPosted Oct 20, 2011, 7:32 AM
Thanks for your reply.
I am looking to do it using some code - either in my C# and OLEDB code or at least using some predefined code in Excel.
Thanks
TulasiPosted Oct 20, 2011, 7:25 AM
When importing data from external sources you may find that your number values unexpectedly import as text.
It's usually obvious when this happens - the numbers are left-aligned.
The cells may look like a number, but Excel thinks that they are text.
You'll find that you can't perform calculations against "text-numbers". For the picture below, if I used the formula =SUM(A1:A10) then the result would be 0.
Excel 2002 (Excel XP) made some advances in this area by way of automatic error-checking (aka theGreen Triangle).
You can quickly convert the cell to a proper number by highlighting your list of misbehaving numbers, click the exclaimation mark and choose 'Convert to Number' from the dropdown.
For those of you running a version of Excel less than 2002, the trick I use goes as follows:
1. Copy a Blank Cell (or a cell containing the number 0)
2. Select your list of text-numbers
3. Choose Paste Special from the Edit menu.
4. Paste=Values, Operation=Add
By applying a math operation on the text-numbers, the result is a number!
Sure beats pressing "F2 Enter" 100 times like I used to.
http://www.dailydoseofexcel.com/archives/2006/02/18/number-stored-as-text/
Please mark the answer as Accepted if it helps you.