Scriptom Groovy格式化Excel示例

Question

我正在寻找一些Groovy为Excel文档执行基本格式化命令的示例。我也想知道在哪里可以找到这些命令的存储库。

您将如何：

插入一行

将单元格格式设置为短日期、短时间等。

加粗整列或整行

Answer 1

这是怎么回事( POI 3.9)。

假设您在/tmp/test.xls中有一个输入XLS文件，这应该会进行您要求的修改，然后将工作簿写出一个新文件/tmp/test2.xls。我已经添加了注释，所以希望它应该是有意义的:-)

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