java中如何判a等于1,如何从一个Excel工作表读取列A1和执行字符串匹配在Java程序中定义变量...

I need to read one fixed column of an Excel sheet and from that column I need to find out the variables and load them into a Java program.

For example I need to read column a1 which contains the following data: "What is /@v1@/% of /@v2@/?"

/@v1@/ and /@v2@/ are the variables. My Java program needs to detect these variables and insert random values into them. I have done the following:

import java.io.FileInputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

public class ACGS {

public static void main(String[] args) throws Exception {

//test file is located in your project path

FileInputStream fileIn = new FileInputStream("C://users/admin/Desktop/Content.xls");

//read file

POIFSFileSystem fs = new POIFSFileSystem(fileIn);

HSSFWorkbook filename = new HSSFWorkbook(fs);

//open sheet 0 which is first sheet of your worksheet

HSSFSheet sheet = filename.getSheetAt(0);

//we will search for column index containing string "Your Column Name" in the row 0 (which is first row of a worksheet

String columnWanted = "v4";

Integer columnNo = null;

//output all not null values to the list

List cells = new ArrayList();

Row firstRow = sheet.getRow(0);

for(Cell cell:firstRow){

if (cell.getStringCellValue().equals(columnWanted)){

columnNo = cell.getColumnIndex();

}

}

if (columnNo != null){

for (Row row : sheet) {

Cell c = row.getCell(columnNo);

if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {

// Nothing in the cell in this row, skip it

} else {

cells.add(c);

}

}

}else{

System.out.println("could not find column " + columnWanted + " in first row of " + fileIn.toString());

}

}

}

Could you tell me how can I proceed further?

解决方案

It sounds like you can use an Excel template processing solution. The two I'm aware of are jXLS and JETT (disclaimer: I wrote JETT and I maintain it).

The libraries are similar, in that they both rely on Apache Commons JEXL to provide expression support in the template spreadsheet.

In your template, you currently have placeholders such as /@v1@/ that you need to replace with the actual variable value. In either framework, you can use ${ and } to signify an expression that should be replaced with a value. This would be the cell value in your template spreadsheet.

What is ${v1}% of ${v2}?

In your Java code, you create a Map that maps variable names to values, e.g.

Map beans = new HashMap();

beans.put("v1", 50);

beans.put("v2", 16);

The libraries use this Map to provide values to the expressions to be evaluated.

Then, you call the API entry point to create another spreadsheet that has the expressions replaced with values. In JETT, this would be something like:

ExcelTransformer transformer = new ExcelTransformer();

transformer.transform(inputTemplateFilename, outputFilename, beans);

In jXLS, it would be very similar:

XLSTransformer transformer = new XLSTransformer();

transformer.transformXLS(inputTemplateFilename, beans, outputFilename);

The output in either case would be, for the example cell above:

What is 50% of 16?