Rows formatting problem

Aspose.Cells Product Family
1

Hi,

I am formatting existing excel file in vb.net.

Looks like I cannot format individual row in the range.

Dim oSheet As Worksheet = oWB.Worksheets(0)

'Create range.

Dim oRange As Range

oSheet.Cells.CreateRange(0, 0, 5, 5)

oRange.Name = "DataRange"

'Declare a style object.

Dim oRangeStyle As Style

oRangeStyle = oWB.Styles(oWB.Styles.Add())

oRangeStyle.Font..Size = "12"

oRange.Style = oRangeStyle

After that, I want to change color just in first row of the range

Dim oColumnNameStyle As Style = oWB.Styles(oWB.Styles.Add())

oColumnNameStyle = oSheet.Cells.Rows(0).Style

oColumnNameStyle.Font.IsBold = True

oColumnNameStyle.Font.Color = Color.Blue

oSheet.Cells.Rows(intHeaderRow).Style = oColumnNameStyle

After I format one row in the range, whole range was formatted as first row.

In addition, I have a question:

I used to format excel files using Excel.Application object.

oXL = CType(CreateObject("Excel.Application"), Excel.Application)

oXL.Visible = True

After line oXL.Visible = True, excel file was visible and I could see what I do in every line of the code.

Is it available with Aspose.Cells

Please help.

2

Hi,

Thanks for considering Aspose.

Well, I tested a bit, there seems to have some problem with formatting ranges. When we change any range cell's existing format, the whole range gets disturbed and takes that format. We look into it to figure out and get back to you soon.

For second query, where it is seen.... in MS Excel? Well related Aspose.Cells, It does not have any interface, rather it is a Spreadsheet management library. Although you may utilize (in Asp.Net App) an overloaded method Workbook.Save() i.e.,

Overloads Public Sub Save( _
ByVal fileName As String, _
ByVal fileFormatType As FileFormatType, _
ByVal saveType As SaveType, _
ByVal response As HttpResponse _
) where SaveType enum has OpenInBrowser and OpenInExcel members. The server utilizes this method to send the output excel file to the client's brwoser / client's excel.

In WinForms, you may try System.Diagnostics.Process.Start(fileName) to open and view an exccl file into the browser.

Thank you.

3

Hi Amjad,

Thanks for the response.

Please let me know when you figure out the problem ASAP.

Thanks.

4

Hi,

Thanks for considering Aspose.

Actually, when you use Range.Style to format a range, a same Style object is assigned to all cells in this range. So if you change formatting of any cell, it will affects all other cells.

Please use Range.ApplyStyle method to formatting ranges. It will solve this problem. We attached a new version here which solves a bug in ApplyStyle method, please try it. And also when you format a row/column you may also use Row/Column.ApplyStyle method.

I tested it with your code (made a few addition and adjustments to it) and it works fine for your need, Following it the code:

Dim oWB As Workbook = New Workbook()

Dim oSheet As Worksheet = oWB.Worksheets(0)

Dim i As Integer

Dim j As Integer

For i = 0 To 10

For j = 0 To 10

oSheet.Cells(i, j).PutValue(i.ToString() + "," + j.ToString())

Next

Next

'Create range.

Dim oRange As Range = oSheet.Cells.CreateRange(0, 0, 5, 5)

oRange.Name = "DataRange"

'Declare a style object.

Dim oRangeStyle As Style

'Declare a StyleFlag object.

Dim flg As StyleFlag = New StyleFlag()

oRangeStyle = oWB.Styles(oWB.Styles.Add())

flg.FontSize = True

oRangeStyle.Font.Size = "12"

oRange.ApplyStyle(oRangeStyle, flg)

'After that, I want to change color just in first row of the range

Dim intHeaderRow As Integer = 0

Dim oColumnNameStyle As Style = oWB.Styles(oWB.Styles.Add())

'Declare a StyleFlag Object.

Dim flag As StyleFlag = New StyleFlag()

flag.FontSize = True

flag.FontBold = True

flag.FontColor = True

oColumnNameStyle.Font.Size = "12"

oColumnNameStyle.Font.IsBold = True

oColumnNameStyle.Font.Color = Color.Blue

oSheet.Cells.Rows(intHeaderRow).ApplyStyle(oColumnNameStyle, flag)

'Save the excel file.

oWB.Save("d:\test\formatrow.xls")

Thank you.

5

Thank you, it works.

6

Hi,

I have to add ForegroundColor to the row but StyleFlag doesn’t have this property.

Please let me know how I can set ForegroundColor for the row.

Dim oColumnNameStyle As Style = oWB.Styles(oWB.Styles.Add())
Dim oColumnNameFlag As StyleFlag = New StyleFlag()
oColumnNameFlag.FontBold = True
oColumnNameStyle.Font.IsBold = True
oColumnNameStyle.ForegroundColor = Color.Green
oSheet.Cells.Rows(intHeaderRow).ApplyStyle(oColumnNameStyle, oColumnNameFlag)

Thanks

7

Hi,

Thanks for considering Aspose.

Well, you may use styleFlag.CellShading = True to apply foreground color.

Dim oColumnNameStyle As Style = oWB.Styles(oWB.Styles.Add())

Dim oColumnNameFlag As StyleFlag = New StyleFlag()

oColumnNameFlag.FontBold = True

oColumnNameStyle.Font.IsBold = True

oColumnNameStyle.ForegroundColor = Color.Green

oColumnNameStyle.Pattern = BackgroundType.Solid

oColumnNameFlag.CellShading = True

oSheet.Cells.Rows(intHeaderRow).ApplyStyle(oColumnNameStyle, oColumnNameFlag)

Thank you.

8

Thanks, it works.