I am appying formatting on a row but it doesnt get applied.
Heres my code -
ExWorkBook.Styles.Add()
Dim sty As Style = ExWorkBook.Styles(0)
sty.Font.Color = Color.FromArgb(220, 220, 220)
Dim flag As StyleFlag = New StyleFlag
flag.FontColor = True
Dim row As Row = ExWorkSheet.Cells.Rows(0)
row.ApplyStyle(sty, flag)
Hi,
Thanks for considering Aspose.
Well, the color you are applying is not present on the standard excel color palette which has 56 colors (0-55) indexed, please check :
Aspose.Cells API Reference pages
So you have to add it on the color palette first before using it. I tried the following code and it works fine (This color is very ligh gray):
Dim ExWorkBook As Workbook = New Workbook()
Dim ExWorkSheet As Worksheet = ExWorkBook.Worksheets(0)
Dim chk As Boolean = ExWorkBook.IsColorInPalette(Color.FromArgb(220, 220, 220))
MessageBox.Show(chk.ToString())
ExWorkBook.ChangePalette(Color.FromArgb(220, 220, 220), 55)
ExWorkBook.Styles.Add()
Dim sty As Style = ExWorkBook.Styles(0)
sty.Font.Color = Color.FromArgb(220, 220, 220)
Dim flag As StyleFlag = New StyleFlag()
flag.FontColor = True
Dim row As Row = ExWorkSheet.Cells.Rows(0)
row.ApplyStyle(sty, flag)
ExWorkBook.Save("d:\test\formattingrow.xls")
Thank you.
I have attached the file.Heres my code-
ExWorkBook = New Workbook
strFileName = "D:\location.xls"
ExWorkBook.Open(strFileName)
ExWorkSheet = ExWorkBook.Worksheets(0)
ExWorkSheet.Cells.Merge(1, 1, 1, 8)
ExWorkSheet.Cells("A1").PutValue(strRegion & " " & strMonth & "-" & strYear & "(" & strPT & ")")
ExWorkSheet.Cells("A1").Style.Font.Color = Color.Red
ExWorkSheet.Cells("A1").Style.BackgroundColor = Color.Gold
ExWorkBook.Styles.Add()
Dim sty As Style = ExWorkBook.Styles(0)
sty.Font.Underline = FontUnderlineType.Single
sty.Borders.SetStyle(CellBorderType.Medium)
sty.BackgroundColor = Color.FromArgb(200, 200, 200)
sty.Font.Color = Color.FromArgb(220, 220, 220)
Dim flag As StyleFlag = New StyleFlag
flag.FontUnderline = True
flag.FontColor = True
Dim row As Row = ExWorkSheet.Cells.Rows(0)
row.ApplyStyle(sty, flag)
ExWorkSheet.Cells.Rows(0).Style.Borders.SetStyle(CellBorderType.Medium)
ExWorkSheet.Cells.Rows(0).Style.Font.Color = Color.FromArgb(0, 0, 0)
ExWorkSheet.Cells.Rows(0).Style.BackgroundColor = Color.FromArgb(200, 200, 200)
ExWorkSheet.Cells.SetRowHeight(0, 50)
strReportName = "Stack and Rank"
ExWorkSheet.Cells.Rows(2).Style.Font.IsBold = True
ExWorkSheet.Cells.Rows(2).Style.HorizontalAlignment = TextAlignmentType.Center
ExWorkSheet.PageSetup.Orientation = PageOrientationType.Landscape
ExWorkSheet.IsPageBreakPreview = True
ExWorkSheet.AutoFitColumns()
ExWorkSheet.Cells.CreateRange(1, 1, ExWorkSheet.Cells.Rows.Count, ExWorkSheet.Cells.Columns.Count).Style.Borders.SetStyle(CellBorderType.Medium)
'Set Margin
ExWorkSheet.PageSetup.TopMargin = 5
ExWorkSheet.PageSetup.LeftMargin = 5
ExWorkSheet.PageSetup.RightMargin = 5
ExWorkSheet.PageSetup.BottomMargin = 5
'Report Header/Footer
ExWorkSheet.PageSetup.SetHeader(0, "&B &14 Stack and Rank") ' for " & strRegion & " " & strMonth & "-" & strYear
ExWorkSheet.PageSetup.SetHeader(2, "Printed On : &D")
ExWorkSheet.PageSetup.SetFooter(2, "Page &P of &N")
ExWorkSheet.PageSetup.SetFooter(0, "Note: The Daily Average is calculated based on the number of days containing a revenue transaction.")
'Setting paper size to "Letter" size
ExWorkSheet.PageSetup.PaperSize = PaperSizeType.PaperLetter
'Repeat the first row
ExWorkSheet.PageSetup.PrintTitleRows = "$1:$1"
ExWorkSheet.Cells("A1").Style.IsTextWrapped = True
ExWorkSheet.AutoFitColumns()
ExWorkSheet.AutoFitRows()
ExWorkSheet.IsPageBreakPreview = False
ExWorkBook.Save(strFileName)
Hi,
Thanks for considering Aspose.
Could you please post your template excel file here, so that we may check and resolve the issue soon.
To attach file(s), zip the file(s), Click "Options" tab, click "Add/Update" button to "Browse" the file and "Save" it.
And which version of Aspose.Cells you are using? Did you try the latest version(4.2).
Thank you.
i have edited the my previous post and attached the sample file to be formatted with my code.
Hi,
How did you extract this template file, I mean from the third party etc.? The excel file may not be in the proper BIFF8 format and therefore some formattings might not be applied properly. We will look into it, check your template file to figure it out and get back to you soon.
And by the way, We checked your code a bit, I think it needs to be optimized. I mean, at one place you change a cell's background color and font text color, In the very next lines you define a style and change formattings of the cell's row, modify the filling color and font text color. And Moreover after two three lines, you again change the style of that row related background color and text color again after again. So you are mixing a lots of other things too. For getting information about how to apply background, foreground and font text color of the cells, please check the thread: <A href="</A></P> <P>Thank you. </P> <P> </P>
1. Row.Style property is read-only. Please don't change its settings.
2. When you create a Range, Range.Style will return null.
3. When setting colors, please make sure it's included in the palette.
Following is my sample code:
Dim exworkbook As Workbook
Dim strFileName As String
Dim exworksheet As Worksheet
Dim strReportName As String
ExWorkBook = New Workbook()
strFileName = "D:\test\location.xls"
ExWorkBook.Open(strFileName)
ExWorkSheet = ExWorkBook.Worksheets(0)
exworksheet.Cells.Merge(1, 1, 1, 8)
exworkbook.ChangePalette(Color.Gold, 55)
exworkbook.ChangePalette(Color.FromArgb(200, 200, 200), 54)
exworksheet.Cells("A1").Style.Font.Color = Color.Red
ExWorkSheet.Cells("A1").Style.BackgroundColor = Color.Gold
ExWorkBook.Styles.Add()
Dim sty As Style = ExWorkBook.Styles(0)
sty.Font.Underline = FontUnderlineType.Single
sty.Borders.SetStyle(CellBorderType.Medium)
sty.Font.Color = Color.FromArgb(0, 0, 0)
sty.ForegroundColor() = Color.FromArgb(200, 200, 200)
sty.Pattern = BackgroundType.Solid
Dim flag As StyleFlag = New StyleFlag()
flag.FontUnderline = True
flag.FontColor = True
flag.CellShading = True
Dim row As Row = ExWorkSheet.Cells.Rows(0)
row.ApplyStyle(sty, flag)
ExWorkSheet.Cells.SetRowHeight(0, 50)
ExWorkSheet.AutoFitColumns()
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black)
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.TopBorder, CellBorderType.Medium, Color.Black)
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Black)
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black)
strFileName = "d:\test\abc.xls"
ExWorkBook.Save(strFileName)
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black)
Why is 2 added to maxdaatcolumn. Also I want border around each cell containing the data.
Hi,
exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2).SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Medium, Color.Black)
Why is 2 added to maxdaatcolumn
Well, Cells.MaxDataRow and Cells.MaxDataColumn return the maximum row / column index which has data in it. For ...."Why is 2 added to maxdaatcolumn"... Actually, your template file contains the maximum data column "S" but the the cells (which contains data) are merged... i.e. Q,R,S are merged to become Q column (Q2:Q34), so we have to add 2 to MaxDataColumn to get the complete data range of cells for setting borders around them.
Also I want border around each cell containing the data.
Please try the code and it works fine:
Dim exworkbook As Workbook
Dim strFileName As String
Dim exworksheet As Worksheet
Dim strReportName As String
exworkbook = New Workbook()
strFileName = "D:\test\location.xls"
exworkbook.Open(strFileName)
exworksheet = exworkbook.Worksheets(0)
exworksheet.Cells.Merge(1, 1, 1, 8)
exworkbook.ChangePalette(Color.Gold, 55)
exworkbook.ChangePalette(Color.FromArgb(200, 200, 200), 54)
exworksheet.Cells("A1").Style.Font.Color = Color.Red
exworksheet.Cells("A1").Style.BackgroundColor = Color.Gold
exworkbook.Styles.Add()
Dim sty As Style = exworkbook.Styles(0)
sty.Font.Underline = FontUnderlineType.Single
sty.Borders.SetStyle(CellBorderType.Medium)
sty.Font.Color = Color.FromArgb(0, 0, 0)
sty.ForegroundColor = Color.FromArgb(200, 200, 200)
sty.Pattern = BackgroundType.Solid
Dim flag As StyleFlag = New StyleFlag()
flag.FontUnderline = True
flag.FontColor = True
flag.CellShading = True
Dim row As Row = exworksheet.Cells.Rows(0)
row.ApplyStyle(sty, flag)
exworksheet.Cells.SetRowHeight(0, 50)
exworksheet.AutoFitColumns()
Dim index As Integer = exworkbook.Styles.Add()
Dim stl As Style = exworkbook.Styles(index)
stl.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Medium
stl.Borders(BorderType.TopBorder).Color = Color.Black
stl.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
stl.Borders(BorderType.BottomBorder).Color = Color.Black
stl.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Medium
stl.Borders(BorderType.LeftBorder).Color = Color.Black
stl.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Medium
stl.Borders(BorderType.RightBorder).Color = Color.Black
Dim range1 As Aspose.Cells.Range = exworksheet.Cells.CreateRange(1, 1, exworksheet.Cells.MaxDataRow, exworksheet.Cells.MaxDataColumn + 2)
range1.Style = stl
range1.Name = "MyRange"
strFileName = "d:\test\locationabc.xls"
exworkbook.Save(strFileName)
Thank you.