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', '').init(currentFilePath & currentCharterTemplate);
excelFile = createObject('java', '').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++) {
  theCurrentRow = summarySheet.getRow(rows);
  for (columns = 0; columns <= totalColumns; columns++) {

Using hibernate default schema as a variable

Given you have set up a default_schema in your hibernate configuration

      <property name="hibernate.default_schema">mySchema</property>

Rather than writing a native SQL query like SELECT x FROM mySchema.tableName you can write your query as SELECT x FROM {h-schema}tableName rather than trying to do a find/replace when your schema name inevitably changes. Note that the . after the schema name is not only not required it will not work if it is added.

Hibernate returning BigDecimal instead of Long

I was trying to run a query to get a list of IDs from a database table and then compare that list to individual Ids on the page. The logic was pretty straightforward. Get IDs via a query and then check if other IDs are found in that list of IDs. The query itself was working fine (I’ve reduced the complexity of the query a lot), but my contains statement wasn’t finding any matches (even though they were mostly all matches). It turns out that Hibernate was returning a List of BigDecimal rather than a list of Long and my comparison was a Long value. What I needed to do was force the column to return the datatype I wanted.

addScalar(columnName, dataType)

public List<Long> retrieveOrgTypeWithoutParent() {
 Session session = getHibernateTemplate().getSessionFactory().openSession();

 List<Long> orgTypeIds = session.createSQLQuery(
  "SELECT typeId FROM org_type_cd").addScalar("typeId", StandardBasicTypes.LONG).list();

 return orgTypeIds;