Odd Behavior with HSSFSerializer
I produced a simple Excel document by querying my database and using the HSSFSerializer. It's a simple document; the spreadsheet just formats the data into the same grid layout you would see on a database GUI upon submitting the query. Anyway, I realized that no matter the numerical data that goes into the HSSFSerializer, Excel defaults to outputting them with the trailing 0's cut off. (22.90 becomes 22.9.) I downloaded Gnumeric, formatted numbers to show 2 decimal places, saved the document to Gnumeric XML, and opened it to see what XML was needed to format the decimal properly. (I want all my numbers rounded to two decimal places.) Here is the XML I currently use in my stylesheet: xsl:when test=/page/title='MOU Alert' gmr:StyleRegion startCol=5 startRow=3 endCol=5 xsl:attribute name=endRow xsl:value-of select=count(/page/content/sql:rowset/sql:row)+3 / /xsl:attribute gmr:Style HAlign=1 VAlign=2 WrapText=0 ShrinkToFit=0 Rotation=0 Shade=0 Indent=0 Locked=1 Hidden=0 Fore=0:0:0 Back=:: PatternColor=0:0:0 Format=0.00 gmr:Font Unit=10 Bold=0 Italic=0 Underline=0 StrikeThrough=0 Script=0Arial/gmr:Font /gmr:Style /gmr:StyleRegion /xsl:when If you look at gmr:Style's Format attribute, this is the input that is needed to produce the correct decimal places. This specific snippet of code should round all data in Column 6 (Gnumeric starts counting at 0, so Col=5 is the sixth column) starting at row 3 and ending with the last row of data. If you look at the count function under xsl:attribute name=endRow, this returns the value 4500 from my particular test data. This dynamically tells what row to stop applying the formatting to. There is no way to specify unbounded for endRow, so this count function is the only way I can get it to apply the formatting to every row for any instance of data queried. Now here's the odd behavior. This code works perfectly when I query data that is a few hundred rows long. If there is, for example, 500 rows being queried the count function correctly returns 503, which is the last row I want formatted. (My data outputs on rows 3 to 503 to make room for a heading.) But when I query data from around 700 or so upward (I haven't found an exact cutoff yet), it will never apply the formatting. (All the numbers will have trailing 0's cut off and not rounded to two decimal places.) This is extremely irritating, and this will need to work for thousands of rows. If I simply replace the count fuction with a number like 250, it works. (Rows 3-253 are formatted properly, and the few thousand remaining are left unformatted.) If I start putting in values like 2000, 3500, 11480, it won't apply formatting to ANY row. It gives me the cold shoulder.:-O I was wondering if anyone ran has run into a similar problem and knows a fix. If I was ambiguous anywhere, just let me know and I'll clarify. Thanks, Matt -- View this message in context: http://www.nabble.com/Odd-Behavior-with-HSSFSerializer-tp18115088p18115088.html Sent from the Cocoon - Users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Odd Behavior with HSSFSerializer
I had the same problem. I fixed it like this: !-- Style regions are built for each column for max 999 rows. Larger regions do not seem to work. That's why styles template is called more then once if number of rows to print exceedes 999. -- xsl:template name=styles xsl:param name=startRow/ xsl:param name=size/ xsl:variable name=endRow xsl:choose xsl:when test=($size - $startRow) gt; 999xsl:value-of select=$startRow + 998//xsl:when xsl:otherwisexsl:value-of select=$size//xsl:otherwise /xsl:choose /xsl:variable Styles StyleRegion ... xsl:attribute name=startRowxsl:value-of select=$startRow// xsl:attribute xsl:attribute name=endRowxsl:value-of select=$endRow// xsl:attribute Style ... ... /Style /StyleRegion ... /Styles xsl:if test=($size - $startRow) gt; 999 xsl:call-template name=styles xsl:with-param name=startRowxsl:value-of select=$startRow + 999//xsl:with-param xsl:with-param name=sizexsl:value-of select=$size// xsl:with-param /xsl:call-template /xsl:if /xsl:template The template is called for the first time with: xsl:call-template name=styles xsl:with-param name=startRow1/xsl:with-param xsl:with-param name=sizetotal-nr-of-elements/xsl:with-param /xsl:call-template Barbara On 25 Jun, 2008, at 4:21 pm, Matthew Monkan wrote: I produced a simple Excel document by querying my database and using the HSSFSerializer. It's a simple document; the spreadsheet just formats the data into the same grid layout you would see on a database GUI upon submitting the query. Anyway, I realized that no matter the numerical data that goes into the HSSFSerializer, Excel defaults to outputting them with the trailing 0's cut off. (22.90 becomes 22.9.) I downloaded Gnumeric, formatted numbers to show 2 decimal places, saved the document to Gnumeric XML, and opened it to see what XML was needed to format the decimal properly. (I want all my numbers rounded to two decimal places.) Here is the XML I currently use in my stylesheet: xsl:when test=/page/title='MOU Alert' gmr:StyleRegion startCol=5 startRow=3 endCol=5 xsl:attribute name=endRow xsl:value-of select=count(/page/content/sql:rowset/sql:row)+3 / /xsl:attribute gmr:Style HAlign=1 VAlign=2 WrapText=0 ShrinkToFit=0 Rotation=0 Shade=0 Indent=0 Locked=1 Hidden=0 Fore=0:0:0 Back=:: PatternColor=0:0:0 Format=0.00 gmr:Font Unit=10 Bold=0 Italic=0 Underline=0 StrikeThrough=0 Script=0Arial/gmr:Font /gmr:Style /gmr:StyleRegion /xsl:when If you look at gmr:Style's Format attribute, this is the input that is needed to produce the correct decimal places. This specific snippet of code should round all data in Column 6 (Gnumeric starts counting at 0, so Col=5 is the sixth column) starting at row 3 and ending with the last row of data. If you look at the count function under xsl:attribute name=endRow, this returns the value 4500 from my particular test data. This dynamically tells what row to stop applying the formatting to. There is no way to specify unbounded for endRow, so this count function is the only way I can get it to apply the formatting to every row for any instance of data queried. Now here's the odd behavior. This code works perfectly when I query data that is a few hundred rows long. If there is, for example, 500 rows being queried the count function correctly returns 503, which is the last row I want formatted. (My data outputs on rows 3 to 503 to make room for a heading.) But when I query data from around 700 or so upward (I haven't found an exact cutoff yet), it will never apply the formatting. (All the numbers will have trailing 0's cut off and not rounded to two decimal places.) This is extremely irritating, and this will need to work for thousands of rows. If I simply replace the count fuction with a number like 250, it works. (Rows 3-253 are formatted properly, and the few thousand remaining are left unformatted.) If I start putting in values like 2000, 3500, 11480, it won't apply formatting to ANY row. It gives me the cold shoulder.:-O I was wondering if anyone ran has run into a similar problem and knows a fix. If I was ambiguous anywhere, just let me know and I'll clarify. Thanks, Matt -- View this message in context: http://www.nabble.com/Odd-Behavior- with-HSSFSerializer-tp18115088p18115088.html Sent from the Cocoon - Users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED
Re: Odd Behavior with HSSFSerializer
://www.nabble.com/Odd-Behavior- with-HSSFSerializer-tp18115088p18115088.html Sent from the Cocoon - Users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Odd-Behavior-with-HSSFSerializer-tp18115088p18118539.html Sent from the Cocoon - Users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Odd Behavior with HSSFSerializer
row. It gives me the cold shoulder.:-O I was wondering if anyone ran has run into a similar problem and knows a fix. If I was ambiguous anywhere, just let me know and I'll clarify. Thanks, Matt -- View this message in context: http://www.nabble.com/Odd-Behavior- with-HSSFSerializer-tp18115088p18115088.html Sent from the Cocoon - Users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
