java 在 Apache POI 中按单元格名称设置新单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32637441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set new cell value by cell name in Apache POI
提问by Cosmin
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 和 Apache POI 的新手，但我完成了这项任务，我必须从命令行读取 Excel 文件的名称以及需要修改的列的名称和值。
So in the end the application will run like this: java test -f test.xls -i B2=10;B3=20;B4=30
所以最终应用程序会像这样运行： java test -f test.xls -i B2=10;B3=20;B4=30
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).
我创建了一个包含单元格名称及其值的地图，但我不知道如何使用此地图来按名称访问单元格（例如：B2）并设置新值（例如：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();
}
}
回答by rgettman
Once you have the entry, get the cell reference text, e.g. "B2", and the value.
获得条目后，获取单元格引用文本，例如"B2"和值。
Create a CellReferenceusing the text. Then you can obtain the 0-based row and column indexes that you can use to get the Rowand then the Cellobject, on which you can call setCellValue.
CellReference使用文本创建一个。然后您可以获得基于 0 的行和列索引，您可以使用这些索引来获取Row，然后Cell您可以在其上调用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());
回答by ergonaut
You can access a row and column using these methods. (eg. E101)
您可以使用这些方法访问行和列。(例如。E101)
sheet.getRow(100).getCell(5)
