Scriptom Groovy formating Excel Examples

Question

I am looking for some examples of Groovy doing basic formatting commands for an Excel document. I would also like to know where I can find a repository of these commands too.

How would you:

Insert a row

Format a cell to Short Date, Time, etc.

Bold an entire column or row

Answer 1

How's this (with POI 3.9).

Assuming you have an input XLS file in /tmp/test.xls, this should make the modifications you asked for, and then write the workbook out t a new file /tmp/test2.xls. I've added comments, so hopefully it should make sense :-)

@Grab( 'org.apache.poi:poi:3.9' )
import static org.apache.poi.ss.usermodel.CellStyle.*
import static org.apache.poi.ss.usermodel.IndexedColors.*
import org.apache.poi.hssf.usermodel.*

// Open the spreadsheet
new File( '/tmp/test.xls' ).withInputStream { ins ->
    new HSSFWorkbook( ins ).with { workbook ->
        // Select the first sheet
        getSheetAt( 0 ).with { sheet ->

          // Insert a row at row 2 (zero indexed)
          shiftRows( 1, sheet.lastRowNum, 1 )

          // Add a value to this row in cell 1
          getRow( 1 ).with { row ->
            createCell( 0 ).with { cell ->
              cell.setCellValue( '12:32' )
            }
          }

          // Set the cell format to Time
          // First we need to declare a style
          def timeStyle = workbook.createCellStyle().with { style ->
              dataFormat = HSSFDataFormat.getBuiltinFormat( 'h:mm:ss AM/PM' )
              style
          }
          // Then apply it to our cell
          getRow( 1 ).with { row ->
              getCell( 0 ).with { cell ->
                  cell.cellStyle = timeStyle
              }
          }

          // Make row 1 bold
          // First declare a style
          def boldStyle = workbook.createCellStyle().with { style ->
              style.font = workbook.createFont().with { f ->
                  f.boldweight = HSSFFont.BOLDWEIGHT_BOLD
                  f
              }
              style
          }
          // Then apply it to the row (I can only get this to work doing
          // it to each cell in turn, setting the rowStyle seems to do nothing
          getRow( 0 ).with { row ->
            (0..10).each {
              getCell( it )?.cellStyle = boldStyle
            }
          }
        }

        // Write the modified workbook out to another xls file
        new File( '/tmp/test2.xls' ).withOutputStream { os ->
            write( os )
        }
    }
}