I would like to manipulate the words in an xlsx file which are not red like:
“Black” to “¤Black¤”
Thanks for your query.
Following code loads a workbook and filters text which is not red. Please give it a try and provide your feedback. If it does not fulfill your requirement, describe the requirement in detail and provide sample template file along with the expected output file. We will analyze the requirement and assist you further.
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook wb = new Workbook("ColouredCells.xlsx");
// Instantiating a CellsColor object for foreground color
CellsColor clrForeground = wb.CreateCellsColor();
clrForeground.Color = Color.Red;
// Instantiating a CellsColor object for background color
CellsColor clrBackground = wb.CreateCellsColor();
clrBackground.Color = Color.White;
// Accessing the first worksheet in the Excel file
Worksheet worksheet = wb.Worksheets[0];
// Call AddFillColorFilter function to apply the filter
worksheet.AutoFilter.AddFillColorFilter(0, BackgroundType.Solid, clrForeground, clrBackground);
// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();
// Saving the modified Excel file
wb.Save("FilteredColouredCellsNet.xlsx");
ColouredCells.zip (6.9 KB)
FilteredColouredCellsNet.zip (7.2 KB)
Thank you for your code, I wish to filter by font color, not by cell color, please check the attached original and output file.
It can be good too if it change all black word to a same word just set a ¤ to it’s start and end.
Also I would like to get all words in the output file just the balck words or sentences changed to the flagged one with the ¤ signs.
With DOCX files, I could convert them to XML and change the lines by regex, then convert back to docx.
If you can show me a way by converting, that also can be good where the font colors are showed in a file where I can use regex too.files.zip (14.2 KB)
Thanks for the template file and details.
Well, you may try to use Cell.GetCharacters() and Cell.Characters() methods for your needs to identify cell chars by font settings, see the sample code for your reference:
e.g
Sample code:
string filePath = @"e:\test2\Original.xlsx";
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
foreach (Aspose.Cells.Cell cell in worksheet.Cells)
{
FontSetting[] fntSettings = cell.GetCharacters();
if (fntSettings != null)
{
foreach (FontSetting fnt in fntSettings)
{
if (fnt.Font.Color == System.Drawing.Color.FromArgb(255,0, 0, 0))
{
string blackText = cell.StringValue.Substring(fnt.StartIndex, fnt.Length);
Debug.WriteLine("[Cell Name]: " + cell.Name + " [Black Text]: " + blackText);
}//if
}//foreach
}//if
}Thank you, now I can determine the black words, the last thing I want to replace them with theirself just put this nonprinting character “¤” to their start and end .
I tried using a list, add the words to it, then replace and save into a new document, but it doesn’t work if the word is there more than once.
I tried cell.StringValue.Substring(fnt.StartIndex, fnt.Length).Replace(blackText, “¤” + blackText + “¤”);
but it doesn’t replace it, can you please help me with by replace the found black text with the described modification.
You are awesome by helping me with this!
Could you please share the sample code which you have created using list to replace text. We will analyze and share our feedback with you.
I tried all of the ways, you can see in the following code:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;
namespace ExcelRed
{
class Program
{
static void Main(string[] args)
{
Aspose.Cells.License Wlicense = new Aspose.Cells.License();
Wlicense.SetLicense(“ExcelRed.Aspose.Total.lic”);
string filePath = @"original.xlsx";
string savepath = @"Output.xlsx";
string blackText;
ICollection<String> black = new List<String>();
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
foreach (Aspose.Cells.Cell cell in worksheet.Cells)
{
FontSetting[] fntSettings = cell.GetCharacters();
if (fntSettings != null)
{
foreach (FontSetting fnt in fntSettings)
{
if (fnt.Font.Color == System.Drawing.Color.FromArgb(255, 0, 0, 0))
{
blackText = cell.StringValue.Substring(fnt.StartIndex, fnt.Length);
/*
Debug.WriteLine(fnt.StartIndex + " " + fnt.Length);
//workbook.Replace(blackText, "¤" + blackText + "¤");
//worksheet.Replace(blackText, "¤" + blackText + "¤");
cell.StringValue.Substring(fnt.StartIndex, fnt.Length).Replace(blackText, "¤" + blackText + "¤");
Debug.WriteLine(cell.StringValue.Substring(fnt.StartIndex, fnt.Length).ToString());
*/
Debug.WriteLine("[Cell Name]: " + cell.Name + " [Black Text]: " + "¤" + blackText + "¤");
}//if
}//foreach
}//if
}
/*
foreach (var asd in black.Reverse<String>())
{
//worksheet.Replace(asd, "¤" + asd + "¤");
Debug.WriteLine(asd);
black.Remove(asd);
}
*/
workbook.Save(savepath);
}
}
}
I have tried following logic which works as expected:
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
foreach (Aspose.Cells.Cell cell in worksheet.Cells)
{
List<string> data = new List<string>();
FontSetting[] fntSettings = cell.GetCharacters();
if (fntSettings != null)
{
foreach (FontSetting fnt in fntSettings)
{
if (fnt.Font.Color == Color.FromArgb(255, 0, 0, 0))
{
string blackText = cell.StringValue.Substring(fnt.StartIndex, fnt.Length);
data.Add("¤" + blackText + "¤");
}//if
else
{
string NonBlackText = cell.StringValue.Substring(fnt.StartIndex, fnt.Length);
data.Add(NonBlackText);
}
}//foreach
}//if
else
{
data.Add(cell.StringValue);
}
string finalData = null;
foreach (var sub in data)
finalData += sub;
cell.PutValue(finalData);
}
workbook.Save(path + "output.xlsx");
If this code does not fulfill your requirement, you have to develop your own logic to parse the cell.HtmlString and replace the text between Font tag with COLOR value #000000 as there is no direct option available in Aspose.Cells to perform the required task.
You may follow some article to parse the HtmlString using C# as follows:
How to parse Html using c#