Setting Row Color

[Playground]

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.Rows.Style.BackgroundColor = Color.Black; objExcelDesigner.Excel.Worksheets[0].Cells.Rows.Style.ForegroundColor = Color.Black; objExcelDesigner.Excel.Worksheets[0].Cells.Rows.Style.Font.Color = Color.Black; objExcelDesigner.Excel.Worksheets[0].Cells.Rows.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

[Laurence]

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");

[Playground]

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.

[Laurence]

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;
…

[Playground]

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

[Laurence]

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.