How to read column a1 from an Excel sheet and perf

问题:

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<Cell> cells = new ArrayList<Cell>();

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?

回答1:

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<String, Object> that maps variable names to values, e.g.

Map<String, Object> beans = new HashMap<String, Object>();
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?

回答2:

Instead of checking for equality, you could use contains api from String as below:

cell.getStringCellValue().contains(columnWanted)

Which means

"What is /@v1@/% of /@v2@/?".contains("v4") //No it doesnt
"What is /@v1@/% of /@v2@/?".contains("v2") //Yes it does