Can't modify the Font.Color of an existing Excel Sheet

Aspose.Cells Product Family
1

Hi Guys,

I am not able to change the Font-Color of an Sheet I copy into another Sheet. The problem is, everytime I change that Font-Color, the whole existing formatting of this Sheet is overwritten. I’ve tried your style.copy approach, that does not work either.

Dim style As Style
style = template_sheet.Cells.Columns(0).Style
Dim style2 As Style = template_Workbook.Styles(template_Workbook.Styles.Add())
style2.Copy(style)
style2.Font.Color = System.Drawing.Color.Black
Dim styleFlag As StyleFlag = New StyleFlag
styleFlag.All = True
template_sheet.Cells.Columns(0).ApplyStyle(style2, styleFlag)

Do you have any idea how its done?

Kind Regards,
Matthias

2

Hi,

Thanks for your posting and using Aspose.Cells of .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.8

You should set the style of entire worksheet cells using worksheet.Cells.ApplyStyle() method.

Please see the following code, it sets the font size and color of the entire worksheet. When you insert any text inside any cell of the worksheet, it will be in Arial, 16pt, Red.

The code is both in VB.NET and C#.

I have also attached the screenshot for your reference.

VB.NET


Dim workbook As Workbook = New Workbook()


Dim worksheet As Worksheet = workbook.Worksheets(0)


Dim cellA1 As Cell = worksheet.Cells(“A1”)


Dim st As Style = cellA1.GetStyle()


st.Font.Size = 16

st.Font.Color = Color.Red


Dim stFlag As StyleFlag = New StyleFlag

stFlag.All = True


worksheet.Cells.ApplyStyle(st, stFlag)

workbook.Save(“output.xlsx”)


C#

Workbook workbook= new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cellA1 = worksheet.Cells[“A1”];


Style st = cellA1.GetStyle();


st.Font.Size = 16;

st.Font.Color = Color.Red;


worksheet.Cells.ApplyStyle(st, new StyleFlag() { All = true });


workbook.Save(“output.xlsx”);


Screenshot:

3

Thank you for your fast reply. The problem is, it does not help me at all.

I have to take over the formatting of the other Excel Sheet, I just want to adapt the Font-Color to black. Thats all. No new sheet, no new entries, just copying one Excel Sheet to another Excel Sheet and adapting the Font Color to black.

Kind Regards,
Matthias

4

Hi,

I understand your question but you will use the same code as I have given above.

The only difference will be that I created a worksheet from scratch and you will use the existing worksheet.

The code basically demonstrates how to change/adapt Font Color, Font Size of Excel worksheet. You can surely use it after some modification.

Let me know if you still have any question.

5

Thank you very much, it was another error I had, with your help I could exclude this one. Now it works.

For Each cll As Cell In template_sheet.Cells
Dim st As Style = cll.GetStyle
st.Font.Color = System.Drawing.Color.Black
cll.SetStyle(st)
Next
template_Workbook.Save(templatecopy_name, SaveFormat.Auto)

Do you have maybe a tip for me, how I can make this more performance oriented. Instead of going through every cell, maybe just the copied Columns?

Kind Regards,
Matthias

6

Hi,

Thanks for your feedback.

I have written the following code that changes the font color of all cells into black color, this code is similar to yours except that it does not iterate all cells, it uses Range object.

Please see the source and output files used in this code.

I have also attached the screenshot for your reference.

VB.NET

Dim filePath As String = "F:\source.xlsx"

Dim template_Workbook As Workbook = New Workbook(filePath)

Dim template_sheet As Worksheet = template_Workbook.Worksheets(0)

Dim maxRange As Range = template_sheet.Cells.MaxDisplayRange


Dim st As Style = template_Workbook.CreateStyle

st.Font.Color = Color.Black

Dim stFlag As StyleFlag = New StyleFlag
stFlag.All = True

maxRange.ApplyStyle(st, stFlag)

template_Workbook.Save("output.xlsx")


Screenshot: