Change border color of a particular column

Aspose.Cells Product Family
1

Hi,

Can you please tell me how can I change the border color of a particular set of columns.

Please refer the attached excel in which the last six columns color has gray color and the grid lines are not shown. I need the grid lines to be shown for the last six columns. Please help me as how to achieve this..

Thanks.

2

Hi,

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

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)


You need to use the Style object and set the border’s color and width and apply the style object to whole column using the Column.ApplyStyle() method.

C#


string filePath = @“F:\ColorAdd.xls”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Style st = workbook.CreateStyle();

st.Borders.SetColor(Color.Red);

st.Borders.SetStyle(CellBorderType.Thick);

st.Borders.DiagonalStyle = CellBorderType.None;


StyleFlag flag = new StyleFlag();

flag.All = true;


Column column = worksheet.Cells.Columns[0];

column.ApplyStyle(st, flag);


workbook.Save(filePath + “.out.xls”);


3

Hi,


Please have a look at the following code to achieve what you want.

Workbook workbook = new Workbook("K://ColorAdd.xls");

Worksheet worksheet = workbook.Worksheets[0];

Style style = new Style();
style.Borders.SetColor(Color.Red);
style.Borders.SetStyle(CellBorderType.Thin);

style.Borders.DiagonalStyle = CellBorderType.None;

StyleFlag sf = new StyleFlag();
sf.All = true;
worksheet.Cells.Columns[0].ApplyStyle(style, sf);
workbook.Save("K://ComplexOutput.xls");


4

Please look at my code and help me in which place I need to modify.

int colNo = 38;

Workbook workbook = new Workbook();

workbook.Open(@"D:\Color_Excel.xls");

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.SetRowHeight(0, 55);

sheet.Cells.SetColumnWidth(0, 50);

Aspose.Cells.Style rowStyle = workbook.Styles[workbook.Styles.Add()];

rowStyle.ForegroundColor = System.Drawing.Color.Navy;

rowStyle.Pattern = BackgroundType.Solid;

rowStyle.Font.Color = System.Drawing.Color.White;

StyleFlag rowStyleFlag = new StyleFlag();

rowStyleFlag.All = true;

Aspose.Cells.Row row = sheet.Cells.Rows[0];

row.ApplyStyle(rowStyle, rowStyleFlag);

string[] colorArr = new string[6] { "Gray", "Gray", "Gray", "Gray", "Gray", "Gray" };

for (int arrLength = 0; arrLength < colorArr.Length; arrLength++)

{

Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];

style.ForegroundColor = System.Drawing.Color.FromName(colorArr[arrLength]);

style.Pattern = BackgroundType.Solid;

style.Font.Color = System.Drawing.Color.White;

StyleFlag styleFlag = new StyleFlag();

styleFlag.All = true;

Column column = sheet.Cells.Columns[colNo];

column.ApplyStyle(style, styleFlag);

colNo++;

}

workbook.Save(@"D:\ColorAdd.xls");

5

Hi,

Thank you so much for the code. It works great.

I do have 1 more question please.

When I need to set the color of a color like the below code,

style.ForegroundColor = System.Drawing.Color.Red;

This accepts only dark colors like purple,red,green,yellow etc whereas this doesnot

accept light colors like pink, lightblue etc. Why is it so???

6

Hi,


For XLS files, you need to add/update color to Excel Standard Color palette before using /setting the color. The reason is simple MS Excel Standard Color palette has only 56 colors, one has to change for his desired favorite color that is not present on the palette. See the topic for reference:
Format cells|Documentation


However, for XLSX file formats, it has lots of color and you don't need to change or update color palette any more.

Thank you.
7

Hi,


I think it should work. Please try the following code as an example:


Cell cell = worksheet.Cells[“A3”];

Style st = new Style();

st.ForegroundColor = Color.LightBlue;

st.Pattern = BackgroundType.Solid;

cell.SetStyle(st);

This is working at my end. If it is not working at your end, please follow the guidelines as suggested by Shakeel and Amjad above.
8

Thanks for all your replies.

After adding ChangePallette method only it got worked.

Thank you so much.

9

Glad to know that your issue is resolved now. Feel free to contact us, should you feel any difficulty.