Workbook wb = new Workbook(@“1.xlsx”);
Style style = new CellsFactory().CreateStyle();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Red;
wb.Worksheets[0].Cells.CreateRange(0, 1, false).SetStyle(style, true);
wb.Save(@“2.xlsx”);
The code above will make sheet have 16384 columns.
But manual operations in MS Excel will not.
So what is the equivalent way to do this in Aspose.Cells?
image.jpg (120.1 KB)
After trying your code snippet, the whole (first) row cells will be shaded with red color (background). I tried the same operation in MS Excel manually and found the same behavior. Please open your Excel file into MS Excel manually and then click on row header (1) to select it, now set the fill color to red and you will see that the background color of the whole (first) row (up to 16384 columns) would be in red.
wb.Worksheets[0].Cells.CreateRange(0, 1, false).SetStyle(style, true)
Please note, when you use the above line of code, it will select the whole row to set the formatting/style.
See the following sample code to try it instead and accomplish your task for your reference.
Workbook wb = new Workbook("d:\\files\\Bk_data1.xlsx");
Style style = new CellsFactory().CreateStyle();
style.Pattern = Aspose.Cells.BackgroundType.Solid;
style.ForegroundColor = System.Drawing.Color.Red;
Aspose.Cells.Range range = wb.Worksheets[0].Cells.MaxDisplayRange;
range.SetStyle(style);
//Alternatively, you may apply style cell by cell using Cell.SetStyle() method
//.......No, calling wb.Worksheets[0].Cells.CreateRange(0, 1, false).SetStyle(style, true) and shading whole row in MS Excel have different results.
// step 1
{
Workbook wb = new Workbook(@“1.xlsx”);
Style style = new CellsFactory().CreateStyle();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Red;
wb.Worksheets[0].Cells.CreateRange(0, 1, false).SetStyle(style, true);
wb.Save(@“C:\Users\hzwangzhigang\Desktop\2.xlsx”);
}
// step 2
{
Workbook wb = new Workbook(@"2.xlsx");
Console.WriteLine(wb.Worksheets[0].Cells.MaxColumn);
}
In step 2 I can see the sheet will have 16384 columns. But if I do step 1 in MS Excel instead, I will get a sheet with only 1 column. Here is the test file and results (by code/by MS Excel). The difference is when dragging the horizontal scroll bar in the bottom right, you can see 2_by_Code.xlsx has 16384 columns, but 2_by_MS_Excel.xlsx does not have.
.
test_file.zip (22.0 KB)
Finally I get a solution. This two lines will work well.
wb.Worksheets[0].Cells.Rows[0].SetStyle(style);
wb.Worksheets[0].Cells.CreateRange(0, 0, 1, wb.Worksheets[0].Cells.MaxColumn + 1).SetStyle(style, true);
The first line will shade the whole row, but will not affect existing cells in the row (as mentioned in your doc).
And the second line will shade all existing cells int the row.
So I can combine two calls to achieve my result now.
But still I think the behavior of wb.Worksheets[0].Cells.CreateRange(0, 1, false).SetStyle(style, true) is a little bit strange.
@Zhigang_Wang
From your sample code, it can be seen that you have created an entire line of scope and set the style. So the maximum column has changed. This is the expected outcome. You can obtain the desired data through different attributes, please refer to the following example code.
Workbook wb = new Workbook(filePath + "1.xlsx");
Console.WriteLine("max column: " + wb.Worksheets[0].Cells.MaxColumn);
Style style = new CellsFactory().CreateStyle();
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Red;
Range range = wb.Worksheets[0].Cells.CreateRange(0, 1, false);
Console.WriteLine("range: " + range.RefersTo);
range.SetStyle(style, true);
wb.Save(filePath + "2.xlsx");
wb = new Workbook(filePath + "2.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine("max column: " + cells.MaxColumn);
Console.WriteLine("max data column: " + cells.MaxDataColumn);
Console.WriteLine("max row: " + cells.MaxRow);
Console.WriteLine("max data row: " + cells.MaxDataRow);
The output result:
max column: 0
range: =Sheet1!$1:$1
max column: 16383
max data column: 0
max row: 0
max data row: 0
Your understanding is correct. When styling a row, the style is stored on the row and no new cells are created. If you create a range and style it, and the cells referenced by the range does not exist, new cells will be created, so you will find that the maximum column has changed.
Thank you for your explanation. I am clear now : CreateRange(0, 1, false) means 1*16384 cells.
@Zhigang_Wang
You are welcome. Creating a range does not create new cells, but when you style the range, it creates new cells. Because the style is stored on cells.
If you have any questions, please feel free to contact us at any time.
@Zhigang_Wang
If you want to apply style to the whole row, please use Row.ApplyStyle() method.
And we will call Row.ApplyStyle() if the range contains whole rows too in the next version.
When we apply the style to the range, we have to apply style to each cell because we can not change the style of the row if the range is a part of row.
Thank you, Row.ApplyStyle works very well!