Free Support Forum - aspose.com

Setting Row Color

Hi,

We are outputting our Execl document using the ExcelDesigner and a template Excel document.

After processing the Excel document I want to loop through the rows and set the row color for a row that matches my search criteria.

I am just running tests right now so I’m changing the row color for row 6.


objExcelDesigner.Excel.Worksheets[0].Cells.RowsDevil.Style.BackgroundColor = Color.Black;

objExcelDesigner.Excel.Worksheets[0].Cells.RowsDevil.Style.ForegroundColor = Color.Black;

objExcelDesigner.Excel.Worksheets[0].Cells.RowsDevil.Style.Font.Color = Color.Black;

objExcelDesigner.Excel.Worksheets[0].Cells.RowsDevil.Style.Pattern = BackgroundType.Solid;







After running this code it will color the row properly. What it doesn’t do is color the row where my content is located. It only colors the columns of the row after my generated content ends. Is this a bug that other people are experiencing or am I setting the color wrong or something?

Thanks,

Martin

Row style setting doesn't overwrite the format setting in cells. In your case, you can try to set row style first, then set data for cells. For example:

Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;
cells.Rows[0].Style.ForegroundColor = Color.Red;
cells.Rows[0].Style.Pattern = BackgroundType.Solid;

cells["A1"].PutValue("hello");

I tried your suggestion, but it just pushes the cell down below my data where I had the formatting.

I need to be able to set the colour after the fact since I will be setting colors depending on cell values.

I tried removing all default styles from my template document, but it still won't colour the cell. It's like the cell style is locked after it has been processed.

Hmmmmm, maybe I need to generate my documents differently.

If you have to set colors depending on cell values, you can try:

cell.Style.Font.Color = Color.Blue;
cell.Style.Foreground.Color = Color.Yellow;

Okay, I did some more testing and figured a little more out. Here is the code that we use to generate our excel file:


//Set the license

Aspose.Excel.License objLicense = new Aspose.Excel.License();

objLicense.SetLicense(“Aspose.Excel.lic”);



//Create an instance of the ASPOSE excel designer
ExcelDesigner objExcelDesigner = new ExcelDesigner();


//Open the template file

objExcelDesigner.Open(sTemplatePath + sTemplateFile);


//Set the datasource for the document

objExcelDesigner.SetDataSource(dsReport);

objExcelDesigner.SetDataSource(“Variable”, “Single Variable”);


//Build the document

objExcelDesigner.Process();


for(int i=0; i<30; i++)

{

objExcelDesigner.Excel.Worksheets[0].Cells[7,i].Style.ForegroundColor = Color.Red;

objExcelDesigner.Excel.Worksheets[0].Cells[7,i].Style.Pattern = BackgroundType.Solid;

}


//Save the document

objExcelDesigner.Save(sSaveFile, SaveType.OpenInExcel, FileFormatType.Default, this.Response);


As you can see in the for loop near the bottom of the code block I am setting the cell colors for all cells from 0 to 29.

Now shouldn’t I be able to do the same thing with:


objExcelDesigner.Excel.Worksheets[0].Cells.Rows[7].Style.ForegroundColor = Color.Red;

objExcelDesigner.Excel.Worksheets[0].Cells.Rows[7].Style.Font.Color = Color.Red;


When I use the above code it won’t color any of the cells that have been populated with the SmartMarkers. Though it will color the rest of the row where there are no smart markers. Is this a bug or just the way it works.

If I have to use the for loop how can I find out how many columns are in the spreadsheet?

Thank you for your time on this,

Martin

The row style setting doesn't overwrite the style on cells with content. But you don't have to loop to find the number of columns to set style for those cells. In your cells with smart makers, please set the formatting as your wish. Aspose.Excel will copy those formattings in the populated cells.