Hi
What would be the easiest approach to set up alternate row colors?
Hi,
Is this FlexCel for .NET or FlexCel for Delphi? If .NET, are you using Reports or XlsFile?
Hi
This is for .NET. I am using XlsFile like this:
Private oXlsFile As FlexCel.XlsAdapter.XlsFile = Nothing
oXlsFile = New FlexCel.XlsAdapter.XlsFile(True)
' Number of sheets
oXlsFile.NewFile(nTotalSheet)
' Set the column width
oXlsFile.SetRowHeight(1, loRow("HeaderHeight") * 20)
' Initialization
oXlsFile.ActiveSheet = nActiveSheet
' If we have a sheet name
If lcSheetName.Length > 0 Then
oXlsFile.SheetName = lcSheetName
End If
' For each column
For Each loObject In oColumn
' Initialization
lcName = loObject(1)
loTFlxFormatHeader = loObject(2)
loTFlxFormatData = loObject(4)
lnWidth = loObject(3)
' Initialization
lnCounter = lnCounter + 1
' Set the column header
oXlsFile.SetCellFromString(1, lnCounter, lcName, oXlsFile.AddFormat(loTFlxFormatHeader))
' Set the column width
oXlsFile.SetColWidth(lnCounter, lnWidth)
' Set the column data format range
oXlsFile.SetCellFormat(2, lnCounter, nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
Next
...
oXlsFile.Save(lcFile)
I did not include all the code but this should give you an idea.
Hi,
You should set the row format while setting the cells. The code you quoted is for setting the columns, but after that you should have something like:
For i = 1 to RecordCount
...
oXlsFile.SetCellValue(row, col, value)
...
Next
In this loop, you can add a
oXlsFile.SetRowFormat(row, someformat, false);
And make someformat alternate between colors.
Now, some notes on performance, since you are doing big spreadsheets:
1)You don't need to call
oXlsFile.SetCellFormat(2, lnCounter, nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
Or SetCellFormat at all.
When doing SetCellValue (or SetCellFromString), there is an XF parameter where you can set the format for the cell together with the value in a single call.
So you can do:
oXlsFile.SetCellValue(row, col, value, oXlsFile.AddFormat(loTFlxFormatData))
Instead of doing:
oXlsFile.SetCellValue(row, col, value,)
oXlsFile.SetCellFormat(row, col, oXlsFile.AddFormat(loTFlxFormatData));
Setting the cell format and value at the same time is faster than setting the value first and then the format in 2 calls.
This is similar for SetCellFromString, even if as explained earlier you should use SetCellValue when possible.
2)For the big loop (where you spend most of the time), you might get some extra cpu cycles by caching the formats. That is, instead of doing:
oXlsFile.SetCellValue(row, col, value, oXlsFile.AddFormat(loTFlxFormatData))
You could call AddFormat before entering the loop, like:
dim XFFormatData= oXlsFile.AddFormat(loTFlxFormatData)
for i = 1 to row
oXlsFile.SetCellValue(row, col, value, XFFormatData)
next
This will be faster too. Of course, you'll need to cache all formats you are going to use, so you'll have for example XFFormatPercentEvenRow, XFFormatPercentOddRow, etc.
Then apply the correct format directly instead of adding with xls.AddFormat each time. xls.AddFormat won't add the format if it exists, so from a functional point is the same, but AddFormat has to search in the existing formats to check if the format already exists, and while that is fast, if you have millions of cells time sums up.
3)For the row, there is last parameter that is "ResetRow". In the example I set it to false, and for performance this is what you need.
The thing is, in Excel you can format full columns, full rows, and cells. And they have this order of priority. So if you format column A blue, row 1 green and cell A1 red, A1 will be red. If you format col A blue and row 1 green A1 will be green.
The row format will apply only to empty cells, since non empty cells will have the format you set in
xls.SetCellValue or xls.SetCellFormat.
If you set ResetRow = true, then SetRowFormat will re-format all cells in the row so their format matches the format in the row. But again, you are doing extra work here: Formatting the cells with SetCellValue, then reformatting them when you apply SetRowFormat.
You should set the correct row colors when calling SetCellValue, and then set the row format with SetRowFormat only for the empty cells.
Hi
Thanks for the information
I did try this:
' It is not possible to create an object directly as this is Friend, so we have to get it from Excel
loTFlxFormat = oXlsFile.GetCellVisibleFormatDef(lnCounter + 2, 1)
loTFlxFormat.FillPattern.BgColor = FlexCel.Core.Colors.Blue
' For each record
For lnCounter = 0 To oData.nCount - 1
loRow = oData.oRows(lnCounter)
' Reset the values
lnColumn = 0
' If this is even
If lnCounter Mod 2 = 0 Then
' Initialize the row background color
oXlsFile.SetRowFormat(lnCounter + 2, oXlsFile.AddFormat(loTFlxFormat), False)
End If
I see that it goes in the If lnCounter Mod 2 = 0 at every two records. But, the row is not blue. There must be something wrong in loTFlxFormat in the way I initialize it.
As far the mention about not passing oXlsFile.AddFormat at every cell initialization, what you saw there was only for the header row. And, as each header column could have a different format, this is why I have to do it like this. On the data sections, for all the rows, the "Set the column data format range" line is where I set the format for an entire column. So, if I have 2000 rows in one sheet, this is only one call. I do not know how much simpler I can make it.
Hi,
Change
loTFlxFormat.FillPattern.BgColor = FlexCel.Core.Colors.Blue
to
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.Blue
This is something that is sometimes confusing, but we use the same convention as Excel does.
The thing is: FgColor isn't the text color: That is Font.Color. FbColor and BgColor are both used when you have a fill pattern, like say horizontal line hatch fill. Then the horizontal lines will be in the color of FgColor and the background in the color of BgColor. But when you have a solid fill, Excel uses the FgColor to paint the cell and ignores bgcolor. It is like if solid fill had some lines painted in fgcolor covering all the cell, and the bgcolor is fully covered and invisible.
Hi
I changed as requested but I cannot see any background color:
' It is not possible to create an object directly as this is Friend, so we have to get it from Excel
loTFlxFormat = oXlsFile.GetCellVisibleFormatDef(lnCounter + 2, 1)
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.Blue
' Save it in memory so to avoid calling AddFormat() every time
lnXF = oXlsFile.AddFormat(loTFlxFormat)
' For each record
For lnCounter = 0 To oData.nCount - 1
loRow = oData.oRows(lnCounter)
' Reset the values
lnColumn = 0
' If this is even
If lnCounter Mod 2 = 0 Then
' Initialize the row background color
oXlsFile.SetRowFormat(lnCounter + 2, lnXF, False)
End If
Are you setting the fill pattern to solid instead of none?
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.Blue
loTFlxFormat.FillPattern.Pattern = FlexCel.Core.TFlxPatternStyle.Solid
Hi
I am not sure if this is needed but I did change as requested and it still does not show the background:
' It is not possible to create an object directly as this is Friend, so we have to get it from Excel
loTFlxFormat = oXlsFile.GetCellVisibleFormatDef(2, 1)
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.Blue
loTFlxFormat.FillPattern.Pattern = FlexCel.Core.TFlxPatternStyle.Solid
' Save it in memory so to avoid calling AddFormat() every time
lnXF = oXlsFile.AddFormat(loTFlxFormat)
' For each record
For lnCounter = 0 To oData.nCount - 1
loRow = oData.oRows(lnCounter)
' Reset the values
lnColumn = 0
' If this is even
If lnCounter Mod 2 = 0 Then
' Initialize the row background color
oXlsFile.SetRowFormat(lnCounter + 2, lnXF, False)
End If
Yes, setting the fill style to solid is necessary.
The code you have now should work. I've just tried it in a console application. I created a new console appliaction, and added this code:
Option Strict Off
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Module Module1
Sub Main()
Dim oData_nCount = 10
Dim oData_oRows = 5
Dim oXlsFile As XlsFile = New XlsFile(1, True)
Dim loTFlxFormat As TFlxFormat
' It is not possible to create an object directly as this is Friend, so we have to get it from Excel
loTFlxFormat = oXlsFile.GetCellVisibleFormatDef(2, 1)
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.Blue
loTFlxFormat.FillPattern.Pattern = FlexCel.Core.TFlxPatternStyle.Solid
' Save it in memory so to avoid calling AddFormat() every time
Dim lnXF = oXlsFile.AddFormat(loTFlxFormat)
' For each record
For lnCounter = 0 To oData_nCount - 1
Dim loRow = oData_oRows
' Reset the values
Dim lnColumn = 0
' If this is even
If lnCounter Mod 2 = 0 Then
' Initialize the row background color
oXlsFile.SetRowFormat(lnCounter + 2, lnXF, False)
End If
Next
oXlsFile.Save("r:\test.xlsx")
End Sub
End Module
The result is a file with alternating row colors.
Could it be that later in your code you are resetting the row formats?
Hi
Thanks, here is the reason why it is not working:
' For each column
For Each loObject In oColumn
' Initialization
lcName = loObject(1)
loTFlxFormatHeader = loObject(2)
loTFlxFormatData = loObject(4)
lnWidth = loObject(3)
' Initialization
lnCounter = lnCounter + 1
' Set the column header
oXlsFile.SetCellValue(1, lnCounter, lcName, oXlsFile.AddFormat(loTFlxFormatHeader))
' Set the column width
oXlsFile.SetColWidth(lnCounter, lnWidth)
' Set the column data format range
'oXlsFile.SetCellFormat(2, lnCounter, oData.nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
Next
' It is not possible to create an object directly as this is Friend, so we have to get it from Excel
loTFlxFormat = oXlsFile.GetCellVisibleFormatDef(2, 1)
loTFlxFormat.FillPattern.Pattern = FlexCel.Core.TFlxPatternStyle.Solid
loTFlxFormat.FillPattern.FgColor = FlexCel.Core.Colors.LightBlue
' Save it in memory so to avoid calling AddFormat() every time
lnXF = oXlsFile.AddFormat(loTFlxFormat)
' For each record
For lnCounter = 0 To oData.nCount - 1
loRow = oData.oRows(lnCounter)
' Reset the values
lnColumn = 0
' If this is even
If lnCounter Mod 2 = 0 Then
' Initialize the row background color
oXlsFile.SetRowFormat(lnCounter + 2, lnXF, False)
End If
Above, for each column, I define the format range. Once I commented the line, it worked. But, I need to format the column range. How can it be possible to have both?
Hi,
In Excel you can format cells, full rows and full columns, and that is the priority.
That means, if you format a cell yellow, a row green and a column blue, the cell will be yellow. If you format a row green and a column blue and there is no cell format, then the cell will be green.
But you aren't formatting full columns (calling SetColFormat). Instead you are formatting all the cells in the range with the call:
oXlsFile.SetCellFormat(2, lnCounter, oData.nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
Once those cells are formatted, the format in the row doesn't matter. You need to format the cells as you want when setting the format of the cells, and use SetColFormat for the empty cells. But if you are setting all the row formats, then the col format will be ignored anyway.
Really what you would need to do if you want this to be as fast as possible is to don't call SetCellFormat at all. Just set the formats when setting the cell values. You will need to calculate the correct cell format for every cell (odd cells are blue, for example), but this will be the faster way.
You can also make multiple passes (as you would in Excel): That is format all columns with some format, then format all the rows keeping the formats in the columns and finally format the cells themselves. But you are doing 3 passes over all the data, which is 3 times slower as it needs to be.
If you calculate the format for the cell and apply it at the moment you call SetCellValue(...) using the XF parameter in SetCellValue this code will go much faster.
Hi
Thanks for the information
With that approach, it means I have to initialize all cells of a row, even if it is empty, in order to get the background. Presently, my code skip empty values, thus not initializing a cell if it has no value, which saves a lot of time.
I will have to consider the 1, 2, 3 approach instead.
Hi
BTW, I am not sure this priority works. Can you confirm that to me again?
I just tried by moving SetRowFormat() after I have initialized the entire spreadsheet. And, only where the cells have not been initialized that the background color works. It means that where ever I have initialized a cell, the SetCellValue() format remains as is, even if I have SetRowFormat() at the very end of my code.
Skipping empty values is not saving a lot of time, because in the call:
oXlsFile.SetCellFormat(2, lnCounter, oData.nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
You are actually setting all the cells in the full range to be not empty, so they can have a format. What this call does under the hood is to loop over all the cells in the range and create null cells with the specified format.
So just setting those cellvalues to null when you set the data should be much faster than setting all values to null before starting, and then skipping the nulls on a second pass.
The thing is: In excel you can have only those formats Imentioned before: cell, row and column. If you don't have a column (setColFormat) or row (SetRowFormat) in a cell, the only way to format it is to create a null cell with formatting. So that's what xls.SetCellFormat(Range) does. It formats the existing cells, and creates null cells for the rest.
I would restructure the code to make a single pass, and set all the cells in the range. While it normally doesn't matter, if you have many cells, the extra pass will just slow the process.
Priority is Cell, row, column. If you set a row format, it will apply only to the empty cells in the row.
You can set resetRow to true when calling:
SetRowFormat(int row, int XF, bool resetRow);
But what SetRowFormat(row, XF, true) will do is to:
1)set the row format to XF
2)loop over all the cells in the range, and set the Cell formats to XF. While from a functional point of view you are "formatting the row", this is slower as you are setting the row format and then changing the cell formats to match.
Again, if you have much data, the fastest is to set all values and formats once with
xls.SetCellValue(row, col, value, XF);
All the other stuff is re setting the cell format under the hood, and it will be slower.
Ok, I thought by priority you meant that we could see in that order and the later would work. But, basically, by priority, I think you meant that once a cell is set, even if we set the format at the row level, this will be ignored as we already set something higher in the chain of priority.
I will refactor this and will let you know.
Indeed, this is the priority Excle uses in the xls/x files. You have to consider we are speaking at a very low level here: the way xls and xlsx work.
Xls and xlsx have the ability to set the row format for a row, the column format for a column, and the cell format for a cell. And the priorities it uses are as explained.
Of course, if you think about it at a higher level, you can see that the "logical" thing when you set a row format is to set the format for all the cells. This is what Excel does when you select a row and format it. And this is what FlexCel does if you call SetRowFormat(row, xf, true);
But if you look at it from what it is really happening, this is a very wasteful way to set formats. Under the hood, when you select a row and paint it blue in Excel (or in FlexCel if ResetRow = true), what Excel does is to set the row format to blue, and format all the existing cells in the row to blue. But this isn't efficient, as you are setting a lot of cell formats that you might set again later.
If efficiency is needed (which happens for big files) you need to think at a lower level, on what is actually happening in the file. In that level the fastest you can do is to just loop over the cells (including blank ones) and set the values and formats in a single call (Setting "Nothing" value for empty cells)
Ok, this works.
What I do, instead of adding a format for every cell, as I know ahead of processing all records what format applies to which column, I have applied a one time XF conversion in my collection. Then, when I scan the records, I simply assign XF to the data cell.
However, to support alternate row color, in my collection, I have added another format for the cell, thus one format with no color and one format for the alternate row color. Then, as you can see in the code, if I am on an even row number, I will use the format for that one instead.
Here is a short overview on the main code:
' For each record
For lnCounter = 0 To oData.nCount - 1
loRow = oData.oRows(lnCounter)
' Reset the values
lnColumn = 0
' For each column
For Each loObject In oColumn
' Initialization
lnXF = loObject(12)
' If we use alternate row color
If lAlternateRowColor Then
lnXFAlternate = loObject(13)
' If this is even
If lnCounter Mod 2 = 0 Then
lnXF = lnXFAlternate
End If
End If
' Initialization
lnColumn = lnColumn + 1
' Initialize lcValue here, if applicable
...
' If we are initializating directly from the data
If lcValue.Length = 0 Then
' Initialize the value in the cell
oXlsFile.SetCellValue(lnCounter + 2, lnColumn, loRow(lnColumn - 1), lnXF)
Else
' Initialize the value in the cell
oXlsFile.SetCellValue(lnCounter + 2, lnColumn, lcValue, lnXF)
End If
Next
Next
For every 10 minutes of processing, as I am now initializing all the cells, thus also considering the empty cells, it takes about 30 seconds more. Even if you say that it should be faster, based on my initial results of last week, I was able to confirm that skipping cell initialization was ending up to be faster as I do not have to send them to Flexcel.
So, I can process my reports in 9 minutes and 50 seconds instead of 9 minutes and 11 seconds.
...approx.
Skipping cell initialization should be faster, I am not saying the opposite. What I am saying is that you aren't skipping cell initialization, you are actually initializing twice.
When you call:
oXlsFile.SetCellFormat(2, lnCounter, oData.nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
internally what happens is that FlexCel runs a loop from row 2 to oData.ncount +1 and initializesevery cell to null. Then in your code, you don't initialize the null cells, but you will reinitialize everything that was null.
FlexCel code is pure C#, and so intializing with FlexCel shouldn't be different to initializing yourself.
Did you remove the line:
oXlsFile.SetCellFormat(2, lnCounter, oData.nCount + 1, lnCounter, oXlsFile.AddFormat(loTFlxFormatData))
From your code to run the tests? You should have 0 calls to SetCellFormat, if you are formatting with SetCellValue.