Java实现Excel列随机取值至另一列的方法及内置函数咨询

Great questions! Let's break this down into two parts for clarity:

Absolutely, Excel has a straightforward way to do this using a combination of INDEX and RANDBETWEEN functions. Here's how it works:

Suppose your target values are in cells A2:A10 (skipping the header in A1). The formula would be:

=INDEX(A2:A10, RANDBETWEEN(1, COUNTA(A2:A10)))

Let's break down each part:

• COUNTA(A2:A10) counts the number of non-empty cells in the range, so this adapts automatically if you add or remove values.
• RANDBETWEEN(1, [count]) generates a random integer between 1 and the total number of values.
• INDEX(A2:A10, [random-number]) picks the value at the random position from the range.

Note: Every time you refresh the sheet (press F9), the function will generate a new random selection. If you want to lock the value after selection, copy the cell and paste it as values only.

To do this in Java, we'll use Apache POI—the most popular library for reading/writing Excel files. Here's a step-by-step implementation:

First, add Apache POI dependencies #

If you're using Maven, add these to your pom.xml:

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>
</dependencies>

Complete Java Code Example #

This code will read all non-empty values from a specified column, pick one randomly, and write it to a target column in the same sheet:

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class ExcelRandomValuePicker {
    public static void main(String[] args) {
        String excelFilePath = "your-file-path.xlsx";
        int sourceColumnIndex = 0; // Column A (0-based index)
        int targetColumnIndex = 1; // Column B (0-based index)
        int targetRowIndex = 1; // Row 2 (0-based index, skipping header row)

        try (Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFilePath))) {
            Sheet sheet = workbook.getSheetAt(0); // Get the first sheet
            List<String> values = new ArrayList<>();

            // Step 1: Read all non-empty values from the source column
            for (Row row : sheet) {
                Cell cell = row.getCell(sourceColumnIndex);
                if (cell != null && cell.getCellType() == CellType.STRING && !cell.getStringCellValue().trim().isEmpty()) {
                    values.add(cell.getStringCellValue().trim());
                }
            }

            // Handle empty source column case
            if (values.isEmpty()) {
                System.out.println("Source column has no non-empty values to pick from.");
                return;
            }

            // Step 2: Pick a random value
            Random random = new Random();
            String randomValue = values.get(random.nextInt(values.size()));

            // Step 3: Write the random value to the target cell
            Row targetRow = sheet.getRow(targetRowIndex);
            if (targetRow == null) {
                targetRow = sheet.createRow(targetRowIndex);
            }
            Cell targetCell = targetRow.createCell(targetColumnIndex);
            targetCell.setCellValue(randomValue);

            // Step 4: Save the modified Excel file
            try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                workbook.write(outputStream);
                System.out.println("Random value '" + randomValue + "' written successfully!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Key Notes: #

• Adjust excelFilePath, sourceColumnIndex, targetColumnIndex, and targetRowIndex to match your actual file and requirements.
• The code handles both .xls and .xlsx files using WorkbookFactory.
• We skip empty cells and whitespace-only values to avoid picking invalid entries.
• Added error handling for empty source columns and file operations.

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