pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
ExcelUtil
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
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.ss.usermodel.CellType;
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;
import com.fs.model.system.ExamOptions;
import com.fs.model.system.ExamRecord;
import com.fs.model.system.Question;
public class ExcelUtil {
//读操作
public static List<Question> getData(File file) {
List<Question> exams = new ArrayList<>();
// 获得工作簿
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(file);
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 获得工作表个数
int sheetCount = workbook.getNumberOfSheets();
// 遍历工作表
for (int i = 0; i < sheetCount; i++) {
Sheet sheet = workbook.getSheetAt(i);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数,先获得一行,在得到该行列数
Row tmp = sheet.getRow(0);
if (tmp == null) {
continue;
}
//int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 1; row < rows; row++) {
Row r = sheet.getRow(row);
Question question = new Question();
List<String> options = new ArrayList<>();
int col = 0;
while(true) {
String temp = "";
try {
r.getCell(col).setCellType(CellType.STRING);
temp = r.getCell(col).getStringCellValue();
} catch (NullPointerException e) {
temp = "";
}
ExamOptions option = new ExamOptions();
switch (col) {
case 0:
question.setqName(temp);
break;
case 1:
question.setqScore(Integer.parseInt(temp));
break;
case 2:
question.setAnswer(temp);
break;
default:
if (!"".equals(temp)) {
options.add(temp);
break;
} else {
break;
}
}
if ("".equals(temp)) {
break;
} else {
col++;
}
}
question.setOptions(options);
exams.add(question);
}
}
return exams;
}
//写操作
public static String saveToServer(List<ExamRecord> records){
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作表
HSSFSheet sheet = workbook.createSheet("sheet1");
HSSFRow firstRow = sheet.createRow(0);
firstRow.createCell(0).setCellValue("序号");
firstRow.createCell(1).setCellValue("试题名称");
firstRow.createCell(2).setCellValue("用户名");
firstRow.createCell(3).setCellValue("开始时间");
firstRow.createCell(4).setCellValue("考试用时");
firstRow.createCell(5).setCellValue("试题总分");
firstRow.createCell(6).setCellValue("用户得分");
firstRow.createCell(7).setCellValue("考试状态");
for (int row = 1; row <= records.size(); row++) {
HSSFRow rows = sheet.createRow(row);
rows.createCell(0).setCellValue(row);
rows.createCell(1).setCellValue(records.get(row-1).getExamType());
rows.createCell(2).setCellValue(records.get(row-1).getUserName());
rows.createCell(3).setCellValue(records.get(row-1).getStartTime());
if (records.get(row-1).getExamTime() != null) {
rows.createCell(4).setCellValue(records.get(row-1).getExamTime());
}
if (records.get(row-1).getTotalScore() != null) {
rows.createCell(5).setCellValue(records.get(row-1).getTotalScore());
}
if (records.get(row-1).getUserScore() != null) {
rows.createCell(6).setCellValue(records.get(row-1).getUserScore());
}
rows.createCell(7).setCellValue(records.get(row-1).getExamState());
}
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = new Date();
String nowDate = format.format(date);
String url = "C:/Users/Administrator/Desktop/server_exam/record" + nowDate + ".xls";
File xlsFile = new File(url);
FileOutputStream xlsStream;
try {
xlsStream = new FileOutputStream(xlsFile);
workbook.write(xlsStream);
} catch (IOException e) {
e.printStackTrace();
}
return url;
}
}