468,512 Members | 1,485 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,512 developers. It's quick & easy.

Eliminate a hyphen/underscore for a subscript

Hi. I need to eliminate the underscore for X_2 where the 2 is a subscript. What is the code like? I have a sample here but can't figure out which is what and how to change it, as I did try to change but no changes took place at all. Please help. Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. string Anthem = "Star Spangled Banner";
  2. Anthem = Anthem.Replace(" ", ""); 
  3.  
p/s:I have a column called Symbol and there are values in it such as X_1, X_2.
Jan 25 '10 #1
18 3063
tlhintoq
3,525 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. string myValue = "X_2";
  2. string newValue = myValue.Replace("_","");
The MSDN for string.replace explains this in detail.
Jan 25 '10 #2
I am still getting an error. I have replaced "X_2" with "B3,B4" as i have in the excel column. It says identifer expected at the "B4". Do I need to have a method written for it?
Here's what I have done:
Expand|Select|Wrap|Line Numbers
  1. string myValue = ("B3","B4");
  2. string newValue = myValue.Replace("_", "");
  3.  
Jan 26 '10 #3
tlhintoq
3,525 Expert 2GB
Well... yeah... You are trying to assign two different values to one string.
string myValue = ("B3","B4");
You can't assign both "B3" and "B4" to a single string.

Also, neither of these string contain an underscore - so how are you expecting to replace an underscore that doesn't exist?
Jan 26 '10 #4
So how else should I put it? I need to get the rows first right? The rows are B3 and B4, they already have values of X_2 and X_3 respectively. I need to eliminate the underscore. Please help. Thanks.
Jan 26 '10 #5
tlhintoq
3,525 Expert 2GB
What rows? What are you talking about? There is no "rows" property of a string object or inside the .Replace() method.
Jan 26 '10 #6
In these rows, there are values as I mentioned earlier, the X_2 and X_3. How do I assign the code to eliminate the underscores? That's all. The code you gave me didn't work. Because, why did you put 'string myValue = ("B3","B4");'? The underscores contains in the rows of B3 and B4.
Jan 26 '10 #7
tlhintoq
3,525 Expert 2GB
OH..... You are working on an Excel spreadsheet. It helps if you make that clear. How would I know you are working on an Excel spreadsheet from the title:
Eliminate a hyphen/underscore for a subscript
Or anywhere in the original question
Hi. I need to eliminate the underscore for X_2 where the 2 is a subscript. What is the code like? I have a sample here but can't figure out which is what and how to change it, as I did try to change but no changes took place at all. Please help. Thanks in advance.

Expand|Select|Wrap|Line Numbers
  1. string Anthem = "Star Spangled Banner";
  2. Anthem = Anthem.Replace(" ", ""); 
Jan 26 '10 #8
I'm sorry. I should have made that clear. My mistake. So any solution?
Jan 26 '10 #9
tlhintoq
3,525 Expert 2GB
Are you able to read the Excel spreadsheet programmatically? Are you able to obtain a value from a given cell using the Microsoft.Office.Excel.Interop framework?

I might suggest you work on just being able to read a spreadsheet before trying to figure out how to change one.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://support.microsoft.com/kb/302084
Jan 26 '10 #10
Yes, I am able to read the spreadsheet. All values can be displayed. Right now, i just need a way to replace the _ with a space. From X_2 to X2. Thats all. Any idea?. Thanks in advance.
Here's the full coding:

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using Excel = Microsoft.Office.Interop.Excel;
  5.  
  6. namespace UncFunc
  7. {
  8.     class Report
  9.     {
  10.  
  11.         public void Create(Quantity q)
  12.         {
  13.             Excel.Application objApp;
  14.             Excel._Workbook objBook;
  15.             Excel.Workbooks objBooks;
  16.             Excel.Sheets objSheets;
  17.             Excel._Worksheet objSheet;
  18.             Excel.Range range;
  19.             Dictionary<int, Quantity> rootQty = q.RootQuantities;
  20.  
  21.             try
  22.             {
  23.                 // Instantiate Excel and start a new workbook.
  24.                 objApp = new Excel.Application();
  25.                 objBooks = objApp.Workbooks;
  26.                 objBook = objBooks.Add(Type.Missing);
  27.                 objSheets = objBook.Worksheets;
  28.                 objSheet = (Excel._Worksheet)objSheets.get_Item(1);
  29.  
  30.                 //Get the range where the starting cell has the address
  31.                 //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
  32.                 range = objSheet.get_Range("A1", Type.Missing);
  33.                 range = range.get_Resize(6,9);
  34.  
  35.                 //Create an array.
  36.                 string[,] saRet = new string[rootQty.Count+2, 9];
  37.  
  38.  
  39.                 saRet[0, 4] = "Root Quantities";
  40.                 saRet[1, 0] = "String";
  41.                 saRet[1, 1] = "Symbol";
  42.                 saRet[1, 2] = "Limit Description";
  43.                 saRet[1, 3] = "Value";
  44.                 saRet[1, 4] = "Limit";
  45.                 saRet[1, 5] = "DF";
  46.                 saRet[1, 6] = "Divisor";
  47.                 saRet[1, 7] = "Std Unc";
  48.                 saRet[1, 8] = "Unit";
  49.  
  50.  
  51.                 int[] aryKeys= new int[rootQty.Keys.Count];
  52.                 Dictionary<int,Quantity>.KeyCollection oKeys = rootQty.Keys;
  53.                 oKeys.CopyTo(aryKeys, 0);
  54.  
  55.                 //Fill the array.
  56.                 for (long iRow = 0; iRow < rootQty.Count; iRow++)
  57.                 {
  58.  
  59.                     //for (long iCol = 0; iCol < 4; iCol++)
  60.                     //{
  61.                         int iKey = aryKeys[iRow];
  62.  
  63.                         Quantity qty = rootQty[iKey];
  64.  
  65.                         String sSymbol = qty.Symbol;//column B
  66.                         String sLimitDescription = qty.LimitDescription;// column C
  67.                         String sValue = qty.Value.ToString();//column D
  68.                         String sLimit = qty.Limit.ToString();//column E
  69.                         String sDF = qty.df.ToString();//column F
  70.                         String sDivisor = qty.Divisor.ToString();//column G
  71.                         String sStdUnc = qty.StdUnc.ToString();//column H
  72.                         String sUnit = qty.Unit;
  73.  
  74.                         saRet[iRow+2, 1] = sSymbol;
  75.                         saRet[iRow + 2, 2] = sLimitDescription;
  76.                         saRet[iRow + 2, 3] = sValue.ToString();
  77.                         saRet[iRow + 2, 4] = sLimit.ToString();
  78.                         saRet[iRow + 2, 5] = sDF.ToString();
  79.                         saRet[iRow + 2, 6] = sDivisor.ToString();
  80.                         saRet[iRow + 2, 7] = sStdUnc.ToString();
  81.                         saRet[iRow + 2, 8] = sUnit;
  82.  
  83.  
  84.                     //Previous statements                
  85.                         //saRet[iRow+2,0] = sSymbol;
  86.                         //saRet[iRow+2,1] = sValue;
  87.                         //saRet[iRow+2,2] = sStdUnc;
  88.                         //saRet[iRow+2,3] = qty.Unit; //sUnit
  89.  
  90.                         //Put the row and column address in the cell.
  91.                         //saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
  92.  
  93.  
  94.                   // }
  95.                 }
  96.  
  97.  
  98.                 //Set the range value to the array.
  99.                 range.set_Value(Type.Missing, saRet);
  100.  
  101.  
  102.                 //Set Subscript
  103.                 range = range.get_Resize(6, 9);
  104.                 objSheet.get_Range("A1", Type.Missing);
  105.                 //range = range.get_Resize(0, 2);
  106.                 //range.get_Range(2, 0);
  107.  
  108.                 objSheet.get_Range("A1", "A6").EntireColumn.AutoFit();
  109.                 objSheet.get_Range("B3", "B6").get_Characters(2, 2).Font.Subscript = true;  //column and character chosen respectively
  110.  
  111.                 //objSheet.get_Range("B4","B6").Font.Subscript = true;
  112.                 //objSheet.get_Range(1, 1).Font.Subscript = true;
  113.  
  114.  
  115.                 //Set Bold(Added)
  116.                 objSheet.get_Range("A1", "I1").Font.Bold = true;
  117.                 objSheet.get_Range("A2", "I2").Font.Bold = true;
  118.                 //range = range.get_Resize(1, 1);
  119.                 //range.get_Characters(1,1).Font.Bold = true;
  120.                 //range.Font.Bold = true;
  121.  
  122.  
  123.                 string myString;
  124.                 myString = ;
  125.                 myString = Regex.Replace(myString, "_", "");
  126. //the one above needs the modification
  127.                 //string myValue = ("X_2");
  128.                 //string newValue = myValue.Remove(1,1);
  129.  
  130.  
  131.  
  132.  
  133.  
  134.                 //Return control of Excel to the user.
  135.                 objApp.Visible = true;
  136.                 objApp.UserControl = true;
  137.  
  138.                 objSheet.get_Range("A1", "I6").BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium,
  139.                             Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);// added
  140.  
  141.  
  142.                 //Save
  143.                 objBook.SaveAs("Report Uncertainty.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
  144.                     Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
  145.                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  146.             }
  147.             catch (Exception theException)
  148.             {
  149.                 String errorMessage;
  150.                 errorMessage = "Error: ";
  151.                 errorMessage = String.Concat(errorMessage, theException.Message);
  152.                 errorMessage = String.Concat(errorMessage, " Line: ");
  153.                 errorMessage = String.Concat(errorMessage, theException.Source);
  154.  
  155.                 Console.WriteLine(errorMessage, "Error");
  156.             }
  157.         }
  158.  
  159.     }
  160. }
  161.  
p/s I have also stated the one which needed to be modified, in Bold.
Jan 26 '10 #11
I have typed the 2 lines in immediate window, and it seemed to work but it wont display as such in the excel file. Thats what i don't understand. Please help. Thanks.
Jan 26 '10 #12
tlhintoq
3,525 Expert 2GB
Right now, i just need a way to replace the _ with a space. From X_2 to X2.
There is no space in your example. You have gone from X_2 to X2 (no space)

I have shown you how to do the replace.
Expand|Select|Wrap|Line Numbers
  1. string myValue = "X_2";
  2. string newValue = myValue.Replace("_","");
The replace method is very simple. The first parameter is the character you want to look for (the one to be replaced), the second parameter is the one to insert (the doing the replacing)
In the above example all underscores are replaced with "" (no space)

Expand|Select|Wrap|Line Numbers
  1. string myValue = "X-2";
  2. string newValue = myValue.Replace("-","4");
In this example all hyphens are replaced with the number 4

Expand|Select|Wrap|Line Numbers
  1. string myValue = "X&2";
  2. string newValue = myValue.Replace("&","B");
In this example all & symbols are replaced with the letter B
Jan 26 '10 #13
tlhintoq
3,525 Expert 2GB
p/s I have also stated the one which needed to be modified, in Bold.
Expand|Select|Wrap|Line Numbers
  1.                 string myString;
  2.                 myString = ;
  3.                 myString = Regex.Replace(myString, "_", "");
  4. //the one above needs the modification
  5.                 //string myValue = ("X_2");
  6.                 //string newValue = myValue.Remove(1,1);
  7.  
Here is what you have done.
You created a new string named myString.
It has no value.
Then you try to replace all the underscores with nulls.
Guess what... This code works perfectly. It just doesn't produce the results you want. But it does exactly what you told it to do: It went through the string 'myString' and replaced all the underscores with an empty string.

Since you know how to get the values from the spreadsheet... Those are the values you need to evaluate. Don't make a new string. Evaluate the values from the cells of your spreadsheet.
Jan 26 '10 #14
Thank you so much!! Really appreciate your time explaining it. The problem is that it doesnt display the results in excel but in the immediate window it does. Why is that so?
Jan 27 '10 #15
tlhintoq
3,525 Expert 2GB
You didn't write the code you posted, did you?
You're not really familiar with the concept of variables, right?

You aren't actually working directly on the spreadsheet.
The code you presented *copies* values from the spreadsheet into variables.
You then manipulate the data, such as remove the underscores.
Then you need to write the new data back to a spreadsheet.

So you are seeing the *copied* values change. You still need to make a new spread sheet and save the updated data.
Jan 27 '10 #16
The code was given by my boss, written specifically to call a spreadsheet. I wrote the codes to display the data and so on. The only thing now is that that code to eliminate the _ is not displaying on the spreadsheet.
Jan 27 '10 #17
tlhintoq
3,525 Expert 2GB
Rshin: Out of 6 threads you've started here, ALL of them are about how to do this Excel project. Maybe it's time to tell your boss that you don't have the training to complete this project. It might be the nudge (s)he needs to get you some more training which can only further your career.

Either that or your company needs to quit underbidding qualified coders on projects that they don't have the expertise to handle - because it is unfair to their staff to expect results they aren't prepared to produce. Nobody needs that kind of pressure placed upon them.
Jan 27 '10 #18
I've already completed 3 quarter of what I am suppose to do. I dont know why he gave me this project. I just need to know why isnt that displying as it should in excel. I know its not that hard to mend it.
Jan 27 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by Steve | last post: by
5 posts views Thread by Steve | last post: by
3 posts views Thread by Tony Young | last post: by
27 posts views Thread by The Bicycling Guitarist | last post: by
13 posts views Thread by Matt | last post: by
22 posts views Thread by stevenkobes | last post: by
51 posts views Thread by Pedro Graca | last post: by
6 posts views Thread by josh | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.