获取一个文件夹下的数据排版格式相同的Excel表格的数据

//获取文件夹下的所有Excel文件 
public List<File> getAllFilesUnderFolder() {

String path = "C:\\Users\\Administrator\\Desktop\\导入题库"; //文件夹路径
File folder = new File(path);
if (!folder.exists()) {

System.out.println(path + " not exists");

return null;

}

List<File> fileList = new LinkedList<File>();
File files[] = folder.listFiles();
for (int i = 0; i < files.length; i++) {

File file = files[i];

if (file.getName().contains(".xls") || file.getName().contains(".xlsx")) {

System.err.print("第 " + (i + 1) + " 个文件：");

System.err.println(file);

fileList.add(file);

}

}
System.err.println("-------------------------------------------------------------\n");

return fileList;

}


@Test
public void importAllFiles() throws IOException {

List<File> fileList = getAllFilesUnderFolder();
int page = 0;
int all = 0;
StringBuilder message = new StringBuilder();
for (File file : fileList) {

page++;

FileInputStream inputStream = new FileInputStream(file);

// 根据指定的文件输入流导入Excel从而产生Workbook对象

Workbook wb = null;

try {

wb = new XSSFWorkbook(inputStream);

} catch (Exception e) {

wb = new HSSFWorkbook(inputStream);

}

// 获取Excel文档中的第一个表单

Sheet sheet = wb.getSheetAt(0);

int sum = 0;

int judgeNum = 0;

int singleNum = 0;

int multiNum = 0;

message.append("--------------------------------------------------");

message.append("文件名称: " + file.getName() + "\n");

for (Row r : sheet) {

message.append("第 " + (r.getRowNum() + 1) + "行！\n");

// 如果当前行的行号（从0开始）未达到2（第三行）则从新循环

if (r.getRowNum() < 1) {

continue;

}

if (r.getCell(0) == null) {

break;

}

for (int i = 0; i < 13; i++) {

if (r.getCell(i) == null) {

r.createCell(i).setCellValue("");

}

}

// 创建实体类

ExamRole examRole = examRoleDao.findByName("管理员");

// 取出当前行第1个单元格数据，并封装在person实体的相关属性上

if (r.getCell(0).getRichStringCellValue().toString() != null) {

KnowledgeBase knowledgeBase = knowledgeBaseDao.findByName(r.getCell(1).getRichStringCellValue().toString());

if (knowledgeBase == null) {

knowledgeBase = new KnowledgeBase();

knowledgeBase.setName(r.getCell(1).getRichStringCellValue().toString());

knowledgeBase.setPos(1);

knowledgeBase.setRole(examRole);

knowledgeBaseDao.save(knowledgeBase);

}

String QuestionTypeName = r.getCell(3).getRichStringCellValue().toString();

QuestionType questionType = questionTypeDao.findByQuestionTypeName(QuestionTypeName);

Question question = new Question();

question.setKnowledgeBase(knowledgeBase);

question.setQuestionType(questionType);

question.setName(r.getCell(4).getRichStringCellValue().toString());

question.setStatus("可用");

if ("是非题".equals(questionType.getName())) {

question.setWeight(1);

} else if ("单选题".equals(questionType.getName())) {

question.setWeight(2);

} else if ("多选题".equals(questionType.getName())) {

question.setWeight(3);

}

questionDao.save(question);

if ("是非题".equals(questionType.getName())) {

QuestionOption questionOption1 = new QuestionOption();;

QuestionOption questionOption2 = new QuestionOption();;

if ("F".equalsIgnoreCase(r.getCell(5).getRichStringCellValue().toString())) {

questionOption1.setAnswer(false);

} else {

questionOption1.setAnswer(true);

}

questionOption1.setContext(r.getCell(6).getRichStringCellValue().toString());

if ("F".equalsIgnoreCase(r.getCell(7).getRichStringCellValue().toString())) {

questionOption2.setAnswer(false);

} else {

questionOption2.setAnswer(true);

}

questionOption2.setContext(r.getCell(8).getRichStringCellValue().toString());

if (questionOption1 != null) {

questionOption1.setQuestion(question);

questionOptionDao.save(questionOption1);

}

if (questionOption2 != null) {

questionOption2.setQuestion(question);

questionOptionDao.save(questionOption2);

judgeNum++;

}

} else if ("单选题".equals(questionType.getName()) || "多选题".equals(questionType.getName())) {

QuestionOption questionOption1 = new QuestionOption();;

QuestionOption questionOption2 = new QuestionOption();;

QuestionOption questionOption3 = new QuestionOption();;

QuestionOption questionOption4 = new QuestionOption();;

if ("F".equalsIgnoreCase(r.getCell(5).getRichStringCellValue().toString())) {

questionOption1.setAnswer(false);

switch (r.getCell(6).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption1.setContext(String.valueOf(r.getCell(6).getNumericCellValue()));

break;

default:

questionOption1.setContext(r.getCell(6).getRichStringCellValue().toString());

break;

}

} else if ("T".equalsIgnoreCase(r.getCell(5).getRichStringCellValue().toString())) {

questionOption1.setAnswer(true);

switch (r.getCell(6).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption1.setContext(String.valueOf(r.getCell(6).getNumericCellValue()));

break;

default:

questionOption1.setContext(r.getCell(6).getRichStringCellValue().toString());

break;

}

}

if ("F".equalsIgnoreCase(r.getCell(7).getRichStringCellValue().toString())) {

questionOption2.setAnswer(false);

switch (r.getCell(8).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption2.setContext(String.valueOf(r.getCell(8).getNumericCellValue()));

break;

default:

questionOption2.setContext(r.getCell(8).getRichStringCellValue().toString());

break;

}

} else if ("T".equalsIgnoreCase(r.getCell(7).getRichStringCellValue().toString())) {

questionOption2.setAnswer(true);

switch (r.getCell(8).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption2.setContext(String.valueOf(r.getCell(8).getNumericCellValue()));

break;

default:

questionOption2.setContext(r.getCell(8).getRichStringCellValue().toString());

break;

}

}

if ("F".equalsIgnoreCase(r.getCell(9).getRichStringCellValue().toString())) {

questionOption3.setAnswer(false);

switch (r.getCell(10).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption3.setContext(String.valueOf(r.getCell(10).getNumericCellValue()));

break;

default:

questionOption3.setContext(r.getCell(10).getRichStringCellValue().toString());

break;

}

} else if ("T".equalsIgnoreCase(r.getCell(9).getRichStringCellValue().toString())) {

questionOption3.setAnswer(true);

switch (r.getCell(10).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption3.setContext(String.valueOf(r.getCell(10).getNumericCellValue()));

break;

default:

questionOption3.setContext(r.getCell(10).getRichStringCellValue().toString());

break;

}

}

if ("F".equalsIgnoreCase(r.getCell(11).getRichStringCellValue().toString())) {

questionOption4.setAnswer(false);

switch (r.getCell(12).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption4.setContext(String.valueOf(r.getCell(12).getNumericCellValue()));

break;

default:

questionOption4.setContext(r.getCell(6).getRichStringCellValue().toString());

break;

}

} else if ("T".equalsIgnoreCase(r.getCell(11).getRichStringCellValue().toString())) {

questionOption4.setAnswer(true);

switch (r.getCell(12).getCellType()) {

case Cell.CELL_TYPE_NUMERIC:

questionOption4.setContext(String.valueOf(r.getCell(12).getNumericCellValue()));

break;

default:

questionOption4.setContext(r.getCell(12).getRichStringCellValue().toString());

break;

}

}

if (questionOption1 != null) {

questionOption1.setQuestion(question);

questionOptionDao.save(questionOption1);

if ("单选题".equals(questionType.getName())) {

singleNum++;

} else {

multiNum++;

}

}

if (questionOption2 != null) {

questionOption2.setQuestion(question);

questionOptionDao.save(questionOption2);

}

if (questionOption3 != null) {

questionOption3.setQuestion(question);

questionOptionDao.save(questionOption3);

}

if (questionOption4 != null) {

questionOption4.setQuestion(question);

questionOptionDao.save(questionOption4);

}

}

}

sum++;

}

message.append("第 " + page + " 个文件" + file.getName() + "，共 " + sum + " 题！其中，判断题共 " + judgeNum + " 题，单选题共 " + singleNum + " 题，多选题共 " + multiNum + "题！\n");

all += sum;

}
message.append("合计 " + all + "题!\n");
System.err.println(message);

}