Scriptom Groovy格式化Excel示例 [英] Scriptom Groovy formating Excel Examples

问题描述

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

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.

您将如何：

插入一行

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

Format a cell to Short Date, Time, etc.

加粗整列或行

推荐答案

这是如何（与POI 3.9）。

How's this (with POI 3.9).

假设在 /tmp/test.xls 中有一个输入XLS文件，这应该进行所要求的修改，然后将工作簿写入新文件 /tmp/test2.xls 。我已经添加了评论，所以希望它有道理： - ）

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

这篇关于Scriptom Groovy格式化Excel示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！