Hi
Can someone provides a small example on how to handle rotation definition to a cell? Your documentation says we have to pass a byte. I was not able to find any documentation on the proper way to do this. Obviously, it is not just the degree assignation as this does not work. I would assume that TFlxFormatHeader.Rotation=90 would have worked. But, this byte issue is complicating things.
Thanks
Hi,
If you press F1 in "Rotation" it should bring this help:
/// <summary>
/// Text Rotation in degrees. <br/>
/// 0 - 90 is up, <br/>
/// 91 - 180 is down, <br/>
/// 255 is vertical.
/// </summary>
This is the way Excel handles it internally, and FlexCel is just passing the value. You can't rotate text to the 2nd and 3rd quadrants (negative x), so a byte is enough to handle all possibilities. There is also an special value: 255 which means vertical and is similar to pressing the "Vertical" button in the Excel cell properties dialog.
As always, remember that you can find all of this stuff with APIMate, just set the rotation in Excel, save the file and open it with APIMate. It will show the code to get that rotation with FlexCel.
Hi
Well, lets make it simple. In Excel, I have set it up to -90. So, if I set this has is in Flexcel, it gives "Arithmetic operation resulted in an overflow.". If I tried 270, it gives "Arithmetic operation resulted in an overflow.". So, how am I suppose to have the support for -90 in Flexcel?
The previous message also did not detail anything about the byte syntax that would need to be used if I would want to use it.
The help shown in your message, I already saw it. But, that is useless for -90.
What is APIMate? Is that a tool from your company?
Hi,
-90 is 180
-89 is 179
and so on
I've updated the help to make it more clear.
About APIMate, it is a tool that comes with FlexCel, it is installed in start menu->TMS FlexCel->Tools (or you can just search for apimate in the start menu).
You use it this way: Create a file in Excel, save it and open it with APIMate. It will tell you the code you need to create that file in FlexCel, You can choose between delphi an c++ builder in flexcel for vcl and c# or vb.net in flexcel for .net. I've just made it myself here and I get:
//Set the cell values
fmt := xls.GetCellVisibleFormatDef(1, 1);
fmt.Rotation := 180;
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
While APIMate won't cover 100% of flexcel funcyionality, it oes cover a lot, and I use it myself whenever I need to know "How to" do something. For example, if you want to know how to set an autofilter, just set it in Excel and use apmate.
Hi
This works. But, I have to handle it like this:
' Initialization
lnDegree = loRow("Degree")
' If this is negative
If lnDegree < 0 Then
lnDegree = 270 - (180 + lnDegree)
End If
loTFlxFormatHeader.Rotation = lnDegree
So, basically, if I understood correctly, if the user enters a negative value, which represents exactly what they would put in Excel, I would have to apply this formula so it would match your utility.
You have to understand that users are using a framework data entry form to enter values in the database for the cell properties. They will enter is exactly as if they would be in Excel. So, in Excel, for that one, it is -90. However, to your utility, -90 is not accepted and we have to apply a formula.
I just do not understand why this is not a direct assignation with the same value as if we would be in Excel.
Thanks for the information on APIMate. That would be useful.
Hi,
You are right that it isn't intuitive, and probably if I made the choice today I would use a direct angle instead of this.
But hat happens is that FlexCel API is very low level and designed very close to the xls file format specification. Inside an xls file, the angle is stored as a byte codified in the way explained. So, we are just exposing this byte in the API to the user.
The reason for being so low level was mostly performance and also to avoid unnecessary conversions. In this case, the property setter for the rotation would have to do the code you wrote:
' Initialization
lnDegree = loRow("Degree")
' If this is negative
If lnDegree < 0 Then
lnDegree = 270 - (180 + lnDegree)
End If
lnDegree = loRow("Degree")
' If this is negative
If lnDegree < 0 Then
lnDegree = 270 - (180 + lnDegree)
End If
when you set the rotation in order to store it in a byte inside the xls file, and do the inverse when reading from an xls file to convert from that byte to an angle that looks like the one you see in Excel.
And in many cases those 2 conversions would be unnecessary, because you were just reading for example the rotation in one cell and copying it to another. Take in account also that FlexCel API was originally created to support Reports (which ironically aren't yet available in FlexCel 6 for VCL, but this will probably change next week with FlexCel 6.6). Reports do a lot of copying of cells and format everywhere, and converting the original byte to an angle and then the angle back to a byte for every cell copied would just slow things down (you might be copying literally millions of cells in a report)
But well, this is the justification on why it was done this way when it was designed, about 10 years ago. I was very strict at that time in preserving the "low level" of the API, and performance and "do as less work as possible" would always win over usability at that time. (computers were also slower then, so it made more sense too).
You can always code a higher level api over a lower level one (so you can make a helper RotationInDegrees property which does conversion forward and back), but you can't code a low level api over a high level one. So I always wanted the guts to be available.
As said, if I were to design it today, I would probably use an angle as parameter, because the performance drop won't matter in today's machines (and probably not even in 10 year ago machines). But today more important than the extra simplicity is the backward compatibility: We can't just change rotation because it would break older code. We could add a "RotationInDegrees" property, but it would just make the api larger, and that's not something we want.
In any case, I still favor low level approaches in many other situations. While I've relaxed it for trivial conversions like this, for any conversion that takes some effort, we will just make available what goes in the xls/x file. One example might be the units for row heights and column widths. The units you set are direclty what is written in the file, not the fake units Excle shows, with those pixels that aren't even remotely real pixels)
Hi
Yes, this is what my framework does. It contains a Report class which is a high level class to yours and allows the user to enter the degree as if he would be in Excel. Then, the high level class simply converts it.
However, instead of applying that formula, this byte thing is still a mystery. You mentioned that if I would work with a byte, I wouldn't have to apply that conversion. Could you provide an example in VB.NET on how to define this byte and pass it to the Rotation property?
I would ratter much like to have something like that then to have this mathematical conversion.
Also, yes, you are right. Any little overhead will add extra CPU time. Recently, I adjusted the way the report were constructed as the old developer did a lot of unnecessary calls to Flexcel. That dropped the time from 40%.
In regards to that, presently, I generate a 40 MB report. That is a .xlsx format. This is a binary format. My SQL select command takes about 0.8 seconds to gather thousands of records. However, it takes about 75 seconds to build the report. Is the upcoming version providing a faster processing on the cell initialization, which I believe is what takes time?
About the byte thing, what I was just saying is that in an xls file (the only files that existed when we designed the TFlxFormat structure), the rotation is stored as a byte in the file. So FlexCel just reads the bytes from the xls file and stores them into memory, without further processing.
As said, in this particular case the advantage isn't big, but as a general rule FlexCel tries to just give access to the raw bytes in the xls file. The xls file is actually a dump of the memory structures that Excel has in memory, so it is a very efficient way to store the data.
The advantage of not converting can be seen in this case:
1) We read the rotation from the xls file. Say it is -90, so we will read 180.
2)If the api "converts" the value, when you call GetRotation it will convert this 180 we just read to -90.
3)If now you want to copy the format to other cell, now you will call SetRoation(-90), and it will be converted back to 180 which is the number stored in the xls file.
Without conversions, it is 180 all the way. And it also has the advantage of supporting "vertical text", which is 255. While if we converted to angles, we would have to have other property to specify that text is vertical, since vertical is different from 90 or -90.
Now, this is about xls, which is a low level file format itself, and we are a low level api on top of it. But xlsx is a different story: It isn't a low level file format like xls which is just a serialization of the objects in Excel itself. Xlsx isn't a binary format like xls, it is a zip file which contains xml files inside. So at its core, xlsx is a very verbose text file compressed.
You can see this easily by renaming an xlsx file as .zip, and opening it with a zip reader. You'll see the xml files inside. This of course makes xlsx a much slower file format than xls, since we need to
1)Convert the binary structures in memory to xml
2)Compress all the data with zip compression.
Same for reading an xlsx file, we need to parse xml instead of just reading array of bytes as we do with xls. But xlsx has the advantage of supporting more than 65536 rows, and also being smaller because the file is already zipped.
If you consider that an xlsx file is already compressed, then 40 mb isn't very little, it is quite a huge file. You can try unzipping the xlsx file and looking at the size of the xml files inside, but I suspect they will be big.
And I also imagine that mysql takes more than 0.8 seconds in getting the data, and you are counting that time as FlexCel time. The thing is, there are 2 stages in an SQL select: Query the data (which I think is what takes 0.8 secs) and then fetching the data from the db. The time spent fetching the data is spent every time you do a xls.SetCellValue(row, col, readvaluefromdb); Remember fetching can be slow specially if you are accessing the db though a network.
So I would ask what is the time that it takes mysql to select and fetch all the data. That is, if you select and then save the data to a text file, is it still 0.8 secs or does it take longer?
About making FlexCel faster, as said FlexCel is quite optimized already for this kind of stuff, and it works at a very low level, so I wouldn't expect much improvements on that side. One thing that can slow down things is if you use SetCellFromString instead of SetCellValue. SetCellValue should be much faster. If you are already using SetCellValue, I am not sure if much more can be done. Writing a compressed 40mb file does take time.
Hi
Not really, my framework handles all the metrics and the SQL portion is 0.8 second, than about 75 seconds are required for the Flexcel part. I was able to gain a few seconds by using SetCellValue() everywhere. On condition, it was either SetCellValue() or SetCellFromString(). Now, I use only the one you suggested. Are you saying that SetCellFromString() is deprecated?
As far as saving the data directly into a text file, I will have to do some tests.
We upgraded to bypass the 65k limit. It has proven to work very well.
About the speed, yes, the SQL is 0.8, but the "FlexCel part" includes the time to actually fetch the values from the SQL server, unless you fetch all the records in advance (and you shouldn't fetch all the records in advance, because it would consume a lot of memory without need)
Everytime you do
xls.SetCellValue(row, col, GetValueFromDb())
you are also timing the time needed to fetch the values from the database. This is why I asked for the time to write all the data to disk, because it includes the time to fetch all records.
SetCellValue enters what you pass to it directly into the cell.
So if you do:
xls.SetCellValue(1,1,7);
this will enter the number 7 in A1.
If you do:
xls.SetCellValue(1,1,"7");
it will enter the string "7" into the file.
On the other side, SetCellFromString(1,1,"7")
will enter the number 7, not the string "7" into the cell.
and SetCellFromString("1/1/2000") will enter the date 1/1/2000.
SetCellFromString is doing much more work than SetCellValue, it needs to see if the string can be a number, a date, a boolean, a formula, etc, and if it is convert it and call SetCellValue with the converted value.
But if you are reading the values from a database, you normally already have numbers, strings dates, etc depending in the column datatype, so there is no need to convert them to strings, call setcellformstring which will convert them back to numbers, dates, etc. Just cell SetCellValue with the date, number or whatever the database sends.
Hi
The data is in memory. Flexcel does not talk to SQL Server directly. I initialize all cells into a For/Next for each column inside a For/Next for each row. From a dataset, then, I set the value by using Flexcel.
Or, maybe I missed something in what you are trying to say.
After another test I just did, just using SetCellValue() is not good enough. I am loosing the format I have applied to several ranges such as for currencies.
For example, in my code I have something like this:
' Based on the type of field
Select Case loField.Type
' Character, memo, NVarChar, VarChar, Varchar(MAX), NVarchar(MAX)
Case "C", "M", "O", "V", "X", "Y"
' Integer
Case "I"
' Numeric
Case "N"
' If this is a dollar amount field
If loField.Dollar Then
lcFormatGenerated = "$#,##0.00;$#,##0.00"
Else
lcFormatGenerated = "#,##0.00;#,##0.00"
End If
' Date, Datetime
Case "D", "T"
lcFormatGenerated = "yyyy/mm/dd hh:mm:ss"
' Boolean
Case "L"
' Binary
Case "E"
' Big integer
Case "B"
' Else
Case Else
End Select
And, then below, I need to verify if I force the data to be sent as a string or not:
' If we display as string
If llDisplayAsString Then
' Initialize the value in the cell
oXlsFile.SetCellValue(lnCounter + 2, lnColumn, lcValue)
Else
' Initialize the value in the cell
oXlsFile.SetCellFromString(lnCounter + 2, lnColumn, lcValue)
End If
This makes sure that the currency values, for the columns showing amounts, will be set ok by preserving the format I have defined. In the latest test, when I used only SetCellValue(), it ignored my formatting, put everything left aligned and got rid of the dollar sign.
Still not sure about the reference when you mention "if you are reading the values from a database". Yes, I do. But, this goes in a dataset and then I set all cells one by one.
Hi
Ok, I understand what you are saying.
I got confused by an old developer who converted the data to a string for 100% of the cell initialization. So, I have adjusted that, simplified all this and now the code only uses SetCellValue() which is saving 20% of CPU time.