This project has moved. For the latest updates, please go here.

How to access individual chars in a cell? Range.Characters doesn't work

Mar 9, 2013 at 4:55 PM
Hi all,

I am trying to port from VBA to .NET one of my old applications to transliterate the content of an Excel sheet to and from cyrillic codepage. For that I need to access the individual characters in each cell, as cells or parts of cells that are formatted in italic must stay invariant in the process.

I am stuck with this (simplified) version of my code:
Excel.Range theRange = exlSheet.UsedRange;            
Excel.Range theCell;
int rowsCt = theRange.Rows.Count;
int colsCt = theRange.Columns.Count;
for (int iRow = 1; iRow <= rowsCt; iRow++)
      {
           for (int iCol = 1; iCol <= colCt; iKol++)
            {
                 // this the current cell
                  theCell = theRange.Cells[iRow, iCol];
                
After this row I thought I intended to use the Characters object to loop through the individual characters in the cell, but this:
for (int iChar = 1; i <= theCell.Characters.Count; i++)
{
      if (theCell.Characters[iChar, 1].Font.FontStyle = "Italic") 
      {
           ...
doesn't work, although it works in VBA code.
Visual Studio displays a red squiggly line with a tooltip text "Cannot apply indexing with [] to an expression of type 'NetOffice.ExcelApi.Characters'".

I understand what the problem is, but I see no other way to work character by character in the cell.
I need that because I know no other way to find parts of cell content that are formatted in italic except to loop through the cell characters and note the beginnings and ends of the italic parts, and then transliterate only the parts that are not in italic.

I use NetOffice 1.0 with .NET Framework 3.5.

Thank you in advance for any help.
Developer
Mar 10, 2013 at 11:32 AM
You should loop through cell characters like this:
for (int iChar = 1; iChar <= theCell.Characters.Count; iChar++)
{
  if (theCell.Characters(iChar, 1).Font.FontStyle == "Italic") 
  {
    ...
  }
And here is one more tip to check for italicized characters: FontStyle depends on language version so you'd better use Font.Italic property.
for (int iChar = 1; iChar <= theCell.Characters.Count; iChar++)
{
  //if (theCell.Characters(iChar, 1).Font.FontStyle == "Italic") doesn't work on Russian Excel
  //if (theCell.Characters(iChar, 1).Font.FontStyle == "курсив") works on Russian Excel
  if (theCell.Characters(iChar, 1).Font.Italic) //doesn't depend on Excel language version
  {
    ...
  }
I've checked this code with current NetOffice version (1.5.1.2). I don't know whether it works with NetOffice 1.0 and what is the reason for using old version.
Mar 10, 2013 at 1:27 PM
Thank you for your reply.

Unfortunately, it doesn't work, for the syntax you propose Visual Studio says "Non-invocable member 'ExcelApi.Range.Characters' cannot be used like a method", which makes sense, Characters is not a method of a Range type object,, and the syntax with rounded brackets can't work.

I also downloaded and tried with NetOffice 1.5, but I get the same behavior and message.
Mar 10, 2013 at 2:05 PM
Edited Mar 10, 2013 at 2:06 PM
Finally solved it.
The problem was the fact that the Characters property of the Range object is not directly usable in .NET in the same form as it is in VBA. As there is no Character object, it is necessary to use the get_Characters method of the Range object to extract a range that is one character long, and then work with that character. So, this code works:
for (int iChar = 1; i <= theCell.Characters.Count; i++)
{
      Excel.Collection oneChar = theCell.get_Characters(iChar, 1): 
      if ((bool)oneChar.Font.Italic) 
      {
         ...
Coordinator
Mar 10, 2013 at 8:32 PM
you can use theCell.Characters(iChar, 1) as well in C# and NetOffice 1.5. (btw: you have to use the get_ version in VB.NET)

Sebastian