You must Sign In to post a response.
  • Category: .NET

    How to modfiy the excel cells

    i have excel sheet in that one column contains "alt+enter" spaces how i remove that spaces using .net program
  • #767366
    Hi,
    Use following formulaes:
    TRIM(NameOfcell)
    SUBSTITUTE(NameOfcell, " ", "")

    Or Use RegEx

    using System.Text.RegularExpressions;

    NameOfcell.Value = Regex.Replace(NameOfcell.Value, @"\s+", "");

  • #767386
    with the help of interop object you can do it, refer Microsoft.office.interop.excel object in your program, with the of excel application

    Microsoft.Office.Interop.Excel.Range cells = reportSheet.Cells;
    // Cycle through each newline code and replace.
    foreach (var newline in new string[] { "\r\n", "\r", "\n" })
    {
    cells.Replace(newline, "",
    Microsoft.Office.Interop.Excel.XlLookAt.xlPart, // Use xlPart instead of xlWhole.
    Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, false,
    false, true, false);
    }

    The safest way to ensure all line breaks are replaced is to cycle through all possibilities and replace each:

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767537
    You could use a free Excel API (https://www.nuget.org/packages/FreeSpire.XLS/) to load the Excel document, locate the string "alt+enter" by FindText, and then replace it with another string.


Sign In to post your comments