Open Existing Excel File in Groovy

Question

How do I open an existing excel file in Groovy so I can start manipulating it?

http://stackoverflow.com/questions/521274/edit-existing-excel-files-using-jxl-api-apache-poi — tim_yates, Jul 08 '13 at 22:54
That really doesn't show me how to open an existing file in Groovy. :/ — Marco Polo, Jul 09 '13 at 00:38
You can browse an [example](http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java) or get [Apache POI](http://poi.apache.org/spreadsheet/examples.html) to see all the examples in Java. It will be easy enough to convert them to Groovy. — dmahapatro, Jul 09 '13 at 02:28

score 2 · Answer 1 · answered Jul 09 '13 at 08:05

Here's a full example of using POI from Groovy:

@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.HSSFWorkbook

// Make a workbook from scratch
new HSSFWorkbook().with { workbook ->
  def styles = [ LIGHT_BLUE, LIGHT_GREEN, LIGHT_ORANGE ].collect { color ->
    createCellStyle().with { style ->
      fillForegroundColor = color.index
      fillPattern = SOLID_FOREGROUND
      style
    }
  }
  createSheet( 'Output' ).with { sheet ->
    (0..4).each { rownum ->
      createRow( rownum ).with { row ->
        (0..4).each { colnum ->
          createCell( colnum ).with { cell ->
            setCellValue( "[$colnum,$rownum]" )
            cellStyle = styles[ ( ( rownum * 5 ) + colnum ) % styles.size() ]
          }
        }
      }
    }
    new File( '/tmp/test.xls' ).withOutputStream { os ->
      write( os )
    }
  }
}

// Open the spreadsheet, change cell 3, 3 to 'WOO' and save it back out to a new file
new File( '/tmp/test.xls' ).withInputStream { ins ->
  new HSSFWorkbook( ins ).with { workbook ->
    getSheetAt( 0 ).with { sheet ->
      getRow( 2 ).getCell( 2 ).setCellValue( 'WOO' )
    }
    new File( '/tmp/test2.xls' ).withOutputStream { os ->
      write( os )
    }
  }
}

Open Existing Excel File in Groovy

1 Answers1