ExcelConverter.java

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

class RowRecord {
	public int satCount = 0;
	public int unsatCount = 0;
	public int unknownCount = 0;
	public double priority = 0;
	public int maxSymCount = 0;
	public int minSymCount = 0;
	public int sumOfFiles = 0;
	public int sumOfVars = 0;
	public double solveTime = 0;
	public double cosineSimilarity = 0;
	public boolean isCorrect = false;
}

class SheetRecord {
	public String question = "";
	public int likelyCount = 0;
	public int validCount = 0;
	public int correctCount = 0;
	public int rankOfFirstCorrect = -1;
	public int fileCount = 0;
	public int varCount = 0;
	public double time = 0;
	
	@Override
	public String toString() {
		return String.format("%s,%d,%d,%d,%d,%d,%d,%.2f", question, likelyCount, validCount, correctCount, rankOfFirstCorrect, fileCount, varCount, time);
	}
}

public class TestJPF {
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, FileNotFoundException, IOException{
		List<String> questions = new ArrayList<>();
		BufferedReader in = new BufferedReader(new FileReader("questions2.txt"));
		for(String line = in.readLine();line!=null;line = in.readLine()){
			questions.add(line);
		}
		in.close();
		
//		for(String str : questions){
//			if(str.contains("$")){
//				String[] strs = str.split("\\$");
//				System.out.println("& " +strs[1] +" & "+"\\verb|" + strs[0] +"| \\\\");
//			}
//		}
//		System.exit(-1);
		
		Workbook wb = WorkbookFactory.create(new FileInputStream("keyword-io.xlsx"));
		int numberOfSheet = wb.getNumberOfSheets();
		for(int i=2;i<numberOfSheet;i++){
			Sheet sheet = wb.getSheetAt(i);
			int no = (int)sheet.getRow(1).getCell(0).getNumericCellValue();
			String question = questions.get(no-1);
			String[] items = question.split("\\$");
			List<RowRecord> rowlist = new ArrayList<>();
			for(int j=2;j<=sheet.getLastRowNum();j++){
				Row row = sheet.getRow(j);
				if(row.getCell(1)==null){
					break;
				}
				RowRecord rr = new RowRecord(); 
				if(row.getCell(0) != null && row.getCell(0).getStringCellValue().equals("Y")){
					rr.isCorrect = true;
				}
				rr.sumOfFiles = (int) row.getCell(3).getNumericCellValue();
				rr.satCount = (int) row.getCell(4).getNumericCellValue();
				rr.unsatCount = (int) row.getCell(5).getNumericCellValue();
				rr.unknownCount = (int) row.getCell(6).getNumericCellValue();
				rr.maxSymCount = (int) row.getCell(7).getNumericCellValue();
				rr.minSymCount = (int) row.getCell(8).getNumericCellValue();
				rr.sumOfVars = (int) row.getCell(9).getNumericCellValue();
				rr.priority = row.getCell(10).getNumericCellValue();
				rr.solveTime = row.getCell(11).getNumericCellValue();
//				rr.cosineSimilarity = row.getCell(12).getNumericCellValue();
				rowlist.add(rr);
			}
			SheetRecord sr = new SheetRecord();
			sr.question = items[1];
			rowlist.sort(new Comparator<RowRecord>() {

				@Override
				public int compare(RowRecord o1, RowRecord o2) {
					int priComp = Double.compare(o1.priority, o2.priority);
					if(priComp != 0){
						return -priComp;
					} else {
						return -Double.compare(o1.cosineSimilarity, o2.cosineSimilarity);
					}
				}
				
			});
			for(int j=0;j<rowlist.size();j++) {
				RowRecord rr = rowlist.get(j);
				if(rr.priority > 0){
					sr.likelyCount ++;
				}
				if(rr.priority >= 1) {
					sr.validCount ++;
				}
				if(rr.isCorrect){
					sr.correctCount ++;
					if(sr.rankOfFirstCorrect == -1){
						sr.rankOfFirstCorrect = j+1;
					}
				}
				sr.fileCount += rr.sumOfFiles;
				sr.varCount += rr.sumOfVars;
				sr.time += rr.solveTime;
			}
			System.out.println(sr);
//			System.out.printf("%s, %.0f,%.0f,%d,%.2f,%.0f,%.0f\n",items[1],numberOfResult,numberOfValid,ft,sumOfTime,sumOfFiles,sumOfVars);
		}
		wb.close();
		
//		System.out.println("=============");
		
//		wb = WorkbookFactory.create(new FileInputStream("data8.xlsx"));
//		numberOfSheet = wb.getNumberOfSheets();
//		for(int i=1;i<numberOfSheet;i++){
//			Sheet sheet = wb.getSheetAt(i);
//			Row row = sheet.getRow(1);
//			double time = Double.parseDouble(
//					row.getCell(1).getStringCellValue().replace(" seconds", ""));
//			int no = (int)sheet.getRow(1).getCell(0).getNumericCellValue();
//			String question = questions.get(no-1);
//			String[] items = question.split("\\$");
//			System.out.printf("%s, %.2f\n",items[1],time);
//		}
//		wb.close();
	}
}