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
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.
@Playground,
Aspose.Cells is the latest product that has replaced Aspose.Excel which is no more available now. This new product contains all the latest features to apply formatting in Excel workbooks. Here is an example that uses Aspose.Cells to set background color of entire rows.
//Create workbook
Workbook workbook = new Workbook();
//Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Create styke object
Style st = workbook.CreateStyle();
st.Pattern = BackgroundType.Solid;
st.ForegroundColor = Color.Red;
//Create style flag
StyleFlag flag = new StyleFlag();
flag.CellShading = true;
//Apply colors to rows
//3rd row
worksheet.Cells.Rows[2].ApplyStyle(st, flag);
//10th row
worksheet.Cells.Rows[9].ApplyStyle(st, flag);
//Save the workbook
workbook.Save("output_Color.xlsx");
Here is a list of articles that explains formatting in workbooks with ready to run examples:
Cells Formatting
Format Cells using GetStyle and SetStyle Methods
Conditional Formatting
Apply Advanced Conditional Formatting
You can test the features of this product free of cost using the latest free trial version here:
Aspose.Cells for .NET (Latest Version)
This link points to a runnable solution that can be used to test all the advanced features of this product.
