Adding rows to an excel spreadsheet using apache poi

In a recent project for a client I was tasked with modifying an existing excel spreadsheet to add data from a query. Being familiar with Java and ColdFusion I assumed this would be a pretty trivial exercise. Read the existing file, get the sheet and then write the data, but I ran into an issue where adding rows using shiftRows didn’t make them writable and/or visible to apache poi. I realized I needed to literally add the rows and the columns to the excel spreadsheet to be able to change the values. Not a big deal code-wise and also really fast to complete, but frustrating to figure out.

currentCharterTemplate = 'existingWorkbook.xlsx';
currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentCharterTemplate);
excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

summarySheet = xssfWorkbook.getSheetAt(0);
totalColumns = 12;
rowsToAdd = query.recordCount;
//add enough rows/columns to the spreadsheet to handle the record count of the query and the sort fields
for (rows = 1; rows <= rowsToAdd; rows++) {
  summarySheet.createRow(rows);
  theCurrentRow = summarySheet.getRow(rows);
  for (columns = 0; columns <= totalColumns; columns++) {
    theCurrentRow.createCell(columns);
    theCurrentRow.getCell(columns);
  }
}

ColdFusion and Apache POI modifying an existing file

I was tasked with creating an excel spreadsheet that mimics a template from a vendor and after a couple hours of struggling I realized it was fruitless and it’d be easier to modify the existing file. The existing file had macros that were ran to validate certain things and all I was adding was names, date of birth, weight and gender.

The following is the gist of what I ended up doing. This is 100% using the poi built into ColdFusion and does not require extra jars or adding anything to the classpath.

<cfscript>
  currentTemplate = 'filename.xlsx';
  currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
  javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentTemplate);
  excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
  xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

  // get the first sheet index is 0 based in Java
  sheet1 = xssfWorkbook.getSheetAt(0);
  rowIterator = sheet1.iterator();
  while (rowIterator.hasNext()) {
    currentRow = rowIterator.next();

    // row 8 is where we want to start writing the names
    if (currentRow.getRowNum() > 7) {
      cellIterator = currentRow.iterator();

      while (cellIterator.hasNext()) {
        //1 - last name, 2 first name, 3 middle, 4 DOB, 5 Gender, 6 Weight
        currentCell = cellIterator.next();
        //when in doubt of methods dump out the whole java object
        //writeDump(currentCell); abort;
        currentCellNumber = currentCell.getColumnIndex();
        currentQueryRow = currentRow.getRowNum() - 7;
        // writing an empty row made the macro think I was adding a user
        if (len(trim(getTravelers['lastName'][currentQueryRow]))) {
          if (currentCellNumber == 1) {
            currentCell.setCellValue(getTravelers['lastName'][currentQueryRow]);
          }
          if (currentCellNumber == 2) {
            currentCell.setCellValue(getTravelers['firstName'][currentQueryRow]);
          }
          if (currentCellNumber == 3) {
            currentCell.setCellValue(getTravelers['middle'][currentQueryRow]);
          }
          if (currentCellNumber == 4) {
            currentCell.setCellValue(dateFormat(getTravelers['dob'][currentQueryRow], 'mm/dd/yyyy'));
          }
          if (currentCellNumber == 5) {
            currentCell.setCellValue(left(getTravelers['gender'][currentQueryRow], 1));
          }
          if (currentCellNumber == 6) {
            currentCell.setCellValue(getTravelers['weight'][currentQueryRow]);
          }
        }
      }
    }
  }

  // need to force formulas to be recalculated once the file is written
  formulaEvaluator = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator');
  formulaEvaluator.evaluateAllFormulaCells(xssfWorkbook);
  // and once the workbook is opened
  xssfWorkbook.setForceFormulaRecalculation(true);

  // close the file input stream
  excelFile.close();
  // create a new excel file
  newFileName = 'roster.xlsx';
  newFile = createObject('java', 'java.io.File').init(currentFilePath & newFileName);
  newExcelFile = createObject('java', 'java.io.FileOutputStream').init(newFile);
  xssfWorkbook.write(newExcelFile);
  newExcelFile.close();
</cfscript>

Multiple insert statement without counter

I often have a need to write an INSERT statement that adds multiple rows to a database. Obviously you can run any ┬ánumber of inserts separately, but that’s innefficient, so I loop over a list with a counter to know when to end. Recently I learned that the counter is unnecessary and you can create a fake SELECT statement to avoid needing a counter

<cfquery>
INSERT INTO Attendees (Schedule_ID, Attendee_ID, Meeting_ID, User_ID)
<cfloop list="#variables[eventlocation]#" index="i">
  SELECT #Schedule_ID#, #i#, #Meeting_ID#, 1
  UNION ALL
</cfloop>
<!--- returns no rows --->
SELECT 0, 0, 0, 0
WHERE 1 = 0
</cfquery>

For the sake of brevity I’ve avoided omitted the cfqueryparam’s that should always be used in queries.

Converting roman numerals to numbers using ColdFusion

I recently worked on a project that required translating roman numerals to the numerical counterpart, this needed to work for anything from 1-2000. I wrote my own using TDD and eventually came up with this

component {
  public function romanToDecimal(romanNumber) {
    var newNumber = 0;
    var previousNumber = 0;
    var romanToNumberMapping = {M:1000, D:500, C:100, L:50, X:10, V:5, I:1};
    var romanNumeral = ucase(romanNumber);
    for (var oneChar = romanNumeral.length() - 1; oneChar >= 0; oneChar--) {
      var oneLetter = romanNumeral.charAt(oneChar);
      if (previousNumber > romanToNumberMapping[oneLetter]) {
        newNumber-=romanToNumberMapping[oneLetter];
      } else {
        newNumber+=romanToNumberMapping[oneLetter];
      }
      previousNumber = romanToNumberMapping[oneLetter];
    }
    return newNumber;
  }
}

It’s simple enough that you should be able to add each roman numeral and it’s number into the mapping and this should theoretically work for any roman numeral, but I only tested up to 2000. This assumes that you have entered a valid roman numeral that can be translated to 1-2000.

The tests are written in MXUnit, so it’s a lot of repetition. I’ll be following up with a groovy example using spock that should have a much cleaner looking test. I’ve attached the test file and additionally have submitted this to cflib for approval.

You can download the test here if you’re so inclined.

Batch inserting records to a database

I was writing some code for a client that processed a csv file and inserted each row into a database, when there were a couple hundred rows the processing time was very quick, but when there are 8000 rows this can take quite a while even on a very fast database, so I wrote up some code to batch insert the records (which I’ve done too many times).

I was using MSSQL, so the max parameters I could send at one time was 2100 (210 rows x 10 columns), but your mileage may vary. I’d set rowsPerInsert as high as possible and decrease as necessary. You should be able to easily modify the pseudo code below.

<cfscript>
  totalrows = 5000; // this would come from a structCount or something similar
  rowsPerInsert = 210;
  //this creates a struct with alternating values of true/false for test purposes
  stRows = {};
  for (i=1; i<=totalRows; i++) {
    stRows[i] = i MOD 2 ? true : false;
  }
</cfscript>
<cfoutput>
<cfloop from="0" to="#totalRows - 1#" step="#rowsPerInsert#" index="totalRow">
  <br><br>INSERT #totalRow#<br>
  <cfloop from="1" to="#rowsPerInsert#" index="indRow">
    <cfset rowNumber = totalRow + indRow>
    <cfif rowNumber LTE totalRows>
      #rowNumber# #stRows[rowNumber]# <!--- SELECT goes here --->
      <cfif indRow NEQ rowsPerInsert AND rowNumber NEQ totalRows> UNION ALL</cfif>
      <br>
    </cfif>
  </cfloop>
</cfloop>
</cfoutput>

This is mostly for me to steal from myself once I need this again, but hopefully you find it useful as well.

ColdFusion Query of Queries strips whitespace

I was tasked with tracking down a defect at work this week. We are creating a URL based on the results of a query and somehow a string of ” ABC DEF 12345″ was being turned into “ABC DEF 12345” (DB2 FTW!) The webservice we were using was unchanged and was still returning the whitespace, so I knew the issue was something on my end. We have a few pieces of code that strip out duplicate spaces and extra line breaks, so I removed those, but the issue still persisted. After working through about a dozen solutions I called over Scott Busche and we repeated everything I already gone through, but when we stumbled upon a Query of Queries, Scott suggested that could be the culprit.

The query of queries was used to filter a result set down to the past two years only (the webservice we’re using just ignores end date, which is awesome in itself), so we googled and quickly found that QoQ trims leading and trailing whitespace from columns and has been doing this since ColdFusion 6, so I don’t think this is considered a bug, just something to be aware of.

How to solve the issue?
We googled and found this stack overflow question, but that solution only worked for Railo and then we found a post on Ben Nadel’s blog (obviously) that pointed out a Java function that would do what we needed, but when removing more than one row in the query you would eventually run into an index out of bounds error the way he had it structured, but that’s easy enough to fix.

<cfloop from="#myQuery.recordCount#" to="1" step="-1" index="currentRow">
  <cfif myQuery.runDate[currentRow] LT left(getStartDateTime(),8)>
    <cfset myQuery.removeRows(currentRow - 1, 1)>
  </cfif>
</cfloop>

After implementing this fix our query was working correctly, but we still needed to add URLEncodedFormat to our URL parameters, so the whitespace wouldn’t be stripped (and because it should have been done in the first place).

FWIW Railo 4.2 does NOT strip out whitespace. Test case below

<cfset q = queryNew("columnA", "varchar", [["   AAA"]])>
<cfquery name="test" dbtype="query">
SELECT columnA
FROM q
</cfquery>

<cfdump eval=test>
<cfdump eval=len(test.columnA)>
<cfdump var="#replace(test.columnA,' ','a space','all')#">

Returns
number 6
string a spacea spacea spaceAAA

You can also run this code on trycf.com

UPDATE: Adam Cameron pinged me on twitter (and Ray added in the comments) that this is fixed in ColdFusion 11.

Connecting MS SQL database to Railo datasource

Tonight was the 2nd time I’ve struggled getting Microsoft SQL Server set up to communicate with Railo (and really anything at all), so I thought I’d write up some quick tips on what I had to do. Once you think you have everything set up there are 2 steps that are left.

Go to Microsoft SQL Server 20XX > Configuration Tools > SQL Server Configuration Manager

sqlconfig

From there open up TCP/IP and make sure Listen All is set to No
tcpipproperties

After that go to the IP Addresses tab and make sure Active and Enabled are yes (enabled defaults to no) and make sure you’re using the IP and correct TCP Port when configuring your datasource.

Thanks to this MSDN post for getting me on the right track.

cf.Objective() Proposal – Getting started with Responsive Web Design

Interested in learning about responsive web design? Head on over to the cf.Objective() Trello board and vote for my talk on Responsive Web Design and any others that you would like to attend.

Here’s a brief description of what my talk will be about

Target Audience: Anyone interested in creating one website for all devices

Assumed Knowledge: Basic CSS and HTML experience

Objective: Introduce responsive web design and why it’s important

Why I’m Qualified: Part of a team that created a mobile website for a large insurance company.

What the audience will learn:
What is responsive Web Design
When is responsive Web Design appropriate
Pro/Cons of using a framework
Fixed vs Fluid layout
Concept of columns and grids

Anything else you’d like to learn? Feel free to comment here and/or on the Trello board and if my talk is selected I’ll try to work a few suggestions in as well.

Using ColdFusion to parse a list with empty values

I was parsing through a csv file today and having issues with empty values. The data I’m receiving is in a format the 3rd party cannot update, so my data looked something like this

1,,abc,32,,gef

Dumping the list provides the expected list, but looping through the list it skips over values

<cfoutput>
  <cfloop list="#list1#" index="i">
    #i#<br>
  </cfloop>
</cfoutput>

produces

1
abc
32
gef

After googling this for a bit I gave up and asked my brother. He told me to use the ‘includeemptyfields’ attribute in listtoarray(). I knew I could turn a list into an array, but I wasn’t aware of the third attribute until today, the default is obviously false, so blank values were never included.

The code below produces the desired output. Notice I am now looping through an array instead of a list.

<cfset newlist = listToArray(list1,',',true) />
<cfoutput>
  <cfloop array="#newlist#" index="i">
    #i#<br>
  </cfloop>
</cfoutput>

1

abc
32

gef

get US Bank Holidays UDF

I was looking for an existing UDF on cflib.org for current bank holidays but the only one I could find was for German holidays only. With this function you can pass in a year otherwise it will assume the current year is to be used.

The following holidays are calculated using this UDF.

Holiday Official Date
New Year’s Day January 1
Independence Day July 4
Veterans Day November 11
Christmas Day December 25
Inauguration Day January 20th (Year after election year – multiple of 4)
MLK’s Birthday Third Monday in January
George Washington’s Birthday Third Monday in February
Memorial Day Last Monday in May
Labor Day First Monday in September
Columbus Day Second Monday in October
Thanksgiving Day Fourth Thursday in November

 

 

<cffunction name=”getUSBankHolidays” access=”public” output=”false” returntype=”struct” hint=”general bank holidays for US”>
<cfargument name=”iYear” default=”#Year(now())#” />

<cfset var currentYear = arguments.iYear />

<cfset var strResult =
{ NewYears = createDate(currentYear,1,1),
Independence = createDate(currentYear,7,4),
Veterans = createDate(currentYear,11,11),
Christmas = createDate(currentYear,12,25)
} />

<cfif NOT (currentYear – 1) MOD 4>
<cfset strResult.Inauguration = createDate(currentYear,1,20) />
</cfif>

<cfset strResult.MLKBirthday = createDate(currentYear,1,GetNthOccOfDayInMonth(3,2,1,currentYear)) />
<cfset strResult.WashingtonsBirthday = createDate(currentYear,2,GetNthOccOfDayInMonth(3,2,2,currentYear)) />
<cfset strResult.MemorialDay = createDate(currentYear,5,(DaysInMonth(createDate(2012,5,1))) – (DayOfWeek(createDate(2012,5,DaysInMonth(createDate(2012,5,1)))) – 2)) />
<cfset strResult.LaborDay = createDate(currentYear,9,GetNthOccOfDayInMonth(1,2,9,currentYear)) />
<cfset strResult.ColumbusDay = createDate(currentYear,10,GetNthOccOfDayInMonth(2,2,10,currentYear)) />
<cfset strResult.Thanksgiving = createDate(currentYear,11,GetNthOccOfDayInMonth(4,6,11,currentYear)) />

<cfreturn strResult />
</cffunction>

This function is long overdue for me as monitors that run on week days run on Holidays and result in a lot of false positive ‘down’ notifications. If the array returned is empty then the scheduled task should run. If the array has records then it’s a holiday

 

This UDF requires the getNthOccOfDayInMonth function from cflib.org