Set Text to Black, Remove Fill but Leave Table Formatting

Aspose.Cells Product Family
1

Hello,

I am trying to make any text in my spreadsheet black, and to remove the background colors (fill) of the cells. When doing this I need to maintain the table styles on any tables.

With Excel and COM, I was able to use the call Cells.Interior to make these changes without affecting any tables. See the attached image: the text is black but the table is still styled.

TextFormattingUsingCOM.png (63.6 KB)

This is my sample Excel file: TextFormatting.zip (30.2 KB)

Is there a way do the same using Aspose? This is my current code:

List<Aspose.Cells.Tables.TableStyleType> tableStylesList = new List<Aspose.Cells.Tables.TableStyleType>();
foreach (Aspose.Cells.Worksheet wSheet in wBook.Worksheets)
{
    if (!wSheet.IsVisible) continue;
    tableStylesList.Clear();

    // Cleat text formatting and fill color
    if (SetTextToBlack || RemoveFillColor)
    {
        if (wSheet.ListObjects.Count > 0)
        {
            // Save any table formatting
            foreach (Aspose.Cells.Tables.ListObject listObj in wSheet.ListObjects)
            {
                tableStylesList.Add(listObj.TableStyleType);
            }
        }

        // Set our style
        wSheet.Cells.ApplyStyle(style, flag);

        // Now fix up any table style
        if (wSheet.ListObjects.Count > 0)
        {
            // Save any table formatting
            int idx = 0;
            foreach (Aspose.Cells.Tables.ListObject listObj in wSheet.ListObjects)
            {
                listObj.TableStyleType = tableStylesList[idx];
                idx++;
                listObj.ApplyStyleToRange();
            }
        }
    }
}

Thanks,

Sheri

2

@sheri_steeves

Thanks for using Aspose APIs.

Your code looks good and it should fulfill your needs. Besides, you can access all styles of your workbook using these APIs.

Workbook.CountOfStylesInPool
Workbook.GetStyleInPool()

You can also iterate all the cells inside your worksheet and skip the styles inside the tables with your own logic. Please see the following code and its output Excel file for your reference.

Output Excel File:
output.zip (30.5 KB)

C#

Workbook wb = new Workbook("TextFormatting.xlsx");

Worksheet ws = wb.Worksheets[0];

var o = ws.Cells.GetEnumerator();

while(o.MoveNext())
{
    Cell c = o.Current as Cell;

    //Write some logic here to skip if the cells belong to a table
    Debug.WriteLine(c.Name + "---" + c.IsInTable);

    Style st = c.GetStyle();
    st.Pattern = BackgroundType.None;
    st.Font.Color = Color.Black;
    c.SetStyle(st);
}

wb.Save("output.xlsx");
3

@shakeel.faiz

The code I provided does not do what I need it to do - it removes the background\fill on the tables but saving the table styles and applying them again does not update the style back to the original table style.

I tried the code you provided but the Cell.IsInTable always returns FALSE, even when I know I’m on a cell that is in a table.

IEnumerator cellsEnum = wSheet.Cells.GetEnumerator();
while (cellsEnum.MoveNext())
{
    Aspose.Cells.Cell currentCell = cellsEnum.Current as Aspose.Cells.Cell;
    Aspose.Cells.Style currentStyle = currentCell.GetStyle();
    
     // IsInTable is ALWAYS FALSE
    if (RemoveFillColor & !currentCell.IsInTable)
    {
        currentStyle.Pattern = Aspose.Cells.BackgroundType.None;
        //currentStyle.BackgroundColor = System.Drawing.Color.Empty;
        //currentStyle.ForegroundColor = System.Drawing.Color.Empty;
    }
    
    if( SetTextToBlack)
    {
        currentStyle.Font.Color = System.Drawing.Color.Black;
    }
    
    currentCell.SetStyle(currentStyle);
}

I simplified my testing file to just a single table, see attached file.
TextFormattingOnTable.zip (14.9 KB)

I am using Aspose.Cells for .NET 18.4.1.

Thanks,

Sheri

4

@sheri_steeves

We are afraid, you will have to write your own algorithm. We just explained that this is do able. Aspose.Cells can remove or apply formatting of the cell but it is your job to find out and not to remove formatting of the cells that belong to tables.

For your mentioned issue, we have logged it as

  • CELLSNET-46078 - Cell.IsInTable always returns False even if the Cell belongs to a Table
5

@sheri_steeves

For your issue

  • CELLSNET-46078 - Cell.IsInTable always returns False even if the Cell belongs to a Table

Cell.IsInTable property is to denote whether one cell is part of table formula, please see its API reference. But it is true that the naming of this property causes a bit ambiguity for user. We will make it obsolete in next release and provide one new property for it. i.e.

  • Cell.IsTableFormula

To check whether one cell is in table(ListObject), it is better for user to gather ranges of tables (ListObject.DataRange) at first, and then loop the ranges to check whether the cell is in one of those ranges. It is more efficient than providing check mechanism for every Cell object.

For your requirement, the most efficient way should be as:

for (int i = workbook.CountOfStylesInPool - 1; i > -1; i--) 
{ 
	Style style = workbook.GetStyleInPool(i); 
	style.Pattern = BackgroundType.None; 
	style.Font.Color = Color.Black; 
}