Bold an entire row using Aspose.Cells in VB .NET

Aspose.Cells Product Family
1

Hello, I'm having trouble bolding an entire row in my Spreadsheet. The code I am using is:

Dim wb As New aCells.Workbook

Dim ws As aCells.Worksheet = wb.Worksheets(0)

ws.Cells.ImportDataTable(dt, True, 0, 0)

ws.Cells.Columns(3).Style.Number = 14

ws.Cells.Rows(0).Style.Font.IsBold = True

ws.AutoFitColumns()

ws.AutoFitRows()

wb.Save("Usage.xls", aCells.FileFormatType.Default, aCells.SaveType.OpenInExcel, Response)

As you can see it is pretty simple code. The line ws.Cells.Rows(0).Style.Font.IsBold = True doesn't appear to do anything. It seems pretty straight forward...am I doing something wrong, or is this a bug?

Thanks!

Dan

2

Hi Dan,

Thanks for considering Aspose.

In the newer versions of Aspose.Cells, you will format a row/column in the following way, I have changed you code a bit and it will work fine:

Dim wb As New aCells.Workbook

Dim ws As aCells.Worksheet = wb.Worksheets(0)

ws.Cells.ImportDataTable(dt, True, 0, 0)

Dim style1 As Style = wb.Styles(wb.Styles.Add())

style1.Number = 14

Dim flag1 As StyleFlag = New StyleFlag()

flag1.NumberFormat =True

Dim col As Column = ws.Cells.Columns(3)

column.ApplyStyle(style1,flag1)

Dim style2 As Style = wb.Styles(wb.Styles.Add())

style2.Font.IsBold=True

Dim flag2 As StyleFlag = New StyleFlag()

flag2.FontBold = True

Dim row As Row = ws.Cells.Rows(0)

row.ApplyStyle(style2,flag2)

ws.AutoFitColumns()

ws.AutoFitRows()

wb.Save("Usage.xls", aCells.FileFormatType.Default, aCells.SaveType.OpenInExcel, Response)

Thank you.

3

I'll give that a try and let you know how it goes. Out of curiosity, may I ask why you have changed it to be so much more complicated? I would much rather use one line rather than having to create and apply styles and flags over 10+ lines. If you can fix it so that I have the option to apply a single style change in one line (like my code shows) OR apply a style object (like your code shows), I would appreciate it!

Thanks!

Dan

4

One more thing I forgot to mention is that it seems strange that I would have to not only create a style, but create a StyleFlag that essentially duplicates my request.

For example, Style sets bold to True and Flag sets Bold to True.

Is there a reason for this?

Dan

5

Hi Dan,

Cells in a row may have different formattings. For example, A1's font color is red and A2's font color is blue. If you set a Style object to the whole row, they will be changed to same formattings. So A1's font color and A2's font color may be all changed to black. But I think that isn't what you wished. You only wish to set A1, A2 font to bold but don't want to change their color. StyleFlag is for this purpose.

If you set Style object's font bold to true and StyleFlag also sets bold to true, the whole row's font will be bold.

If you set Style object's font bold to false and StyleFlag also sets bold to true, the whole row's font will be normal, no matter if it's bold before.

Is it clear?

6

Laurence,

Yes, your explanation makes sense, but I still think it is overly complicated to perform such a simple task as bolding the entire row. My solution would be that:

myWorksheet.Cells.Rows(0).Style.Font.IsBold = True

would simply traverse through all the cells in that row and set the font to bold, regardless of what font type/color/size/etc is in each cell. Right now I am achieving this same thing via a loop.

For i As Integer = 0 To myWorksheet.Cells.MaxDataColumn
myWorksheet.Cells(0,i).Style.Font.IsBold = True
Next

Now granted that is only 3 lines of code, but I would assume that the 1 line of code:

myWorksheet.Cells.Rows(0).Style.Font.IsBold = True

would do the same thing as that loop. Doesn't seem very hard. Could you implement this? And don't just do it for Font.IsBold, but for every applicable attribute, such as Color, IsBold, IsItalic, IsStrikeout, IsSubscript, IsSuperscript, Name, Size, Underline. And why stop there when every Style attribute should be able to carry through.

I think this is reasonable and the right way to do it since in Excel I don't have to create a style and then apply that style to the row that I want, I simply click the row header (i.e. myWorksheet.Cells.Rows(0)) and then click Bold (i.e. .Style.Font.IsBold = True).

Please let me know what you think. I just don't see any benefit in creating styles and style flags when you could easily incorporate this into the built-in functionality.

Thanks for your consideration!

Dan

7

Hi Dan,

Well, Creating Style objects can definitely give you better performance and save your memory in the long run.

http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/PerformanceTips.html

Thank you.