使用Groovy脚本实现反向循环写入数据至Excel文件

现有Groovy脚本可将数据写入Excel文件，需修改实现反向循环，让输出行顺序与输入相反。

原脚本 #

import java.io.BufferedReader;
import java.io.InputStreamReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

for (int i = 0; i < dataContext.getDataCount(); i++) {
InputStream is = dataContext.getStream(i);
Properties props = dataContext.getProperties(i);
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Data Sheet");
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
int rownum = 0;

while ((line = reader.readLine()) != null) {
  Row row = sheet.createRow(rownum);
  rownum++;
  int cellnum = 0;
  String[] fields = line.split(",");
  for (int j = 0; j < fields.length; j++) {
   row.createCell(cellnum).setCellValue(fields[j]);
   cellnum++;
  }

}

ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
is = new ByteArrayInputStream(baos.toByteArray());
dataContext.storeStream(is, props);
}

效果对比 #

• 实际输出效果：数据按读取顺序从上到下排列
• 期望输出效果：数据按读取顺序从下到上排列（最后一行输入行显示在Excel最顶部）

修改后的脚本 #

import java.io.BufferedReader;
import java.io.InputStreamReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

for (int i = 0; i < dataContext.getDataCount(); i++) {
    InputStream is = dataContext.getStream(i);
    Properties props = dataContext.getProperties(i);
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Data Sheet");
    BufferedReader reader = new BufferedReader(new InputStreamReader(is));
    
    // 先将所有行读取到列表中
    List<String> lines = new ArrayList<>();
    String line;
    while ((line = reader.readLine()) != null) {
        lines.add(line);
    }
    
    int rownum = 0;
    // 反向遍历列表，从最后一行开始写入
    for (int k = lines.size() - 1; k >= 0; k--) {
        Row row = sheet.createRow(rownum);
        rownum++;
        int cellnum = 0;
        String[] fields = lines.get(k).split(",");
        for (int j = 0; j < fields.length; j++) {
            row.createCell(cellnum).setCellValue(fields[j]);
            cellnum++;
        }
    }

    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);
    is = new ByteArrayInputStream(baos.toByteArray());
    dataContext.storeStream(is, props);
}

修改说明 #

1. 读取所有行到列表：先把输入流的所有行读取到ArrayList中，获取完整的行数据集合，为反向遍历做准备
2. 反向遍历写入：从列表的最后一个索引开始循环到第一个索引，同时按顺序创建Excel行，实现输入行的反向输出

内容的提问来源于stack exchange，提问作者user6927606