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

Is POI out of the question instead of Scriptom? – tim_yates Jul 10 '13 at 14:58 — tim_yates, Jul 10 '13 at 14:58
Not if you can do it with the POI 3.9 – Marco Polo Jul 10 '13 at 15:30 — Marco Polo, Jul 10 '13 at 15:30

score 2 · Answer 1 · answered Jul 10 '13 at 16:05

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 )
        }
    }
}

Nicely done. I suggest you add it to your blog as well, as a use case of POI in Groovy. :) — dmahapatro, Jul 10 '13 at 16:44
@dmahapatro good idea :-D the blog's been feeling neglected of late ;-) — tim_yates, Jul 10 '13 at 16:58
I dig [into it](http://blog.bloidonia.com/) every now and then to see what you are cooking. ;) — dmahapatro, Jul 10 '13 at 17:09
AWESOME! Can you tell me how to insert an entire row at the very top of the spreed sheet? This is the last piece to my puzzle. — Marco Polo, Jul 10 '13 at 18:54

Scriptom Groovy formating Excel Examples

1 Answers1

Linked