none
Apply trim to a column in excel

    Question

  • Hi,

    My application is an excel addin . I want to apply Trim for a column in excel (delete trailing blanks) through C# code.

    Is there anyway to do this.

    Regards,

    Divya


    divya
    Thursday, June 16, 2011 4:35 AM

Answers

  • I think you need to read on SpecialCells Option.

    http://msdn.microsoft.com/ko-kr/library/microsoft.office.interop.excel.range.specialcells(office.11).aspx

    1. number 1000 row, text 1000 row

    ----first foreach  0.48~~~~~ , second foreach 0.949~~~~

    2. number 100 row, text 1000 row

    ----first foreach  0.49~~~~~ , second foreach 0.56~~~~

    And your foreach code + test

     

    Test in code

      private void button1_Click(object sender, EventArgs e)
      {
       //this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues).Replace(" ", "", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, false, false, missing, missing);
    
       
       double time1 = DateTime.Now.Ticks;
       foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues))
       {    
        rng.Value2 = rng.Text.ToString().Trim();
        
       }
    
       double time2 = DateTime.Now.Ticks;
       double totalTime = (time2 - time1) / 10000000;
       MessageBox.Show(totalTime.ToString());
    
    
    
       time1 = DateTime.Now.Ticks;
       foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants,missing))
       {
        rng.Value2 = rng.Text.ToString().Trim();    
       }
       time2 = DateTime.Now.Ticks;
       totalTime = (time2 - time1) / 10000000;
    
       MessageBox.Show(totalTime.ToString());
    
    
      }
    


    http://vsto.tistory.com

    • Marked as answer by Bruce Song Thursday, July 07, 2011 1:40 PM
    Thursday, June 16, 2011 8:53 AM

All replies

  • I hope this helps

    this.Range["a1", missing].Value2 = this.Range["a1", missing].Value2.ToString().Trim();


    http://vsto.tistory.com
    Thursday, June 16, 2011 5:20 AM
  • hi,

    it will not work as

    this.Range["a1", missing].Value2.ToString() returns "system.object[,]"

    thanks


    divya
    Thursday, June 16, 2011 5:37 AM
  • Hello,

    Did you try to use above code I posted?

    Please, try to use below Addin Code. I think it'll work.

    Globals.ThisAddIn.Application.ActiveCell.Value2 = Globals.ThisAddIn.Application.ActiveCell.Value2.ToString().Trim();


    http://vsto.tistory.com
    Thursday, June 16, 2011 6:21 AM
  • Thanks for the reply.

    Yes, I tried your previous code, it will not work.

    Globals.ThisAddIn.Application.ActiveCell.Value2 = Globals.ThisAddIn.Application.ActiveCell.Value2.ToString().Trim(); - this code will just trim contents of a single cell, not an entire column. I can do this method for each cell, but it is time consuming when the number of rows are high.

    I want to know if there is any method to trim contents of an entire column, not cell by cell

     


    divya
    Thursday, June 16, 2011 7:28 AM
  • I hope this Helps

    1.

          foreach(Excel.Range rng in this.Range["a1",missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues))
          {
            rng.Value2 = rng.Text.ToString().Trim();
          }
    
    


    2.

    this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues).Replace(" ", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, false, false, missing, missing);

     


    http://vsto.tistory.com
    Thursday, June 16, 2011 8:09 AM
  • In the first option, it will loop through each cell which is time consuming.

    Second option will replace all the spaces in the column, not only trailing blanks.. for e.g. if the string is 'Hello World ', it will change to 'HelloWorld'


    divya
    Thursday, June 16, 2011 8:38 AM
  • I think you need to read on SpecialCells Option.

    http://msdn.microsoft.com/ko-kr/library/microsoft.office.interop.excel.range.specialcells(office.11).aspx

    1. number 1000 row, text 1000 row

    ----first foreach  0.48~~~~~ , second foreach 0.949~~~~

    2. number 100 row, text 1000 row

    ----first foreach  0.49~~~~~ , second foreach 0.56~~~~

    And your foreach code + test

     

    Test in code

      private void button1_Click(object sender, EventArgs e)
      {
       //this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues).Replace(" ", "", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, false, false, missing, missing);
    
       
       double time1 = DateTime.Now.Ticks;
       foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues))
       {    
        rng.Value2 = rng.Text.ToString().Trim();
        
       }
    
       double time2 = DateTime.Now.Ticks;
       double totalTime = (time2 - time1) / 10000000;
       MessageBox.Show(totalTime.ToString());
    
    
    
       time1 = DateTime.Now.Ticks;
       foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants,missing))
       {
        rng.Value2 = rng.Text.ToString().Trim();    
       }
       time2 = DateTime.Now.Ticks;
       totalTime = (time2 - time1) / 10000000;
    
       MessageBox.Show(totalTime.ToString());
    
    
      }
    


    http://vsto.tistory.com

    • Marked as answer by Bruce Song Thursday, July 07, 2011 1:40 PM
    Thursday, June 16, 2011 8:53 AM
  • This method uses  excel's function. I think it is faster way than using vsto and above link I posted is useful as well.

    Hope this is helpful.

      this.Range["d:d", missing].Insert(Excel.XlInsertShiftDirection.xlShiftToRight, missing);
          this.Range["d1:d1000", missing].Formula = "=trim(c1)";
          this.Range["d1:d1000", missing].Copy(missing);
          this.Range["d1:d1000", missing].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
    
    

     

     


    http://vsto.tistory.com
    Monday, June 20, 2011 1:49 AM