How to Make an Ugly Excel Document: Otherwise known as Unit Testing POIUtility 4.0 (Apache POI 4.0.1) compatible

I've been working on a revamp of Ben Nadel's excellent but aging POIUtility for the generation of Excel files.   I've been needing XLSX support so I updated the code to use Apache POI 4.0.1 and embedded Mark Mandel's JavaLoader and the needed packages for it to work standalone without java lib dependencies on the server.

Here's a summary of what I've done so far;

• Uses Apache POI 4.0.1 with JavaLoader
• Supports creation of both XLS and XLSX
• RowSpan support.  Either by itself or with colSpan to create a range (see ugly image below)
• poi:row update attribute when working with existing templates.
• refactored the CSSRule.cfc  into cfscript, 

TODO: Work on updating the POIUtility.cfc now that the groundwork is complete.

I did yank the coldfusion_mx_7 folder, figured it's about time for people to move ahead on that one.

Tested on CommandBox with ColdFusion 2018

Go to my repo or the code https://github.com/wiggick/POIUtility.cfc

Side by side results of XLS and XLST Generation
Color, fill pattern, and region test.  (note text colors are just random selection )

Crystal Report Tricks: Using special fields in formulas

When you want to use a special field in Crystal Reports in a formula, you can specify the special field  by using the special field name without spaces.  I admit I haven't tried them all, but in this case, the comments are multi-line and I wanted to grab just the first line.

split(ReportComments,chr(13))[1]

Oracle Tricks: Getting a range of numbers

Another method for getting a range of dates from Oracle PL/SQL

select last_day(to_date(to_char(months.month) || '/1/' ||
                        to_char(years.year),
                        'mm/dd/yyyy')) as range_EOM
  from (select rownum year from dual connect by level <= 2019) years,
       (select rownum month from dual connect by level <= 12) months
 where years.year >= 2000
   and months.month >= 1

Grabbing UTC Hour from Oracle

A simple sql script for obtaining gmt hour from Oracle

select sys_extract_utc(systimestamp) as UTC,
extract(hour from sys_extract_utc(systimestamp) ) as UTC_HOUR,
extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as timestamp) ) as  local_hour
from dual

ColdFusion POI:Row and updating

I was working with the POI custom tags from Ben Nadel and noticed that the tag was creating the rows rather than updating. Adding a get and check fixed the issue.

<!--- Try Getting the  row. --->
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.getRow( JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) ) )>

  <cfif ! StructKeyExists(VARIABLES, "Row")>
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.CreateRow(
   JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) )
   ) />
  </cfif>

KornShell: Replace spaces with underscores

A simple ksh that will replace a space with "_"

#!/bin/ksh
find /your/file/directory -type f -name "* *" | \
while read file; do \
newfile="$(echo "$file" | sed 's/ /_/g')"
mv "${file}" "${newfile}"
done

Generating a series of Previous Month End Dates for a given number of years in Oracle

A very short oracle query for generating a range of previous month end dates for a number of years

  with years as  
  (select extract(year from sysdate) - (level - 1) as year  
   from dual  
  connect by level <=  
    (select extract(year from sysdate) -  
        extract(year from date '2000-12-01')   --range of years sysdate to 2001 
     from dual)  
   order by year desc),  
 months as  
  (  
  select rownum as month_num,to_char(date '2000-12-01' + numtoyminterval(level,'month'),'MONTH') as month  
    from dual  
   connect by level <= 12  
 )  
 select last_day(to_date(to_char(years.year) || '-' ||   
 to_char(months.month_num) || '-1',  
         'yyyy-mm-dd')) as as_of_date  
  from years, months  
  where last_day(to_date(years.year || '-' || months.month, 'yyyy-mm')) < sysdate