在 Apache POI 中按单元格名称设置新单元格值 [英] Set new cell value by cell name in Apache POI

问题描述

我是 Java 和 Apache POI 的新手，但我完成了这项任务，我必须从命令行读取 Excel 文件的名称以及需要修改的列的名称和值.

I'm new to Java and Apache POI, but I got this task where I have to read from the command line the name of the Excel file and the name and values of the columns that needs to be modified.

所以最终应用程序会像这样运行:java test -f test.xls -i B2=10;B3=20;B4=30

So in the end the application will run like this: java test -f test.xls -i B2=10;B3=20;B4=30

我创建了一个包含单元格名称及其值的映射，但我不知道如何使用此映射来按名称访问单元格(例如:B2)并设置新值(例如: 10).

I have created a map that holds the cell name and their values, but I don't know how to use this map in order to access cells by their name (eg.: B2) and set the new value (eg.: 10).

到目前为止我的代码如下:

My code so far is bellow:

package test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;

public class ReadExcel {
    public static void main(String[] args) throws FileNotFoundException, IOException {
        // Will contain cell name / value pair for input cells and output cells
        Map<String, String> inputCellsMap = new HashMap<String, String>();
        Map<String, String> outputCellsMap = new HashMap<String, String>();

        // Open the Excel file
        FileInputStream file = new FileInputStream(new File(args[1]));

        // Get the current workbook
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get the first sheet of the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Get the input cells that need to be modified and
        // store their name and value in the inputCellsMap
        for (String element : args[3].split(";")) {
            inputCellsMap.put(element.split("=")[0], element.split("=")[1]);
        }

        // Loop through the cells that need to be modified and 
        // set the new value in the Excel document
        Iterator<Entry<String,String>> iterator = inputCellsMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String,String> entry = (Map.Entry<String,String>) iterator.next();

            // TODO
            // Get cells by name and set their new value

            //System.out.println("Key : " + entry.getKey() + " Value :" + entry.getValue());
        }           

        workbook.close();       
    }
}

推荐答案

获得条目后，获取单元格引用文本，例如"B2" 和值.

Once you have the entry, get the cell reference text, e.g. "B2", and the value.

创建一个CellReference 使用文本.然后，您可以获得基于 0 的行和列索引，您可以使用这些索引来获取 Row 和 Cell 对象，您可以在其上调用 setCellValue.

Create a CellReference using the text. Then you can obtain the 0-based row and column indexes that you can use to get the Row and then the Cell object, on which you can call setCellValue.

CellReference cr = new CellReference(entry.getKey());
int r = cr.getRow();
int c = cr.getCol();

Row row = sheet.getRow(r);
if (row == null)
    row = sheet.createRow(r);
Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
cell.setCellValue(entry.getValue());

这篇关于在 Apache POI 中按单元格名称设置新单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！